Provisioning a SQL Server VDB

This topic describes how to provision a virtual database (VDB) from a SQL Server dSource.

Prerequisites

• You must have already linked a dSource from a source database, as described in Linking a SQL Server dSource or have already created a VDB from which you want to provision another VDB.

• You must have already set up Windows target environments and installed the Delphix Connector on them, as described in Adding a SQL Server Standalone Target Environment.

• Make sure that you have the required privileges on the target environment.

• If you are provisioning to a different target environment than the one where the staging database has been set up, make sure that the two environments have compatible operating systems. For more information on the staging database and the validated sync process.

• If using Change Data Capture (CDC):

  •  The SQL Server instance on which the VDB is being provisioned or exported must support CDC.

  • The SQL Server Agent for the instance must be running otherwise adding a CDC capture and cleanup jobs will fail.

Procedure

When provisioning or refreshing a SQL Server VDBs, both the DB_CHAINING and TRUSTWORTHY database parameters will be disabled (even if they were enabled on the dSource).

1. Login to the Delphix Management application.

2. Click Manage.

3. Select Datasets.

4. Select a dSource.

5. Click Timeflow tab.

6. Next to a snapshot select theProvision VDB icon. The Provision VDB panel opens.

7. Select a target environment.

8. On the Target Configuration page, specify a Mount Path.

   1. Linux and Unix hosts, this mount path must be the full path and not include symlinks.

9. On the Configuration page, do the following:

   1. Under Target Group, select a Target Group for the VDB.

   2. Enter a database name.

   3. Under Recovery Model, click the drop-down list to select a recovery model. You can choose one of the following:

      1. Simple - This option is recommended and selected by default. It allows SQL Server to automatically mark parts of its transaction log file for re-use if they are not in use.

      2. Full - This option lets you take the responsibility for taking backups and log backups of the VDB to an external location.

      3. Bulk Logged - This option lets you take the responsibility for taking backups and log backups of the VDB to an external location.

d. Specify any Pre-Scripts or Post-Scripts that should be used during the provisioning process. For CDC users, it is recommended that VDB Post Start hooks be configured to automatically start these jobs on successful provisioning

It is advised to use a Post Start hook as it is executed in all VDB operations like provision, refresh, rewind, disable/enable. Also, CDC jobs get deleted whenever we disable a VDB. The Post Start hook will add the CDC jobs again on enabling the VDB.

e. Under VDB configuration, enable Auto VDB Restart to allow the Delphix Engine to automatically restart the VDB when it detects the target host reboot.

f. To enable Change Data Capture (CDC), select the Enable checkbox. 

g. To enable VDB configuration parameters, select the VDB Configuration Parameters checkbox. This step displays a new page to either select an existing template or set configuration parameters.

READ_COMMITTED_SNAPSHOT is the only parameter that can be defined here, and the allowed values are OFF and ON.

To do so, perform the following steps on the VDB Configure Parameters page:

a. Click the plus icon to add a new key-value pair as a new template for the configuration parameters.

b. OR, From the Select Template dropdown list, select an existing configuration template to be applied on the VDB. Applied VDB Config Template will be displayed under the Configuration tab in the dataset and will remain editable.

h. Click Next.

CDC documentation

For more information see:

• About Change Data Capture 

• Enable and Disable Change Data Capture

10. Under Policies, select a Snapshot Policy for the VDB and click Next.

11. Under Masking, select Mask this VDB checkbox to mask your data during provisioning and then select one of the following masking options:

    1. Select an existing masking job

    2. Masking Job is not currently available for the selected data type, please mask using script(s) instead. If you select this option, you should define a Configure Clone script in the Hooks step to mask the dataset.

12. Under Hooks, specify any Hooks to be used during the provisioning process. For more information, see Hooks for SQL Server.

13. Click Next.

14. The final summary tab will enable you to review your configurations.

15. Click Submit.

When provisioning starts, the VDB will appear in the Datasets panel. Select the VDB and navigate to the Status tab to see the progress of the job. When provisioning is complete, you can see more information on the Configuration tab.

You can select a SQL Server instance that has a higher version than the source database and the VDB will be automatically upgraded. For more information about compatibility between different versions of SQL Server, see SQL Server Support Matrix.

Provisioning by snapshot or logSync

When provisioning by snapshot, you can provision to the start of any particular snapshot, either by time or LSN.

You can take a new snapshot of the dSource and provision from it by clicking the Camera icon.

Provisioning By Snapshot

Warnings and errors on target environments during VDB operations

It is expected that a number of informational, warning, or error messages may appear in the Windows Event Log or SQL Server Error Log during VDB operations such as Provision and Refresh.

VDB operations perform administrative tasks that include adding and removing iSCSI disks; adding, backing up, restoring, and dropping databases; and changing database properties.

If VDB operations complete successfully, messages that occur during these operations can usually be ignored.