SE の雑記

SQL Server 2012 以降では AlwaysOn 可用性グループが実装され、これによりデータベースミラーリングは将来のバージョンの SQL Server では削除予定の機能となりました。
データベース ミラーリング (SQL Server)

ただし、AlwaysOn については Enterprise Edition/ Active Directory が必要となるため、

• ワークグループ環境
• Standard Edition × 2 (プリンシパル / ミラー用)
• Express Edition (監視サーバー用)

の構成で、ミラーリングを組まざるを得ないこともあるかと思います。

Azure の仮想マシン (Azure VM / VIrtual Machine) でデータベースミラーリングを組む方法としては チュートリアル:Azure で高可用性を実現するデータベース ミラーリング があります。
このチュートリアルでは証明書を使用したエンドポイントへの接続を行っていますが、証明書を使用した場合は、
How to replace expired certificates used in database mirroring
SQL Yoga : Certificate Expired, Mirroring Stopped
データベース ミラーリング エンドポイントで発信接続に証明書を使用できるようにする (Transact-SQL)
に記載されているように証明書の有効期限を意識する必要があるかと思います。
# デフォルトでは 1 年で有効期限が切れるかと。

証明書を使用しないエンドポイントへの接続としては

• 同一のユーザー名 / パスワードのユーザーで SQL Server のサービスを起動

し、エンドポイント間の接続を行う方法があるかと思います。
こちらはチュートリアルには記載されていない方法となりますが、実際の環境ではこちらで運用されていることが多いかと。
組み方にもよるかもしれませんが、Azure の仮想マシン上に SQL Server 2014 をインストールした環境で同一のユーザー名 / パスワードで起動している SQL Server 間でミラーリングを組む際に試行錯誤する箇所があったのでメモとして。

私が構築した環境では、ミラーリングを開始しようとすると、以下のエラーが発生していました。

ミラーリングの開始中にエラーが発生しました。
データベース ‘MIRRORDB’ のAlterに失敗しました。  (Microsoft.SqlServer.Smo)
Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。 (Microsoft.SqlServer.ConnectionInfo)

サーバー ネットワーク アドレス "TCP://DBM02:5022" にアクセスできないか、このアドレスが存在しません。ネットワーク アドレス名と、ローカル エンドポイントおよびリモート エンドポイントのポートが操作可能であることを確認してください。 (Microsoft SQL Server、エラー: 1418)

SQL Server のサービスに使用しているアカウントは同一ユーザー名 / パスワードなのですがこのエラーが発生していました。
各サーバーの ERRORLOG を確認するとプリンシパル側では、

Error: 1474, Severity: 16, State: 1.
Database mirroring connection error 4 ‘An error occurred while receiving data: ‘10054(既存の接続はリモート ホストに強制的に切断されました。)’.’ for ‘TCP://DBM02:5022’.
Error: 1443, Severity: 16, State: 2.
Database mirroring has been terminated for database ‘MIRRORDB’. This is an informational message only. No user action is required.
Error: 1474, Severity: 16, State: 1.
Database mirroring connection error 4 ‘An error occurred while receiving data: ‘10054(既存の接続はリモート ホストに強制的に切断されました。)’.’ for ‘TCP://DBM02:5022’.
Error: 1443, Severity: 16, State: 2.
Database mirroring has been terminated for database ‘MIRRORDB’. This is an informational message only. No user action is required.

ミラー側では、

Database Mirroring login attempt by user ‘DBM02SQLServiceUser.’ failed with error: ‘Connection handshake failed. The login ‘DBM02SQLServiceUser’ does not have CONNECT permission on the endpoint. State 84.’.  [CLIENT: 10.0.0.4]

というようなエラーが発生していました。
エラーの内容から SQL Server のサービスアカウントがミラーリングで使用しているエンドポイントに対して接続ができないためエラーとなっているようです。

ミラーリングのエンドポイントですが、SSMS から実施した場合、[セキュリティの構成] を行った際に以下のようなクエリが実行され作成されています。

CREATE ENDPOINT [ミラー化]
	AS TCP (LISTENER_PORT = 5022)
	FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED ALGORITHM RC4)
go
IF (SELECT state FROM sys.endpoints WHERE name = N'ミラー化')  0
BEGIN
	ALTER ENDPOINT [ミラー化] STATE = STARTED
END
go

エンドポイントの作成と、エンドポイントの開始は実行しているようですが、権限系の設定は行われていないようでした。

この環境ですがセキュリティの構成を行う際に、以下のようにサービスアカウントをブランクの状態で設定していました。

そのため、上記のクエリのようにサービスアカウントのログインの作成とログインに対してのエンドポイントの接続許可が行われなかったようです。

正常に接続するためには以下のようなクエリが実行される必要があります。

IF (SELECT state FROM sys.endpoints WHERE name = N'ミラー化')  0
BEGIN
	ALTER ENDPOINT [ミラー化] STATE = STARTED
END
go
CREATE LOGIN [DBM01SQLServiceUser] FROM WINDOWS
go
GRANT CONNECT ON ENDPOINT::[ミラー化] TO [DBM01SQLServiceUser]
go

そのため、プリンシパル / ミラー (ウィットネスを拘置する場合にはウィットネスも) に対して、

• SQL Server のサービスユーザーでログインを作成
• 作成したログインにミラーリング用エンドポイントの接続を許可

という手順を実施することで以下のようにワークグループ環境の Azure の仮想マシン 証明書を使用しないミラーリング環境を構築することができます。

以下は Azure の仮想マシンで Standard Edition × 2 + Express Edition でミラーリングを設定したものになります。

# Express Edition は既定のインスタンスとしてインストールをしていますので、SQLEXPRESS という名前付きインスタンスではありません。

権限の設定関連については、以下のシステムテーブルから確認をするとどのように設定されているかを把握できるかと思います。

select * from sys.server_permissions WHERE class = 105
select * from sys.endpoints WHERE type = 4
select * from sys.server_principals
select * from sys.server_role_members