Monitor when Azure SQL Managed Instance performs automatic database backups
Azure SQL Managed Instance is fully-managed SQL Server instance hosted in Azure cloud that takes care about basic management operations such as doing database backups, patching underlying infrastructure, etc.
Managed Instance guarantees that every database has the most recent backups so you cannot loose more than 5–10 min of changes even in the worst case scenario. Managed Instance takes weekly full database backups, incremental backup every 12 hours, and log backup every 5–10 minutes.
Sometime, you would need to monitor the backups to verify that Managed Instance is actually taking the backups. The main reason might be compliance — although Managed Instance guarantees backup policy you would need to have some mechanism that check that backups are happening in the planned intervals.
Azure SQL Managed Instance emits events (or so called XEvents) whenever the backup of database is completed and enables you to track the progress of backup operations. Managed Instance enables you to use XEvent sessions that collects the emitted events. In this story you will see how to configure XEvent session that tracks the automated backups in Managed Instance.
Configuring XEvent session
As a first step you would need to configure XEvent session that will track the backup events. The following script creates that kind of session:
CREATE EVENT SESSION [Backup trace] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace(
WHERE operation_type = 0
AND trace_message LIKE '%100 percent%')
ADD TARGET package0.ring_buffer
ALTER EVENT SESSION [Backup trace] ON SERVER
STATE = start;
The XEvent session is started and collection information about the backups.
Monitoring automatic backup progress
Once you have created XEvent session, you can use the following query to monitor the backup events:
a AS (
SELECT xed = CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'Backup trace'
d.n.value('(@timestamp)', 'datetime2') AS [timestamp],
ISNULL(db.name, d.n.value('(data[@name="database_name"]/value)', 'varchar(200)')) AS database_name,
d.n.value('(data[@name="trace_message"]/value)', 'varchar(4000)') AS trace_message
CROSS APPLY xed.nodes('/RingBufferTarget/event') d(n)
LEFT JOIN master.sys.databases db
ON db.physical_database_name = d.n.value('(data[@name="database_name"]/value)', 'varchar(200)')
SELECT * FROM b
This query will return the time when backup is completed, the name of the database, and the total number of bytes processed, as shown on the following screenshot:
As you can notice automatic database backups are taken every 5–10 minutes. System databases master and msdb are small databases so Managed Instance can take a backup every 5 minutes. On this instance I have one user databases that is backed-up every 5–10 minutes.
As you can see you can easily monitor and verify that Managed Instance is taking the automated backups.