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

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 anche come eventi estesi o XEvent) durante l'attività di backup, che può essere usata per la creazione di 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 per Enterprise Audit.

I controlli dell'organizzazione potrebbero 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 (100)
    DB_NAME(DB_ID(bs.database_name)) AS [Database Name],
    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),
    CASE
        WHEN bs.compressed_backup_size > 0
        THEN CONVERT (FLOAT, bs.backup_size) / CONVERT (FLOAT, bs.compressed_backup_size)
        ELSE NULL
    END
    ) AS [Compression Ratio],
    bs.is_copy_only,
    -- bs.user_name, -- Applicable only for user-initiated COPY ONLY backups.
    bs.has_backup_checksums,
    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_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 bs.[type] = 'D'
    -- AND bs.[is_copy_only] = 1  -- If you want to filter out for user initiated COPY ONLY backups.
ORDER BY bs.backup_finish_date DESC
OPTION (RECOMPILE); -- Optimize for ad hoc execution

Annotazioni

Quando si eseguono query su tabelle di msdb sistema come dbo.backupmediaset o dbo.backupset, vengono visualizzati campi correlati alla crittografia che indicano che i file di backup non sono crittografati. Questo stato riflette solo la crittografia a livello di motore. Tutti i backup automatici vengono crittografati quando sono a riposo.

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

Tracciamento di base

Configurare una sessione XEvent di base per acquisire 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 sessione XEvent di base:

CREATE EVENT SESSION [Basic 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 [Basic 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ù verboso, riempie il buffer circolare più velocemente, quindi le voci potrebbero essere riciclate più rapidamente rispetto allo script di base.

Usare T-SQL per configurare la sessione XEvent verbosa:

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

Tracciamento di base

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

WITH
a AS (SELECT CAST (xet.target_data AS XML) AS xed
    FROM sys.dm_xe_session_targets AS xet
         INNER 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') AS d(n)
         LEFT OUTER JOIN master.sys.databases AS 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 dell'output della query precedente:

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 T-SQL interroga la sessione XEvent dettagliata e restituisce il nome del database, insieme all'inizio e alla fine dei backup completi, differenziali e del log.

WITH
a AS (SELECT CAST (xet.target_data AS XML) AS xed
    FROM sys.dm_xe_session_targets AS xet
         INNER 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') AS d(n)
         LEFT OUTER JOIN master.sys.databases AS 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:

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

Contenuti correlati

• Ripristinare un database in Istanza gestita di SQL di Azure a un punto precedente nel tempo
• Gestire la conservazione a lungo termine dei backup dell'Istanza gestita di SQL di Azure
• Backup automatizzati nell'Istanza SQL gestita di Azure