非ドメイン(ActiveDirectory)環境で２台のサーバでSQL Server 2005 のミラーリングを構成するメモです。 以前のサイトで掲載していた内容を本サイトの記事に移動しました。

作成する環境は次のとおり

  • ミラーリング構成 : プリンシパル,ミラーDBサーバ,ウィットネスサーバなし
  • 環境:SQL Server 2005 SP1 Developer Edition,Windows 2003 Server R2

次のページが大いに参考になります。ってかほぼそのまんまです。
データベース ミラーリングで発信接続に証明書を使用できるようにする方法
Setting Up Login Accounts for Database Mirroring
Troubleshooting Database Mirroring Setup

プリンシパルサーバでの環境設定１

非ドメイン環境でSQL Server 2005をミラーリング構成にする場合、プリンシパル、ミラーDB間の通信を行うために 証明書が必要になります。そのため次の作業を行います。

  1. データベースのマスターキーを作成していない場合、データベースのマスターキーを作成する。
  2. 証明書を作成する。
  3. エンドポイントを作成する。
  4. 証明書をバックアップする。

上記の処理をやっているのが次のコードです。データベースマスターキーパスワードや 証明書名(例ではCERT_PRINCIPAL)、エンドポイント名(例ではPRICIPAL_Mirroring)、証明書 の有効期間(開始:START_DATE,失効:EXPIRY_DATE)は適当に設定して下さい。また、バックアップした証明書 はミラーDBにコピーします。

------------プリンシパルDB側でやる処理1--------------
USE master;
GO

/**
 * まだ作っていなければマスタキーを作成します
 */
-- データベースマスターキーの作成
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Lx78CUPasW0RDH0GE';
--DROP MASTER KEY
GO

/**
 * EndPointに使用する証明書を作成します。
 * ここではENCRYPTION BY PASSWORDは使用しない。
 */
-- 証明書を作成する
CREATE CERTIFICATE CERT_PRINCIPAL
  WITH SUBJECT = 'PRINCEPAL CERTIFICATE',
  START_DATE = '8/7/2006', EXPIRY_DATE = '8/15/2008';
GO
--DROP CERTIFICATE CERT_PRINCEPAL
/**
 * エンドポイントを作成します
 */
CREATE ENDPOINT [PRICIPAL_Mirroring]
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
   ) 
  FOR DATABASE_MIRRORING (
	AUTHENTICATION = CERTIFICATE CERT_PRINCIPAL
  , ENCRYPTION = SUPPORTED
  , ROLE = PARTNER
  );
GO

/**
 * 証明書をバックアップします
 */
BACKUP CERTIFICATE CERT_PRINCIPAL TO FILE = 'D:\PRINCIPAL_CERT.cer';
GO

-- 上記の証明書をミラーDBにコピーする

/*
 * おまけ:いろいろ確認するためのSQLたち
 *	select * from sys.endpoints
 *
 *	USE master;
 *	SELECT * FROM sys.certificates;
 */

ミラーサーバでの環境設定１

プリンシパルサーバと同様に次の手順を実施します。

  1. データベースのマスターキーを作成していない場合、データベースのマスターキーを作成する。
  2. 証明書を作成する。
  3. エンドポイントを作成する。
  4. 証明書をバックアップする。

プリンシパルサーバの環境設定１と同様、マスターキーのパスワード、証明書名等は適当に設定してください。

---------------以下ミラーDB側でやる処理１--------------------
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'H0geH0geAroAro';
GO

/**
 * EndPointに使用する証明書を作成します。
 * ここではENCRYPTION BY PASSWORDは使用しない。
 */
-- 証明書を作成する
CREATE CERTIFICATE CERT_MIRROR
  WITH SUBJECT = 'MIRROR CERTIFICATE',
  START_DATE = '8/7/2006', EXPIRY_DATE = '8/15/2008';
GO
--DROP CERTIFICATE CERT_MIRROR
/**
 * エンドポイントを作成します
 */

CREATE ENDPOINT [MIRROR_Mirroring]
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5022
   ) 
  FOR DATABASE_MIRRORING (
	AUTHENTICATION = CERTIFICATE CERT_MIRROR
  , ENCRYPTION = SUPPORTED
  , ROLE = PARTNER
  );
GO


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

プリンシパルサーバでの環境設定２

プリンシパルサーバで、ミラーリングサーバ通信用のログインユーザを作成し、 エンドポイントへの接続を行えるようにします。具体的な作業は次のとおり。

  1. ミラーサーバ用のログインを作成
  2. ミラーサーバ用のログインにミラーサーバで作成した証明書を関連付ける
  3. ログインユーザにエンドポイントへのコネクト権限を付与する

以下が実際のクエリです。ログイン、ユーザ名、パスワード等は適当に設定して下さい。

---------------プリンシパルDB側でやる処理２
/**
 * ミラーDBに対するプリンシパルDBのログインを作成する
 */
USE master;
CREATE LOGIN Login_Mirror WITH PASSWORD = 'Tekit0u2OO5';
GO

CREATE USER User_Mirror FOR LOGIN Login_Mirror;
GO

/**
 * 証明書をユーザと関連付ける
 */
CREATE CERTIFICATE CERT_User_Mirror
   AUTHORIZATION User_Mirror
   FROM FILE = 'D:\Mirror_CERT.cer'
GO

/**
 * ミラーリングのエンドポイント用のログインにCONNECT
 * 権限をきょかする
 */
GRANT CONNECT ON ENDPOINT::PRINCIPAL_Mirroring TO [Login_Mirror];
GO

ミラーサーバでの環境設定２

ミラーサーバでもプリンシパルサーバと同じように、今度はプリンシパルサーバ通信用の ログインユーザを作成し、エンドポイントへの接続を行えるようにします。具体的な作業は次のとおり。

  1. プリンシパルサーバ用のログインを作成
  2. プリンシパルサーバ用のログインにプリンシパルサーバで作成した証明書を関連付ける
  3. ログインユーザにエンドポイントへのコネクト権限を付与する

以下が実際のクエリです。ログイン、ユーザ名、パスワード等は適当に設定して下さい。

---------------ここからミラーDB側でやる処理２
/**
 * プリンシパルDBに対するミラーDBのログインを作成する
 */
USE master;
CREATE LOGIN Login_Principal WITH PASSWORD = 'TekeTekeNishiK0ri';
GO

CREATE USER User_Principal FOR LOGIN Login_Principal;
GO

/**
 * 証明書をユーザと関連付ける
 */
CREATE CERTIFICATE CERT_User_Principal
   AUTHORIZATION User_Principal
   FROM FILE = 'C:\Principal_CERT.cer'
GO

/**
 * ミラーリングのエンドポイント用のログインにCONNECT
 * 権限をきょかする
 */
GRANT CONNECT ON ENDPOINT::[MIRROR_Mirroring] TO [Login_Principal];
GO

ミラーリング開始

こっから記憶と記録があいまいなのですが、ミラーリングを開始できるようになったはずです。
(もし動かない場合はプリンシパルからミラーリングのウィザードを実行してください。m(_ _)m)

はじめにミラーサーバから接続先を設定します。[DB名]はミラーリングを構成するデータベース名を設定 してください。192.168.0.101はプリンシパルサーバのIPアドレスと仮定しています。システム名で接続 する場合はIPアドレスをシステム名に置き換えて下さい。

-- ミラーDB上で接続を設定する
USE master;
GO

-- 192.168.0.101はプリンシパルDBサーバのIPアドレス
-- システム名で接続する場合はhttp://PrincipalDB:5022のように記述する
ALTER DATABASE [DB名]
  SET PARTNER = 'TCP://192.168.0.101:5022'
GO

次にプリンシパルサーバからの接続先を設定します。

-- プリンシパルDB上で接続を設定する
USE master;
GO

-- 192.168.0.102はミラーDBサーバのIPアドレス
-- システム名で接続する場合はhttp://MirrorDB:5022のように記述する
ALTER DATABASE [DB名]
  SET PARTNER = 'TCP://192.168.0.102:5022'
GO

動作確認

プリンシパル、ミラーサーバのどちらかに接続して次のコマンドを実行します。 DB名はミラーリング構成をしたデータベース名を入力してください。ミラーリングの接続 状態が得られます。項目の詳細はBooks onlineを参照して下さい。

select * from sys.database_mirroring
where database_id = DB_ID('DB名');

SQL Server Management Studio上でプリンシパルサーバに接続し、オブジェクトエクスプローラ上から ミラーリング構成を行ったデータベースを表示すると、プリンシパルと表示されているはずです。

オブジェクトエクスプローラ上で[ミラーリングを構成したデータベースを右クリック]→[タスク]→[ミラー] からもミラーリングの状態を確認できます。ミラーリング状態の画面イメージはどっかのWeb上にいろいろ あると思うのでそちらを参考にしてください。画面イメージをマスクするのがめんどいのですm(_ _)m。