Monitorizar a atividade de cópias de segurança para a Azure SQL Managed Instance
Aplica-se a:
Instância Gerenciada SQL do Azure
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).
Descrição geral
O Azure SQL Managed Instance armazena informações de cópia de segurança na base de dados msdb e também emite eventos (também conhecido como Eventos Expandidos ou XEvents) durante a atividade de cópia de segurança com o objetivo de criar 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. Estas informações podem ser integradas no software de monitorização da cópia de segurança e também utilizadas para Auditorias Empresariais.
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 (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 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 o seu negócio. 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 1000 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 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 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 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;
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 esse script é mais detalhado, ele preenche o buffer de anel mais rapidamente, então as entradas podem reciclar mais rápido 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 o Transact-SQL (T-SQL) para consultar os resultados do buffer de anel e monitorar o progresso do backup. Quando 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 simples
O seguinte código Transact-SQL (T-SQL) consulta a sessão XEvent simples e retorna o nome do banco de dados, o número total de bytes processados e o tempo de conclusão do 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
A captura de tela a seguir mostra um exemplo da saída da consulta acima:
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 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 dos 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óximos passos
Quando o backup for concluído, você poderá restaurar para um point-in-time ou configurar uma política de retenção de longo prazo.
Para saber mais, consulte Backups automatizados.