SQL Server ミラーリング

SQL Server ミラーリングについて、サーバー間の通信を設定する概念を書いておきます。

まず、（A)発信側サーバーでの処理
１．マスターキーを作る（パスワードを設定）
　　CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass';
　　という感じ。
２．証明書を作成する。
　　CREATE CERTIFICATE　～
３．エンドポイントを作成する。（エンドポイントには２の証明書が関連付けする）
　　CREATE ENDPOINT Endpoint_Mirroring
     STATE = STARTED
     AS TCP (
      LISTENER_PORT=●●●●
      , LISTENER_IP = ALL
     )
     FOR DATABASE_MIRRORING (
        AUTHENTICATION = CERTIFICATE 証明書名
       , ENCRYPTION = REQUIRED ALGORITHM AES
       , ROLE = ALL
   );
このALGORITHMの「AES」の部分はサーバー全てで同じでなければならないらしい(?)
ミラーリング用のエンドポイントは一つしか作れないので、送受信の相互にこのエンドポイントを使う事になるのだと思います。このため、暗号化のアルゴリズムが同じ無ければならないのだと思いますが、本当なら送受信で別々のエンドポイントを作れるようにしておいた方がスマートだと思いますが、どうでしょうか？
何か理由があるのだと思いますが、どうもこの辺りの設計思想が分かりにくくて、設定時に悩んでしまいます。


４．証明書をファイルに書き出して、それを受信側サーバーに移しておく。

次に（B)受信サーバー側での処理です。
１．ログインを作成する（パスワードを設定）。
２．そのログイン に対してユーザーを作る。
３．発信側の証明書ファイルからログイン用の証明書を作る。
　　その時に２のユーザーに関連付けしておく。
４．エンドポイントにログインをGRANTしておく。

以上にて通信が行えるようになるはずです。
監視サーバーを設置する場合は、プリンシパル、ミラー、監視のそれぞれで相互に通信できるようにしなければなりませんので、以上のような処理をそれぞれのサーバーで行う事になります。

SQL Server ネタを

ミラーリング構成で、サーバー機の故障により再度ミラー構成にした時に手間取ったので、備忘録として書いておきます。

1. エンドポイントの状況を見るSQL
   SELECT * FROM sys.database_mirroring_endpoints ;
   ここで、encryption_algorithmが異なっていると通信できないとの事。
   
2.  certificateを調べるSQL
   SELECT * FROM sys.certificates;
   
3. ミラーリングの状態を見る
   SELECT
       DB_NAME(database_id) AS 'DatabaseName',
       mirroring_role_desc,
       mirroring_safety_level_desc,
       mirroring_state_desc,
       mirroring_safety_sequence,
       mirroring_role_sequence,
       mirroring_partner_instance,
       mirroring_witness_name,
       mirroring_witness_state_desc,
       mirroring_failover_lsn
   FROM sys.database_mirroring
   WHERE mirroring_guid IS NOT NULL;
   
4. エンドポイントのアクセス権を見る
   SELECT 'Metadata Check';
   SELECT EP.name, SP.STATE,
      CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
         AS GRANTOR,
      SP.TYPE AS PERMISSION,
      CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
         AS GRANTEE
      FROM sys.server_permissions SP , sys.endpoints EP
      WHERE SP.major_id = EP.endpoint_id
      ORDER BY Permission,grantor, grantee;
   GO