Automating Stored Procedures When Refreshing Virtual SQL Server Databases

Provisioning new virtual databases is easy. Go into the Delphix administrative console, find the dSource you want, find the snapshot for the point-in-time you want on that dSource, click the Provision button, specify the database server where the new database should reside, and hey presto! In 10 minutes or less, a new virtual database is ready for use.

Except not quite...

That new VDB still has all of the data from the source database, and if that source database is production, then that means that it still has production passwords. And it still has confidential data. All of that has to be fixed before the database can be used by developers or testers.

So, even though you can now clone from production in minutes instead of hours or days, you still have to do the same post-clone and post-refresh processing tasks you've always had to do prior to Delphix. Now, you just do them immediately rather than later.

If you're willing to automate those tasks, whether in T-SQL stored procedures or in Windows Powershell scripts, then Delphix can help by embedding them as part of the operations of provision or refresh.

Delphix offers hooks, which are programmatic callouts which fire before and after certain actions by the Delphix virtualization engine, such as as a refresh action...

Hooks provide the ability to execute Powershell code on the target Windows server as the Windows domain account registered with Delphix, either before or after the successful completion of an action. Here is what the form for adding hooks within a VDB looks like...

Here are the actions for which hooks can be entered, also seen listed in the screenshot above...

• Provision

  • Configure Clone hook fires after the action completes

• Refresh

  • Pre-Refresh hook fires before the action begins

  • Post-Refresh hook fires after the action completes successfully

  • Configure Clone hook fires after the Post-Refresh hook completes successfully

• Rewind

  • Pre-Rewind hook fires before the action begins

  • Post-Rewind hook fires after the action completes successfully

• Snapshot

  • Pre-Snapshot hook fires before the action begins

  • Post-Snapshot hook fires after the action completes successfully

• Start

  • Pre-Start hook fires before the action begins

  • Post-Start hook fires after the action completes successfully

• Stop

  • Pre-Stop hook fires before the action begins

  • Post-Stop hook fires after the action completes successfully

The Easy Guide to Data Virtualization for DevOps Test Data 

Clarify your understanding of data virtualization with this white paper. You’ll learn how and why data virtualization reduces business risk, simplifies data version control, and boosts productivity. You’ll view diagrams of how it improves physical storage and manual provisioning. And you’ll learn how data virtualization slots into a DevOps test data management solution like Delphix to bring even more benefits to enterprises. 

Get your copy 

So back to the problem at hand...

We want some actions to take place automatically each time we refresh our virtual database (VDB). As it turn out, we have two Transact-SQL stored procedures already coded to do the job...

1. stored procedure MSDB.DBO.CAPTURE_ACCTS_PASSWDS @DatabaseName

   • Saves all of the current accounts and account passwords to a set of tables in the MSDB system database

2. stored procedure MSDB.DBO.REAPPLY_ACCTS_PASSWDS @DatabaseName

   • Re-applies all of the current accounts and account passwords from the information previously stored in the MSDB system database

"@DatabaseName", which is the name of the VDB, is the only parameter for these stored procedures.

I haven't posted the T-SQL code for these stored procedures, partly because it is always going to be very customized to its environment, but mostly because I am not proficient with T-SQL myself, and I would just be copying someone else's code for a time.

So looking at our list of Delphix hooks, it should be clear that we need to call the CAPTURE_PASSWORDS stored procedure during the Pre-Refresh hook, and call the REAPPLY_PASSWORDS stored procedure during the Post-Refresh hook. Since hooks only call Powershell code and not T-SQL, here is some Powershell code we can use...

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:
TGorman 02nov15 first version
#================================================================================ 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

Of course, this code does more than just calling the stored procedure with the database name as the sole parameter; it is also qualifying command-line parameters, creating and updating a log file, and handling possible error conditions. This hook text can be downloaded from online HERE.

Once this script is saved on the target Windows host server where the VDB resides, then we can call it from the Pre-Refresh and Post-Refresh hooks.

Here we see the Pre-Refresh hook being called with a call to the CALLSP.PS1 powershell script located within the C:\DELPHIX\SCRIPTS directory on the target Windows server...

Likewise, we see here how the Post-Refresh hook is constructed...

And finally, with both hooks created, we can see them in the Configuration panel together like this...

So now, whenever the refresh operation is performed on the VDB named VV11, these hooks will execute the Powershell script, which will in turn execute the specified stored procedures, and so when the refresh operation is complete, the account and password settings which were present prior to the refresh operation will still be present.

If you have any questions, please feel free to contact me at tim.gorman@delphix.com.