SQL Server hook operation notes

SQL server clusters

When linking from, or provisioning to cluster environments, hook operations will not run once on each node in the cluster. Instead, the Delphix Engine always runs all hooks on the instance primary node.

Run powershell operation

The RunPowershell operation executes a PowerShell script on a Windows environment. The environment user runs this shell command from their home directory. The Delphix Engine captures and logs all output of the script. If the script fails, the output is displayed in the Delphix Management application and command-line interface (CLI) to aid in debugging.

If successful, the script must exit with an exit code of 0. All other exit codes will be treated as an operation failure.

Example of a run powershell Operation

You can input the full command contents into the Run powershell operation.

$removedir = $Env:DIRECTORY_TO_REMOVE
 
if ((Test-Path $removedir) -And (Get-Item $removedir) -is [System.IO.DirectoryInfo]) {
    Remove-Item -Recurse -Force $removedir
} else {
    exit 1
}
exit 0

SQL server environment variables

Operations that run user-provided scripts have access to environment variables. For operations associated with specific dSources or virtual databases (VDBs), the Delphix Engine will always set environment variables so that the user-provided operations can use them to access the dSource or VDB.

dSource environment variables

Staging variables

We have the following environment variables applicable to Staging Push dSources.

VDB environment variables

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 or the error will not be detected. The Powershell -File prefix and exit $LASTEXITCODE suffix are required to pass the script's exit code up to the layer calling the script.

Delphix does not perform error checking on PowerShell hook scripts. The script should perform error checking and logging, and return a non-zero exit code to indicate the script’s failure. Failure to return a non-zero exit code when appropriate means that Delphix will think the hook script succeeded and mark the VDB provision/refresh/rewind job as a success, when it should be seen as a failure. This is especially important when masking data is part of the hook – the VDB should not be released to users when the hook failed to mask data.

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

• Set $ ErrorActionPreference. This 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 $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.

  CODE

  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"