MSSQL/Mirroring

Microsoft SQL Server データベースミラーリング

常にデータのコピーを行い、完全なデータの冗長を行う。SQL Server 2012 以降は、AlwaysOn 可用性グループを使用する。

ミラーリング環境の作成

前提条件

Standard エディション以上の“同一バージョン・エディション”のインスタンス2つ
- 自動フェールオーバーを行うためには、監視サーバ(Express でも可)が、別途必要
データベースの復旧モデルが“完全”であること
すべてのインスタンスが“同じ照合順序”を利用していること
SQLSERVER サービスが、ローカルシステム(LocalSystem)以外の同じアカウントであること
- ADドメイン環境ではドメインユーザ、ワークグループ環境では同じ名前・パスワードのローカルユーザ
サービスのアカウントでログイン(Windows 認証)出来ること。
- ※SQL Server 認証では出来ない？

ミラーリングの主な作業内容

プリンシパル側で“完全バックアップ”と“ログバックアップ”を行う
ミラー側で上記を“NORECOVER でリストア”する
プリンシパル側でミラーリング構成を設定・開始する
ミラー側のログインユーザ設定（フェールオーバー時に使えるようにしておく）※下記参照

ミラーリング構成時のログインユーザ作成

ログイン名とは別に SID が付与され、各データベースのアクセスユーザは SID で識別される。そのため、プリンシパル側とミラー側で SID を同じにしないと、フェールオーバーした際にデータベースにアクセスできなくなる。

SID は“master.sys.server_principals ”で確認できる。また、“CREATE LOGIN”で SID の指定が出来る。

プリンシパル側でのユーザ作成とSIDの確認

USE [master]
GO
-- ユーザ作成
CREATE LOGIN [new_user] WITH PASSWORD=N'password', DEFAULT_DATABASE=[database_name], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- SID確認
SELECT sid FROM master.sys.server_principals WHERE name='new_user'

ミラー側でのユーザ作成

USE [master]
GO
-- ユーザ作成(SIDはプリンシパル側で採番されたSID)
CREATE LOGIN [new_user] WITH PASSWORD=N'password', SID=0x1234567890ABCDEF1234567890ABCDEF, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

プリンシパル側でのDBアクセスユーザ

ロールメンバーの設定は任意に

USE [database_name]
GO
CREATE USER [new_user] FOR LOGIN [new_user] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_datareader', N'new_user'
GO
EXEC sp_addrolemember N'db_datawriter', N'new_user'
GO

ミラーリング構成時のクライアント接続

クライアントからの接続文字列には、フェイルオーバーパートナーを記述し、プリシンバルおよびミラー両方のサーバの情報を記述する。また、デフォルトのネットワークプロトコルの指定では、TCP/IPと名前付きパイプと両方で接続が施行され、イニシャルパートナー(Server の方)の試行で、15秒経過し接続タイムアウトとなってしまう。

https://technet.microsoft.com/ja-jp/library/ms366348%28v=sql.105%29.aspx

TCP/IP での接続文字列例

Server=プリシンバル側サーバ;Failover Partner=ミラー側サーバ;User Id=接続ユーザ名;Password=パスワード;Database=既定データベース;Network=dbmssocn;

フェールオーバー

監視サーバがある場合は、自動でフェールオーバーする。

手動でフェイルオーバー

プリンシパル側で以下のコマンドを実行します。

ALTER DATABASE [database_name] SET PARTNER FAILOVER;

手動でフェイルオーバー (障害時)

プリンシパル側で障害が発生した場合は、以下のコマンドでミラー側で強制的に有効にする。

ALTER DATABASE [database_name] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

このとき、ミラー側のデータベースのステータスは、“復旧中”から“プリシンバル、接続解除”となる。

手動でフェイルオーバー (復帰時)

障害から復帰したら、以下のコマンドでミラーを再開する。

ALTER DATABASE [database_name] SET PARTNER RESUME;

この段階では、元のミラー側がプリンシバル、復帰したサーバがミラーになっているので、元の状態に戻したい場合は、手動フェールオーバーする必要がある。

　データベースミラーリングモニタ

SQL Server Management Studio で、データベースを選択、「タスク」＞「データベースミラーリングモニタの起動」で起動できるが、単独のアプリケーションなので以下の方法で直接起動も出来る。

アプリケーションは、SSMS をインストールした場所にある。“C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\sqlmonitor.exe”

そのまま起動すると、レプリケーションモニタが起動するが、/M オプションを指定することで、表示するモニターを指定することができる。/M dbmmonitor でミラーリングモニタが起動する。

最終更新時間：2016年05月26日 14時22分41秒　指摘や意見などあればSandBoxのBBSへ。

INDEX