Azure SQL Managed Instance のバックアップ アクティビティの監視

適用対象:Azure SQL Managed Instance

この記事では、msdb データベースのクエリを実行するか、拡張イベント (XEvent) セッションを構成することによって、Azure SQL Managed Instance のバックアップ アクティビティを監視する方法について説明します。

概要

Azure SQL Managed Instance は 、msdb データベース にバックアップ情報を格納し、バックアップ アクティビティ中にイベント ( 拡張イベントまたは XEvents とも呼ばれます) を出力します。これはレポートに使用できます。 XEvent セッションを構成して、バックアップの状態、バックアップの種類、サイズ、時刻、 msdb データベース内の場所などの情報を追跡します。 この情報は、バックアップ監視ソフトウェアと統合でき、Enterprise Audit にも使用できます。

エンタープライズ監査では、バックアップの成功、バックアップの時刻、バックアップの期間の証明が必要になる場合があります。

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

XEvent セッションを構成する

SQL Managed Instance バックアップの進行状況を記録するには、拡張イベント backup_restore_progress_trace を使用します。 必要に応じて XEvent セッションを変更し、ビジネスのために関心のある情報を追跡します。 これらの T-SQL スニペットを使用すると、XEvent セッションがリング バッファーに格納されますが、Azure Blob Storage に書き込むこともできます。 リング バッファーにデータを格納する XEvent セッションには約 1,000 メッセージの制限があるため、最近のアクティビティの追跡にのみ使用する必要があります。 さらに、リング バッファー データはフェールオーバー時に失われます。 そのため、バックアップの履歴レコードの場合は、代わりにイベント ファイルに書き込みます。

基本的な追跡

完全バックアップの完了に関するイベントをキャプチャする基本的な 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 セッションでの差分バックアップの出力例を示しています。

関連コンテンツ

• Azure SQL Managed Instance のデータベースを以前の時点に復元する
• Azure SQL Managed Instance の長期的なバックアップ保有期間を管理する
• Azure SQL Managed Instance での自動バックアップ