PowerShell Jobs to time failing over a SQL Cluster
We're moving to Windows 2008 and there's more and more opportunities to use PowerShell to help with my work. This is very cool. Next up is a 2008 R2 SQL cluster. I've always wondered how long it actually takes for a failover to happen and SQL be functional, we don't have a test cluster to find this information out so whilst it's not in production I'm doing some testing and it turns out it's around 14 seconds! A lot quick than I expected.
This isn't under load, that's the next step but just firing a simple query at the DB. This is possibly a flaw in this test - I'm not a DBA and don't know if this SQL query could return before the databases a fully attached. The load testing should highlight that. What I've found interesting is using jobs to test connections to the databases in parallel. It 's the first time I've used jobs to do something useful.
The names are commented out to protect the innocent.
# #### ## Run this in a local PowerShell session #### $sql = "select convert(varchar,SYSDATETIME()) + ' - ' + DB_NAME()" #$sql = "select convert(VARCHAR(19), GETDATE(), 120) + ' - ' + DB_NAME()" $databases = @("XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX") foreach ($database in $databases) { $Job = Start-Job -ScriptBlock { Add-PSSnapin SqlServerCmdletSnapin100; do { try { $output = Invoke-Sqlcmd -Query $args[1] -Database $args[0] -ServerInstance "Cluster"; $time = "[" + (Get-Date) + "]" Write-Output "$time $($output.Column1)"; } catch { $time = "[" + (Get-Date) + "]" Write-Output "$time Unable to run the SQL - $_" }# $output = "" Start-Sleep -Seconds 5 } while ( 1 -eq 1 ) } -ArgumentList $database, $sql }
This is a foreach loop that will create a PowerShell Job for each database in the array of databases. The code run for each job is in the script block - it's important to remember that this code is going to be run in it's own session. There are no profiles loaded, no modules\snapins\functions\variables from your current session so they need to be loaded in the script or passed via the -ArgumentList parameter.
In my case I've needed to load the SQL Snapin and pass two variables to the script. These are referenced by the $args variable. Each job created by this script will perform the $sql query against the database and log the time that it was done or log when it errored. This is done in a loop so I can get an idea of when SQL is unavilable and when it comes back.
You may be wondering how to get the information from the jobs!? as expected with PS that's easy.
Get-Job | Receive-Job -Keep
that'll return all the information on the Jobs running and keep the information (if you just want to get the information and not keep it don't pass the keep switch, of course you can assign this command to a variable).
If you've got some other jobs running\hanging around you can use the Id of the Jobs your're interested in to get the information (as an advancement the above script could return the Ids to an array and that be used):
Get-Job -Id 17,15,13,11 | Receive-Job -Keep
Well that's how to set up a collections of jobs - the next bit is failing over the cluster - it's been recommended that the groups failover in a particular order and we have 3 of them so here's the command used to failover:
# #### ## Run this in a PowerShell session on Node1 or Node2 #### $t = measure-command{ Get-ClusterGroup "Group1" | Move-ClusterGroup -Verbose ; Get-ClusterGroup "Group2" | Move-ClusterGroup -Verbose ; Get-ClusterGroup "Group3" | Move-ClusterGroup -Verbose ; }
Voilà a timed failover.