SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

AlwaysOn 可用性グループの推定データ損失時間をクエリで取得

leave a comment

AlwaysOn 可用性グループのダッシュボードでは、列を追加することで、プライマリレプリカとセカンダリレプリカ間のデータ損失の推定時間を取得することができます。

image

同期モードでプライマリ/セカンダリが起動している場合には、データ損失時間は原則として発生しませんが、非同期コミットモードを使用している / メンテナンスのためセカンダリレプリカが停止している場合などは、サーバー間でデータの更新状況に差が発生し、データ損失の可能性が出てきます。

この情報をクエリで取得するための方法をメモとして。

推定データ損失時間ですが、プライマリとセカンダリの最終のコミット時間の差を元に算出しているようです。
この情報は [sys.dm_hadr_database_replica_state] から取得することができます。

プライマリレプリカで以下のクエリを実行することで、サーバー間のコミット時間の差を抽出することができます。
# セカンダリが一台しかいない場合のクエリなので汎用性が高くないですが。

SET NOCOUNT ON
GO
DECLARE @PrimaryCommitTime datetime, @SecondaryCommitTime datetime

SELECT
	@PrimaryCommitTime = (
	SELECT 
		drs.last_commit_time
	FROM
		sys.dm_hadr_database_replica_States AS drs
		LEFT JOIN
		sys.dm_hadr_name_id_map AS nip
		ON
		nip.ag_id = drs.group_id
		LEFT JOIN
		sys.availability_replicas ar
		ON
		ar.replica_id = drs.replica_id
		LEFT JOIN
		sys.availability_databases_cluster adc
		ON
		adc.group_database_id = drs.group_database_id
	WHERE
			replica_server_name = 'ALWAYSON-SQL-01'
	),
	@SecondaryCommitTime = (
	SELECT 
		drs.last_commit_time
	FROM
		sys.dm_hadr_database_replica_States AS drs
		LEFT JOIN
		sys.dm_hadr_name_id_map AS nip
		ON
		nip.ag_id = drs.group_id
		LEFT JOIN
		sys.availability_replicas ar
		ON
		ar.replica_id = drs.replica_id
		LEFT JOIN
		sys.availability_databases_cluster adc
		ON
		adc.group_database_id = drs.group_database_id
	WHERE
			replica_server_name = 'ALWAYSON-SQL-02'
	)

SELECT
	@PrimaryCommitTime AS PrimaryCommitTime
	, @SecondaryCommitTime AS SecondaryCommitTime
	, DATEDIFF(ms, @SecondaryCommitTime, @PrimaryCommitTime) AS DIFF

WAITFOR DELAY '00:00:05'
GO 1000


実行すると以下のような結果を取得することができ、DIFF にミリ秒単位でコミット時間の差を取得することができます。

image

取得できるデータの傾向としては、パフォーマンスモニターの [Database Replica] の [Log Send Queue] と同等の情報が取得できるかと。

AlwaysOn 可用性グループを遠隔の拠点を利用して構築した場合、非同期モードで運用するのが一般的かと思います。

そのような場合には、データ損失時間がどの程度発生するかを把握して、定期的に損失の長い期間で、時間が発生している場合はアラートを上げるような運用も考慮しないといけないのかもしれないですね。

Written by masayuki.ozawa

9月 9th, 2014 at 9:00 am

Posted in SQL Server

Tagged with

Leave a Reply

*