Мониторинг резервного копирования для Управляемого экземпляра SQL Azure

Область применения: Управляемый экземпляр SQL Azure

В этой статье описано, как отслеживать действия резервного msdb копирования для Управляемый экземпляр SQL Azure путем запроса базы данных или настройки сеансов расширенных событий (XEvent).

Обзор

Управляемый экземпляр SQL Azure хранит сведения о резервном копировании в базе данных msdb и генерирует события (также называемые расширенными событиями, или XEvents) во время резервного копирования для формирования отчетов. Настройте сеанс XEvent для отслеживания таких сведений, как состояние резервного копирования, тип резервного копирования, размер, время и расположение в msdb базе данных. Эти сведения можно интегрировать с ПО для мониторинга резервного копирования, а также использовать для корпоративного аудита.

Для корпоративного аудита может потребоваться подтверждение успешности резервного копирования, его времени и длительности.

Запрос базы данных msdb

Чтобы просмотреть действие резервного копирования, выполните следующий запрос из пользовательской базы данных:

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

Настройка сеанса XEvent

Для записи хода резервного копирования Управляемого экземпляра SQL используйте расширенное событие backup_restore_progress_trace. При необходимости изменяйте сеансы XEvent таким образом, чтобы отслеживать интересующие вас сведения. Эти фрагменты кода T-SQL сохраняют сеансы XEvent в кольцевом буфере, но можно также записывать данные в Хранилище BLOB-объектов Azure. Для сеансов XEvent, которые хранят данные в кольцевом буфере, установлено ограничение в 1000 сообщений, поэтому их следует использовать только для наблюдения за недавними действиями. Кроме того, при отработке отказа данные кольцевого буфера теряются. Таким образом, для ведения истории резервного копирования данные следует записывать в файл событий.

Простое отслеживание

Вы можете настроить простой сеанс XEvent для записи простых событий полного резервного копирования. Этот скрипт собирает имя базы данных, общее количество обработанных байтов и время завершения резервного копирования.

Для настройки простого сеанса XEvent используйте следующий код Transact-SQL (T-SQL):

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;

Подробное отслеживание

Вы можете настроить подробный сеанс XEvent для отслеживания подробных сведений о резервном копировании. Этот скрипт фиксирует начало и окончание полного и разностного резервного копирования, а также архивации журналов. Поскольку этот скрипт более подробный, он заполняет кольцевой буфер быстрее, поэтому записи могут перезаписываться быстрее, чем в случае простого скрипта.

Для настройки подробного сеанса XEvent используйте следующий код Transact-SQL (T-SQL):

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 запрашивать результаты в кольцевом буфере и отслеживать ход выполнения резервного копирования можно с помощью Transact-SQL (T-SQL). После запуска XEvent собирает все события резервного копирования, и записи добавляются в сеанс примерно каждые 5–10 минут.

Простое отслеживание

Следующий код Transact-SQL (T-SQL) запрашивает простой сеанс XEvent и возвращает имя базы данных, общее число обработанных байтов и время завершения резервного копирования:

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

На следующем снимке экрана показан пример выходных данных приведенного выше запроса:

В этом примере выполняется автоматическое резервное копирование пяти баз данных в течение 2 часов и 30 минут, а в сеансе XEvent — 130 записей.

Подробное отслеживание

Следующий код Transact-SQL (T-SQL) запрашивает подробный сеанс XEvent и возвращает имя базы данных, время начала и окончания полного и разностного резервного копирования, а также архивации журналов.

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

На следующем снимке экрана показан пример полного резервного копирования в сеансе XEvent:

На следующем снимке экрана показан пример выходных данных разностного резервного копирования в сеансе XEvent:

Следующие шаги

После завершения резервного копирования вы можете восстановить данные на момент времени или настроить политику долгосрочного хранения.

Дополнительные сведения см. в статье об автоматическом резервном копировании.