Nota:
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
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 también emite eventos (también conocidos como eventos extendidos o XEvents) durante la actividad de copia de seguridad, que se puede usar para la creación de 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 copias de seguridad y también se puede usar para Auditoría empresarial.
Las auditorías empresariales pueden requerir una prueba de las copias de seguridad correctas, el tiempo 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 (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
Nota:
Al consultar msdb tablas del sistema como dbo.backupmediaset o dbo.backupset, verá campos relacionados con el cifrado que indican que los archivos de copia de seguridad no están cifrados. Este estado solo refleja el cifrado de nivel de motor. Todas las copias de seguridad automáticas se cifran en reposo.
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 XEvent que almacenan datos en el búfer de anillo tienen un límite de aproximadamente 1000 mensajes, por lo que solo se debe 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 básico
Configure una sesión XEvent básica para capturar eventos 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 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;
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 verboso, llena el búfer de anillo más rápido, por lo que las entradas pueden reciclarse más rápido que con el script básico.
Use 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 XEvent, puede usar T-SQL para consultar los resultados del búfer del 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 básico
El siguiente código T-SQL consulta la sesión XEvent básica y devuelve el nombre de la base de datos, el número total de bytes procesados y la hora en que se completó la copia de seguridad:
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;
En la captura de pantalla siguiente se muestra un ejemplo de la salida de la consulta anterior:
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 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 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;
En la captura de pantalla siguiente, se muestra un ejemplo de una copia de seguridad completa en la sesión de XEvent:
En la captura de pantalla siguiente, se muestra un ejemplo de una salida de una copia de seguridad diferencial en la sesión de XEvent:
