Executing SQL in a PowerShell Hook Script (KBA1370)
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.
# 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.
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.
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."