Monitorar a atividade de backup para a Instância Gerenciada SQL do Azure

Aplica-se a:Azure SQL Managed Instance

Este artigo ensina como monitorar a atividade de backup para a Instância Gerenciada SQL do Azure consultando o msdb banco de dados ou configurando sessões de eventos estendidos (XEvent).

Visão geral

A Instância Gerenciada SQL do Azure armazena informações de backup no banco de dados msdb e também emite eventos (também conhecidos como Eventos Estendidos ou XEvents) durante a atividade de backup, que podem ser usados para relatórios. Configure uma sessão XEvent para controlar informações como status de backup, tipo de backup, tamanho, hora e local no msdb banco de dados. Essas informações podem ser integradas ao software de monitoramento de backup e também usadas para auditoria empresarial.

As auditorias corporativas podem exigir prova de backups bem-sucedidos, tempo de backup e duração do backup.

Consultar banco de dados msdb

Para exibir a atividade de backup, execute a seguinte consulta do banco de dados definido pelo usuário:

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

Configurar sessão XEvent

Use o evento backup_restore_progress_trace estendido para registrar o progresso do backup da Instância Gerenciada SQL. Modifique as sessões do XEvent conforme necessário para acompanhar as informações em que você está interessado para sua empresa. Esses trechos do T-SQL armazenam as sessões XEvent no buffer de anel, mas também é possível gravar no Armazenamento de Blobs do Azure. As sessões XEvent que armazenam dados no buffer de anel têm um limite de cerca de 1.000 mensagens, portanto, só devem ser usadas para rastrear atividades recentes. Além disso, os dados do buffer de anel são perdidos após o failover. Como tal, para um registo histórico de backups, grave num arquivo de eventos.

Rastreamento básico

Configure uma sessão XEvent básica para capturar eventos sobre backups completos. Esse script coleta o nome do banco de dados, o número total de bytes processados e o tempo de conclusão do backup.

Use Transact-SQL (T-SQL) para configurar a sessão XEvent básica:

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;

Rastreamento detalhado

Configure uma sessão XEvent detalhada para rastrear mais detalhes sobre sua atividade de backup. Esse script captura o início e o fim de backups completos, diferenciais e de log. Como este script é mais verboso, ele preenche o buffer de anel de forma mais rápida, então as entradas podem ser recicladas mais rapidamente do que com o script básico.

Use o T-SQL para configurar a sessão XEvent detalhada:

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;

Monitorar o progresso do backup

Depois que a sessão XEvent é criada, você pode usar o T-SQL para consultar os resultados do buffer de anel e monitorar o progresso do backup. Depois que o XEvent é iniciado, ele coleta todos os eventos de backup para que as entradas sejam adicionadas à sessão aproximadamente a cada 5-10 minutos.

Rastreamento básico

O código T-SQL a seguir consulta a sessão XEvent básica e retorna o nome do banco de dados, o número total de bytes processados e a hora de conclusão do 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;

A captura de tela a seguir mostra um exemplo da saída da consulta anterior:

Neste exemplo, cinco bancos de dados foram copiados automaticamente ao longo de 2 horas e 30 minutos, e há 130 entradas na sessão XEvent.

Rastreamento detalhado

O código T-SQL a seguir consulta a sessão XEvent detalhada e retorna o nome do banco de dados, bem como o início e o término dos backups completos, diferenciais e de 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;

A captura de tela a seguir mostra um exemplo de um backup completo na sessão XEvent:

A captura de tela a seguir mostra um exemplo de uma saída de um backup diferencial na sessão XEvent:

Conteúdo relacionado

• Restaurar um banco de dados na Instância Gerenciada SQL do Azure para um point-in-time anterior
• Gerenciar a retenção de backup de longo prazo da Instância Gerenciada SQL do Azure
• Backups automatizados na Instância Gerenciada SQL do Azure