Using pre- and post-scripts with SQL Server dSources

Overview

This topic describes the use of pre- and post-scripts with dSources that are created from SQL Server source databases.

Pre-scripts and post-scripts are Windows PowerShell code executed on the Staging Target host before and after a SnapSync of a dSource. You can specify pre- and post-scripts in the wizard for creating a dSource, or you can modify them afterward by navigating to the Configuration > Standard tab. You can also set pre- and post-scripts using the Delphix command-line interface (CLI) or REST Web API.

The Delphix Engine executes a pre-script on the Staging Target host prior to the SnapSync of a dSource. If it is an initial snapshot, or manual snapshot by selecting the snapshot button on the GUI, the pre/post scripts do not get executed for dSources. Since the dSource resides within a restoring database in the SQL instance on the Staging Target host, the script can perform queries on the instance if a database account is available. 

Hooks do allow for a pre/post snapshot hook, but the pre/post scripts do not

A post-script is executed after the SnapSync on a dSource completes. If the post-script fails, the provision, refresh, or rewind operation will also fail with an error message, and a fault will be created on the dSource.

Pre/Post hooks allow for pre/post snapshot hooks for which there is no equivalent in the old pre/post scripts. Pre/Post scripts are run during validated sync, for which there is no equivalent hook operation today.

Pre- and post-scripts are supported for backward compatibility with older versions of the Delphix Engine.

Associating scripts with a dSource

Pre- and Post-scripts can be associated with a dSource in one of two ways:

During the dSource linking process

1. Login to the Delphix Management application.

2. In the top menu bar, click Manage.

3. Select Datasets.

4. Select the Plus icon and then select Add dSource.

5. In the Hooks tab of the Add dSource wizard, select the Plus icon to add new Pre Script and PostScript hooks.

6. Enter the calling syntax of the Windows PowerShell script into either or both of the appropriate fields.

   1. The calling environment is used during linking, as shown in the Environment Details panel of the Delphix environment (Manage > Environments)

   2. Four (4) environment variables will be populated with the name of the Delphix dSource, the SQL Server instance name and port, and the SQL Server database

   3. name.

After linking, using the configuration tab of the datasets details page

1. Login to the Delphix Management application.

2. In the top menu bar, click Manage.

3. Select Datasets to display the SQL Server dSources and VDBs.

4. Select a SQL Server dSource from the listed Datasets in the left-hand navigation bar.

5. Click the Configuration panel, select the Hooks sub-tab.

6. By selecting a Plus icon you can create hooks from a template or create a new hook.

   

7.  Enter the calling syntax of the Windows Powershell script into either or both of the appropriate fields

   1. The calling environment is that of the primary Environment User account, as shown in the Environment Details panel of the Delphix environment (Manage > Environments)

   2. Four (4) environment variables will be populated with the name of the VDB, the SQL Server instance name and port, and the SQL Server database name.

   3. Select the Create to accept the change.

Execution context for SQL server scripts

For dSources, pre- and post-scripts are executed in the context of the staging host user that was provided when linking.