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.
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 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.