Monitorare l'attività di backup per Istanza gestita di SQL di Azure

  • Articolo

Si applica a: Istanza gestita di SQL di Azure SQL

Questo articolo illustra come monitorare l'attività di backup per Istanza gestita di SQL di Azure eseguendo query sul msdb database o configurando sessioni di eventi estesi (XEvent).

Panoramica

Istanza gestita di SQL di Azure archivia le informazioni di backup nel database msdb e genera anche eventi (noti come eventi estesi o XEvent) durante l'attività di backup allo scopo di creare report. Configurare una sessione XEvent per tenere traccia delle informazioni, ad esempio lo stato del backup, il tipo di backup, le dimensioni, il tempo e il percorso all'interno del database msdb. Queste informazioni possono essere integrate con il software di monitoraggio dei backup e usate anche allo scopo di controlli enterprise.

I controlli aziendali possono richiedere la prova dei backup riusciti, del tempo di backup e della durata del backup.

Query sul database msdb

Per visualizzare l'attività di backup, eseguire la query seguente dal database definito dall'utente:

SELECT TOP (30) bs.machine_name, bs.server_name, DB_NAME(DB_ID(bs.database_name)) AS [Database Name], bs.recovery_model,
CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],
CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,
DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
bs.backup_finish_date AS [Backup Finish Date], bmf.physical_device_name AS [Backup Location], bmf.physical_block_size
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id  
WHERE DB_ID(bs.database_name) = DB_ID()
AND bs.[type] = 'D' 
ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);

Configurare la sessione XEvent

Usare l'evento esteso backup_restore_progress_trace per registrare lo stato di avanzamento del backup Istanza gestita di SQL. Modificare le sessioni XEvent in base alle esigenze per tenere traccia delle informazioni a cui si è interessati per l'azienda. Questi frammenti di codice T-SQL archiviano le sessioni XEvent nel buffer circolare, ma è anche possibile scrivere in Archiviazione BLOB di Azure. Le sessioni XEvent che archiviano i dati nel buffer circolare hanno un limite di circa 1000 messaggi, quindi devono essere usate solo per tenere traccia delle attività recenti. Inoltre, i dati del buffer circolare vengono persi al failover. Di conseguenza, per un record cronologico dei backup, scrivere in un file di evento.

Rilevamento semplice

Configurare una semplice sessione XEvent per acquisire semplici eventi relativi al completamento dei backup completi. Questo script raccoglie il nome del database, il numero totale di byte elaborati e l'ora di completamento del backup.

Usare Transact-SQL (T-SQL) per configurare la semplice sessione XEvent:

CREATE EVENT SESSION [Simple 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
WITH(STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [Simple backup trace] ON SERVER
STATE = start;

Rilevamento dettagliato

Configurare una sessione XEvent dettagliata per tenere traccia di maggiori dettagli sull'attività di backup. Questo script acquisisce l'avvio e la fine dei backup completi, differenziali e del log. Poiché questo script è più dettagliato, riempie il buffer circolare più velocemente, permettendo dunque di riciclare le voci più velocemente rispetto allo script semplice.

Usare Transact-SQL (T-SQL) per configurare la sessione XEvent dettagliata:

CREATE EVENT SESSION [Verbose backup trace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace(
    WHERE (
              [operation_type]=(0) AND (
              [trace_message] like '%100 percent%' OR 
              [trace_message] like '%BACKUP DATABASE%' OR [trace_message] like '%BACKUP LOG%'))
       )
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
       MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
       TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

ALTER EVENT SESSION [Verbose backup trace] ON SERVER
STATE = start;

Monitorare i progressi del backup

Dopo aver creato la sessione XEvent, è possibile usare Transact-SQL (T-SQL) per eseguire query sui risultati del buffer circolare e monitorare lo stato del backup. Dopo l'avvio dell'evento XEvent, raccoglie tutti gli eventi di backup in modo che le voci vengano aggiunte alla sessione approssimativamente ogni 5-10 minuti.

Rilevamento semplice

Il codice Transact-SQL (T-SQL) seguente esegue una query sulla sessione XEvent semplice e restituisce il nome del database, il numero totale di byte elaborati e l'ora di completamento del backup:

WITH
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'),
b AS(SELECT
d.n.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
ISNULL(db.name, d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')) AS database_name,
d.n.value('(data[@name="trace_message"]/value)[1]', 'varchar(4000)') AS trace_message
FROM a
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)[1]', 'varchar(200)'))
SELECT * FROM b

Lo screenshot seguente mostra un esempio di output della query precedente:

Screenshot dell'output XEvent

In questo esempio è stato eseguito automaticamente il backup di cinque database nel corso di 2 ore e 30 minuti e sono presenti 130 voci nella sessione XEvent.

Rilevamento dettagliato

Il seguente codice Transact-SQL (T-SQL) esegue una query sulla sessione XEvent dettagliata e restituisce il nome del database, nonché l'inizio e la fine dei backup completi, differenziali e del log.

WITH
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 = 'Verbose backup trace'),
b AS(SELECT
d.n.value('(@timestamp)[1]', 'datetime2') AS [timestamp],
ISNULL(db.name, d.n.value('(data[@name="database_name"]/value)[1]', 'varchar(200)')) AS database_name,
d.n.value('(data[@name="trace_message"]/value)[1]', 'varchar(4000)') AS trace_message
FROM a
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)[1]', 'varchar(200)'))
SELECT * FROM b

Lo screenshot seguente mostra un esempio di backup completo nella sessione XEvent:

Output XEvent che mostra i backup completi

Lo screenshot seguente mostra un esempio di output di un backup differenziale nella sessione XEvent:

Output XEvent che mostra i backup differenziali

Passaggi successivi

Una volta completato il backup, è possibile eseguire il ripristino a un punto nel tempo o configurare un criterio di conservazione a lungo termine.

Per altre informazioni, vedere Backup automatizzati.