Compartilhar via

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

Aplica-se a:Instância Gerenciada de SQL do Azure

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

Visão geral

A Instância Gerenciada de 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 pode ser usada para relatórios. Configure uma sessão XEvent para acompanhar informações como status de backup, tipo de backup, tamanho, tempo e local no banco de dados msdb. Essas informações podem ser integradas ao software de monitoramento de backup e também usadas para Auditoria Empresarial.

As Auditorias Empresariais podem exigir a prova de backups bem-sucedidos, o tempo de backup e a duração do backup.

Consultar o banco de dados msdb

Para exibir a atividade de backup, execute a seguinte consulta no 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

Observação

Ao consultar msdb tabelas do sistema como dbo.backupmediaset ou dbo.backupset, você vê campos relacionados à criptografia indicando que os arquivos de backup não são criptografados. Esse status reflete apenas a criptografia no nível do mecanismo. Todos os backups automáticos são criptografados em repouso.

Configurar a sessão XEvent

Use o evento estendido backup_restore_progress_trace para registrar o progresso do back-up da Instância Gerenciada de SQL. Modifique as sessões XEvent conforme necessário para acompanhar as informações interessantes para sua empresa. Esses snippets T-SQL armazenam as sessões XEvent no buffer de anéis, mas também é possível gravar no Armazenamento de Blobs do Azure. As sessões XEvent que armazenam dados no buffer de anéis têm um limite de cerca de 1.000 mensagens, portanto, só devem ser usadas para acompanhar a atividade recente. Além disso, os dados do buffer de anéis são perdidos após o failover. Dessa forma, para um registro histórico de backups, grave em um arquivo de eventos.

Acompanhamento básico

Configure uma sessão básica do XEvent para capturar eventos sobre backups completos. Esse script coleta o nome do banco de dados, o número total de bytes processados e a hora em que o backup foi concluído.

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

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;

Acompanhamento detalhado

Configure uma sessão XEvent detalhada para acompanhar mais detalhes sobre a atividade de backup. Esse script captura o início e o fim dos backups completos, diferenciais e de log. Como esse script é mais detalhado, ele preenche o buffer de anel mais rapidamente, portanto, as entradas podem ser recicladas mais rapidamente do que com o script básico.

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

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 for criada, você poderá 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 a 10 minutos.

Acompanhamento 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 em que o backup foi concluído:

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:

Captura de tela da saída do XEvent.

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

Acompanhamento detalhado

O código T-SQL a seguir consulta a sessão verbosa do XEvent e retorna o nome do banco de dados, bem como o início e o fim 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:

Captura de tela da saída do XEvent mostrando backups completos.

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

Captura de tela da saída do XEvent que mostra backups diferenciais.

Conteúdo relacionado