Share via

Monitoring AlwaysOn Health with PowerShell - Part 4 : Integration with SQL Server Agent

  • Article

Part 4 – Scheduling and Notification with SQL Agent

In Part 3, we introduced the problem of automatically monitoring our availability group. We made some progress, developing a simple script that evaluates the health of an AG on a given server instance, and throws an error when the AG is a state warranting investigation. We now need to figure out how to run this script regularly, and how to notify the right people when failure occurs.

This problem is not trivial. An availability group spans multiple server instances, so the natural question is: which server instance do we run the script against? The answer is that the script must always run against the current primary server instance. One approach here is to run the script on every server instance, but only take action when the server instance is a primary, which works well with our script from Part 3 – if you recall we detect this case, and simply throw a warning when the script is run against a secondary.

But, now we need to figure out how to run our script on multiple server instances, and do so on a regular schedule. And we still need some sort of notification mechanism. Fortunately, there’s a tool that solves all of these problems: SQL Server Agent. To sweeten the deal, SQL Agent (from 2008 upwards) has built-in support for PowerShell, meaning I can directly invoke PowerShell commands and scripts from Agent job definitions.

If you’re not familiar with SQL Server Agent, take some time to familiarize yourself with the technology here: https://msdn.microsoft.com/en-us/library/ms187061.aspx. Here is a short blurb describing SQL Agent from that knowledge base:

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs . SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday after hours, you can automate this task. Schedule the backup to run after 22:00 Monday through Friday; if the backup encounters a problem, SQL Server Agent can record the event and notify you.

From this short blurb, we learn that SQL Server Agent allows us to schedule jobs across multiple servers, and supports notification, which is exactly what we want. Let’s look at a simple example, configuring a single agent job on a server instance to run our script from Part 3.

Single Instance Configuration

Here’s an overview of what we want to accomplish: on a single server instance (preferably the current primary of the AG), we want to set up an agent job that will run the script from Part 3. When this script throws an error, we will send an e-mail to an operator, using Database Mail. 

The prerequisites:

  1. You need to have SQL Agent running.
  2. Need to have Database Mail configured on your server instance (https://msdn.microsoft.com/en-us/library/ms175887.aspx).
  3. Need to integrate SQL Agent with Database Mail (https://msdn.microsoft.com/en-us/library/ms186358.aspx)
  4. Need to add an operator to your SQL Agent configuration (https://technet.microsoft.com/en-us/library/ms175962.aspx). 

For my example, I’ve created an operator called “Availability Group Operator”.

To create the job, I right click on the Jobs folder and select New Job…, which will launch the New Job dialog.

On the “General” page of this dialog, we need to give our job a name, say, “Monitor Availability Group”.

On the steps page, click the “New” button. This will launch a separate dialog for creating the job step. Give the step a name, then for type select “PowerShell”. Make sure you have the script from Part 3 saved to a local file on the server, say to C:\scripts\monitorag.ps1. Then in the command section of the job step, invoke the script, setting the ServerName parameter to the desired ServerInstance and the GroupName parameter to the name of the group you want to monitor.

 

The text above is hard to read, so here is how I'm invoking the script in the command window. Note that this is the same as how I would invoke the script from the SQLPS shell:

C:\scripts\monitorag.ps1 -ServerName "wsnavely1-fs821" -GroupName "VLEApplication"

Click "OK" and the job step will be created. Back in the "Create Job" wizard, the next interesting page is the “Schedules” page. Here we can schedule our job to run at a regular interval. Move to this page and click the “New” button. This will launch a dialog that allows you to construct a very detailed schedule for your job. For this demo I’ll schedule the job to run every 15 minutes. 

 

After clicking “OK” here, we can move to “Notifications” page. I’ll configure the job to send an email to the operator I created earlier.

Finally, click “OK” on the “New Job” wizard. We’ve successfully created an agent job that will run our PowerShell script every fifteen minutes, and send an email to an operator when the script fails.

Multiserver Management

Naively, we could repeat the process above for each server that hosts our availability group. However, SQL Agent has built-in capabilities for handling jobs that run across multiple servers. Reference https://msdn.microsoft.com/en-us/library/ms180992.aspx for more details.

This concludes our 4 part series on using PowerShell to monitor your AlwaysOn Availability groups. We first gained a basic understand of how the AlwaysOn health cmdlets function, then created a script with these cmdlets that we ultimately invoked from a SQL Agent job to give us scheduling and notification.

Comments