Tuesday, June 16, 2009

Fun with Powershell & Red-Gate SQL Compare

Last week I was asked to help out on a project where a group of users that didn't have access to Production needed to be able to perform schema compares of Dev <-> Prod DBs. We currently use Red-Gate SQL Compare which is a great product, BTW so a solution was designed around it. The solution decided on was to use SQL Compare to generate Snapshots of Prod nightly that could then be compared against without the need for access to Prod (Note: a SQL Compare Snapshot is a flat file which contains all information needed to perform a complete schema compare, but no actual data).

We wanted the process that generated the Snapshots to automatically run nightly against all online, non system DBs on all Prod SQL Instances, also excluding any DBs reserved for DBA usage only. The process should also be able to be manually run for an individual DB, one Instance or a group of SQL Instances. My final solution involved 2 powershell scripts & a scheduled job. One script actually creates the Snapshots & one script runs 4 iterations of the create script in parallel (more on this later). There are a few considerations to take into considerations when using this method, SQL Compare 8.0 is required to create Snapshots from the command line & to compare against the Snapshots. There is a free utility from Red-Gate that allows for command line creation of Snapshots (they are also usable by SQL Compare 7.0 and higher) that can be found here "http://www.red-gate.com/downloads/labs/RedGate.SQLSnapper.zip" in our case we needed some of the features offered by Professional Edition so we went that route.

The automated steps looks like this...

  1. Scheduled job "Create RG Snaps" runs...
  2. PWS Script "Run-RGSnaps.ps1" which make 4 parallel calls to...
  3. PWS Script "Create-RGSnaps.ps1" which save the snaps to... \\server\FOLDER\RedGate_Snaps\ in...
  4. SERVER$INSTANCE.DB naming format

There are 3 methods by which the PWS Script "Create-RGSnaps.ps1" can be run manually.

  • One Database
  • Open Powershell
  • Navigate to scripts folder, issue a "sl C:\\Script" (no quotes) command
  • To run against yourdb on SERVER1\INSTANCE1, for example, issue a "./Create-RGSnaps.ps1 SERVER1\INSTANCE1 yourdb" (no quotes) command. Output will look like the below...

SQL Compare Command Line V8.0.0.965==============================================================================

Copyright c Red Gate Software Ltd 1999-2009 Serial Number: 012-345-678901-2345 Creating database snapshot file '\\SERVER\FOLDER\RedGate_Snaps\SERVER1$INSTANCE1.yourdb .snp' from database 'yourdb ' on 'server1\instance1'...

OK

  • One Instance
  • Open Powershell
  • Navigate to scripts folder, issue a "sl C:\\Script" (no quotes) command
  • To run against SERVER1\INSTANCE1, for example, issue a "./Create-RGSnaps.ps1 SERVER1\INSTANCE1" (no quotes) command. Output will look the same as above...

  • Multiple Instances
  • Open powershell
  • Navigate to scripts folder, issue a "sl C:\\Script" (no quotes) command
  • To run against SERVER1\INSTANCE1 & SERVER2\INSTANCE2, for example, create a file named Instances.txt (or a name of your choice) in "C:\\Script" that contains entries for SERVER1\INSTANCE1 & SERVER2\INSTANCE2 (one line per instance)
  • Issue a "Get-Content Instances.txt ForEach {./Create-RGSnaps $_}" (no quotes) command. Output will look the same as above...

The script that does the work "Create-RGSnaps.ps1" takes a required parameter of Instance & an optional parameter of Database. If a value for Database is passed the script calls the command line interface for SQL Compare with no further processing. If no value for Database is passed the script parses all DBs on the Instance that are active/accessable & calls SQL Compare for each DB. The code is below...

  1. param($Instance = $(throw 'Instance is required.'), $Database)   
  2.   
  3. $ErrorActionPreference = "SilentlyContinue"  
  4.   
  5. $Path = "\\SERVER\FOLDER\RedGate_Snaps\"  
  6. $Query = "SELECT name FROM dbo.sysdatabases (NOLOCK) WHERE dbid > 4 AND name NOT IN ('distribution','DBADatabase') AND has_dbaccess(name) = 1"  
  7.  
  8. #########################  
  9. function Get-SQLData  
  10. {  
  11.     param($Instance = $(throw 'Instance is required.'), $DBName = $(throw 'Database is required.'), $Query = $(throw 'Query is required.'))  
  12.       
  13.     Write-Verbose "Get-SqlData Instance:$Instance Database:$DBName Query:$Query"  
  14.       
  15.     $ConnString = "Server=$Instance;Database=$DBName;Integrated Security=SSPI;"  
  16.     $DataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" ($Query,$ConnString)  
  17.     $DataTable = New-Object "System.Data.DataTable"  
  18.     [void]$DataAdapter.fill($DataTable)  
  19.     $DataTable  
  20. } #Get-SQLData  
  21.  
  22. #########################  
  23. function Get-Databases  
  24. {  
  25.     param($Instance = $(throw 'Instance is required.'))  
  26.  
  27.     Get-SqlData $Instance 'master' $Query  
  28.  
  29. } #Get-Databases  
  30.  
  31. #########################  
  32. function Invoke-SqlCompare  
  33. {  
  34.     param($Instance = $(throw 'Instance is required.'), $Database = $(throw 'Database is required.'), $Path = $(throw 'Path for Snap files required.'))  
  35.  
  36.     $InstanceStrng = ($Instance -replace('\\','`$')).ToUpper()  
  37.     $Command = '&"C:\Program Files (x86)\Red Gate\SQL Compare 8\sqlcompare.exe" /Server1:' + $Instance + ' /database1:"' + $Database + '" /makesnapshot:"' + $Path + $InstanceStrng + '.' + $Database + '.snp" /force'   
  38.   
  39.     Invoke-Expression $Command  
  40.   
  41. #Invoke-SqlCompare   
  42.   
  43. #########################   
  44. #Main   
  45.   
  46. If ($Database)   
  47.     {Invoke-SQLCompare $Instance $Database $Path}   
  48. Else   
  49.     {Get-Databases $Instance | ForEach {Invoke-SQLCompare $Instance $_.name $Path}}   

The script that runs the Create script in parallel "Run-RGSnaps.ps1" was written by my boss, his blog is here http://chadwickmiller.spaces.live.com/default.aspx & was only modified by me to meet my needs. Since in Powershell 1.0 there's no way to spawn background processes the Run script launches new PWS threads unitil it reaches the number defined as it's max & then checks every 15 seconds for less than the max threads (excluding itself) & if less than the defined max, spawns a new thread. I take no credit for his excellent work, just want to include for completeness. The script takes no parameters, it has a built-in function to get a list of Prod Instances we have defined in a DB & pulls back the ones marked active & passes them to the create script to run in parallel.

I tested running serially and with 4 & 8 iterations in parallel with nothing else running on the host server. The best balance between performance & resource utilization was at 4 parallel iterations (your results will probably vary). Serially resource usage was minimal we saw 8% CPU, 447 DBs across 26 Instances completed in 56 minutes average; at 4 parallel iteration we saw 33% CPU & completion of the same DBs/Instances took 15 minutes average; at 4 parallel iteration we saw 85% CPU & completion of the same DBs/Instances took 12 minutes average. the server we're running this on has 4 single core procs, my suspicion is that you can run as many threads as you have processor cores. Code is below...

  1. $ScriptRoot = "C:\<yourdir>\Script\"  
  2. $SQLPSXServer = "SERVER3\INSTANCE3"  
  3. $SQLPSXDatabase = "DB3"  
  4. $Query = "SELECT <column> FROM dbo.<table> (NOLOCK) WHERE IsEnabled = 'true'"  
  5. $MaxThread = 4  
  6. $ServerList = New-Object System.Collections.ArrayList  
  7.  
  8. #########################  
  9. function Get-SQLData  
  10. {  
  11.     param($Instance = $(throw 'Instance is required.'), $DBName = $(throw 'Database is required.'), $Query = $(throw 'Query is required.'))  
  12.       
  13.     Write-Verbose "Get-SqlData Instance:$Instance Database:$DBName Query:$Query"  
  14.       
  15.     $ConnString = "Server=$Instance;Database=$DBName;Integrated Security=SSPI;"  
  16.     $DataAdapter = New-Object "System.Data.SqlClient.SqlDataAdapter" ($Query,$ConnString)  
  17.     $DataTable = New-Object "System.Data.DataTable"  
  18.     [void]$DataAdapter.fill($DataTable)  
  19.     $DataTable  
  20. } #Get-SQLData  
  21.  
  22. ########################  
  23. function Get-SqlList  
  24. {     
  25.     param ($SQLPSXServer = $(throw 'SQLPSX Instance is required.'), $SQLPSXDatabase = $(throw 'SQLPSX Database is required.'), $Query = $(throw 'Query is required.'))  
  26.  
  27.     Get-SqlData $SQLPSXServer $SQLPSXDatabase $Query | ForEach {$ServerList.Add($_.Server) > $null}  
  28. }# Get-SqlList  
  29.  
  30. ########################  
  31. function LaunchThread  
  32. {  
  33.     param($SQLServer)  
  34.  
  35.     $StartInfo = new-object System.Diagnostics.ProcessStartInfo  
  36.     $StartInfo.FileName = "$pshome\powershell.exe"  
  37.     $StartCommand = $ScriptRoot + "Create-RGSnaps.ps1 " + $SQLServer  
  38.     $StartInfo.Arguments = " -NoProfile -Command " + $StartCommand  
  39.     $StartInfo.WorkingDirectory = $ScriptRoot  
  40.     $StartInfo.LoadUserProfile = $true  
  41.     $StartInfo.UseShellExecute = $true  
  42.     [System.Diagnostics.Process]::Start($StartInfo) > $null  
  43. }# LaunchThread  
  44.  
  45. ########################  
  46. #Main  
  47.  
  48. Get-SqlList $SQLPSXServer $SQLPSXDatabase $Query  
  49.  
  50. While ($ServerList.Count -gt 0)  
  51. {  
  52.     If ($(Get-Process | where {$_.ProcessName -eq 'Powershell' -and $_.Id -ne $PID} | Measure-Object).count -lt $MaxThread)  
  53.     {     
  54.         $Server = $ServerList[0]  
  55.         LaunchThread $Server  
  56.         $ServerList.Remove("$server")   
  57.     }   
  58.     Else   
  59.     {      
  60.         [System.Threading.Thread]::Sleep(15000)   
  61.     }   
  62. }  

No comments:

Post a Comment