Skip to main content
Delphix

Executing SQL in a PowerShell Hook Script (KBA1370)

  1. Last updated
  2. Save as PDF
 

 

KBA

KBA#1370

Applicable Delphix Engine Versions 

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0

5.3

 5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

4.3

4.3.1.0, 4.3.2.0, 4.3.2.1, 4.3.3.0, 4.3.4.0, 4.3.4.1, 4.3.5.0

4.2

4.2.0.0, 4.2.0.3, 4.2.1.0, 4.2.1.1, 4.2.2.0, 4.2.2.1, 4.2.3.0, 4.2.4.0 , 4.2.5.0, 4.2.5.1

4.1

4.1.0.0, 4.1.2.0, 4.1.3.0, 4.1.3.1, 4.1.3.2, 4.1.4.0, 4.1.5.0, 4.1.6.0

Issue

As part of provisioning a SQL Server virtual database (VDB), you may want to execute SQL against the VDB, to perform customizations such as:

  • Modifying or removing data within tables (DML)
  • Modifying or removing tables, stored procedures or other database objects (DDL)
  • Modifying database configuration (DCL)

The Continuous Data Engine introduced the Hook Scripts feature in version 5.2, allowing these customizations to be executed using PowerShell scripts. These are described further in our document Hook Scripts for Automation and Customization.

Resolution

The following sample hook operation illustrates the use of the PowerShell Invoke-Sqlcmd command, and is provided as a template from which further customizations and scripting can be performed.

The sample queries described in this script would typically be used in one of the following Hook Operations:

  • Configure Clone, which runs during VDB Provision and VDB Refresh operations, before the VDB Snapshot is taken.
  • Post Refresh, which runs only during VDB Refresh operations, before the VDB Snapshot is taken.

A list of the available Hook Points, and a description of when each is used, is available in the document Hook Scripts for Automation and Customization.

 

important

Important:

As described in the Support Policy Scope of Delphix Customer Support (KBA9706), Delphix Support is unable to assist with the behavior and customization of Hook Operations as part of a support case, including the use and extension of this sample script.

Questions regarding the use, customization or extension of Hook Scripts should be referred to the Delphix Community. Our Professional Services team can also assist in extending our products to meet your business requirements.

  
# Sample hook script for SQL Server VDB Operations
#
# Statement of Support
# This software is provided as-is, without warranty of any kind or commercial support
# through Delphix. Questions, issues, feature requests, and contributions should be 
# directed to the community as outlined in the Delphix Community Guidelines.

# Tell PowerShell to stop executing the script in the case of an error
$ErrorActionPreference = "Stop";

# Load the SQL Server PowerShell module (not required in newer versions of PowerShell)
Import-Module sqlps

# Environment variables are set automatically during hook execution.
$VDB_HOST = $env:VDB_INSTANCE_HOST
$VDB_PORT = $env:VDB_INSTANCE_PORT
$VDB_INSTANCE = $env:VDB_INSTANCE_NAME
$VDB_NAME = $env:VDB_DATABASE_NAME
$CONNECT_STRING = "$VDB_HOST\$VDB_INSTANCE,$VDB_PORT"

# The following can be uncommented in case of "login from untrusted domain"
# errors (see KBA1534)
# $VDB_HOST = $env:computerName

# Set location and filename of logs from this hook operation
$LOG_PATH = "C:\Temp"
$LOG_FILENAME = "SampleHook_$((Get-Date).ToString('yyyyMMdd')).log"

if (Test-Path -Path $LOG_PATH) {
    $LOG_FILE = Join-Path -Path $LOG_PATH -ChildPath $LOG_FILENAME
}
else {
    throw "Cannot locate directory $LOG_PATH to write log files. Please review the Hook Script for this container."
}

"$((Get-Date).ToString('yyyy-MM-dd hh:mm:ss')) Executing Hook Script for database $VDB_NAME." | Out-File -Append -FilePath $LOG_FILE

#
# Define the SQL Commands to manipulate your database here
#

$SQL_COMMAND = @"
CREATE TABLE [dbo].[dlpxHookDemo]
(id int, textcol nvarchar(10));
INSERT INTO [dbo].[dlpxHookDemo] (id, textcol) VALUES (1, N'✔');
"@

#
# This block executes the provided SQL Command.
# To execute an existing SQL file, replace -Query $SQL_COMMAND `
# with -InputFile "C:\path\to\my\script.sql" `
#

try {
    Invoke-Sqlcmd `
      -Database $VDB_NAME `
      -Query $SQL_COMMAND `
      -ServerInstance $CONNECT_STRING `
      -OutputSqlErrors $true `
      | Out-File -Append -FilePath $LOG_FILE

    "$((Get-Date).ToString('yyyy-MM-dd hh:mm:ss')) Hook Script executed successfully for database $VDB_NAME." | Out-File -Append -FilePath $LOG_FILE
    "$((Get-Date).ToString('yyyy-MM-dd hh:mm:ss')) -- End of Hook Script." | Out-File -Append -FilePath $LOG_FILE
}
catch {
    "$((Get-Date).ToString('yyyy-MM-dd hh:mm:ss')) Hook Script FAILED for database for database $VDB_NAME. Error output follows." | Out-File -Append -FilePath $LOG_FILE
    $Error[0] | Out-File -Append -FilePath $LOG_FILE
    "$((Get-Date).ToString('yyyy-MM-dd hh:mm:ss')) -- End of Hook Script." | Out-File -Append -FilePath $LOG_FILE
    throw "Unable to execute hook query via Invoke-Sqlcmd. For more details see the Hook Script log file at $LOG_FILE ."
}

This script can be added directly as a Hook Operation Script, or defined as a Hook operation template for re-use in multiple VDBs.

HookTest.png

Calling other scripts or executables as part of a Hook Operation

You can use a Hook Operation to run a PowerShell script or an executable hosted on a file system or shared network directory that is accessible to the Target Environment. This may be useful in situations such as:

  • The script will be maintained by database administrators or application developers that do not have access to the Continuous Data Engine.
  • The script is part of a third party system and may change without notice.
  • The script is complex, and cannot be easily managed and updated through the Hook Operation interface.

 

Note

Note:

The following limitations should also be taken into consideration when referencing other scripts as part of a Hook Operation:

  • The script can be modified by anyone with write access to that file, rather than Continuous Data Engine administrators.
  • VDB Provision or Refresh operations may fail if a VDB is migrated to a different server, and the external script is not present or accessible on that server.

 

The following sample hook operation invokes a second script located at C:\Scripts\My Script.ps1.

A VDB Provision or Refresh operation using this hook will fail if the second script fails, does not exist, or returns a non-zero exit code. For more details on error handling in Hook Operations, see Error handling for SQL Server PowerShell scripts.

# Sample hook script for invoking other PowerShell scripts
#
# Statement of Support
# This software is provided as-is, without warranty of any kind or commercial support
# through Delphix. Questions, issues, feature requests, and contributions should be
# directed to the community as outlined in the Delphix Community Guidelines.

# Tell PowerShell to stop executing the script in the case of an error
# This will be inherited by scripts sourced using 
$ErrorActionPreference = "Stop";

# This function will check the exit code of the last script or command executed,
# and trigger failure of this script if it was not 0 (success).
function exitOnFailure{
    if (!$?) {
        Write-Error "Error code: $LASTEXITCODE. Message: $($args[0])"
        exit 1
    }
}

# Execute the script using the call (&) operator.

& "C:\Scripts\My Script.ps1"
exitOnFailure "Execution of My Script failed."

 

Related Articles

 

Privacy Policy Terms of Use Legal About
© 2024 Delphix Corp