ミラーリングの状態や設定をSQL文で確認する方法

SQL Server 2005 SQL Server 2008

SQL Server 2005 や、2008 で構築したミラーリングの状態は、「データベース ミラーリング モニタ」で表示することが出来ますが、SQL文を記述することでも取得できるようです。

定期的に起動するバッチで状態をメールし、監視する際の参考にメモ。


ミラーリングの状態や、エンドポイントの状態、証明書の設定などは、カタログビューから取得できるようです。
以降では、カタログビューを使用した内容になっています。

ミラーリングの状態

まずは、ミラーリングの状態から。

sys.database_mirroring のビューから、「データベース ミラーリング モニタ」の「現在のロール」、「ミラー化の状態」、「ミラーリング監視接続」が取得できるようです。

SELECT
     DB_NAME(database_id) [DBName]
    ,mirroring_role_desc
    ,mirroring_state_desc
    ,mirroring_witness_state_desc
FROM
    sys.database_mirroring
WHERE
    mirroring_guid IS NOT NULL


抽出項目の詳細は、参考URLを参照していただくと分かるかと思いますが、列名と説明を一部抜粋しました。

列名 説明 コメント
database_id データベースの ID DB_NAME関数でデータベース名に変換しています
mirroring_role_desc ミラー化におけるローカル データベースのロール 「データベース ミラーリング モニタ」の「現在のロール」に相当
mirroring_state_desc ミラー データベースとデータベース ミラーリング セッションの状態 「データベース ミラーリング モニタ」の「ミラー化の状態」に相当
mirroring_witness_state_desc 状態 「データベース ミラーリング モニタ」の「ミラーリング監視接続」に相当


このSQL文を、TESTDB というデータベースをミラー化した、プリンシパルとミラーのサーバに対して実行すると、以下の様に返ってきます。

DBName mirroring_role_desc mirroring_state_desc mirroring_witness_state_desc
TESTDB PRINCIPAL SYNCHRONIZED CONNECTED
  • ミラー
DBName mirroring_role_desc mirroring_state_desc mirroring_witness_state_desc
TESTDB MIRROR SYNCHRONIZED CONNECTED
参考URL

監視サーバ

監視サーバからは、sys.database_mirroring_witnesses で、監視サーバーのロール毎の状態を取得できますが、どのサーバがプリンシパルになっているかを取得する程度が良いかも。

SQL Server 2008 からは、セッションの同期状態の説明が取得できるようになったようです。

なお、前述の sys.database_mirroring にはバージョンによる項目有無の差異は確認できなかったのですが、sys.database_mirroring_witnesses には差異がありましたので、項目比較表を作ってみました。

  • sys.database_mirroring_witnesses の項目比較
列名 2005 2008 2008 R2
database_name ○ ○ ○
principal_server_name ○ ○ ○
mirror_server_name ○ ○ ○
safety_level ○ ○ ○
safety_level_desc ○ ○ ○
safety_sequence_number ○ ○ ○
role_sequence_number ○ ○ ○
mirroring_guid ○ ○ ○
family_guid ○ ○ ○
is_suspended ○ ○ ○
is_suspended_sequence_number ○ ○ ○
partner_sync_state × ○ ○

この、partner_sync_state なんですが、手元の環境だと IN_SYNC なんです。MSDNの説明にない・・・。

そんなわけで、信用できそうな項目と、確認に必要そうな項目でSQL文を作成してみました。

SELECT
     database_name
    ,principal_server_name
    ,mirror_server_name
FROM
    sys.database_mirroring_witnesses


実行すると、こんな感じに返ってきます。

database_name principal_server_name mirror_server_name
TESTDB TCP://DBSERVER1:7024 TCP://DBSERVER2:7024
参考URL

エンドポイント

稼動してしまえば、監視する必要は無いかとは思いますが、状態を取得できるようなので、エントリーします。

以下は、エンドポイントを TCP/IP で設定した際の確認用SQL文の例です。

SELECT
     DME.name
    ,DME.protocol_desc
    ,DME.state_desc
    ,DME.role_desc
    ,DME.connection_auth_desc
    ,DME.encryption_algorithm_desc
    ,TE.port
    ,TE.ip_address
FROM
    sys.database_mirroring_endpoints AS DME
INNER JOIN
    sys.tcp_endpoints AS TE
ON  TE.endpoint_id = DME.endpoint_id

エンドポイントを作成する際に設定する項目を抽出するようにしてみました。
MSDNにあるCREATE文

CREATE ENDPOINT endpoint_mirroring
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (
       AUTHENTICATION = WINDOWS KERBEROS,
       ENCRYPTION = SUPPORTED,
       ROLE=ALL);
GO

を意識しています。（CREATE文引用：CREATE ENDPOINT (Transact-SQL) - SQL Server | Microsoft Docs）

実行するとこんな感じです。（上記CREATE文とは違う内容でエンドポイントを作成したので、相違があります）

name protocol_desc state_desc role_desc connection_auth_desc encryption_algorithm_desc port ip_address
endpoint_mirroring TCP STARTED ALL CERTIFICATE AES 7024 NULL
参考URL

証明書

ワークグループ(ドメインサーバが無い)環境でミラーリングを構築する際に必要となる証明書についても、確認用のSQL文を考えてみました。

有効期限が切れてしまうと問題になるかと思うので、設定後の証明書を確認する際に使用。
また、どのユーザ、ログインに紐付けたのかを確認できるようにしてみました。

SELECT
     CRT.name
    ,CRT.subject
    ,CRT.pvt_key_encryption_type_desc
    ,CRT.start_date
    ,CRT.expiry_date
    ,DP.name AS [USER_NAME]
    ,DP.type_desc AS USER_TYPE
    ,SL.name AS LOGIN_NAME
    ,SL.type_desc AS [LOGIN_TYPE]
FROM
    sys.certificates AS CRT
INNER JOIN
    sys.database_principals AS DP
ON  DP.principal_id = CRT.principal_id
INNER JOIN
    sys.sql_logins AS SL
ON  SL.sid = DP.sid