Part 3 – A Simple Monitoring Application for AlwaysOn
In Part 1 and Part 2 of this series, we learned quite a bit about how the AlwaysOn health cmdlets operate. But why use these cmdlets when you can just use the dashboard? The answer is automation: I can use these cmdlets build an application that that monitors my availability group when I’m away from the computer. In this part, we’ll make progress towards developing such an application. Our overall goal is an application that looks something like this:
Gathering Data and Health Evaluation
To begin, let’s look a simple script that monitors a single availability group on a single server instance. The script will throw an error if the availability group is in a state that warrants investigation. The script is hosted here:
http://gallery.technet.microsoft.com/scriptcenter/AlwaysOn-Availability-23bc88b5
I'll give a basic overview of the script (it helps to have the script open while reading the description below).
To start, the script has two parameters: ServerName and GroupName. The ServerName parameter specifies the name of the server where the availability group is hosted (this should be the current primary of the availability group). The GroupName parameter specifies the name of the availability group on this server. In the script body, we first connect to the given server instance using Windows Authentication (we assume that the user running this script has sufficient permission to do so). We then setup some DefaultInitFields on the server instance, which as discussed in part 2 facilitates the efficient loading of collections in SMO. Next, we try to query the server for data related to this availability group. If the availability group doesn’t exist, we’ll throw an error. Also, if the PrimaryReplicaServerName field on the group isn’t set, we’ll error out – if this field is empty, it may indicate that the availability group is offline, so this case warrants investigation. If we discover that the given server instance is in fact a secondary replica, we terminate with a warning: health evaluation should always be executed from the primary replica to guarantee accurate results.
Finally, we run the health cmdlets against the availability group, its replicas, and its databases, and throw an error if any of these objects are in a critical state. Of course this logic can be tweaked to suit the needs of a particular application. For example you may want to throw an error when objects are in a warning state as well. Or perhaps you can tolerate a certain number of critical objects before throwing an error.
To try this script out, first copy it to a local file on your server, say C:\scripts\monitorag.ps1 . Then open a PowerShell console and launch SQLPS, as we have done in previous parts of this series. Next, ensure that your execution policy is set to RemoteSigned. Since SQLPS has an execution policy separate from the system execution policy, you have to specify the - Scope parameter, as follows:
Set-ExecutionPolicy RemoteSigned -Scope Process
This indicates that we're setting the execution local execution policy for the process SQLPS. Then, you can run the script from the local file. For example, if I copy a script to C:\scripts\temp.ps1, my session would look like:
PS C:\> SQLPS
Microsoft SQL Server PowerShell
Version 11.0.2100.54
Microsoft Corp. All rights reserved.
PS SQLSERVER:\> Set-ExecutionPolicy RemoteSigned -Scope Process
PS SQLSERVER:\> C:\scripts\monitorag.ps1 -ServerName "myserver\myinstance" -GroupName "myag"
Scheduling and Notification
Let’s take a look at our application flow chart again and mark what’s left to do.
All the hard work is done, we just need to figure out how to run our monitoring script regularly and send notifications. Of course, you can do all of this from PowerShell: you could write another script that runs the script above in a while-loop, sleeping between evaluations. And you could send mails through an SMTP server as your means of notification (for example using the Send-MailMessage cmdlet). However, SQL Server provides another mechanism for accomplishing this task: SQL Agent. In the concluding chapter of this post, we’ll show how to plug the script we wrote above into a SQL Agent job to accomplish scheduling and notification.