データベースミラーリング接続の構成と状態を出力する：SQL Server動的管理ビューレファレンス（22）

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、データベースミラーリング接続の構成と状態の出力について解説します。

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は「sys.dm_db_mirroring_connections」の、データベースミラーリング接続の構成と状態の出力について解説します。対応バージョンは、SQL Server 2008以降です。

概要

　データベースミラーリングでは、プリンシパルサーバとミラーサーバ、ミラーリング監視サーバ（構成した場合）の間で接続を行い、変更の同期や状態の監視などの通信を相互に行っています。

　「sys.dm_db_mirroring_connections」動的管理ビューを使用することで、データベースミラーリングのプリンシパルサーバとミラーサーバ、ミラーリング監視サーバ間で作成される接続について、構成や状態、通信状況などを確認できます。

• sys.dm_db_mirroring_connections (Transact-SQL)

出力内容

列名	データ型	説明
connection_id	uniqueidentifier	接続の識別子
transport_stream_id	uniqueidentifier	この接続で使用されるネットワークインタフェース(SNI) の識別子
state	smallint	接続の現在の状態 　1 = NEW 　2 = CONNECTING 　3 = CONNECTED 　4 = LOGGED_IN 　5 = CLOSED
state_desc	nvarchar(60)	stateの説明
connect_time	datetime	接続日時
login_time	datetime	接続先へのログインに成功した日時
authentication_method	nvarchar(128)	NTLM、KerberosなどのWindows認証方法の名前
principal_name	nvarchar(128)	接続権限が検証されるログイン名
remote_user_name	nvarchar(128)	接続先の認証で使用されるリモートユーザー名
last_activity_time	datetime	情報の送受信に接続が使用された最後の日時
is_accept	bit	接続がリモート側で生成されたかどうか 　1 = リモートインスタンスから要求された接続 　0 = ローカルインスタンスで開始された接続
login_state	smallint	接続のログインプロセスの状態 　0 = INITIAL 　1 = WAIT LOGIN NEGOTIATE 　2 = ONE ISC 　3 = ONE ASC 　4 = TWO ISC 　5 = TWO ASC 　6 = WAIT ISC Confirm 　7 = WAIT ASC Confirm 　8 = WAIT REJECT 　9 = WAIT PRE-MASTER SECRET 　10 = WAIT VALIDATION 　11 = WAIT ARBITRATION 　12 = ONLINE 　13 = ERROR
login_state_desc	nvarchar(60)	login_stateの説明
peer_certificate_id	int	リモートインスタンスで使用する認証用証明書のローカルのオブジェクトID
encryption_algorithm	smallint	接続で使用される暗号化アルゴリズム 　0 = なし 　1 = RC4 　2 = AES 　3 = なし、RC4 　4 = なし、AES 　5 = RC4、AES 　6 = AES、RC4 　7 = なし、RC4、AES 　8 = なし、AES、RC4
encryption_algorithm_desc	nvarchar(60)	encryption_algorithmの説明
receives_posted	smallint	完了していない非同期ネットワーク受信の数
is_receive_flow_controlled	bit	ネットワークがビジー状態のときに行われたフロー制御が原因で、ネットワーク受信が延期されたかどうか 　1 = True
sends_posted	smallint	完了していない非同期ネットワーク送信の数
is_send_flow_controlled	bit	ネットワークがビジー状態のときに行われたフロー制御が原因で、ネットワーク送信が延期されたかどうか 　1 = True
total_bytes_sent	bigint	この接続で送信された合計バイト数
total_bytes_received	bigint	この接続で受信された合計バイト数
total_fragments_sent	bigint	この接続で送信されたデータベースミラーリングメッセージフラグメントの合計数
total_fragments_received	bigint	この接続で受信されたデータベースミラーリングメッセージフラグメントの合計数
total_sends	bigint	この接続で送信された要求数
total_receives	bigint	この接続で受信された要求数
peer_arbitration_id	uniqueidentifier	エンドポイントの内部識別子

動作例

　同期コミットのデータベースミラーリングを1つのデータベースで有効化し、ミラーリング監視も構成しました（図1）。

図1　ミラーリング監視を含めたデータベースミラーリングを構成した

　プリンシパルサーバに接続して「sys.dm_db_mirroring_connections」動的管理ビューを出力します。「sys.dm_db_mirroring_connections」動的管理ビューには、接続先サーバを識別する情報が含まれていませんので、接続に関する情報を出力できる「sys.dm_exec_connections」動的管理ビューと組み合わせました（図2）。

図2　プリンシパルサーバで出力すると4つの接続が出力される

　合計4つの接続が出力されました。「sys.dm_exec_connections」動的管理ビューの「client_net_address」列の値から、ミラーサーバとの接続が2つ、ミラーリング監視サーバとの接続が2つ存在することが分かります。「is_accept」列の値や「client_tcp_port」列、「local_tcp_port」列で確認できるポート番号から、データベースミラーリングを構成する他のサーバのミラーリングエンドポイントへの接続と、他のサーバからこのサーバのミラーリングエンドポイントへの接続の、双方の接続が使用されることが分かります。「sys.dm_db_mirroring_connections」動的管理ビューの「total_bytes_sent」列、「total_bytes_received」列の値からは、送受信されたデータサイズを確認できます。

　ミラーサーバに接続して、「sys.dm_db_mirroring_connections」動的管理ビューを出力します（図3）。

図3　ミラーサーバで出力しても4つの接続が出力される

　ミラーサーバでもプリンシパルサーバと同様に4つの接続が出力されました。ミラーサーバとミラーリング監視サーバとの間の通信は、プリンシパルサーバで動的管理ビューを出力した時には表示されませんでしたので、この動的管理ビューの出力対象は、動的管理ビューを出力したサーバで行われている接続のみのようです。

　ミラーリング監視サーバに接続して、「sys.dm_db_mirroring_connections」動的管理ビューを出力します（図4）。

図4　ミラーリング監視サーバで出力しても4つの行が出力される

　プリンシパルサーバやミラーサーバと同様に、ミラーリング監視サーバでも4つの接続が出力されました。

　次に、ミラーサーバを停止します。プリンシパルサーバに接続して「sys.dm_db_mirroring_connections」動的管理ビューを出力したところ、停止したミラーサーバとの接続は表示されず、ミラーリング監視サーバとの2つの接続のみが出力されました。切断された接続についての情報は、表示されないようです（図5）。

図5　ミラーサーバを停止すると、切断された接続の情報は表示されなくなった

※本Tipsは、「Windows Server 2019」上に「SQL Server 2019 CTP2」をインストールした環境を想定して解説しています。

筆者紹介

椎名 武史（しいな たけし）

日本ユニシス株式会社所属。Microsoft MVP for Data Platform（2017〜）。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。

伊東 敏章（いとう としあき）

日本ユニシス株式会社所属。入社以来SQL Server一筋で評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。社内のプログラミングコンテストで4回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。