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

適用対象: Azure SQL Managed Instance

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

概要

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

Enterprise 監査には、バックアップが正常に終了したことの証明、バックアップの時刻、およびバックアップ操作の期間が必要とされる場合があります。

msdb データベースのクエリを実行する

バックアップ アクティビティを表示するには、ユーザー定義データベースから次のクエリを実行します:

SQL

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

単純な追跡

完了した完全バックアップに関する単純なイベントを収集するには、単純な XEvent セッションを構成します。 このスクリプトを使用すると、データベースの名前、処理された合計バイト数、バックアップが完了した時刻が収集されます。

Transact-SQL (T SQL) を使用して単純な XEvent セッションを構成します。

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 セッションを構成します。 このスクリプトを使用すると、完全と差分の両方およびログのバックアップの開始と終了が収集されます。 このスクリプトはより詳細なので、リング バッファーがより短時間でいっぱいになります。そのため、単純なスクリプトよりも短時間でエントリがリサイクルされる可能性があります。

Transact-SQL (T SQL) を使用して詳細な XEvent セッションを構成します。

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 セッションに対してクエリを実行し、データベースの名前、処理された合計バイト数、バックアップが完了した時刻を返すものです。

SQL

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 分の間に 5 つのデータベースが自動的にバックアップされ、XEvent セッションに 130 個のエントリがあります。

詳細な追跡

次の Transact-SQL (T-SQL) コードは、詳細な XEvent セッションに対してクエリを実行し、データベースの名前のほか、完全と差分の両方およびログのバックアップの開始と終了を返すものです。

SQL

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 セッションでの差分バックアップの出力例を示しています。

次のステップ

バックアップが完了したら、特定の時点に復元することや、長期保有ポリシーを構成することができます。

詳細については、自動バックアップに関するページを参照してください。