MS-SQL Source Management

Owned by Michael Litner
Last updated: Jul 15, 2024
8 min read

General

A source host is a server that hosts the source databases. A test data environment includes access definition to one source database and a GI. This section describes how to create and manage a Source Host and a Test Data Environment.

A Golden Image (GI) refers to a pre-configured template of a database environment. This template encapsulates a snapshot of the database including its schema, data, configurations, and associated applications. It serves as a master copy from which multiple instances of the database can be rapidly deployed, ensuring consistency, reliability, and quick scalability across different virtual environments.

Supported types of Golden Images for MSSQL database

Each type of Golden Image for MSSQL databases serves a specific set of needs, from real-time replication to disaster recovery and cloud-based management. The choice of GI should align with the operational objectives and the specific environment, whether it’s on-premises or cloud-based like AWS RDS.

1. Live Golden Image (Live GI)

A Live GI for SQL Server is a real-time, operational replica of a production database. It includes all current data, configurations, and running state of the database. Suitable for immediate replication needs, creating test environments that mirror the production state

2. Native SQL Server Backup

This type of Golden Image is created using SQL Server’s native backup functionality. It consists of full, differential, or transaction log backups, capturing the database at a specific point in time. Ideal for point-in-time recovery, it enables the restoration of the database to a specific historical state.

3. AWS RDS MSSQL Golden Image

Specifically designed for SQL Server databases running on Amazon RDS, this Golden Image type incorporates the functionalities of SQL Server with optimizations for the AWS cloud infrastructure.

 

Storage Protocol configuration

Storage option

This parameter determines the type of storage protocol used for the database environment. The available options are iSCSI (Internet Small Computer System Interface) and NFS (Network File System).

Capacity (GB)

This parameter specifies the storage capacity allocated to the MSSQL Server database environment, measured in gigabytes (GB). The capacity should be chosen based on the size of your database and expected growth. Ensure enough space not only for the current database size but also for future data, backups, logs, and additional overhead.

User and Password

These optional parameters are part of the security credentials required for accessing the storage.
The username utilized for authentication to the storage system and the corresponding password for the provided username.

To open the Source work area:

  1. On the navigation bar, click Source.

The Source Hosts window appears displaying all the source hosts that have been added to the system.

You can quickly locate a source host by typing its letters on the Search bar. The list updates promptly.

To add a source host, see https://accelario.atlassian.net/wiki/spaces/DVPD/pages/2176980307/21%3AMS-SQL+Initial+Setup#To-add-a-source-host

To modify a source host:

  1. Select the source host.

  2. Click Modify source host.

  3. Modify the source host as required.

  1. To save your changes, click Modify.

To see source host information:

  1. Select the source host.

  2. Click Source host info.

The Info window appears.

  1. To return to the main Source Management work area, click Close.

To remove a source host:

  1. Select the source host.

  2. Click Remove source host.

3. Click Remove to confirm the deletion or Cancel to exit without deleting the source host.

After the source host is removed, the following message appears: Source Host has been removed.

To create a test data environment from the VDB Management window:

  1. Open the VDB Management window.

2. In the VDB Management window, select a Test Data Environment.

Live GI

To create a Live GI:

  1. Click Create LIVE GI.

2. Enter a Name.

  1. Click Create.

The VDB Management window displays the GI that was created.

To modify a Live GI:

  1. Modify the Golden Image Parameters as required.

  1. Click Next to go to the next Modify Test Data Environment window.

  2. Modify the Source DB Parameters as required.

  1. To save your changes, click Modify.

SQL Native Server Backup

To create a SQL Native Server Backup:

  1. Click Create Native GI.

2. Enter a Name.

3. Click Create.

The VDB Management window displays the GI that was created.

To modify a native SQL Server Backup:

  1. Modify the Golden Image Parameters as required.

2. Click Next to go to the next Modify Test Data Environment window.

3. Modify the Source DB Parameters as required.

  1. To save your changes, click Modify.

RDS AWS SQL

To create or modify RDS AWS SQL GI:

One time configuration of the AWS environment

  1. Go to S3 service
    Create S3 bucket with name accelario_rds_bucket.

  2. Go to IAM service.

  3. Click the Roles from left menu and then click the Create role.

    • Under Select type of trusted entity, select the AWS service.

    • Under Choose a use case (that will use this role), select the RDS 

    • In Use cases for other AWS services choose RDS - Add Role to Database

    • Click the Next: Permissions.

    • Click the Next: Tags.

    • Click the Next: Review.

    • Enter the Role name.

    • Click the Create role. The Role is created successfully. You will be navigated to create role page with newly created role.

  4. In the role list, click on the newly created role. Now, let’s add an inline policy to the role. This policy grants access to the S3 bucket.

    • Under Permissions tab, click the Create inline policy.

    • Click the JSON tab

    • Copy the below policy and set ${s3_bucket_name} to the name of your bucket (e.g., accelario_rds_bucket). Note: The policy in the JSON can be changed as per your requirement.

{     "Version": "2012-10-17",     "Statement": [         {             "Effect": "Allow",             "Action": [                 "s3:ListBucket"             ],             "Resource": [                 "arn:aws:s3:::${accelario_rds_bucket}"             ]         },         {             "Effect": "Allow",             "Action": [                 "s3:PutObject",                 "s3:GetObject",                 "s3:DeleteObject",                 "s3:PutObjectAcl"             ],             "Resource": [                 "arn:aws:s3:::${accelario_rds_bucket}/*"             ]         }     ] } 

 

  1. Click the Review Policy.

  2. Enter the Policy name.

  3. Click the Create policy.

  4. In the role summary, copy the Instance Profile ARNs.

e.g. arn:aws:iam::680471277400:role/Accelario_RDS_S3

  1. Assign created IAM role to RDS instance

    • Go to your RDS instance

    • Scroll down to Manage IAM Roles

    • Click “Add role” button and choose the role you created

  2. Create a user with permissions RDS_full_access, S3_full_access.
    Make sure, the user has access key and secret key

  3. On the source server, create an empty directory, that will be used to store backups copied from S3

Configuration of a new GI

  1. Create GI.

Create or modify Test data environment.

 

Provide AWS parameters, credentials

Provide AWS DB parameters:

 

 

Test Data Environment

To create a test data environment:

To edit a test data environment:

  1. Select the test data environment.

  2. Click Modify Test Data environment.

The Modify Test Data Environment window appears. Modify the test data environment name if necessary.

  1. Click Next to go to the next Modify Test Data Environment window.

To see information about a test data environment:

  1. Select the test data environment.

  2. Click Test Data Environment Info.

The Info window appears.

  1. To return to the main Source Management work area, click Close.

To remove a test data environment:

  1. Select the test data environment.

  2. Click Remove Test Data Environment.

3. Click Remove to confirm the deletion or Cancel to exit with deleting the test data environment.