Executing SQL in a PowerShell Hook Script (KBA1370)
- Last updated
- Save as PDF
KBA
KBA#1370Applicable 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
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 (Hook Scripts for Automation and Customization). Please note that prior to Delphix 5.2, MSSQL Hooks are not enabled by default. If you are running a version of Delphix older than 5.2 and you need to enable the MSSQL Hooks feature, 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:
# 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.
-- 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:
# # 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:
There are also many other PowerShell scripts available under the Delphix Connector directory (by default installed under "C:\Program Files\Delphix\DelphixConnector
").