By: Ben Snaidero | Updated: 2014-01-15 | Comments (7) | Related: > Clustering
Problem
We generally find out that a SQL Server Cluster has failed over to another node in the cluster after the fact when looking at logs for some other reason. This tip will look at using PowerShell to send out an email notification when a failover occurs.
Solution
There are many different methods we could use to find out that a SQL Server Cluster has failed over. Since the process of failover is really just the windows SQL Service stopping on one node and starting on another we could check the windows event log. Alternatively we could scan the SQL Error Log for these actions. Since we are using PowerShell for this tip and with this comes an easy way to generate the windows cluster log, let's use this log file to check for any SQL Server Cluster failovers. When writing any PowerShell scripts I usually like to write each command individually to make sure they run successfully before putting them together. This process makes troubleshooting a little easier as anyone who has done anything in PowerShell before knows that even single commands can get pretty complicated.
In order to generate this notification we'll need to write something to perform the following four tasks:
- Generate the Windows Cluster log
- Scan the Cluster log file for a SQL Cluster failover
- Send an email alert noting the SQL Cluster failover
- Schedule the PowerShell script
Step 1. Generate the Windows Cluster log
Before we can use any of the Failover Cluster cmdlets we need to import the FailoverClusters module into our PowerShell session. To do this we can run the following command.
Import-Module FailoverClusters
Once the FailoverClusters module is loaded, all we need to do to generate the cluster log file is to run the Get-ClusterLog cmdlet. One thing to note is most PowerShell commands do not need to be run from the local machine and can be called from another machine by specifying the node/computername parameter if required. For this tip we will assume that all commands are running on the local machine however we will include the node parameter so it could be run from any machine in your domain without modification.
The below example will output the cluster log file in the C:\SQL directory for the last 24 hours.
Get-ClusterLog -Node SQLTEST1 -Cluster SQLTESTCLUST -Destination C:\SQL -TimeSpan 1440
After running the above command you should see the following file in your C:\SQL directory.
Step 2. Scan the Cluster log file for a SQL Cluster failover
Next we have to scan this log file looking for a failover. The easiest way I've found to do this is to search the file for the "OnlinePending-->Online" message for your SQL cluster resource. We can accomplish this by reading in the cluster log file using the Get-Content cmdlet and then pipe this through the Select-String cmdlet searching for this pattern. You can see the complete command below as well as a sample output. Note that we've added another pipe to Out-String. This is so we can use this output as the body of our email in the next step.
Get-Content -Path C:\SQL\SQLTEST1_cluster.log |
Select-String -Pattern "SQL Server(.)*OnlinePending`-`->Online" |
Out-String
000016ec.00001a54::2013/12/23-18:08:42.809 INFO
[RCM] TransitionToState(SQL Server (INST1)) OnlinePending-->Online.
000016ec.00000d74::2013/12/23-18:08:43.436 INFO
[RCM] TransitionToState(SQL Server Agent (INST1)) OnlinePending-->Online.
Step 3. Send email alert noting the SQL Cluster failover
All that's left to do now is send the email notification. In order to do this we will first store the result of the command above into a variable and test if this variable is empty. If it does in fact contain data then we send out an email using the Send-MailMessage cmdlet with the details that we pulled from the cluster log file. Below is the command to send the email. Note: The "`" character tells PowerShell the command continues onto the next line.
if ($message) {
Send-MailMessage -To "[email protected]" -Subject "Cluster Failover Alert - SQLTEST1" `
-From "[email protected]" -Body $message `
-SmtpServer "smtp.domain.com"
}
Complete PowerShell script
Putting all of the above steps together we now have our completed script which we can deploy to our cluster. One thing to note with the completed script is I've added some variables for the Send-MailMessage parameters so the email can be configured a little bit easier. I also added a step at the very end of the script to delete the log file that we created.
Step 4. Schedule the PowerShell script
The last thing we need to do is schedule our script which we will do using "Task Scheduler". Since my PowerShell command retrieves the last 24 hours of log data from the cluster log file I will schedule my task to run every 24 hours. This can be changed to suit your needs and you could run it every 10 minutes if that's what is required. After starting Task Scheduler from the Administrative Tools menu select "Create Task" and fill out the tabs as follows.
General tab...
Switch to the Triggers tab and click "New".
Switch to the Actions tab and click "New".
Once done we simply click "OK" to create the task. Note: You will have to input the password for the account you have configured to run this task.
Other Notes
In my case the above script was enough to test for failover as I know which machine an instance should be running on so when I see it come online on another node I know it's failed over. If your environment does not have a convention similar to this you may also want to add another job which alerts you when an instance goes offline so you're able to distinguish failovers from simple restarts. To do this you would use the same commands but search for the string "OfflineSavingCheckpoints-->Offline". I prefer to see all restarts as I like to know when my SQL instances have gone offline, whatever the reason.
Next Steps
- Use PSScheduledJob module to schedule background jobs in PowerShell
- More information in how to use the Get-Help cmdlet in PowerShell
- More tips on PowerShell
About the author
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-01-15
