Using pre- and post-scripts with SQL Server VDBs

Overview

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

Pre-scripts and post-scripts are Windows PowerShell code executed on the VDB target host before and after the provision, refresh, or rewind of a VDB. You can specify pre- and post-scripts in the wizard for creating a VDB, 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 intent of these scripts is a customization of the data contents or configuration of a VDB while it is being manipulated. Actions performed by pre-scripts and post-scripts effectively become an integrated part of the provision, refresh, or rewind actions for that VDB.

The pre-script executes during the initial provision of a VDB. During refresh and rewind, the PowerShell script referenced in a pre-script is executed after the VDB has been stopped and unmounted, but before the new VDB is mounted. If the pre-script fails, the refresh or rewind operation will also fail with an error message.

During provision, refresh, and rewind, the PowerShell script referenced in a post-script is executed after the Delphix engine has mounted and started the VDB. 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 VDB.

You can use a pre-script to capture configuration file settings, but not the contents of the soon-to-be recreated VDB; a pre-script executes too late to access the VDB which has already been shut down and unmounted. This makes pre-script functionality much less useful than hook operations like Pre-Refresh.

You can use a post-script to run data transformation operations on newly-provisioned, newly-refreshed, or newly-rewound VDBs. These operations include data masking and setting non-production account passwords in place of cloned production passwords.

Pre- and post-scripts are an older customization mechanism for SQL Server virtual databases. They have been replaced by hook operations, which have been the standard customization mechanism on all other data platforms.

Pre- and post-scripts are supported for backward compatibility with older versions of the Delphix Engine. Delphix encourages everyone to use Hooks for customizing SQL Server VDBs for future implementations, if possible.

Associating scripts with a VDB

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

During the VDB provisioning process

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. From the listed Datasets in the left-hand navigation bar, select a SQL Server dSource or VDB.

5. Click the TimeFlow.

6. Click Provision.

7. In the first Target Environment step of the Provision VDB wizard, there are fields for Pre Script and Post Script.

8. 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.

After provisioning, using the configuration tab of the Datasets details page

1. In the Datasets panel, click the virtual dataset.

2. Click the Configuration tab.

3. Within the Configuration tab, click the Hooks tab.

4. Select the hook to edit.

   1. Click the Plus icon to add a new operation.

   2. Select the type of operation or click

      

      to load a hook operation template.

   3. Click the text area and edit the contents of the operation.

   4. To remove an operation from the list, click the Trash icon on the operation.

   5. When you have set all hook operations, click the checkmark to save the changes.The current operations at this hook will be displayed. To edit this list of operations, click the Pencil icon in the top right-hand corner of the tab.

      

Execution context for SQL server scripts

For VDBs, pre- and post-scripts are executed in the context of the environment user that was selected during the VDB provision and not the primary environment user. The primary environment user can change over time, all VDB operations are done using the user that was initially selected.

Error handling for SQL server powerShell scripts

If a pre-script or post-script encounters an unrecoverable error during execution, the Delphix Engine expects the script to return with a non-zero exit code. Otherwise, the error will not be detected.

PowerShell gives you a few ways to handle errors in your scripts:

• Set undefinedErrorActionPreference. The allowable values for $ErrorActionPreferenceare:

  • Continue (default) – Continue even if there is an error

  • SilentlyContinue – Acts like Continue with the exception that errors are not displayed

  • Inquire – Prompts the user in case of error

  • Stop : Stops execution after the first error

• Use exception handling by using traps and try/catch blocks to detect errors and return with non-zero exit codes

• Use custom error handling that can be invoked after launching each command in the script to correctly detect errors. The following example shows how you can use the function verifySuccess to detect whether the previous command failed, and if it did print, print an error message and return with an exit code of 1.

function die {
    Write-Error "Error: $($args[0])"
    exit 1
}
function verifySuccess {
    if (!$?) {
        die "$($args[0])"
    }
}
Write-Output "I'd rather be in Hawaii"
verifySuccess "WRITE_OUTPUT_FAILED"
& C:\Program Files\Delphix\scripts\myscript.ps1
verifySuccess "MY_SCRIPT_FAILED"