In this article, you will find the required steps and setup to deploy diagnostic settings to your Azure SQL database. After creating a diagnostic setting, we will see an example where the Azure SQL database that we are going to delete is recreated, it automatically retrieves the diagnostic settings we created before. This is especially complained about by customers using databases that are rebuilt with the same name using the ARM/ terraform template. Because the diagnostic settings show the information of the deleted databases.
The Intelligent Insights (SQLInsights) log, which is independent of Azure Monitor SQL Insights (preview) and is the most significant diagnostic telemetry that you may export, is crucial. Artificial intelligence is used by Intelligent Insights to continuously monitor database usage and identify events that have a negative impact on performance. Once an issue is discovered, a thorough examination is carried out to produce an Intelligent Insights log with an intelligent evaluation of the problem. This evaluation includes a study of the underlying causes of the database performance problem and, when appropriate, suggestions for performance enhancements. To access the information in this log, you must configure the streaming export of the log.
You can export a range of performance measures in addition to the Intelligent Insights log as well as additional database logs. The performance indicators and resource logs that you can set up for streaming export to one or more destinations are listed in the following table. Single databases, elastic pools, pooled databases, managed instances, and instance databases can all have this diagnostic telemetry setup for them.
One of the Azure resources listed below can receive this diagnostic telemetry and analyze it.
- Log Analytics workspace
- Azure Event Hubs
- Azure Storage (we will use this option in our example)
In the below steps, we will be using Azure Portal to deploy diagnostic settings to the Azure SQL database and then we will delete the Azure SQL database without removing the diagnostic settings and after that, we will recreate it with the same name.
- Access your Azure portal and create a new Azure SQL database if you don’t have an existing one, and you can follow this link for the required step.
- After the Azure SQL database is created, we will add diagnostics to the database using Diagnostic settings under Monitoring.
- With the help of the add diagnostic setting, we determine the categories and metrics we want to collect. Then we indicate to which destination we will send this data.
Note: it’s required to have “Storage account” to select Archive to a storage account. If you don't already have an Azure Storage account, you can create one with the help of the link I shared.
- After successfully completing the diagnostic setting, we remove our Azure SQL database without touching the diagnostic setting.
- Then, when we recreate the Azure SQL database with the same name, we see that the diagnostic setting we created before without any action is automatically connected to the database created with the same name again.
When we investigated this internally, we learned that this behavior is normal and covers all azure resources. If you delete a resource in Azure that has an existing diagnostic setting and then recreates that resource it will have the old diagnostics setting attached to it. The diagnostics settings don’t get cleaned up from the resource when you delete the resource.
The recommended steps to take are to delete the diagnostics settings from the resource, in this case, the SQL database, before deleting the resource itself. This will ensure that the diagnostics settings will not come back when the resource is recreated. There are a few ways you can achieve this.
PowerShell: https://docs.microsoft.com/en-us/powershell/module/az.monitor/remove-azdiagnosticsetting?view=azps-8...
API: https://docs.microsoft.com/en-us/rest/api/monitor/diagnostic-settings/delete?tabs=HTTP
I hope this article was helpful for you, please feel free to share your feedback in the comments section.
Emre Gokoglu
For additional information about Azure SQL database diagnostic settings, you can review the below documents;
Configure streaming export of metrics and resource logs - Azure SQL Database | Microsoft Learn
Diagnostic settings in Azure Monitor - Azure Monitor | Microsoft Learn