Supervisión de la actividad de copia de seguridad para Azure SQL Managed Instance

Artículo
04/26/2023

Se aplica a: Azure SQL Managed Instance

En este artículo se explica cómo supervisar la actividad de copia de seguridad para Azure SQL Managed Instance consultando la base de datos msdb o configurando sesiones de eventos extendidas (XEvent).

Información general

Azure SQL Managed Instance almacena información de copia de seguridad en la base de datos msdb y emite eventos (también conocidos como eventos extendidos o XEvents) durante la actividad de copia de seguridad con el fin de crear informes. Configure una sesión de XEvent para realizar un seguimiento de información como el estado de la copia de seguridad, el tipo de copia de seguridad, el tamaño, la hora y la ubicación en la base de datos msdb. Esta información se puede integrar con el software de supervisión de copia de seguridad y también se puede usar para las auditorías de la empresa.

Las auditorías de la empresa pueden requerir una evidencia de copias de seguridad correctas, la hora de copia de seguridad y la duración de la copia de seguridad.

Consulta de la base de datos msdb

Para ver la actividad de copia de seguridad, ejecute la siguiente consulta desde la base de datos definida por el usuario:

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);

Configuración de la sesión de XEvent

Use el evento extendido backup_restore_progress_trace para registrar el progreso de la copia de seguridad de SQL Managed Instance. Modifique las sesiones de XEvent según sea necesario para realizar un seguimiento de la información que le interesa para su empresa. Estos fragmentos de código T-SQL almacenan las sesiones de XEvent en el búfer en anillo, pero también es posible escribir en Azure Blob Storage. Las sesiones de XEvent que almacenan datos en el búfer en anillo tienen un límite de aproximadamente 1000 mensajes, por lo que solo se deben usar para realizar un seguimiento de la actividad reciente. Además, los datos del búfer en anillo se pierden tras la conmutación por error. Por lo tanto, para un registro histórico de copias de seguridad, escriba en un archivo de eventos en su lugar.

Seguimiento simple

Configure una sesión de XEvent simple para capturar eventos simples sobre las copias de seguridad completas. Este script recopila el nombre de la base de datos, el número total de bytes procesados y la hora en la que se completó la copia de seguridad.

Use Transact-SQL (T-SQL) para configurar la sesión de XEvent simple:

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;

Seguimiento detallado

Configure una sesión de XEvent detallada para realizar un seguimiento más detallado de la actividad de copia de seguridad. Este script captura el inicio y el final de las copias de seguridad completas, diferenciales y de registros. Dado que este script es más detallado, rellena el búfer en anillo más rápido, por lo que las entradas se pueden reciclar más rápido que con el script simple.

Use Transact-SQL (T-SQL) para configurar la sesión de XEvent detallada:

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;

Supervisión del progreso de la copia de seguridad

Una vez creada la sesión de XEvent, puede usar Transact-SQL (T-SQL) para consultar los resultados del búfer en anillo y supervisar el progreso de la copia de seguridad. Una vez que se inicia XEvent, recopila todos los eventos de copia de seguridad, por lo que se agregan entradas a la sesión aproximadamente cada 5-10 minutos.

Seguimiento simple

El siguiente código de Transact-SQL (T-SQL) consulta la sesión de XEvent simple y devuelve el nombre de la base de datos, el número total de bytes procesados y la hora en la que se completó la copia de seguridad:

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

La captura de pantalla siguiente muestra un ejemplo de la salida de la consulta anterior:

Captura de pantalla de la salida de xEvent

En este ejemplo, se ha realizado una copia de seguridad automática de cinco bases de datos en el transcurso de 2 horas y 30 minutos, y hay 130 entradas en la sesión de XEvent.

Seguimiento detallado

El siguiente código Transact-SQL (T-SQL) consulta la sesión de XEvent detallada y devuelve el nombre de la base de datos, así como el inicio y el final de las copias de seguridad completas, diferenciales y de registros.

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

En la captura de pantalla siguiente, se muestra un ejemplo de una copia de seguridad completa en la sesión de XEvent:

Salida de XEvent que muestra copias de seguridad completas