SQL Server のセキュリティ設定として、[ログイン] と [ユーザー] があります。
ログインの情報に関しては [master] データベースに格納され、SQL Server に接続をするために必要となります。
そのログインにデータベースのユーザーをマッピングし、データベース内で権限が付与されます。
データベースのユーザー情報に関しては各データベースに格納されています。
# SQL Server 認証を使っている場合の概略です。
Denali で追加された Contained Databases を Availability Groups と組み合わせることで可用性グループ内のサーバーでログイン情報の同期をとることが可能となります。
今回はこの内容についてまとめていきたいと思います。
■Contained Database を使用しない場合のログインの管理
まずは、Contained Database を使用しない場合の各サーバー間のログイン情報の管理を見ていきたいと思います。
Availability Groups では、システムデータベースの同期はできませんので、データベースの同期状態は以下のようになります。
この状態でプライマリに新規にログインを作成し、そのログインをユーザーデータベースにユーザーとしてマッピングします。
その時の各サーバーの状態ですが以下のようになります。
ユーザーの情報は各データベースに格納していますので、プライマリの設定内容は同期がされますが、ログインの情報に関しては master に格納がされていますので、新規に作成したログインをマッピングした場合、サーバーにログインがいない状態のユーザーとして設定がされていることになります。
この状態だとログインが存在していないのでそのユーザーは SQL Server にログインすることができず、結果としてそのユーザーは使用できない状態となります。
バックアップの取得元と異なるサーバーにデータベースを復元したときに発生する現象と同じ状態ですね。
[HOWTO] SQL Server を実行しているサーバー間でデータベースを移動するときに、権限の問題を解決する方法
SQL Server を実行しているコンピューター間でデータベースを移動する方法
同じ SID を使用して、セカンダリでログインを作成すれば正常な状態になるのですが、せっかく同期をとっているのでできたらログインの作成をせずにログイン情報が連携されると運用が効率よくできますよね。
Denali では Contained Databases を使用することでログイン情報を含めた形でプライマリとセカンダリの同期をすることができます。
■Contained Databases を使用したサーバー間でのログインの同期
Contained Databases の設定については以前投稿をしましたので今回は省略します。
Contained Databases を使用したログインを意識しないデータベースの移動
Contained Databases を使用してログインを作成した場合、以下のような構成をとることが可能となります。
Contained Database が有効になっている場合、ログインとして利用ができるユーザーを各データベース内に作成することができます。
このユーザーであれば、同期をしているデータベース内で情報が完結しているため、新規にユーザー (ログイン) を作成した場合にもログインの再マッピングを考えなくても、情報が同期されている状態となります。
パスワードに関してもプライマリで変更した内容がセカンダリに伝搬されます。
# Contained Databases を使用しない場合、パスワードの情報は master に格納されているため、各サーバー間で同期されませんのでサーバーごとに再設定をする必要があります。
Contained Databases でログインを各データベース内に格納することで、ログインの一元管理はできなくなるので複数のデータベースで単一のログインを使用しているような場合、この構成が運用面でメリットがあるかは要検討になるかと思いますが、構成によっては運用効率が良くなることがあるかと思います。
新機能を組み合わせた運用。いろいろと調べてみる必要がありそうですね。