Running post script for EVERY VDB that is created from a source

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Hi
We want to implement a script that is run against every VDB that is created from a particular dSource.

If I put it in the post script of the dSource, would that work?  every VDB that gets created from this will run this script after creation/refresh?

This script would be a SQL script which would set certain security and disable triggers
Photo of Mark Hayter

Mark Hayter

  • 766 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Tim Gorman

Tim Gorman, Field Services

  • 2,824 Points 2k badge 2x thumb
Official Response
Mark,

The pre- and post-scripts which are associated with a dSource only run before and after a SnapSync operation to that dSource.  They are not associated with VDB operations at all.

The post-script associated with the VDBs would do the job for you.  All you'd need to do is set the post-script for each VDB upon creation or after creation.

An important note:  if you are running a version of the Delphix virtualization engine later than 4.2 (i.e. check Help > About), then you have the capability to use "hooks" rather than the pre- and post-scripts in MSSQL.  There are seven different types of hooks for VDBs, providing far more functionality that the pre- and post-scripts.  Hooks also provide you the ability to create "templates" so that you can deploy hooks to VDBs more easily.  However, enabling hooks for MSSQL requires the functionality to be enabled by Delphix Support, so you'd need to create a support case to do so.  Documentation for hooks in MSSQL is found online HERE.

Another important note:  both pre-/post-scripts and hooks run as a session of Powershell under the Delphix environment user account within the Windows target host, so running SQL or T-SQL commands involves first connecting into the VDB database on the MSSQL instance.

Below, I am posting a powershell script suitable for use within an MSSQL hook for executing a stored procedure with no parameters.  You'll want to modify it to include parameters, or to run SQL statements.  This is just example code with no warranty...

#================================================================================
# 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 is either
#    "RFRSH_COPYRIGHTS" or "RFRSH_APPLYRIGHTS" and these stored procedures
#    take the name of the VDB as a parameter.
#
# Command-line parameters:
#
#    $dirPath        working directory in which scripts and log files reside
#    $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]$dirPath = "~~~"
    ,[string]$fqSpName = "~~~"
)
#
#--------------------------------------------------------------------------------
# Verify the "$dirPath" and "$fqSpName" command-line parameter values...
#--------------------------------------------------------------------------------
if ( $dirPath -eq "~~~" ) {
    throw "Command-line parameter 'dirPath' not found"
}
if ( $fqSpName -eq "~~~" ) {
    throw "Command-line parameter 'fqSpName' not found"
}
#
#--------------------------------------------------------------------------------
# Clean up a log file to capture future output from this script...
#--------------------------------------------------------------------------------
$timeStamp = Get-Date -UFormat "%Y%m%d_%H%M%S"
$logFile = $dirPath + "\" + $env:VDB_DATABASE_NAME + "_" + $timeStamp + "_RIGHTS.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 + "_*_RIGHTS.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
Not sure if that is readable, but if you have any difficulties, please email me at "tim.gorman@delphix.com" and I'll email the script.

Hope this helps!
(Edited)