Using Pre- and Post-Scripts with SQL Server dSources

This topic describes the use of pre- and post-scripts with dSources.

For each script, you can add the script path and its arguments when linking a dSource in one of two ways. Either:

During the Add dSource wizard process, in the Data Management screen, click Advanced.

or

On the back of the dSource card, click the Pencil icon next to the Pre Script and Post Script fields.

To update pre- and post-script information:

Flip over the dSource card.
Click on the Pencil icon next to the Pre Script and Post Script fields.
When finished, click the check mark icon.

Note that pre-scripts are executed before the SnapSync policy, and if the script fails, SnapSync will fail as well. In the case of a post-script, the script will execute after SnapSync is complete. If a post-script fails, you will see an error message.

Using Scripts with SQL Server dSources and Virtual Databases (VDBs)

For SQL Server dSources, pre- and post-scripts are incorporated into the validated sync process.
For SQL Server single instance environments, scripts must exist and be readable on the staging environment.
Scripts can also be run as part of the SQL Server VDB provisioning process, in which case they must exist and be readable on the target environment.
For SQL Server, both dSource and VDB scripts can be either text or binary executables.

Execution Context for SQL Server Scripts

Pre- and Post-Scripts for dSources are executed in the context of the primary Windows user account of the staging environment for the dSource.

Pre- and Post-Scripts for VDBs are executed in the context of the primary Windows user account of the target environment.

Available Variables for SQL Server dSource Scripts

These environment variables are set by Delphix Engine for scripts running on a SQL Server dSource:

Environment Variables	Description
`SOURCE_INSTANCE_HOST`	Hostname of linked instance for the dSource
`SOURCE_INSTANCE_PORT`	Port of linked instance for the dSource
`SOURCE_INSTANCE_NAME`	Name of linked instance for the dSource
`SOURCE_DATABASE_NAME`	Name of database linked for the dSource

Available Variables for SQL Server VDB Scripts

These environment variables are set by Delphix Engine for scripts running on a SQL Server VDB:

```
VDB_INSTANCE_HOST
```
```
VDB_INSTANCE_PORT
```
```
VDB_INSTANCE_NAME
```
```
VDB_DATABASE_NAME
```

Error handling for SQL Server PowerShell Scripts

If the pre- or post-script execution results in an error, 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 $ ErrorActionPreference. T his only applies to PowerShell Cmdlets. For scripts or other executables such as sqlcmd, PowerShell will return with exit code 0 even if there is an error, regardless of the value of $ErrorActionPreference. The allowable values for $ErrorActionPreference are:
- Continue (default) : Continue even if there is an error.
- SilentlyContinue : 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

Custom error handling that can be invoked after each command execution to correctly detect errors:

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"

&amp; C:\Program Files\Delphix\scripts\myscript.ps1
verifySuccess "MY_SCRIPT_FAILED"