Sunday, February 11, 2018

Onboarding Azure SQL Analytics

One of the solutions I am working on for a client makes heavy use of the Azure SQL platform.  As such, getting diagnostics and metrics data from all those databases can be quite a challenge.  Luckily for us, OMS has an Azure SQL Analytics solution which is currently in preview.  You can read more about the solution here.

The goal of this post is to talk a little bit about how to onboard databases into the solution.  The easiest way, and least scalable, is to use the portal.  Adding on Azure resources in general is a simple as navigating to the Log Analytics Tab, and selecting Azure Resources.

Once you are in that window, you can use the filtering tools up top to grab the view you are looking for.  Clicking on an Azure SQL database will open up the following:

It is important to note that sometimes these options do change.  For example, when the solution was first released, audit was not an option you could select.  I do like how you are able to select the categories you would like to monitor, allowing you to control cost inside of Log Analytics.

Another option for onboarding systems is to use the Enable-AzureRMDiagnostics.ps1 script, released by Microsoft.  This script is not specific to Azure SQL, rather, works on all Azure resources that have this capability built-in.

Instructions for installation and use can be found here.

While the script has a bunch of interactive elements to determine key settings (such as the workspace id), cracking it open simply reveals that it uses the Set-AzureRmDiagnosticSetting cmdlet to enable the appropriate settings.

The script essentially does the following:

- Logs into Azure
- Determines the subscription to use
- Determines the workspace to use
- Analyzes the subscription/resource group for resources that can have metrics/logs enabled
- Uses the Get-AzureRmDiagnosticSetting cmdlet to determine what log/metric settings can be set on a given resource type
- Prompts the user for which categories to enable
- Enables it on the target

The solution is actually pretty slick, and since everything is done via inspection, the script will never age.  Have a look at the parameters list to see automation options.

It is important to note that different resource types have different options enabled.  In the case of SQL, you'll want to enable appropriate settings for:
- Azure SQL Server
- Azure SQL Server Elastic Pools
- Azure SQL Database

In our case, we found it extremely important to compare the results between what the pool was registering, and what an individual database was registering.

In any event, this covers some of the options for onboarding databases to Azure SQL Analytics.  It is a must do as part of your Azure SQL deployment.