적용 대상:
Azure SQL Managed Instance
이 문서에서는 데이터베이스를 쿼리하거나 확장 이벤트(XEvent) 세션을 구성하여 msdb에 대한 백업 작업을 모니터링하는 방법을 설명합니다.
개요
Azure SQL Managed Instance는 msdb 데이터베이스 에 백업 정보를 저장하고, 보고에 사용할 수 있는 백업 작업 중에 이벤트(확장 이벤트 또는 XEvents라고도 함)를 내보냅니다. 백업 상태, 백업 유형, 크기, 시간 및 msdb 데이터베이스 내의 위치와 같은 정보를 추적하도록 XEvent 세션을 구성합니다. 이 정보는 백업 모니터링 소프트웨어와 통합될 수 있으며 엔터프라이즈 감사에도 사용할 수 있습니다.
엔터프라이즈 감사에는 성공적인 백업 증명, 백업 시간 및 백업 기간이 필요할 수 있습니다.
msdb 데이터베이스 쿼리
백업 활동을 보려면 사용자 정의 데이터베이스에서 다음 쿼리를 실행합니다.
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
비고
시스템 테이블(예: dbo.backupmediaset 또는dbo.backupset)을 쿼리할 msdb 때 백업 파일이 암호화되지 않았음을 나타내는 암호화 관련 필드가 표시됩니다. 이 상태는 엔진 수준 암호화만 반영합니다. 모든 자동 백업 은 미사용 시 암호화됩니다.
XEvent 세션 구성
확장 이벤트 backup_restore_progress_trace를 사용하여 SQL Managed Instance 백업의 진행 상황을 기록합니다. 필요에 따라 XEvent 세션을 수정하여 비즈니스에 관심이 있는 정보를 추적합니다. 이러한 T-SQL 조각은 XEvent 세션을 링 버퍼에 저장하지만 Azure Blob Storage에 쓸 수도 있습니다. 링 버퍼에 데이터를 저장하는 XEvent 세션은 약 1,000개의 메시지로 제한되므로 최근 활동을 추적하는 데만 사용해야 합니다. 또한 장애 조치(failover) 시 링 버퍼 데이터가 손실됩니다. 따라서 백업 기록 레코드의 경우 대신 이벤트 파일에 씁니다.
기본 추적
전체 백업에 대한 이벤트를 캡처하도록 기본 XEvent 세션을 구성합니다. 이 스크립트는 데이터베이스 이름, 처리된 총 바이트 수 및 백업이 완료된 시간을 수집합니다.
Transact-SQL(T-SQL)를 사용하여 기본 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;
자세한 추적
자세한 XEvent 세션을 구성하여 백업 작업에 대한 자세한 정보를 추적합니다. 이 스크립트는 전체, 차등 및 로그 백업의 시작과 완료를 캡처합니다. 이 스크립트는 더 자세한 정보이므로 링 버퍼를 더 빠르게 채우므로 항목이 기본 스크립트보다 더 빠르게 재활용될 수 있습니다.
T-SQL을 사용하여 상세한 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;
백업 진행률 모니터링
XEvent 세션을 만든 후 T-SQL을 사용하여 링 버퍼 결과를 쿼리하고 백업 진행률을 모니터링할 수 있습니다. XEvent가 시작되면 모든 백업 이벤트를 수집하므로 약 5~10분마다 항목이 세션에 추가됩니다.
기본 추적
다음 T-SQL 코드는 기본 XEvent 세션을 쿼리하고 데이터베이스 이름, 처리된 총 바이트 수 및 백업이 완료된 시간을 반환합니다.
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;
다음 스크린샷은 이전 쿼리의 출력 예제를 보여줍니다.
이 예제에서는 2시간 30분 동안 5개의 데이터베이스가 자동으로 백업되었으며 XEvent 세션에는 130개의 항목이 있습니다.
자세한 추적
다음 T-SQL 코드는 자세한 XEvent 세션을 쿼리하고 데이터베이스 이름과 전체, 차등 및 로그 백업의 시작 및 완료를 반환합니다.
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;
다음 스크린샷은 XEvent 세션에서 전체 백업의 예제를 보여줍니다.
다음 스크린샷은 XEvent 세션에서 차등 백업의 출력 예제를 보여줍니다.
