Art of the DBA Rotating Header Image

August, 2016:

Creating Alerts for Azure SQL Database with Powershell

Last week, as I watched the Twitters, I saw an interesting blog post from Julie Koesmarno(@MsSQLGirl) about creating alerts for Azure SQL Database. These are cool because one of the many things I get asked about when talking about Azure SQL Database is “how do I monitor this darn thing?” With these alerts, you can monitor performance, capacity, and security settings, setting up either email notifications or webhook responses.

Now, me being me, I tweeted out that it would be cool to do this in PowerShell. I say this not just being a geek about the language, but also because if you want to live in the cloud you need to up your automation and scripting game. PowerShell is the way to manage your Azure resources, especially if you want to set up consistent alerts for all your Azure SQL Databases. Julie pointed me to some examples that, while not specific for Azure SQL Database, got me moving in the right direction. I’d like to share some of that learning here.

The cmdlets

To start, we need to know what cmdlets are at our disposal. You’re going to need the Azure and AzureRM modules first (I recommend using the Powershell gallery installation) and then we just need to use PowerShell’s command syntax to find what we’re looking for. Using Get-Command, just do a wildcard search around ‘Azure’ and ‘Alert’ as my search terms.

Get-Command *Azure*Alert*

2016-08-17_15-02-15

It’s not a long list, but we don’t need much to get our work done. It should be noted that these are generic and can be used across all Azure resources.

Because these cmdlets are generic, we have to identify the specific metrics we can monitor for Azure SQL Database. Fortunately, these are discoverable using the Get-AzureRmMetricDefinition, which is not listed but part of the AzureRM.Insights module (use Get-Command -Module AzureRM.Insights). To use it, we need to know our Azure resource ID, which we can derive from three pieces of information: the resource group, the server name, and the database name.

$ResourceGroup = 'IntroAzureSql'
$server = 'msf-sqldb'
$db = 'MSFADMIN'
$rid = (Get-AzureRmResource -ResourceGroupName $ResourceGroup -ResourceName "$server/$db").ResourceID
Get-AzureRmMetricDefinition -ResourceId $rid | Format-Table

2016-08-17_15-11-26

Most of these Name values match what Julie listed in her post, but the nice thing is if we ever get new ones, we can look them up with this method.

Creating an Alert

So let’s get down to brass tacks and actually create an alert. To do this, we need some info first:

  • The Resource Group we will create the alert in.
  • An Azure location where the alert will live.
  • An Azure SQL Database server and database we are creating the alert for.
  • What metric we will monitor and what is the threshold we will be checking.
  • (optional) An email to send an alert to.

With this, here’s the settings I’m going with:

Resource Group – IntroAzureSql
Location – West US
Server – msf-db
Database – MSFADMIN
Alert – dtu_consumption_percent greater than 90 (Flag if more than 90% DTU usage)

There are a couple other considerations for creating an alert. The first is a time window the alert will check against. This window can be anywhere from five minutes to a full day. The second is what aggregation of the metric we will check (i.e. total, average, maximum). You’ll want to use values and aggregations that make sense, but for the example alert, we’ll check the maximum value over a 5 minute window.

Once we have everything in place, we will call the New-AzureRmAlertRuleEmail cmdlet to create our email notification and Add-AzureRmMetricAlertRule cmdlet to create our alert:

$ResourceGroup = 'IntroAzureSql'
$location = 'West US'
$server = 'msf-sqldb'
$db = 'MSFADMIN'
$rid = (Get-AzureRmResource -ResourceGroupName $ResourceGroup -ResourceName "$server/$db").ResourceID
$email = New-AzureRmAlertRuleEmail -CustomEmails 'mfal@dummy.com' -SendToServiceOwners
Add-AzureRmMetricAlertRule -Name 'DTU90Check' `
-Location $location `
-ResourceGroup $ResourceGroup `
-TargetResourceId $rid `
-MetricName 'dtu_consumption_percent' `
-Operator GreaterThanOrEqual `
-Threshold 90 `
-WindowSize '00:05:00' `
-TimeAggregationOperator Maximum `
-Actions $email

2016-08-17_15-33-45

And now we can look at our shiny new alert using either the portal or Get-AzureRmAlertRule:

2016-08-17_15-37-18

2016-08-17_15-39-54

To get a rid of an alert is easy, too, just call Remove-AzureRmAlertRule:

Remove-AzureRmAlertRule -ResourceGroup $ResourceGroup -Name 'DTU90Check'

2016-08-17_15-42-50

Where To Now?

As you can see managing alerts in PowerShell isn’t all that difficult. The question is “why”? Hopefully you can already see the value, especially if you want to standardize your Azure SQL Database environment. By having all your alerts scripted out, you can apply them in a consistent fashion. This can be part of a larger automation process that helps scale out your environment as necessary. Regardless of how you manage it, using PowerShell can significantly decrease your management time for your Azure environment.

Thanks again to Julie for giving me the inspiration to figure this out!

Stuck in SQLPS

maxresdefaultA month and a half ago, with a lot of fanfare and maybe a little confetti, the SQL Server Tools Team released an update to SQL Server Management Studio that massively updated the SQL Server Powershell experience. As I blogged, this is a big deal and long overdue. I am extremely happy that this is finally getting some attention in Redmond.

However, once the dust settled and I had a chance to dig into things, I discovered that not all was rainbows and candy. The truth of the matter is that SQLPS is not so easily replaced. We are still going to be burdened with using the old module for at least the short term. This means at least being comfortable with the foibles of the old module.

Bundled With SSMS

The first real issue with the new SqlServer Powershell module is that is part of the SQL Server Management Studio install. I’ve long lamented the fact that you can’t install Powershell components separately. This means you’re limited to installing this only in locations where you can install SSMS 2016. Microsoft probably thinks you should be installing SSMS2016 EVERYWHERE, but even if you run on the bleeding edge this is a problem if you’re using Windows Server Core (no GUI, no SSMS).2016-08-11_14-41-38

I have not figured out any workaround to this. It’s possible to copy the module folder from a good install to somewhere else, but I’m nervous about that since something could be missed. This means that, until we can install this separately, users are stuck running SQLPS on servers without management studio. Yes, this means SQL 2016 without SSMS as well.

Not Recognized by SQL Agent

The second issue is that even if you do install SSMS 2016, SQL Agent won’t recognize and give you access to the new module if you use a PowerShell job step. When you create a PowerShell job step, the script in that job step runs within a specific context. It’s hidden from you, but whenever that script runs the first thing that happens is SQL Server launches sqlps.exe.

3285_72b386224056bf940cd5b01341f65e9dSqlps.exe is a “mini-shell”, which configures a few things to support SQL Server and PowerShell together. The important piece here is that the executable is hard coded to use the SQLPS module. Now, sqlps.exe is deprecated and going away, but only in a future SQL Server release. This means if you’re using anything previous to this release (having no idea when the release will happen), you are going to be forced to use the SQLPS module if you are creating a Powershell script job step.

How can you get around this? This is a trick that’s been around for a while. I try to avoid it since it adds additional layers to running PowerShell in an agent job, but it does work. What you do is create a CmdExec agent job and call the Powershell executable to run your task. This approach gives you a LOT more control on how PowerShell is working in your environment, but takes some extra work. Hat tip:Derik Hammer (@sqlhammer)

Stuck In The Past

pastLike I said before, I’m excited and happy the SQL Server Tools Team is improving the PowerShell experience for SQL Server. It has been a long time coming and I hope it continues. This is why it makes the above items SO frustrating, because while all these updates are being made, most of the user base won’t be able to use them. In a world where SQL Server installations still run on 2000 and 2005, it can take a long while to upgrade your core installation to take advantage of these improvements.

Normally, I’d be ok with this. I know that when I use an earlier version of SQL Server, I don’t get the shiny new toys. With SQLPS, though, it’s different. For all these years the experience has been klunky and problematic. While some of this klunky-ness finally got fixed back in March, most of us are still stuck with the workarounds because we do not have a way to install the updates.

What can you do about it? I’m glad you asked. Currently there are two Connect items that address these directly:

You can also make your voice heard on the community Trello board. As users, we’re at the mercy of Microsoft for updating this stuff and making it available to us. The goal is to raise awareness of these issues and let the development teams know how important they are. I will definitely give Microsoft credit for being more responsive to user feedback.