Skip to main content
Delphix

How to Execute SQL in a PowerShell Hook Script

Applicable Delphix Engine Versions 

  • 3.2.x
  • 4.0.x
  • 4.1.x
  • 4.2.x
  • 4.3.x
  • 5.0.x
  • 5.1.x

Issue

Often after provisioning a SQL Server virtual database (VDB), it is desired to execute some SQL against the VDB to configure the database. Delphix provides this functionality through pre- and post-hook scripts that can be specified while provisioning VDBs (Using Pre- and Post-Scripts with SQL Server VDBs). Please note that MSSQL Hooks are not enabled by default, if you need to enable them please file a support ticket.

Troubleshooting

When troubleshooting PowerShell scripts, you can use the following statements to enable/disable tracing that will show up in the Delphix debug logs which can be viewed in a support bundle which can be downloaded to your computer:

PowerShell debugging
# The following line with a trace level of 2 enables tracing on script lines,
# variable assignments, function calls, and scripts.
Set-PSDebug -Trace 2;
 
# The following line turns off tracing.
Set-PSDebug -Trace 0;

It is important to turn off tracing anywhere your script exits so as not to continue tracing in any subsequent scripts that may be executed during the provision operation.

 

Resolution

This example utilizes two scripts. The first script is a standard SQL script containing the SQL statements. The second script is the PowerShell script that executes the sqlcmd command line utility to execute the SQL script. These scripts would need to be accessible from a directory that exists on the server hosting the VDB.

The following example was tested via specifying the fully qualified path to to the SetDatabaseOption.ps1 script in the VDBs "Post Script" dialogue box. Advanced hook operations can be enabled by Delphix Technical Support (Customizing SQL Server VDB Management with Hook Operations).

The following script named "SetDatabaseOption.sql" illustrates enabling READ COMMITTED isolation level on a VDB.

SetDatabaseOption.sql
-- This script is used to turn read_committed_snapshot on.
 
SET NOCOUNT ON;
USE [master];
GO
 
BEGIN TRY 
    ALTER DATABASE [$(VDB_DATABASE_NAME)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(VDB_DATABASE_NAME)] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(VDB_DATABASE_NAME)] SET MULTI_USER WITH NO_WAIT;
    PRINT 'Database [$(VDB_DATABASE_NAME)] successfully configured with RCSI';
END TRY 
BEGIN CATCH 
    PRINT 'Error caught:';
    SELECT  
        ERROR_NUMBER() AS ErrorNumber 
        ,ERROR_MESSAGE() AS ErrorMessage; 
    -- If failure of this operation is not critical to VDB operations, attempt to recover.
    ALTER DATABASE [$(VDB_DATABASE_NAME)] SET MULTI_USER WITH NO_WAIT;
    -- If failure of this operation is dangerous to VDB operations, raise the error to the PowerShell script by uncommenting the line below.
    -- A failed hook operation will trigger a failure of the provisioning job.
    -- RAISERROR ('Setting read_committed_snapshot failed.', 11, 127) WITH NOWAIT, SETERROR;
END CATCH;

 

The following PowerShell script named "SetDatabaseOption.ps1" illustrates executing the above SQL script:

SetDatabaseOption.ps1
#
# Copyright (c) 2014 by Delphix. All rights reserved.
#

Set-Variable UTF8_CODEPAGE 65001 -option readonly
Set-Variable UTF8_ENCODING "System.Text.UTF8Encoding" -option readonly

# Uncomment the following log to turn on debugging
# Set-PSDebug -Trace 2;

$VDB_HOST = $env:VDB_INSTANCE_HOST
$VDB_PORT = $env:VDB_INSTANCE_PORT
$VDB_INSTANCE = $env:VDB_INSTANCE_NAME
$CONNECT_STRING = "$VDB_HOST\$VDB_INSTANCE,$VDB_PORT"

$script:ErrorActionPreference = "Continue"

function die {
    Write-Error "Error: $($args[0])"

    # run exit handler, if defined
    if (Get-Command -type Function -name atExit 2> $null) {
        atExit
    }
        Set-PSDebug -Trace 0;
    exit 1
}

function verifySuccess {
    if (!$?) {
        die "$($args[0])"
    }
}

# Set the codepage to UTF-8, suppressing the command output that tells us the active code page
chcp $UTF8_CODEPAGE >$null
verifySuccess "Failed to set codepage to $UTF8_CODEPAGE (UTF-8)"

# And tell Powershell that, yes, we really would like UTF-8 output.
$OutputEncoding = New-Object -typename $UTF8_ENCODING
verifySuccess "Failed to set `$OutputEncoding to $UTF8_ENCODING"

sqlcmd -l 30 -b -S "$VDB_HOST\$VDB_INSTANCE,$VDB_PORT" -W -E -s ";" -i "C:\Users\delphixaduser\SetDatabaseOption.sql"  -o "C:\Users\Delphixaduser\DelphixPSLog_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).log"

# sqlcmd -l 30 -b -S "localhost\SQL2008R2,62790" -W -E -s ";" -i "C:\Users\delphixaduser\SetDatabaseOption.sql"  -o "C:\Users\Delphixaduser\DelphixPSLog_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).log"

verifySuccess "SQLCMD_FAILED"

# Uncomment the following line to turn off debugging
# Set-PSDebug -Trace 0;

 

Additional Information

The main Delphix documentation provides this useful guide: Using Pre- and Post-Scripts with SQL Server VDBs

There are also many other PowerShell scripts available under the Delphix Connector directory (by default installed under "C:\Program Files\Delphix\DelphixConnector").