Skip to main content
Delphix

Executing SQL in a PowerShell Hook Script (KBA1370)

 

KBA

KBA#1370

Applicable Delphix Engine Versions 

  • All versions of Delphix supporting hooks

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

There are two common approaches to this. The first approach uses the native "sqlcmd" command line utility. The second approach uses the PowerShell "Invoke-Sqlcmd" cmdlet.

Using the Native "sqlcmd" Command Line Utility

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) 2019 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
    }
    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;

Using the PowerShell "Invoke-Sqlcmd" cmdlet

Due to the way Delphix executes PowerShell scripts, it can't run the "Invoke-Sqlcmd" cmdlet directly from the hook dialog box. Instead, create a PowerShell script with the "Invoke-Sqlcmd" cmdlet in a PowerShell script itself. For example here is one called "adduser.ps1":

#
# Copyright (c) 2019 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"


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

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

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

# Hardcoded values
# Invoke-Sqlcmd -Query "USE [Vsoda5];CREATE USER [delphix_vdb] FOR LOGIN [delphix_vdb] WITH DEFAULT_SCHEMA=[dbo];ALTER ROLE [db_owner] ADD MEMBER [delphix_vdb];" -ServerInstance "10-44-9-155"

# Using environment variables
Invoke-Sqlcmd -Database $env:VDB_DATABASE_NAME -Query "CREATE USER [delphix_vdb] FOR LOGIN [delphix_vdb] WITH DEFAULT_SCHEMA=[dbo];ALTER ROLE [db_owner] ADD MEMBER [delphix_vdb];" -ServerInstance $CONNECT_STRING
verifySuccess "SQLCMD FAILED!"

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

exit 0

Then invoke the script in the Delphix dialog box as follows:

Screen Shot 2019-04-29 at 11.26.56 AM.png

 

Note

Note:

The "; exit $LASTEXITCODE" is required to pass the scripts exit code up to the layer calling the script.

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