/
MS-SQL Source Management

MS-SQL Source Management

Owned by Michael Litner
Last updated: May 14, 2025
11 min read

General

A source host is a server that hosts the source databases. A test data environment(TDE) includes definition of Golden Image (GI) to one source database. This section describes how to create and manage a Test Data Environment.

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

For GI’s of types “Native SQL Server Backup“ and “AWS RDS MSSQL“ the source host acts as an intermediate server that hosts an intermediate database. For simplicity it is still called “source”.

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.

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.

It requires an access to a folder where database backups are constantly added.

Live Golden Image (Live GI)

A Live GI for SQL Server is a real-time, operational replica of a 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. It requires that the live database is running on a virtual volume provided by Accelario Virtualization.

Pay attention that the updates/refresh of this replica should be done by the customer. Accelario Virtualization is not responsible for that.

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.

Azure SQL managed instance

Specifically designed for SQL Server databases running on Azure as SQL managed instances. This Golden Image type incorporates the functionalities of SQL Server with optimizations for the Azure cloud infrastructure.

Pay attention that it is NOT “Azure managed SQL Server database”

Storage Protocol configuration

Storage option

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

Capacity (GB)

Applicable for iSCSI only.

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.

Sources(TDE) management

  1. On the navigation bar, click Sources.
    The Source Hosts view is displayed, listing all source hosts that have been added to the system

image-20250429-070952.png

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 MS-SQL Initial Setup Walkthrough | To add a source host

Add a source host

To add a source host, see Install source agent

The source host maybe Windows only.

In case FILESTREAM data exists on the customer database - configure the MS-SQL instance as described here.

Modify, see details or delete a source host

To modify, see details or delete a source host, see Manage source agent

Create a test data environment(TDE)

  1. On the navigation bar, click Sources.

The Source Management window appears.

image-20250429-071242.png

  1. Select the required agent

  2. Click the (+) sign in the right panel

  3. Select DB type.

    image-20250429-071338.png

  4. Enter a Name and an optional description.

    image-20250429-071502.png

  5. Now , choose the GI type

    image-20250429-071552.png

SQL Native Server Backup

Create an SQL Native Server Backup

  1. Enter the TDE details

    image-20250429-081102.png

  • Golden Image Type - Native Server Backup

  • Source Host - choose the source host that was installed earlier

  • Storage type - NFS or ISCSI

  • Capacity - for iSCSI storage type enter total capacity in GB.

  • User and password - optional parameters

  • Snapshot policy - enter retention time in days of the snapshots

  1. The application runs an automatic discovery of all running MSSQL instances and presents them in a list.

  2. Enter the intermediate DB parameters (it is called “Source DB” here).

    image-20250429-085345.png

     

    Instance name - a running MSSQL instance
    Backup folder type - choose where are the backups of MSSQL database located
    Network Path - UNC path to the backup directory, in format of \\server\share\directory1\directory2
    Server Path - path to the backup directory that is located on the intermediate (source) server, where the source agent is installed
    Username - OS user that has permissions to the backup folder, in format of domain\username or .\username
    Password - password of the above user
    Copy-only backup - select if the backups are of type “copy-only”

  3. Enter source database name. This is the name of the real MSSQL database that the backups are taken from.

    image-20250429-082342.png

Enter the database name manually or let the automatic scan to analyze the backup folder and extract all database names that appear there

  1. Click Create
    The VDB Management window displays the GI that was created.

 

Modify an SQL Native Server Backup

  1. From the Sources screen, click the change (pencil) icon of TDE

  2. If needed - update snapshot retention policy

    image-20250429-084820.png

  3. If needed - update intermediate database parameters (called Source DB here)

    image-20250429-085054.png

  4. To save your changes, click Modify.

Live GI

Create a LIVE GI

  1. Enter the TDE details:

    image-20250429-071732.png

  • Golden Image Type - Live GI

  • Source Host - choose the source host that was installed earlier

  • Storage type - NFS or ISCSI

  • Capacity - for iSCSI storage type enter total capacity in GB.

  • User and password - optional parameters

  • Snapshot policy

    • Activate snapshots - select if you want automatic snapshots of the GI to be created

    • Repeat every - choose snapshot frequency

    • Start from - when to start creating snapshots

    • Snapshot retention period - how many days to keep the snapshots, they will be deleted automatically afterwards (if no dependent objects exist)

  1. The application runs an automatic discovery of all running MSSQL instances and presents them in a list.

  2. Enter the intermediate DB parameters (it is called “Source DB” here).

    image-20250429-072255.png

    Instance name - a running MSSQL instance
    Database name - enter a name for a new database that will be created and will act as an intermediate database. The database should not exist on the MSSQL instance that was chosen above

  3. Click Create.

    The Source Management window displays the test data environment for the source host that has been created.

    image-20250429-073139.png

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

    image-20250429-073324.png

  5. Click Create GI.

    image-20250429-073440.png
    image-20250429-073542.png

    Change Description if needed, click Create.
    The creation process is immediate, since only empty database is created in the intermediate MSSQL instance.

  6. The VDB Management window displays the GI that was created.

    image-20250429-073700.png

  7. Once the database is filled with required data , proceed to activate the GI. Click the Activate icon net to the GI.

    image-20250429-073943.png

  8. When the activation is completed, a first snapshot is created and you can proceed with VDB creation.

Modify a Live GI

  1. Click the change (pencil) icon of TDE

  2. If needed - update Snapshot Policy

    image-20250429-080324.png

  3. If needed - update intermediate database name (it is called Source DB here)

    image-20250429-080521.png

Pay attention that changing MSSQL Instance is not allowed.
Press “Test Connection” to verify that connection to the intermediate database works.

  1. To save your changes, click Modify.

RDS AWS MSSQL

Create or modify RDS AWS MSSQL GI

  1. Prepare the environment

  2. Enter the TDE details:

    image-20250429-111110.png

  3. Enter AWS parameters

AWS Credentials

The AWS authentication must be done via access key

  • Access Key ID - This is a unique identifier provided by AWS, for accessing all AWS services. It's used for authentication. Obtain it from the IAM settings in your AWS account.

  • Secret Access Key - A secret key that pairs with your Access Key ID for request authentication and authorization. This key should be generated when creating a new Access Key ID and kept confidential.

AWS Backup Details

  • Backup Directory Name - a directory on the RDS MSSQL instance. e.g.: Backups

  • Compress Backup (Y/N) - Always “Yes”

AWS S3 Bucket Parameters

  • Name - the S3 bucket name that will be used to copy of MSSQL backups between RDS and the intermediate server. How to create it
    E.g. : awss3demoe-bucket

  • Local Mount Path - The designated path for mounting the S3 bucket on the intermediate server. This allows to access the backups that are stored on the S3 bucket. The path should reference an existing, empty directory.
    E.g. : c:\s3backups

  1. Enter the connection details to the RDS MSSQL database:

    Database name - the RDS MSSQL database name
    User - MSSQL user to the RDS database
    Password - password for the above user
    Endpoint - hostname of the RDS database. Can be found in the RDS console, Connectivity & security tab, Endpoint field
    Port - port of the RDS database. Can be found in the same place as the endpoint above
    RDS instance name - the RDS MSSQL name

  2. Enter intermediate database details (called here Source DB)

    image-20250429-113427.png

Instance name - a running MSSQL instance
Database name - enter a name for a new database that will be created and will act as an intermediate database. The database should not exist on the MSSQL instance that was chosen above
VDB user - name of user(MSSQL login) that will be created on each VDB. It is recommended that the login does not exist on the target MSSQL instances
VDB password - password for the above user

  1. Click Create
    The VDB Management window displays the GI that was created.

    image-20250429-113901.png

 

 

Azure SQL managed instance

Create or modify Azure SQL managed instance

  1. Prepare the environment

  2. Enter the TDE details:

    image-20250430-115456.png

     

  3. Enter Azure parameters:

    image-20250430-115604.png
Azure credentials
Azure backup

  • Maximum transfer block size - internal size of a block while backing up. Recommended - 4MB. More details here and here.

  • Compress backup - whether to compress the backups.

  1. Enter the connection details to the Azure SQL managed instance:

    image-20250430-123937.png

Database name - the database name in the Azure SQL managed instance
User - SQL Server login to the above database
Password - password for the above user
Endpoint - host of the Azure SQL managed instance. You can use either private or public one. Goto the Azure console, SQL managed instances, select the instance, Settings, Connection string : choose between VNet-local endpoint for private or public endpoint for public
Port - port of the Azure SQL managed instance (default 3342 for public or 1433 for private)
Test Connection - test connection to the database under Azure SQL managed instance

  1. Enter intermediate database details (called here Source DB)

    image-20250429-113427.png

Instance name - a running MSSQL instance
Database name - enter a name for a new database that will be created and will act as an intermediate database. The database should not exist on the MSSQL instance that was chosen above
VDB user - name of user(MSSQL login) that will be created on each VDB. It is recommended that the login does not exist on the target MSSQL instances
VDB password - password for the above user

  1. Click Create
    The VDB Management window displays the GI that was created.

Test Data Environment (TDE)

See information about a test data environment

  1. Select the test data environment.

  2. Click Test Data Environment Info.

The Info window appears:

image-20250429-114435.png

The info window shows the next details:

  • IP-address - IP address of the intermediate host

  • Name - name of the TDE

  • Description - description of the TDE

  • Version - version the intermediate database

  • Type - type of the GI

  • Instance name - MSSQL instance name of the intermediate database

  • Database name - name of the intermediate database

  • Backup path - location of backup files that came from the real source database

  • Backup Login - OS username that can access the backup path

  • Backup Password - password of the above user, only first letter and length are shown

  • DB Size - size of the intermediate database

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.

After the database is removed, the following message appears: Test Data Environment database has been removed.

Related content