Отслеживание операций резервного копирования в Управляемом экземпляре SQL Azure

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

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

Обзор

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

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

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

Чтобы запросить журнал резервного копирования из msdb, необходимо получить доступ к базе данных msdb в управляемом экземпляре SQL. Например, вы можете получить доступ, являясь членом роли, которая предоставляет SELECT для msdb.dbo.backupset и msdb.dbo.backupmediafamily.

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

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

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

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

Чтобы создать и запустить сеанс XEvent, необходимо иметь разрешение ALTER ANY EVENT SESSION на управляемом экземпляре.

В следующей таблице приведены два сеанса, описанные в этой статье:

Настройка базового отслеживания

Настройте базовый сеанс 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;

Чтобы убедиться, что сеанс запущен, выполните запрос к sys.dm_xe_sessions для Basic backup trace.

Настройте подробное отслеживание

Настройте сеанс 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;

Чтобы подтвердить выполнение сеанса, выполните запросsys.dm_xe_sessions.Verbose backup trace

Мониторинг хода резервного копирования

После создания сеанса XEvent можно использовать T-SQL для запроса результатов буфера кольца и мониторинга хода выполнения резервной копии. После запуска XEvent собирает все события резервного копирования, и записи добавляются в сеанс примерно каждые 5–10 минут.

Запросить базовый сеанс отслеживания

Следующий код T-SQL запрашивает Basic backup trace сеанс и возвращает имя базы данных, общее количество обработанных байтов и время завершения резервной копии:

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 = 'Basic 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;

На следующем снимке экрана показан пример результата запроса Basic backup trace:

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

Запросить подробный сеанс отслеживания

Следующий код T-SQL запрашивает Verbose backup trace сеанс и возвращает имя базы данных, а также начало и завершение полной, разностной и резервной копии журналов.

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:

Связанный контент

• Восстановить базу данных в Управляемом экземпляре SQL Azure до предыдущего момента времени
• Управление долгосрочным хранением резервных копий Управляемого экземпляра SQL Azure
• Автоматическое резервное копирование в управляемом экземпляре Azure SQL