How to setup alerts for deadlocks using Log Analytics
Managed Instance diagnostic events do not support sending deadlock information to Log Analytics. However, through auditing, it's possible to query failed queries along with their reported error messages—though this does not include deadlock XML. We will see how we can send information to Log Analytics and setup an alert for when a deadlock occurs. Step 1 - Deploy Log Analytics Create a Log Analytics workspace if you currently don't have one Create a Log Analytics workspace Step 2 - Add diagnostic setting On the Azure Portal, open the Diagnostic settings of your Azure SQL Managed Instance and choose Add diagnostic setting Select SQL Security Audit Event and choose has destination your Log Analytics workspace Step 3 - Create a server audit on the Azure SQL Managed Instance Run the query below on the Managed Instance Rename the server audit and server audit specification to a name of your choice. CREATE SERVER AUDIT [audittest] TO EXTERNAL_MONITOR GO -- we are adding Login audit, but only BATCH_COMPLETED_GROUP is necessary for query execution CREATE SERVER AUDIT SPECIFICATION audit_server FOR SERVER AUDIT audittest ADD (SUCCESSFUL_LOGIN_GROUP), ~ ADD (BATCH_COMPLETED_GROUP), ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON) GO ALTER SERVER AUDIT [audittest] WITH (STATE = ON) GO Step 4 - Check events on Log Analytics It may take some time for records to begin appearing in Log Analytics. Open your Log Analytics workspace and choose Logs To verify if data is being ingested, run the following query in Log Analytics and wait until you start getting the first results: Make sure that you change servername with your Azure SQL Managed Instance name AzureDiagnostics | where LogicalServerName_s == "servername" | where Category == "SQLSecurityAuditEvents" | take 10 Example: Step 5 - (Optional) Create a deadlock event for testing Create a deadlock scenario so you can see a record on log analytics. Example: Open SSMS and a new query window under the context of a user database (you can create a test database just for this test). create a table on a user database and insert 10 records: create table tb1 (id int identity(1,1) primary key clustered, col1 varchar(30)) go insert into tb1 values ('aaaaaaa') go 10 You can close the query window or reuse for the next step. Open a new query window (or reuse the first query window) and run (leave the query window open after executing): begin transaction update tb1 set col1 = 'bbbb' where id = 1 Open a second query window and run (leave the query window open after executing): begin transaction update tb1 set col1 = 'bbbb' where id = 2 Go back to the first query window opened and run (the query will be blocked - will stay executing): update tb1 set col1 = 'bbbb' where id = 2 Go back to the second query window opened and run (this transaction will be victim of deadlock): update tb1 set col1 = 'bbbb' where id = 1 You can rollback and close all windows after the deadlock exception. Step 6 - (Optional) Check the deadlock exception on Log Analytics Note: the record can take some minutes to appear on Log Analytics Use the query below to obtain the Deadlock events for the last hour (we are looking for Error 1205) Make sure that you change servername with your Azure SQL Managed Instance name AzureDiagnostics | where TimeGenerated > ago(1h) | where LogicalServerName_s == "servername" | where Category == "SQLSecurityAuditEvents" | where succeeded_s == "false" | where additional_information_s contains "Err 1205, Level 13" Step 7 - Use query to Create an Alert Use the query below to create an Alert on Azure Log Analytics Make sure that you change servername with your Azure SQL Managed Instance name. The query checks for deadlocks that occurred on the previous hour. AzureDiagnostics | where TimeGenerated > ago(1h) | where LogicalServerName_s == "servername" | where Category == "SQLSecurityAuditEvents" | where succeeded_s == "false" | where additional_information_s contains "Err 1205, Level 13" Run the query and click on New alert rule Create the alert with the desired settings
How to Monitor Automated Backups in Azure SQL Managed Instance Using T-SQL and SQL Agent
Azure SQL Managed Instance simplifies database operations by automating backups for full, differential, and transaction log types—ensuring point-in-time recovery and business continuity. These backups are managed entirely by the Azure platform, which provides reliability and peace of mind for most operational scenarios. For deeper insights and historical tracking, backup metadata is logged internally in system tables like msdb.dbo.backupset and msdb.dbo.backupmediafamily, which users can query directly. This article demonstrates how to build a lightweight, T-SQL-based alerting mechanism that allows you to track full and differential backup activity using native system views and SQL Agent automation. Problem Scenario While Azure SQL Managed Instance takes care of backups automatically, customers with specific audit and compliance requirements often ask: Can I get alerted if a backup hasn't happened recently? Can I prove that full and differential backups are consistently occurring? Is there a way to build proactive visibility into backup activity without relying on manual checks? This solution provides a way to address those questions using built-in system views and automation features available in SQL MI. Pre-requisites For this use case I assume you already have your DB mail profile configured. You can refer to the following documentations to configure the database mail on SQL Managed instance. Ref: Sending emails in Azure SQL Managed Instance | Microsoft Community Hub Job automation with SQL Agent jobs - Azure SQL Managed Instance | Microsoft Learn SQL Server agent enabled on SQL Managed Instance Access to msdb database: you’ll query backup history tables like backupset, backupmediafamily. Permissions: you need a login with sysadmin or at least access to msdb and permission to create jobs. Strategy of the Solution The monitoring approach consists of: Querying the msdb.dbo.backupset system table Identifying the latest full (type = 'D') and differential (type = 'I') backups Comparing timestamps against expected frequency thresholds: Full backup: every 7 days Differential backup: every 12 hours Sending email notifications using sp_send_dbmail Scheduling the check via a SQL Server Agent job that runs every 12 hours This solution is flexible, auditable, and requires no external components. Summary of Script Logic Loop through a list of databases (default: database1) For each DB: Get most recent full backup; if within 7 days, send success email; otherwise, send failure alert Get most recent differential backup; if within 12 hours, send success email; otherwise, send failure alert Uses sp_send_dbmail to notify recipients Alert Frequency: The job runs every 12 hours and generates up to 2 emails per database per run (1 for full, 1 for differential backup) T-SQL Script USE msdb; GO /* ------------------------------------------------------------------ Script: Backup Monitoring for Azure SQL Managed Instance Purpose: Notify for both successful and failed full/differential backups Frequency: Full = Weekly, Differential = Every 12 hours ------------------------------------------------------------------ */ -- Declare DB name(s). Add more databases here if needed DECLARE @Databases TABLE (DbName NVARCHAR(128)); INSERT INTO @Databases (DbName) VALUES ('database1'); -- Add more like: ('database2'), ('database3') -- Setup email parameters DECLARE @MailProfile SYSNAME = 'AzureManagedInstance_dbmail_profile'; -- ✅ Your Database Mail Profile DECLARE @Recipients NVARCHAR(MAX) = '<recipients_mail@yourdomain.com>'; -- ✅ Your email address DECLARE @Now DATETIME = GETDATE(); -- Loop through databases DECLARE @DbName NVARCHAR(128); DECLARE DbCursor CURSOR FOR SELECT DbName FROM @Databases; OPEN DbCursor; FETCH NEXT FROM DbCursor INTO @DbName; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @Subject NVARCHAR(200); DECLARE NVARCHAR(MAX); ----------------------------- -- 🔍 1. Check FULL BACKUP ----------------------------- DECLARE @LastFullBackup DATETIME; SELECT @LastFullBackup = MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = @DbName AND type = 'D'; -- Full backup IF @LastFullBackup IS NOT NULL AND @LastFullBackup > DATEADD(DAY, -7, @Now) BEGIN -- ✅ Full backup success SET @Subject = 'SUCCESS: Full Backup Taken for ' + @DbName; SET = '✅ A full backup was successfully taken for "' + @DbName + '" on ' + CONVERT(VARCHAR, @LastFullBackup, 120); END ELSE BEGIN -- ❌ Full backup failed SET @Subject = 'FAILURE: No Full Backup in Last 7 Days for ' + @DbName; SET = '❌ No full backup has been taken for "' + @DbName + '" in the past 7 days. Please investigate.'; END EXEC msdb.dbo.sp_send_dbmail = @MailProfile, @recipients = @Recipients, @subject = @Subject, = ; ---------------------------------- -- 🔍 2. Check DIFFERENTIAL BACKUP ---------------------------------- DECLARE @LastDiffBackup DATETIME; SELECT @LastDiffBackup = MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = @DbName AND type = 'I'; -- Differential backup IF @LastDiffBackup IS NOT NULL AND @LastDiffBackup > DATEADD(HOUR, -12, @Now) BEGIN -- ✅ Differential backup success SET @Subject = 'SUCCESS: Differential Backup Taken for ' + @DbName; SET = '✅ A differential backup was successfully taken for "' + @DbName + '" on ' + CONVERT(VARCHAR, @LastDiffBackup, 120); END ELSE BEGIN -- ❌ Differential backup failed SET @Subject = 'FAILURE: No Differential Backup in Last 12 Hours for ' + @DbName; SET = '❌ No differential backup has been taken for "' + @DbName + '" in the past 12 hours. Please investigate.'; END EXEC msdb.dbo.sp_send_dbmail = @MailProfile, @recipients = @Recipients, @subject = @Subject, = ; FETCH NEXT FROM DbCursor INTO @DbName; END; CLOSE DbCursor; DEALLOCATE DbCursor; SQL Server Agent Job Setup To automate the script execution every 12 hours, configure the job as follows: Open SQL Server Management Studio (SSMS) Go to SQL Server Agent > Jobs > New Job General Tab: Name: Backup Monitor - Success and Failures Owner: Select the owner for the job. Steps Tab: Step Name: Check Backups Type: Transact-SQL Script (T-SQL) Database: msdb Paste the script into the command window Schedules Tab: Schedule Name: Every 12 Hours Frequency: Recurs every day, repeats every 12 hours Enable the schedule Notifications Tab (Optional): You may configure job failure alerts here (though script already sends custom alerts) Click OK to save and activate the job Ref here for more details: Create a SQL Server Agent Job in SSMS | Microsoft Learn This script provides a method for monitoring Azure SQL Managed Instance backups, with email notifications to flag both success and failures. It enables teams to proactively manage backup health, meet audit requirements, and ensure backups occur as expected. Improvements & Extensions Want to take it further? Here are next-level ideas: Write backup check results to a custom audit table for history and dashboards (I will try to cover this in my next article) Extend monitoring to transaction log backups (type = 'L') Build a Power BI or SSRS dashboard on top of the log table Useful Documentation Automated Backup Frequency in SQL MI Monitor Backup Activity in SQL MILesson Learned #518:Configuring Database Watcher
Database Watcher was released in November 2024 and is a managed monitoring solution for databases. It is currently supported only for Azure SQL Database and Azure SQL Managed Instance. The setup process for both supported products is comparable. The below training video will feature setting up Database Watcher using SQL Authentication with a system-managed identity. The demo will also show how to set up a free Azure Data Explorer cluster and configure the necessary permissions to allow communication between Database Watcher and the ADX cluster. Several prerequisites will be shown in the demo that need to be configured. Please refer to the "Create and configure a database watcher - Azure SQL Database & SQL Managed Instance" documentation on Microsoft Learn. Additionally, an Azure Key Vault is required for SQL Authentication to store the local SQL user account and password. Please note that this step is not needed if Entra authentication is use. Additionally, we have a new video recorded by Kinye Yu about Configure Database Watcher with Entra ID and User managed Identity. Thanks for these videos Kinye Yu!!!!
Lesson Learned #430: Addressing Error 3201 with Azure Blob Storage: "Operating system error 86"
Today, I worked on a service request that our customer got the following error message: Cannot open backup device 'https://XXX.blob.core.windows.net/NNN/YYY.bak'. Operating system error 86(The specified network password is not correct.). RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201). Following I would like to share with you some details why this issue and the activities done to resolve it.
SQL MI Restore with valid SAS key failing with Operating System Error 86
We will provide details about a workaround for resolving the "Operating System Error 86" encountered during database restoration from Azure Storage on SQL Managed Instance. In this scenario, the Storage Account was not behind firewall and the credential already had a valid SAS token.Testing connectivity against a Managed Instance using a CNAME
As documented here. We can change the SQL Managed Instance default DNS zone ".database.windows.net" to any of your choice. However, the instance name part of the FQDN is mandatory. Whether you are connecting through a public or private endpoint, the virtual cluster manages the connection and then either proxies or redirects it to the appropriate SQL MI resident within the same cluster based on the connection type configured. The virtual cluster utilizes the hostname part of the FQDN in the connection string to lookup for the MI. If the instance name doesn't match an existing one, the connection attempt will fail. For instance, if the FQDN is sqlmi01.a1b2c3d4.database.windows.net and the CNAME is db01.contoso.com it will not work A workaround is to create a 'user@sqlmi01' login on sqlmi01 and pass it in the connection string using SQL Server authentication. This explicitly tells the virtual cluster to disregard the hostname in the FQDN and look up a managed instance called sqlmi01. Another option is to use the same hostname in the CNAME record, so sqlmi01.contoso.com would work. Although this won't suit multiple SQL MIs because SQL MI names are globally unique. One thing to note is that when using option 2 from the section above, is that all encrypted connections will fail by default. Reason being that the server-side certificate will not match with the FQDN provided in the connection string. There are a few ways around this. The first one is to use set the property trustServerCertificate to true. Although we should mention that this is not a recommended setting as that option is inherently insecure. Recently, there were some new releases and announcements for ODBC and OLEDB drivers, I was curious enough to set a test with a CNAME pointing to a managed instance public endpoint and perform some tests with the new settings of said drivers. Specifically, the hostNameInCertificate setting, that could help us with the problem described in option 2 above. As explained on the articles linked above the new releases of ODBC (18) and OLEDB (19) have a few breaking changes, that could impact the connectivity for your particular scenario. Below are the results of my tests using SSMS (.Net SqlClient), ODBC (17 and 18) and OLEDB (18 and 19). Test # Tool Version Authentication Encrypt Connection Encrypt Value Trust Server Certificate Hostname in Certificate Result 1 SSMS (.Net SqlClient) 18.11.1 SQL FALSE N/A FALSE N/A Success 2 SSMS (.Net SqlClient) 18.11.1 SQL TRUE N/A FALSE N/A Failure 3 SSMS (.Net SqlClient) 18.11.1 SQL TRUE N/A TRUE N/A Success 4 SSMS (.Net SqlClient) 18.11.1 AAD FALSE N/A FALSE N/A Failure 5 SSMS (.Net SqlClient) 18.11.1 AAD TRUE N/A FALSE N/A Failure 6 SSMS (.Net SqlClient) 18.11.1 AAD TRUE N/A TRUE N/A Success 7 ODBC 17.9.1.1 SQL FALSE N/A FALSE N/A Success 8 ODBC 17.9.1.1 SQL TRUE N/A FALSE N/A Failure 9 ODBC 17.9.1.1 SQL TRUE N/A TRUE N/A Success 10 ODBC 17.9.1.1 AAD FALSE N/A FALSE N/A Success 11 ODBC 17.9.1.1 AAD TRUE N/A FALSE N/A Failure 12 ODBC 17.9.1.1 AAD TRUE N/A TRUE N/A Success 13 OLEDB 18.6.3 SQL FALSE N/A FALSE N/A Success 14 OLEDB 18.6.3 SQL TRUE N/A FALSE N/A Failure 15 OLEDB 18.6.3 SQL TRUE N/A TRUE N/A Success 16 OLEDB 18.6.3 AAD FALSE N/A FALSE N/A Failure 17 OLEDB 18.6.3 AAD TRUE N/A FALSE N/A Failure 18 OLEDB 18.6.3 AAD TRUE N/A TRUE N/A Failure 19 ODBC 18.0.1.1 SQL FALSE Optional FALSE Blank Failure 20 ODBC 18.0.1.1 SQL FALSE Optional FALSE Set Success 21 ODBC 18.0.1.1 SQL FALSE Optional TRUE Blank Success 22 ODBC 18.0.1.1 SQL TRUE Mandatory FALSE Blank Failure 23 ODBC 18.0.1.1 SQL TRUE Mandatory FALSE Set Success 24 ODBC 18.0.1.1 SQL TRUE Mandatory TRUE Blank Success 25 ODBC 18.0.1.1 SQL TRUE Strict N/A Blank Failure 26 ODBC 18.0.1.1 SQL TRUE Strict N/A Set Failure 27 ODBC 18.0.1.1 AAD FALSE Optional FALSE Blank Failure 28 ODBC 18.0.1.1 AAD FALSE Optional FALSE Set Success 29 ODBC 18.0.1.1 AAD FALSE Optional TRUE Blank Success 30 ODBC 18.0.1.1 AAD TRUE Mandatory FALSE Blank Failure 31 ODBC 18.0.1.1 AAD TRUE Mandatory FALSE Set Success 32 ODBC 18.0.1.1 AAD TRUE Mandatory TRUE Blank Success 33 ODBC 18.0.1.1 AAD TRUE Strict N/A Blank Failure 34 ODBC 18.0.1.1 AAD TRUE Strict N/A Set Failure 35 OLEDB 19.0.0 SQL FALSE Optional FALSE Blank Failure 36 OLEDB 19.0.0 SQL FALSE Optional FALSE Set Success 37 OLEDB 19.0.0 SQL FALSE Optional TRUE Blank Failure 38 OLEDB 19.0.0 SQL TRUE Mandatory FALSE Blank Failure 39 OLEDB 19.0.0 SQL TRUE Mandatory FALSE Set Success 40 OLEDB 19.0.0 SQL TRUE Mandatory TRUE Blank Failure 41 OLEDB 19.0.0 SQL TRUE Strict N/A Blank Failure 42 OLEDB 19.0.0 SQL TRUE Strict N/A Set Failure 43 OLEDB 19.0.0 AAD FALSE Optional FALSE Blank Failure 44 OLEDB 19.0.0 AAD FALSE Optional FALSE Set Success 45 OLEDB 19.0.0 AAD FALSE Optional TRUE Blank Failure 46 OLEDB 19.0.0 AAD TRUE Mandatory FALSE Blank Failure 47 OLEDB 19.0.0 AAD TRUE Mandatory FALSE Set Success 48 OLEDB 19.0.0 AAD TRUE Mandatory TRUE Blank Failure 49 OLEDB 19.0.0 AAD TRUE Strict N/A Blank Failure 50 OLEDB 19.0.0 AAD TRUE Strict N/A Set Failure As we can see there might be a difference on the result depending on the driver, the settings and the version used. Whenever possible try updating and testing your drivers to ensure the stability of your connections Cheers!Lesson Learned #473:Harnessing the Synergy of Linked Server, Python, and sp_execute_external_script
In an era where data management transcends individual database systems, SQL Server offers a sophisticated feature set that includes Linked Server integration, Python scripting, and the powerful sp_execute_external_script function. The main objective of this approach is to leverage a Python script within SQL Server using sp_execute_external_script connecting to other database outside of SQL Server On-premise, for example, Azure SQL Database or Azure SQL Managed Instance as an alternative to employing the pyodbc library. This method not only streamlines processes but also addresses key concerns in security and network configuration, such as opening ports, which are prevalent when using external libraries for database connections. By focusing on querying a Linked Server, we can achieve seamless data integration and manipulation while maintaining a secure and efficient environment.Lesson Learned #470: Resolving 'EXECUTE Permission Denied' Error on sp_send_dbmail in Azure SQL MI
We worked on a service request that our customer encountering an error message "Executed as user: user1. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.", I would like to share with you how was the resolution for this specific error message.
