Hooks for SQL Server

Overview

This topic describes the use of hook operations with dSources created from SQL Server source databases and virtual databases (VDBs) that are created from SQL Server dSources or other VDBs.

Hooks are Windows Powershell code executed on:

1. The staging target host before or after the manual snapshot of a dSource.

2. The VDB target host before and after the provision, refresh, rewind, snapshot, start or stop of a VDB.

Powershell version

While creating a hook, a user can provide the PowerShell version (default version or version 2) in the field "Operation Type" and this PowerShell version will be used to execute the hook script. Here, the default version is the version of PowerShell installed on the target host.

Hooks can be specified in the wizard used during the creation of a VDB, or modified afterward by navigating to the Configuration > Hooks tab. Hooks can also be set using the Delphix command-line interface (CLI) or REST Web API.

Each hook operation represents a user-configurable action that the Delphix virtualization engine will execute. You can configure the custom hook code to fail if they encounter an unexpected error. The failure of a hook operation will cause the enclosing operation to fail.

The Windows environment user for the dSource or VDB runs the "Powershell" executable, which runs the specified PowerShell script on the Staging or VDB Target host. The Delphix Engine captures and logs all output of the script and displays it if a failure occurs.

The intent of hook operations are customization of the data contents or configuration of a dataset while it is being manipulated. Actions performed by hooks effectively become an integrated part of the sync operations of a dSource or the provision, refresh, rewind, snapshot, start, or stop actions for that VDB.

Hooks are mainly used for pre- and post-provisioning operations. For example, you can use hooks to:

• Back up test data before refresh and rewind

• Back up data after provisioning

• Reset configuration settings from production to non-production settings after provisioning

• Create logins for dev/qa users who do not have privileges on production databases

• Sync logins on the target that are cloned from the production database

• Back up configuration data from the database

For more information on Hook Operations, see SQL server hook operation notes

Hook operation templates

You can use operation templates to store commonly used operations, which allows you to avoid repeated code entry when an operation is applicable to more than a single hook, dSource, or virtual dataset. You can manage templates through the Delphix Management application.

You can also create templates from existing hooks by exporting the hooks in the Delphix Management application.

While creating a hook template, the user can provide the PowerShell version (default version or version 2) in the field "Type" and this PowerShell version will be used to execute the hook's script created from this template. Here, the default version is the version of PowerShell installed on the target host.

The existing template's PowerShell version can be changed by using Delphix CLI only because UI currently does not support this feature.

Windows environment variables

When a hook is executed, Delphix will set specific Windows environment variables to provide context, such as the name of the current host, the name of the SQL Server instance and port, and the name of the database. For more information, see SQL server hook operation notes