Simple-Talk article

The folks at Simple-Talk were kind enough to give me an opportunity to write an article for them about Powershell, Reg-Gate SQLCompare & Snapshots.

The result is here, please let me know what you think by voting. Thanks!

Using Powershell to gather NIC info

In the course of troubleshooting a performance issue (in part caused by replication latency), we noticed that the NIC on our SQL server had been changed from Full Duplex to Half Duplex. Needless to say as soon as we switched back to Full Duplex our latency dropped back to what we'd come to expect. This was renforced by the behavior we observed when observing replication latency where it would be delivering transactions in bursts which lines up with the Half Duplex arcitecture of one-way communication.

As as outgrowth of this I thought it would be prudent to check the rest of our environment to make sure we didn't have any more mis-configured NICs. The majority of our Prod SQL Instances are clustered & have 2 NICs, one for the Heartbeat network & one for the Public Network. Per MS best practices for Clustered SQL the Heartbeat NIC is set to "100 Mb Full" & the Public NIC is set to "1000 Mb Full" so differentiating bewteen the NICs is important.

My first thought was to use the Win32_NetworkAdapter class which has properties for speed & duplex, unfortunantly looking at msdn it appears the properties haven't been implemented yet (this was verified in my testing). After viewing an excellent post on the topic by Hugo Peeters, I decided his method would work with a few small changes. My main gripe about this is the fact that MS forces us to (yet again) go to the registry for information that should be in WMI. Since the registry path changes by vendor (and in some cases model number) you'll need to modify your version of the script to account for the NIC vendors/models in your environment.

The script is here, I was not able to get it to show in a usable format on the page so we'll try this instead.

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. }  

Checking for a Service in Powershell

Recently I had the need to check if a service was across all our SQL servers. In the interests of efficiency I thought it would be fun to write a little powershell script to do the checking for me.

Here's what I came up with, just pass the service name & machine(s) you want (either singly on in a *.txt file) & it will spit out a list of all with the machine, friendly service name (if installed) or whatever you choose for those where it's not installed (I chose blank).

The cmd would look like...
./Check-ServiceInstalled SERVICENAME "C:\Lists\Servers.txt"
OR
./Check-ServiceInstalled SERVICENAME MACHINENAME

The Code (using Registry)...
$SvcName = $Args[0]
$ServerName = $Args[1]

$ErrorActionPreference = "SilentlyContinue"

$RegPath = "SYSTEM\\CurrentControlSet\\Services\\" + $SvcName

if ($ServerName -like "*.txt*")
{$Servers = Get-Content $ServerName}
else
{$Servers = $ServerName}

ForEach ($Computer in $Servers)
{
$Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine',$Computer)
$RegKey = $Reg.OpenSubKey($RegPath)
if ($RegKey -eq $null)
{new-object psobject |
add-member -pass NoteProperty Computer $Computer.ToUpper() |
add-member -pass NoteProperty Installed ""
}
else
{new-object psobject |
add-member -pass NoteProperty Computer $Computer.ToUpper() |
add-member -pass NoteProperty Installed $RegKey.GetValue('DisplayName')
}
}

The Code (using gwmi & service)...
$SvcName = $Args[0]
$ServerName = $Args[1]

if ($ServerName -like "*.txt*")
{$Servers = Get-Content $ServerName}
else
{$Servers = $ServerName}

ForEach ($Computer in $Servers)
{
$Svc = Get-WmiObject win32_service -ComputerName $Computer | where {$_.name -eq $SvcName} -ErrorAction SilentlyContinue
if ($Svc -eq $null)
{new-object psobject |
add-member -pass NoteProperty Computer $Computer.ToUpper() |
add-member -pass NoteProperty Installed ""
}
else
{new-object psobject |
add-member -pass NoteProperty Computer $Computer.ToUpper() |
add-member -pass NoteProperty Installed $Svc.DisplayName
}
}

Proactive File Deletion

One of the things I've been trying to accomplish lately is automating more of the repetative "DBA 101" type tasks as much as possible. Among other things, freeing up this time allows me to concentrate your efforts in areas where there a greater value is added from a DBA's input like performance tuning or security auditing. It also greatly reduces the chance of making mistakes, I'm sure we've all had those "execute... whoops!" moments that just make you feel like a fool, or even worse, make you look like a fool in front of your boss. Recently I took a look at our drives & noticed 2 main areas where I thought a scripted solution made sense and was safe (this will vary according to your environment's needs). The areas I decided to focus on were deleting old Red-Gate SQLBackup log files & deleting old backup files. I'm a big fan of Reg-Gate so I don't want this taken as a jab against them in any way. That being said, the Red-Gate GUI places log files in "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\" by default with no retention policy, you can set a retention on these logs, but you have to do it on a case by case basis & frankly I'm too efficient (read: lazy) to do it this way, plus I tend to forget to change the setting. We store all our backups in a specific directory named sqlbu (name changed to protect the innocent) so my backup deletion script is geared to look for that directory on all drives & only delete from that location. The backup delete script also has a few filters that will allow backup files to be kept longer than the days to keep indicated. Specifically it will not delete the file if it has "goldcopy" or "donotdelete" in the name or has "do_not_delete" in the path. I created a SQL Agent job on all our servers that runs 2 powershell scripts that do the actual work, the scripts also return how many files were deleted. The scripts both take a param for the days to keep. SQL Agent Job...

1. USE [msdb]   
2. GO   
3.   
4. EXEC msdb.dbo.sp_add_operator @name=N'DBA',    
5.         @enabled=1,    
6.         @weekday_pager_start_time=90000,    
7.         @weekday_pager_end_time=180000,    
8.         @saturday_pager_start_time=90000,    
9.         @saturday_pager_end_time=180000,    
10.         @sunday_pager_start_time=90000,    
11.         @sunday_pager_end_time=180000,    
12.         @pager_days=0,    
13.         @email_address=N'dba@yourcomp.com',    
14.         @category_name=N'[Uncategorized]'  
15. GO   
16.   
17. BEGIN TRANSACTION  
18. DECLARE @ReturnCode INT  
19. SELECT @ReturnCode = 0   
20.   
21. DECLARE @jobId BINARY(16)   
22. EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'00 Old File Cleanup',    
23.         @enabled=1,    
24.         @notify_level_eventlog=0,    
25.         @notify_level_email=2,    
26.         @notify_level_netsend=0,    
27.         @notify_level_page=0,    
28.         @delete_level=0,    
29.         @description=N'The job will delete all *.bak & *.sqb files older than 3 days that do not have “goldcopy” or “donotdelete” in the name & do not have “do_not_delete” in the path.  The job will also delete all RG log files that are older than 7 days in the “C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\” folder.',    
30.         @category_name=N'[Uncategorized (Local)]',    
31.         @owner_login_name=N'sa',    
32.         @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT  
33. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
34. /****** Object:  Step [Backup Files]    Script Date: 02/25/2009 15:38:25 ******/   
35. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Files',    
36.         @step_id=1,    
37.         @cmdexec_success_code=0,    
38.         @on_success_action=3,    
39.         @on_success_step_id=0,    
40.         @on_fail_action=2,    
41.         @on_fail_step_id=0,    
42.         @retry_attempts=0,    
43.         @retry_interval=0,    
44.         @os_run_priority=0, @subsystem=N'CmdExec',    
45.         @command=N'powershell "& C:\WINDOWS\Script\del-oldbackups-single.ps1 3"',    
46.         @flags=0   
47. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
48. /****** Object:  Step [RG Logs]    Script Date: 02/25/2009 15:38:25 ******/   
49. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RG Logs',    
50.         @step_id=2,    
51.         @cmdexec_success_code=0,    
52.         @on_success_action=1,    
53.         @on_success_step_id=0,    
54.         @on_fail_action=2,    
55.         @on_fail_step_id=0,    
56.         @retry_attempts=0,    
57.         @retry_interval=0,    
58.         @os_run_priority=0, @subsystem=N'CmdExec',    
59.         @command=N'powershell "& C:\WINDOWS\Script\del-rglogs-single.ps1 7"',    
60.         @flags=0   
61. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
62. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1   
63. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
64. EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sat-Weekly',    
65.         @enabled=1,    
66.         @freq_type=8,    
67.         @freq_interval=64,    
68.         @freq_subday_type=1,    
69.         @freq_subday_interval=0,    
70.         @freq_relative_interval=0,    
71.         @freq_recurrence_factor=1,    
72.         @active_start_date=20090225,    
73.         @active_end_date=99991231,    
74.         @active_start_time=30000,    
75.         @active_end_time=235959   
76. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
77. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'  
78. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
79. COMMIT TRANSACTION  
80. GOTO EndSave   
81. QuitWithRollback:   
82.     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION  
83. EndSave:   
84. GO   

Del-OldBackups-Single.ps1...

1. $factor = $Args[0]   
2.   
3. $disks = Get-WmiObject Win32_Volume -filter "DriveType = 3"  
4. $cutoff = (Get-Date).AddDays(-$factor)   
5.   
6. ForEach ($drv in $disks)   
7. {   
8.     $path = $drv.Name + "sqlbu\"  
9.       
10.     if ((Test-Path $path) -eq "True")  
11.     {  
12.         $count = (Get-ChildItem $path -include *.sqb,*.bak -Exclude *goldcopy*,*donotdelete* -recurse |   
13.                     ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer -and $_.PSPath -notlike "*do_not_delete*"}).Count  
14.         if ($count -eq $null) {$count = 0}  
15.               
16.         Get-ChildItem $path -include *.sqb,*.bak -Exclude *goldcopy*,*donotdelete* -recurse |  
17.         ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer -and $_.PSPath -notlike "*do_not_delete*"} |  
18.         Remove-Item  
19.         Write-Host "There were" $count "backup files deleted from" $path  
20.     }   
21. }  

Del-RGLogs-Single.ps1...

1. $factor = $Args[0]   
2.   
3. $cutoff = (Get-Date).AddDays(-$factor)   
4.   
5. $path = "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\"  
6.  
7. if ((Test-Path $path) -eq "True")  
8. {  
9.     $count = (Get-ChildItem $path -include *.log -recurse |   
10.                 ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer}).Count  
11.     if ($count -eq $null) {$count = 0}  
12.           
13.     Get-ChildItem $path -include *.log -recurse |  
14.     ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer} |  
15.     Remove-Item  
16.     Write-Host "There were" $count "files deleted"  
17. }  
18. else {Write-Host "There are no files to be deleted"}