SQL Server 2005 ミラー構築マニュアル(sql2005 データベース同期ミラーリングソリューション)

I. ミラーリングの紹介

1. はじめに

データベースミラーリングは、あるSQL Serverデータベースから、異なるSQL Server環境にある別のSQL Serverデータベースへ、データベーストランザクションを移動させる処理です。ミラーリングは直接アクセスできず、エラーリカバリーの場合のみアクセス可能です。

データベースミラーリングを実行するための最小要件は、2つの異なるSQL Serverランタイム環境です。プライマリサーバーを「ホスト」、セカンドサーバーを「スタンバイ」と呼びます。ホストデータベースは、実際に使用しているデータベースで、ミラーデータベースは、データベースのスタンバイコピーです。トランザクションがベースサーバーに書き込まれると、ミラーデータベースにも転送され、書き込まれます。

BasicとMirroredに加えて、もう一つ"witness"というオプションのコンポーネントを導入することができます。Witnessサーバーは第3のSQL Server 2005インスタンスで、エラーリカバリーの実行タイミングを決定する際に、basicとミラーの間で内部通信を行うために使用されます。このオプションは、自動エラー回復を実装する場合にのみ使用されます。これは、私のコンポーネントの1つが到達不能であるためにエラーリカバリーが必要な場合に、2対1の投票を行う機能を実装しています。Witness Serverは、自動エラーリカバリーを実装する場合にのみ必要です。

2. メリット

以下の表は、SQL Serverの可用性に関する公式ソリューションの比較ですが、当センターでは"バックアップ/リストア"のリカバリーモードを"コールドバックアップ"で使用しています。一般的には、"ホットバックアップ"の方が"コールドバックアップ"よりも可用性が高く、復旧速度が速いので、当センターの現状には合っていると思います。コストを抜きにすれば、ホットバックアップのフェイルオーバークラスターの可用性が最も高いのですが、フェイルオーバークラスターはディスクアレイを使う必要があり、構築自体が複雑なんです。データベースミラーリングは、少なくとも共有ストレージを必要とするフェイルオーバークラスタリングほどハードウェアを必要としません。

2. デメリット

(1) SQL Serverはワンインスタンス、マルチデータベース製品であるため、データベースミラーリング技術はデータベースレベルに基づいており、プライマリデータベースに新しいデータベースを追加するたびに、スタンバイマシンにデータベースを追加し、新しいデータベースに対してミラーリング関係を確立する必要があること。

(2) データベースのログインとユーザーはマスターデータベースに格納され、マスターデータベースはミラーリングできないため、データベース操作ごとにログインとユーザーのコピーをもう1つ保持する必要があります。

(3) データベースジョブを適宜メンテナンスすることができない。

(4) マイクロソフトは、ミラーリングによってクライアントの障害を透過的にできると主張しているが、実際のテストでは、ある条件を満たした場合にのみ透過性が実現され、クライアントがそれをサポートしている場合（.net Framework 2.0+, Microsoft jdbc driver 1.1+ ）のみ透過性が実現可能であることが示されている。

(5) クロスデータベーストランザクション、分散トランザクションのいずれも、データベースのミラーリングをサポートしていません。

これらの問題がない唯一の方法は、quot;ホットバックアップを使用したフェイルオーバークラスタリングquot;です。

I. プライマリとセカンダリのマシンの設定 1. 物理的な接続性

以下の図のように、プライマリデータベースとバックアップデータベースを接続します。

2. SQL Server 2005データベースを確認する

データイメージを作成できるのは、SQL Server 2005 Standard、Enterprise、およびDevelopmentエディションのみです。その他のエディション、すなわちExpressは、証人サーバーとしてのみ使用できます。どうしてもバージョンがわからない場合は、以下のステートメントを実行して確認してください。

select @@version;

このデータベースのミラーリングを実行するには、完全復旧モードを使用するように変更する必要があります。Transact-SQLでこれを行うには、ALTER DATABASEステートメントを使用します。

 USE master;
 ALTER DATABASE <DatabaeName> 
 SET RECOVERY FULL;

II. プライマリおよびバックアップインスタンスの相互運用性
相互運用を実現するためには、ドメインと証明書のどちらかを使用することになりますが、導入の簡便さを考慮すると、以下は証明書を選択する方法となります。例えば、2つのSQL Server 2005インスタンスで5つのデータベースをミラーリングする場合、以下の操作を一度だけ行う必要があります。

1. 証明書の作成（マスターとスタンバイは並行して実行可能です）

--host executes.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , 
START_DATE = '01/01/2008';

-- The standby machine executes.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', 
START_DATE = '01/01/2008';

2. 接続されたエンドポイントを作成します（プライマリとセカンダリは並行して実行可能）。

--host executes.

CREATE ENDPOINT Endpoint_Mirroring 
STATE = STARTED 
AS 
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
FOR 
DATABASE_MIRRORING 
( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

-- The standby machine executes.

CREATE ENDPOINT Endpoint_Mirroring 
STATE = STARTED 
AS 
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
FOR 
DATABASE_MIRRORING 
( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

4. 互換性のある証明書
HOST_A_cert.cerはバックアップマシンのD:㎤SQLBackupにコピーされます。HOST_B_cert.cerは、ホストマシンのD:㎤SQLBackupにコピーされます。

5. 5.ログイン名とユーザーの追加（マスターとスタンバイの並列実行が可能です）
以下の操作は、コマンドラインからのみ実行可能で、グラフィカルインターフェースからは実行できません。(ドキュメント終了時点では、SQL Server 2005の不定番はSP2です)

--Host executes.

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';
 -- The backup machine executes.

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';

III. ミラー関係の確立
例えば、既存のホストに5つのデータベースがある場合、以下の手順を5回実行する必要があります。

1. 手動によるログインとパスワードの同期
第1章で述べたデータベースミラーリングのデメリットの1つは、ログイン名を管理できないことなので、手動でログインを管理する必要があります。

例えば、業務システムがデータベースにアクセスするためのログインとして「myuser」を使用しているが、待機機には「myuser」のログインがないため、プライマリとスタンバイが切り替わると、業務システムはデータベースにログインすることができない。このような状況を "孤立したユーザー "と呼びます。

プライマリデータベースで次のステートメントを実行します。

--Host execution.

CREATE LOGIN HOST_B_login WITH PASSWORD = 'killkill';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

-- The standby machine executes.

CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQLBackup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

対応するユーザ名とsidを検索します（例：上記の「myuser」）。

スタンバイ・データベースで次のステートメントを実行します。

USE master;
select sid,name from syslogins;

ここで、'LoginName'はメインデータベースのログイン名で、sidは上記のSQL文によって見つかったsidです。

例えば、このクエリでは、以下のようにsidとnameが得られます。

シド名
---------------------------------- -----------------
0x074477739DCA0E499C29394FFFC4ADE4 cz_account

次に、ログイン名を作成するSQL文。

USE master;
exec sp_addlogin 
@loginame = '<LoginName>', 
@passwd = '<Password>', 
@sid = <sid> ;

この時点で、データベース内のデータを除けば、バックアップデータベース環境はホストと同期されたものと考えてよいでしょう。

2. スタンバイデータベースの準備
上記に続き、プライマリデータベースとバックアップデータベースのデータを同期させる方法について説明します。

先ほどのフルスタンバイのファイルからリストアを試みることができますが、データのリストア時には"with non recover"のオプションを使用する必要があります。図に示すように

正常に実行されると、データベースは次のようになります。

3. ミラーを作成する

これは実験であり、サーバーにデュアルNICを設定していないため、IPアドレスは図と少し異なりますが、原理は同じです。

-- ホストの実装。

ALTER DATABASE shishan SET PARTNER = 'TCP:/10.168.6.45:5022';

-- メイン実行が失敗した場合、スタンバイ機で以下のステートメントを実行してみてください。

ALTER DATABASE shishan SET PARTNER = 'TCP:/10.168.6.49:5022';

実行に成功すると、上の画像のようにプライマリデータベースとバックアップデータベースがアイコンで表示されます。

構築が失敗し、データベースのトランザクションログが同期されていないと表示された場合、プライマリデータベースとバックアップデータベースのデータ（ログ）が同期されていないため、プライマリデータベースとバックアップデータベースのデータの整合性を保つために、プライマリデータベースで "transaction log"backup を行い、バックアップデータベースへリストアすることが必要です。下図に、"transaction log"のバックアップを示します。

トランザクションログをリストアする場合、以下のようにオプションで "norecovery"を選択する必要があります。

リストア成功後、イメージを作成するSQL文を実行する。

IV. テスト操作

1. 一次および二次インターチェンジ

--ホストの実装。

USEマスターです。
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER;

2. プライマリサーバーがダウンし、スタンバイが起動してサービス中である

-- 待機機が実行されます。

USEマスターです。
ALTER DATABASE <DatabaseName> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSSを設定します。

3. 元のマスターサーバーが復元され、作業を継続することができます、あなたは、ミラーをリセットする必要があります

4、元のマスターサーバーが復元され、作業を継続することができます

-- デフォルトでは、トランザクションのセキュリティレベルは同期モードであるFULLに設定されており、SQL Server 2005 Standard Editionは同期モードのみをサポートしています。

-- トランザクション・セキュリティをオフにすると、セッションの動作モードが非同期に切り替わり、最適なパフォーマンスが得られるようになります。

USEマスターです。
ALTER DATABASE <DatabaseName> SET PARTNER SAFETY FULL; -- トランザクションセーフ、シンクロナスモード
ALTER DATABASE <DatabaseName> SET PARTNER SAFETY OFF; -- トランザクションが安全でない、非同期モード。