• Redgate Monitor
• Monitoring Large Estates

Using SQL Monitor Groups in PowerShell

Placing your SQL Servers into Groups forces you to think about the sort of alerting and notification that you need for different categories of SQL Server machines or clusters, and so provide a natural and obvious way of organizing the entire estate into manageable divisions. This categorization will vary considerably between organizations, but SQL Monitor presents a versatile system to manage it. In this article, I hope to give you a hint of the potential of this API.

The value of Groups for organizing monitored objects

When you set up a whole lot of SQL Servers to be monitored, you are likely to have a few variations in the way that you set up the alerts. Production servers, for example, will probably be on different host machines but you will want a standard way to assign to them the same alert settings. Conversely, those servers in the development area, used for all sorts of stress tests, don’t need many alerts, and you’ll only want to be bothered for certain issues, such as Backup.

Although it is possible to configure the alerts separately for each server, via the SQL Monitor web interface, you’d soon tire of the task. Instead, we create Groups of servers that are likely to require the same alerts and thresholds, and the same notifications and email addresses. By using SQL Monitor Groups and assigning whatever you are monitoring to an appropriate Group, you can more easily apply similar alert configuration settings to SQL Servers.

You can add machines or clusters to a Group (but not, currently individual instances). Once you have assigned your servers to a group, you can use it as a filter for the Global Overview page and Alert Inbox. You can also configure alert settings at the group level. Everything in that group inherits these settings unless they have been specifically customized at a lower level.

You can move one group into another so that it becomes a subgroup. These subgroups can be nested to whatever depth you need. Each subgroup inherits all alert settings from the group above, but you can over-ride the inherited value for that subgroup so that each group in the hierarchy can be customized separately. An obvious use for this would be to assign a different contact address, for the alert recipient, for different subgroups of production servers. You would just create as many subgroups as you need and tweak the notification settings for the subgroup. You then move the server between subgroups according to who is to receive the alerts

Using the SQL Monitor PowerShell API with Groups

The SQL Monitor PowerShell API currently allows you both to send messages about SQL Server changes to SQL Monitor, and perform “bulk configuration” of alerts. The intent of the latter is to make it much easier to implement and then fine-tune an alerting strategy for a large set of monitored servers, such as all servers and instances in a SQL Monitor Group. We can also now script any type of alert, and their notifications, for any type of monitored object (Machine, SQL Instance, Cluster, Availability Group, Azure SQL Server, Azure SQL Database, Job and Azure Elastic Pool). So, for example, you can script out which alerts be enabled for any machine in a Group, their threshold settings, and who should be notified of each type of alert, and where to send alerts for each severity level.

Of course, this also means that one can read these alert settings into PowerShell, for reporting, and save them to disk, under version control. That way, it is easier to track changes, and have a record of the way that the tool is set up in case of mishap or disaster. In this article, we’ll look at how you report the alert settings from groups, as well to compare settings between two groups.

If you haven’t done so, you’ll need to install the PowerShell module. I covered the process in my Tagging SQL Server Changes article, but briefly:

1. Download the Zipped SQL Monitor PowerShell module from SQL Monitor’s Configuration pane.
2. Start PowerShell in Admin mode, determine where your module paths using $Env:PSModulePath and select either the first path (personal) or second path (all users).
3. In this directory for PowerShell modules, create a subdirectory called RedgateSQM and copy into it the module file. Then unzip it into the directory
4. In a new PowerShell window, not in administrator mode, make sure that you can import the module without error, using Import-module RedgateSQM

Save group alert settings to disk

The following PowerShell script will save all the alert settings and notifications for your groups and subgroups to disk, as JSON files, in GitHub. You’ll need to fill in, as parameters to the Connect-SqlMonitor cmdlet, the URL address to the base monitor and the authtoken you’ve gotten from the SQL Monitor user interface. You’ll also need to specify in $pathToAlertSettingFiles the part to the directory where you wish to store your files. In my case, this will be a source control directory.

This will take some time to execute, but when it is finished you should see something like this (I added a few subgroups for testing purposes) ….

And here is a sample of the contents of a file:

This is, I reckon, the simplest way of saving the settings.

Getting the alert settings for an instance

You can, of course, get the alert settings for any monitored object (MonitoredObject), meaning anything that can be monitored by SQL Monitor. Here, as an example is code for saving the alert settings for each instance. The initial lines connecting to the SQL Monitor base monitor, and so on, are the same as above.

This code will take a very long time if you have a lot of instances. If you have more than ten instances, you’ll need to run a version with filters, which I show after this one:

And this is the version if you have a lot of machines. You will need to tweak the filters to suit! This collects the settings for the default SQL Server instance on all the machines beginning with ‘A’:

Aha! I hear you say. That collection of alerts is different. For a Group, you used the $AlertTypes and now, for an instance, we use $InstanceAlertTypes. OK. The Module RedgateSQM provides many collections of alerts, each of which are appropriate for a different monitored object. With Groups, all the alert types are necessary because you can put a cluster, instance, Job, Elastic pool or whatever in a group.

It is probably time to map out these monitored objects and give some sort of indication of the types of alert that are relevant to each type of monitored object.

The SQL Monitor MonitoredObjects

Each of these monitored objects has its own object type, so in PowerShell you must get them from their parent MonitoredObject. This means that the base monitor is always the starting point. From the Base monitor, you can fetch collections of groups, clusters, machines and Azure SQL Servers. From one of these four types of MonitoredObject, you can get the instances, disks, elastic pools and availability groups. You get the jobs and databases from the instance.

Getting the database alert settings

It looks like a long pipeline to get the alert settings for each database but, in fact, if you just want every database, you simply provide no parameters at all. In this code, you can save the alerts for every database to disk. Notice that we need to use the $DatabaseAlertType list of alert types. If you try to use a different list, you’ll get an error when you specify an alert that is not associated with a database.

Comparing the settings between two groups

With the PowerShell module it is relatively easy to compare the settings for the alerts of two different groups. With a few alterations, you can do the same trick with any two objects of the same MonitoredObject type, such as two instances or two clusters.

The easiest way of doing this on an ad-hoc basis is probably to compare the JSON representation of two different groups (or whatever) with a Diff tool or differencing file-viewer. However, if this becomes a routine, then it is best automated. Here is a PowerShell script that does it for you. Remember that you must specify, in the first line, the two SQL Monitor groups you wish to compare. You must have the module loaded and be connected to the base station.

This gives the following result on my system:

I’ve sent it to the Warning output stream just for my own convenience. You’d probably want to write this to a file. You’ll see that I’ve just listed the values that have changed and the name of the variable for the alert type that has changed.

Conclusions

The PowerShell module for SQL Monitor is already useful but has a great deal of potential for extending the use of SQL Monitor. Probably, the most immediate use for the PowerShell module is to suspend monitoring on a server or instance while it is being maintained or updated. However, there are other useful features. I’ve already written about Annotations, which are driven by the PowerShell module. I already find the use of PowerShell for investigating the alerting system to be useful. Not only can I save settings to source control, but I can also check on the differences in settings. It is also possible to update notification settings and change alert settings and their comments.

There is plenty more in PowerShell. It is already possible to add new monitored objects, such as SQL Servers automatically, check on their status, suspend them and remove them from the list. Naturally, I’d like to do a whole lot more because I’m an enthusiast for automating boring database jobs.

Tools in this post

Redgate Monitor

Real-time SQL Server and PostgreSQL performance monitoring, with alerts and diagnostics

Find out more