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 as 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 para fins de relatório. 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 fins de auditoria empresarial.

As auditorias empresariais podem exigir a prova de backups bem-sucedidos, a hora 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 (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);

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

Rastreamento simples

Configure uma sessão XEvent simples para capturar eventos simples sobre backups completos concluídos. 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 XEvent simples:

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;

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 anéis mais rapidamente, portanto, as entradas podem ser recicladas mais rapidamente do que com o script simples.

Use Transact-SQL (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 for criada, você poderá usar Transact-SQL (T-SQL) para consultar os resultados do buffer de anéis 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.

Rastreamento simples

O código Transact-SQL (T-SQL) a seguir consulta a sessão XEvent simples 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 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

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

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 Transact-SQL (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 fim de backups completos, diferenciais e de 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

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:

Próximas etapas

Depois que o backup tiver sido concluído, será possível restaurar para um ponto no tempo ou configurar uma política de retenção de longo prazo.

Para saber mais, confira Backups automatizados.