Azure SQL Analytics—Automating Deployment

One of the cool feature additions to Azure SQL Database is Azure SQL Analytics, an additional feature that connects your Azure SQL Database to Azure Log Analytics. You can use this in conjunction with Azure SQL Database, Azure SQL Managed Instance, or Azure SQL Database Elastic Pools. Once this is configured you can see all sorts of information from your database in Log Analytics.

logazsql

The first step to enabling this is to create a Log Analytics workspace and add the “Azure SQL Database” solution to your workspace. The steps to create that are in the above link. After that is created, you can enable this in your database by launching the Diagnostic Settings blade in the Azure portal from the monitoring section of your Azure SQL Database. This process is pretty straightforward—it’s just a few clicks and you have data in your log analytics workspace.

However, I’m working with a customer who is building their own service based on Azure SQL Database, and I have fully automated their database deployment process. I wanted to take this a few steps further and add the SQL Analytics step as part of our deployment. This was harder than I expected it to be—the code samples in the books online post above weren’t working in my environment. And furthermore, once I got it working, I was having failures in my Azure Automation Runbook once I got the code running in the PowerShell ISE (I was having issues using VS Code on my Mac).

The command shown in books online BOL is here:

Set-AzureRmDiagnosticSetting -ResourceId [your SQL Database’s resource id] -WorkspaceId [resource id of the log analytics workspace] -Enabled $true

When I eventually got that working, it wasn’t enabling what I was expecting it to. I found out that the optional parameter of -Categories was required to in order to enable all of the SQL logging.

My final code looks like this:

#Add Analytics to Database

try {
$sqlDbInfo = Get-AzureRmSqlDatabase -DatabaseName $DatabaseName -ServerName $ServerName -ResourceGroupName $RGName

$sqlresourceID = $($sqlDbInfo).ResourceId.ToString()

$workSpaceid=(Get-AzureRmOperationalInsightsWorkspace).ResourceId.ToString()
$Metrics = @(“AllMetrics”)
$categories=@(“SQLInsights”,”AutomaticTuning”,”QueryStoreWaitstatistics”,”QueryStoreRunTimeStatistics”,”Errors”,”DatabaseWaitStatistics”,”Timeouts”,”Blocks”,”Deadlocks”)

Set-AzureRmDiagnosticSetting -ResourceId $sqlresourceID -WorkspaceID $WorkspaceID -MetricCategory $Metrics -Enabled $true -RetentionInDays 20 -Categories $categories

}
Catch {$ErrorMessage = $_.Exception.Message
“Write-Error Creating Analytics. Error was $ErrorMessage”}
You’ll note I’m having to use ToString() functions on both of my resource id options, and more importantly I’m having to specify the metrics and categories required. I tried using “AllCategories” however that failed, so I took a guess and just explicity named the categories from the portal, and it worked.

The other fun fact, if you want to automate this, and not run it interactively, you will need to import two modules. For whatever reason Get-AzureRMOperationalInsightsWorkspace is in the Azure.RM.Operationalnsights module, while Set-AzureRMDiagnosticsSetting is in Azure.RM.Insights. So you will need to add both of those modules as well as the Azure.RM.Sql module to your runbook.

About jdanton1
A DBA, cyclist, cook (who likes to play chef occasionally)

3 Responses to Azure SQL Analytics—Automating Deployment

  1. Pingback: Automating Azure SQL Database Log Analytics – Curated SQL

  2. Morganwill says:

    Thank you so much, I’ve already found many helpful tips here.
    As I’m desperately looking for some solutions, which i can also use for my company Indiaaccess and Hostasp

    Servers

    Thanks for your info

  3. Morganwill says:

    Thank you so much, I’ve already found many helpful tips here.
    As I’m desperately looking for some solutions, which i can also use for my company Indiaaccess and Hostasp Servers

    Thanks for your info

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: