Sample pre- and post-refresh hook for SQL server
How Pre- and Post-refresh hooks work
The Delphix Engine executes the Pre-Refresh hook before each refresh operation on a virtual database (VDB). It executes the Post-Refresh hook after each refresh operation. If a Configure Clone hook is also defined, then the Post-Refresh hook executes after all Configure Clone hooks.
Generally, after the initial provisioning of a VDB, changes are made to that VDB which should be saved across refreshes. Examples include resetting passwords to production accounts, adding non-production accounts, database references to other databases in production or non-production, and so on.
The Pre-Refresh hook is the first step of a process to capture the data to be saved, ending with the Post-Refresh hook to re-apply all that was captured, after the refresh has completed. So, the order of execution is:
Zero, one, or more Pre-refresh hook(s)
The Refresh operation itself on the VDB
Zero, one, or more Post-refresh hook(s)
The Windows PowerShell script (shown below) is intended to demonstrate how to call a SQL Server stored procedure, taking a single parameter for the VDB database name. In a Pre-Refresh hook, a custom-built stored procedure (named MSDB.DBO.CAPTURE_DB_SETTINGS) can be called to capture all data, and store it in (for example) the MSDB system database, or in a file on the Windows target host server. In a Post-Refresh hook, a custom-built stored procedure (named MSDB.DBO.APPLY_DB_SETTINGS) can be called to access all the data captured by the Pre-Refresh hook and re-apply it into the newly-refreshed VDB.
So, assuming that the Windows PowerShell script below is stored in a file named CALLSP.PS1 within a directory on the VDB target host named D:\DELPHIX\SCRIPTS, the call syntax within the Pre-Refresh hook might look something like this:
D:\Delphix\Scripts\callsp.ps1 MSDB.DBO.CAPTURE_DB_SETTINGS...and the call syntax within the Post-Refresh hook might look something like this:
D:\Delphix\Scripts\callsp.ps1 MSDB.DBO.APPLY_DB_SETTINGSBe aware that Delphix hooks set the following Windows environments from a VDB:
VDB_DATABASE_NAME – the name of the Delphix VDB (not the SQL Server database)
VDB_INSTANCE_NAME – the name of the SQL Server instance
VDB_INSTANCE_PORT – the port number of the SQL Server instance
VDB_INSTANCE_HOST – the name of the Windows host on which the SQL Server instance resides
You can access the values in these environment variables within PowerShell using the $env:variable-name syntax, as shown in the code below.
Sample code
Below is a sample code for the callsp.ps1 script.
Disclaimer
This sample code is offered for illustration purposes only, and does not carry any warranty or guarantee. Employing copies of this code, in whole or in part, indicates acceptance of all risks.
#================================================================================
# File: callsp.ps1
# Type: powershell script
# Author: Delphix Professional Services
# Date: 02-Nov 2015
#
# Copyright and license:
#
# Licensed under the Apache License, Version 2.0 (the "License"); you may
# not use this file except in compliance with the License.
#
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" basis,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#
# See the License for the specific language governing permissions and
# limitations under the License.
#
# Copyright (c) 2015 by Delphix. All rights reserved.
#
# Description:
#
# Call the appropriate stored procedure within the DBO schema in the MSDB
# databse on behalf of the VDB. The stored procedure name the name of the
# database as a parameter called "@DatabaseName"..
#
# Command-line parameters:
#
# $fqSpName fully-qualified stored procedure name
#
# Environment inputs expected:
#
# VDB_DATABASE_NAME SQL Server database name for the VDB
# VDB_INSTANCE_NAME SQL Server instance name for the VDB
# VDB_INSTANCE_PORT SQL Server instance port number for the VDB
# VDB_INSTANCE_HOST SQL Server instance hostname for the VDB
#
# Note:
#
# Modifications:
#================================================================================
param([string]$fqSpName = "~~~")
#
#--------------------------------------------------------------------------------
# Verify the "$dirPath" and "$fqSpName" command-line parameter values...
#--------------------------------------------------------------------------------
if ( $fqSpName -eq "~~~" ) {
throw "Command-line parameter 'fqSpName' not found"
}
#
#--------------------------------------------------------------------------------
# Clean up a log file to capture future output from this script...
#--------------------------------------------------------------------------------
$dirPath = [Environment]::GetFolderPath("Desktop")
$timeStamp = Get-Date -UFormat "%Y%m%d_%H%M%S"
$logFile = $dirPath + "\" + $env:VDB_DATABASE_NAME + "_" + $timeStamp + "_SP.LOG"
"logFile is " + $logFile
#
#--------------------------------------------------------------------------------
# Output the variable names and values to the log file...
#--------------------------------------------------------------------------------
"INFO: dirPath = '" + $dirPath + "'" | Out-File $logFile
"INFO: fqSpName = '" + $fqSpName + "'" | Out-File $logFile -Append
"INFO: env:VDB_INSTANCE_HOST = '" + $env:VDB_INSTANCE_HOST + "'" | Out-File $logFile -Append
"INFO: env:VDB_INSTANCE_NAME = '" + $env:VDB_INSTANCE_NAME + "'" | Out-File $logFile -Append
"INFO: env:VDB_INSTANCE_PORT = '" + $env:VDB_INSTANCE_PORT + "'" | Out-File $logFile -Append
"INFO: env:VDB_DATABASE_NAME = '" + $env:VDB_DATABASE_NAME + "'" | Out-File $logFile -Append
#
#--------------------------------------------------------------------------------
# Housekeeping: remove any existing log files older than 15 days...
#--------------------------------------------------------------------------------
"INFO: removing log files older than 15 days..." | Out-File $logFile -Append
$ageLimit = (Get-Date).AddDays(-15)
$logFilePattern = $env:VDB_DATABASE_NAME + "_*_SP.LOG"
"INFO: logFilePattern = '" + $logFilePattern + "'" | Out-File $logFile -Append
Get-ChildItem -Path $dirPath -recurse -include $logFilePattern |
Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $ageLimit } |
Remove-Item
#
#------------------------------------------------------------------------
# Run the stored procedure...
#------------------------------------------------------------------------
"INFO: Running stored procedure '" + $fqSpName + "' within database '" +
$env:VDB_DATABASE_NAME + "'..." | Out-File $logFile -Append
try {
"INFO: open SQL Server connection..." | Out-File $logFile -Append
$sqlServer = $env:VDB_INSTANCE_HOST + "\" + $env:VDB_INSTANCE_NAME + ", " + $env:VDB_INSTANCE_PORT
"INFO: sqlServer = '" + $sqlServer + "'" | Out-File $logFile -Append
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$sqlServer; Database=MSDB; Integrated Security=SSPI;"
"INFO: conn.ConnectionString = '" + $conn.ConnectionString + "'" | Out-File $logFile -Append
$conn.Open()
$cmd1 = New-Object System.Data.SqlClient.SqlCommand($fqSpName, $conn)
$cmd1.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd1.Parameters.Add('@DatabaseName', $env:VDB_DATABASE_NAME) | Out-null
"INFO: calling " + $fqSpName + ", @DatabaseName = " + $env:VDB_DATABASE_NAME | Out-File $logFile -Append
$exec1 = $cmd1.ExecuteReader()
$exec1.Close()
$conn.Close()
} catch { Throw $Error[0].Exception.Message | Out-File $logFile -Append }
#
"INFO: completed stored procedure '" + $fqSpName + "' within database '" +
$env:VDB_DATABASE_NAME + "' successfully" | Out-File $logFile -Append
#
#------------------------------------------------------------------------
# Exit with success status...
#------------------------------------------------------------------------
exit 0