SQL ServerでのミラーリングTransact-SQL設定メモ（自動フェールオーバーを伴う高い安全性モード＋SQL Server認証）

このところSQL Serverでレプリケーションしてみたりログ配布してみたりミラーリングしてみたりしている。レプリケーションとログ配布はそれほど面倒な設定は要らないのだが、ミラーリングは結構面倒。特にドメインに参加したWindows認証ではなくSQL Server認証をベースとしている場合は各サーバ間で証明書をやりとりしたり、ログインアカウントのSIDを気にしたりしなければいけない。
以下に「自動フェールオーバーを伴う高い安全性モード（完全同期で動作）」かつ「SQL Server認証ベース」の設定例を示す。
基本的にはここに記した内容を各サーバ上でズラズラと実行していけばTransact-SQLのみでミラーリングの設定ができる。
環境は以下のものを想定。
　・プリンシパルサーバ：SQL Server 2008 Standard Edition
　・ミラーサーバ：SQL Server 2008 Standard Edition
　・ミラーリング監視サーバ：SQL Server 2008 Express Edition
Expressは無料、Active-Standby構成の場合はActiveのみのライセンスで良かったはず。監視以外に3台以上使いたい場合はEnterpriseが必要。監視サーバは貧弱でも構わないし、適当なタイミングで落とすことも可能。ただし自動フェイルオーバは「ミラーサーバと監視サーバ間は相互に接続OK」「ミラーサーバからプリンシパルサーバへの接続NG」「監視サーバからプリンシパルサーバへの接続NG」という条件でないと発生しないため注意が必要。
Javaの場合でもMicrosoftのJDBCドライバを使うとサーバ側のフェイルオーバに合わせてクライアント側もシレッと切り替わる。通常はプリンシパルに接続した際にフェイルオーバ先の情報が送られてくるが、初回接続時にいきなり落ちていた場合用に「failoverPartner」も設定しておくこと。ただ、この機構は基本的にインスタンス名（＝ポート番号ではない）を扱うため、SQL Server Browserサービスも起動しておく必要がある。

プリンシパルサーバ

・DB切り替え

USE master;
GO

・データベース「bktest」の作成。

CREATE DATABASE bktest
ON PRIMARY (
  NAME = 'bktest',
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL1\MSSQL\DATA\bktest.mdf',
  SIZE = 3072KB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 1024KB
)
LOG ON (
  NAME = 'bktest_log',
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL1\MSSQL\DATA\bktest_log.ldf',
  SIZE = 1024KB,
  MAXSIZE = 2048GB,
 FILEGROWTH = 10%
);
GO

ALTER DATABASE bktest
  SET RECOVERY FULL;
GO

プリンシパルサーバ

・DB切り替え

USE bktest;
GO

・実験用テーブルの作成。

CREATE TABLE tbl01 (
  col01 VARCHAR(10) NOT NULL,
  col02 VARCHAR(10) NULL,
  col03 VARCHAR(2000) NULL,
  col04 VARCHAR(2000) NULL,
  col05 INT NULL,
  PRIMARY KEY (
    col01 ASC
  )
);
GO

・実験用プロシージャの作成。

CREATE PROCEDURE CreateData
  -- 0 start
  @CNT int,
  @CNT_MAX int
AS
BEGIN
  WHILE @CNT < @CNT_MAX 
  BEGIN
    INSERT INTO tbl01 (col01, col02, col03, col04, col05)
    VALUES (
      'c1_' + CONVERT(VARCHAR, @CNT),
      'c2_' + CONVERT(VARCHAR, @CNT),
      '1234567890',
      '1234567890',
      @CNT
    )
    SET @CNT = @CNT + 1
  END
END;
GO

・実験用データの作成。

EXEC CreateData 0, 20;
GO

・DB切り替え

USE master;
GO

・完全バックアップデータの作成（ログの切捨ては行われない）。
　SQLServerの実行ユーザには共有フォルダへのアクセス権が必要。

BACKUP DATABASE bktest
  TO DISK = '\\backup\bkdir\bktest.bak'
  WITH FORMAT;
GO

・ログバックアップデータの作成（ログの切捨てが行われる）。
　SQLServerの実行ユーザには共有フォルダへのアクセス権が必要。

BACKUP LOG bktest
  TO DISK = '\\backup\bkdir\bktest_log.bak';
GO

ミラーサーバ

・DB切り替え

USE master;
GO

・完全バックアップデータのリストア。

RESTORE DATABASE bktest
  FROM DISK = '\\backup\bkdir\bktest.bak'
  WITH NORECOVERY;
GO

#############################################
# ※バックアップ元のパスとリストア先のパスが異なる場合は以下のようにする。
#
# -------------------------------------------
# RESTORE DATABASE bktest
#   FROM DISK = '\\backup\bkdir\bktest.bak'
#   WITH NORECOVERY,
#   MOVE 'bktest' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2\MSSQL\DATA\bktest.mdf',
#   MOVE 'bktest_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2\MSSQL\DATA\bktest_log.ldf';
# GO
# -------------------------------------------
#############################################

・ログバックアップデータのリストア。

RESTORE LOG bktest
  FROM DISK = '\\backup\bkdir\bktest_log.bak'
  WITH
    FILE = 1,
    NORECOVERY
GO

プリンシパルサーバ/ミラーサーバ

・DB切り替え

USE master;
GO

・通常の接続用ログインの作成
　※ミラーサーバは「SID = 0xNNN,」を追加してプリンシパルサーバと同じSIDにする。
　「SELECT sid FROM master.sys.server_principals WHERE name = 'bkuser'」でSIDが調べられる。
　　SIDが異なるとフェイルオーバ後に名前が同じアカウントでアクセスしても別物とみなされて接続に失敗する。

CREATE LOGIN bkuser
WITH
  PASSWORD = 'password',
  DEFAULT_DATABASE = bktest,
  DEFAULT_LANGUAGE=[日本語],
  CHECK_EXPIRATION=OFF,
  CHECK_POLICY=OFF;
GO

・DB切り替え

USE bktest;
GO

・通常の接続用ユーザの作成とロールの割り当て

CREATE USER bkuser FOR LOGIN bkuser WITH DEFAULT_SCHEMA = dbo;
GO

sp_addrolemember @rolename = 'db_owner', @membername = 'bkuser';
GO

プリンシパルサーバ/ミラーサーバ/監視サーバ

・DB切り替え

USE master;
GO

・既存のエンドポイントの確認（既に存在する場合は再構成してはいけない）。

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints;
GO

#############################################
#※以下Windows認証方式の場合（これは実験してないので記述が正しいかどうか怪しい）。
#
#・エンドポイントの作成（Expressの場合は「ROLE」として「WITNESS」のみ使用可）。ポート「8889」の例。
#--------------------------------------------
#CREATE ENDPOINT bk_mirroring_endpoint
#  STATE = STARTED
#  AS TCP (LISTENER_PORT = 8889)
#  FOR DATABASE_MIRRORING (ROLE = ALL);
#GO
#--------------------------------------------
#
#・他サーバのユーザアクセス許可を設定（同一ドメインに参加しているユーザを使う場合は不要）。他サーバの分だけ実行。
#--------------------------------------------
#CREATE LOGIN [【他サーバ】\bkwinuser] FROM WINDOWS;
#GO
#
#GRANT CONNECT ON ENDPOINT::bk_mirroring_endpoint TO [【他サーバ】\bkwinuser];
#GO
#--------------------------------------------
#############################################

プリンシパルサーバ

・DB切り替え

USE master;
GO

・マスターキーを作成する（この例のパスワードは適当）。既に存在する場合はエラーになる。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ThisIsDummy1';
GO

・プリンシパルサーバ用証明書を作成する（この例の有効期間は適当）。

CREATE CERTIFICATE cert_principal
  WITH SUBJECT = 'PRINCIPAL CERTIFICATE',
  START_DATE = '1/1/2000', EXPIRY_DATE = '12/31/2099';
GO

・証明書をファイルで取得する。

BACKUP CERTIFICATE cert_principal TO FILE = 'C:\cert_principal.cer';
GO

・エンドポイントの作成（Expressの場合は「ROLE」として「WITNESS」のみ使用可）。ポート「8889」の例。

CREATE ENDPOINT bk_mirroring_endpoint
  STATE = STARTED
  AS TCP (LISTENER_PORT = 8889)
  FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE cert_principal,
    ENCRYPTION = SUPPORTED,
    ROLE = PARTNER
  );
GO

ミラーサーバ

・DB切り替え

USE master;
GO

・マスターキーを作成する（この例のパスワードは適当）。既に存在する場合はエラーになる。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ThisIsDummy1';
GO

・ミラーサーバ用証明書を作成する（この例の有効期間は適当）。

CREATE CERTIFICATE cert_mirror
  WITH SUBJECT = 'MIRROR CERTIFICATE',
  START_DATE = '1/1/2000', EXPIRY_DATE = '12/31/2099';
GO

・証明書をファイルで取得する。

BACKUP CERTIFICATE cert_mirror TO FILE = 'C:\cert_mirror.cer';
GO

・エンドポイントの作成（Expressの場合は「ROLE」として「WITNESS」のみ使用可）。ポート「8889」の例。

CREATE ENDPOINT bk_mirroring_endpoint
  STATE = STARTED
  AS TCP (LISTENER_PORT = 8889)
  FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE cert_mirror,
    ENCRYPTION = SUPPORTED,
    ROLE = PARTNER
  );
GO

監視サーバ

・DB切り替え

USE master;
GO

・マスターキーを作成する（この例のパスワードは適当）。既に存在する場合はエラーになる。

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ThisIsDummy1';
GO

・監視サーバ用証明書を作成する（この例の有効期間は適当）。

CREATE CERTIFICATE cert_monitor
  WITH SUBJECT = 'MONITOR CERTIFICATE',
  START_DATE = '1/1/2000', EXPIRY_DATE = '12/31/2099';
GO

・証明書をファイルで取得する。

BACKUP CERTIFICATE cert_monitor TO FILE = 'C:\cert_monitor.cer';
GO

・エンドポイントの作成（Expressの場合は「ROLE」として「WITNESS」のみ使用可）。ポート「8889」の例。

CREATE ENDPOINT bk_mirroring_endpoint
  STATE = STARTED
  AS TCP (LISTENER_PORT = 8889)
  FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE cert_monitor,
    ENCRYPTION = SUPPORTED,
    ROLE = WITNESS
  );
GO

プリンシパルサーバ/ミラーサーバ/監視サーバ

※各証明書を各サーバにコピーする（自サーバのも含めると3サーバに3証明書が格納される）。

プリンシパルサーバ

・DB切り替え

USE master;
GO

・ミラーサーバ用／監視サーバ用アカウントを作成（この例のパスワードは適当）。

CREATE LOGIN login_mirror WITH PASSWORD = 'ThisIsDummy1';
GO

CREATE USER user_mirror FOR LOGIN login_mirror;
GO

CREATE LOGIN login_monitor WITH PASSWORD = 'ThisIsDummy1';
GO

CREATE USER user_monitor FOR LOGIN login_monitor;
GO

・証明書と関連付ける。

CREATE CERTIFICATE cert_user_mirror
  AUTHORIZATION user_mirror
  FROM FILE = 'C:\cert_mirror.cer'
GO

CREATE CERTIFICATE cert_user_monitor
  AUTHORIZATION user_monitor
  FROM FILE = 'C:\cert_monitor.cer'
GO

・エンドポイントのログインに接続権限を付与する。

GRANT CONNECT ON ENDPOINT::bk_mirroring_endpoint TO [login_mirror];
GO

GRANT CONNECT ON ENDPOINT::bk_mirroring_endpoint TO [login_monitor];
GO

ミラーサーバ

・DB切り替え

USE master;
GO

・プリンシパルサーバ用／監視サーバ用アカウントを作成（この例のパスワードは適当）。

CREATE LOGIN login_principal WITH PASSWORD = 'ThisIsDummy1';
GO

CREATE USER user_principal FOR LOGIN login_principal;
GO

CREATE LOGIN login_monitor WITH PASSWORD = 'ThisIsDummy1';
GO

CREATE USER user_monitor FOR LOGIN login_monitor;
GO

・証明書と関連付ける。

CREATE CERTIFICATE cert_user_principal
  AUTHORIZATION user_principal
  FROM FILE = 'C:\cert_principal.cer'
GO

CREATE CERTIFICATE cert_user_monitor
  AUTHORIZATION user_monitor
  FROM FILE = 'C:\cert_monitor.cer'
GO

・エンドポイントのログインに接続権限を付与する。

GRANT CONNECT ON ENDPOINT::bk_mirroring_endpoint TO [login_principal];
GO

GRANT CONNECT ON ENDPOINT::bk_mirroring_endpoint TO [login_monitor];
GO

監視サーバ

・DB切り替え

USE master;
GO

・プリンシパルサーバ用／ミラーサーバ用アカウントを作成（この例のパスワードは適当）。

CREATE LOGIN login_principal WITH PASSWORD = 'ThisIsDummy1';
GO

CREATE USER user_principal FOR LOGIN login_principal;
GO

CREATE LOGIN login_mirror WITH PASSWORD = 'ThisIsDummy1';
GO

CREATE USER user_mirror FOR LOGIN login_mirror;
GO

・証明書と関連付ける。

CREATE CERTIFICATE cert_user_principal
  AUTHORIZATION user_principal
  FROM FILE = 'C:\cert_principal.cer'
GO

CREATE CERTIFICATE cert_user_mirror
  AUTHORIZATION user_mirror
  FROM FILE = 'C:\cert_mirror.cer'
GO

・エンドポイントのログインに接続権限を付与する。

GRANT CONNECT ON ENDPOINT::bk_mirroring_endpoint TO [login_principal];
GO

GRANT CONNECT ON ENDPOINT::bk_mirroring_endpoint TO [login_mirror];
GO

ミラーサーバ

・DB切り替え

USE master;
GO

・プリンシパルサーバをパートナーとして登録。ポート「8889」の例。

ALTER DATABASE bktest
  SET PARTNER = 'TCP://【プリンシパルサーバ】:8889';
GO

プリンシパルサーバ

・DB切り替え

USE master;
GO

・ミラーサーバをパートナーとして登録。ポート「8889」の例。
　先にミラーサーバ側の設定をしないと「データベース ミラーリング用に構成されていません」と怒られる。
　監視サーバはフェイルオーバ後にミラーサーバからでもアクセスできるような名前にしておかないとフェイルオーバ時に設定が消される。

ALTER DATABASE bktest
  SET PARTNER = 'TCP://【ミラーサーバ】:8889';
GO

・監視サーバを登録。ポート「8889」の例。

ALTER DATABASE bktest
  SET WITNESS = 'TCP://【監視サーバ】:8889';
GO