SQL Server Codename "Denali" では Contained Databases という機能が追加されています。
Designing and Implementing a Contained Database
これがDenaliの新機能だ!/第四世代 SQL Server の世界へようこそ(後編)
SQL Server Denali Contained Database Feature
この機能を使うと、今まではシステム情報として持っていたサーバーの照合順序 (主に tempdb の照合順序が影響していたもの) やログイン情報といったものをデータベース側に保持することができるようになります。
# SQL Server Agent のジョブも分離できそうなことをちらっと見たのですがこちらは方法を調べきれておらず…。
これにより、サーバー間でデータベースを移動していた際の照合順序の影響やログインの再作成 (SID 変更の対応) の運用が変わってきます。
今回はこの Contained Databases の機能を使用したログインを Denali 間のデータベースの移動についてまとめてみたいと思います。
# 照合順序 (Contained Database Collations) に関してはまた別の機会にまとめたいと思います。
■Contained Databases の有効化
Contained Databases の設定はデータベース単位で行います。
設定の箇所はデータベースのプロパティの [Options] → [Containment type] になります。
デフォルトでは [None] となっているのですが、これを [Partial] にすることで、Contained Databases を有効にすることが可能です。
ただし、デフォルトの状態で設定をしようとすると以下のエラーとなります。
Contained Databases を有効にするためには、sp_configure で [contained database authentication] を有効にする必要があります。
このあたりの設定については、Migrating to a Contained Database に記載されています。
# 通常のログインによる管理を Contained Databases を使用したユーザー管理に移行するための手順のようです。
まずは以下のクエリを実行して Contained Databases を有効にします。
sp_configure ‘show advanced options’, 1 GO RECONFIGURE GO sp_configure ‘contained database authentication’, 1 GO RECONFIGURE |
そうすると [Partial] に設定が可能となります。
クエリでも変更ができ、その場合は以下のクエリを実行します。
USE [master] |
■ユーザー作成画面の変更
SQL Server のユーザー管理は、データベース ログインとデータベース ユーザーの 2 種類に分かれています。
サーバーにアクセスするためには、ログインの情報が必要となり (パスワードはログインに設定されている)、そのログインを各データベースのユーザーとして関連付けることで、ログインしたユーザーのデータベース内での権限が制御されます。
そのため、通常のデータベース ユーザーの作成画面は以下のようになっています。
データベース ログインの作成画面はこのようになっています。
Contained Databases を有効にしているデータベース上では、ユーザーの作成画面は以下のようになります。
上記の画面では、[User Type] が [SQL user with login] でしたが、Contained Databases を有効にしたデータベースでは、[SQL user with password] が選択されている状態がデフォルトとなります。
# [SQL user with login] も選択可能です。
ログインの指定はなく、ユーザー名に対して、パスワードやデフォルトの言語を設定することが可能となっています。
データベース ユーザーだけで今までログインで賄っていた情報を設定できるようになったわけですね。
今回は [TestUser] というユーザーを作成してみたいと思います。
パスワードに関してはパスワードの複雑さの規則が設定されるようです。
作成したユーザーですがそれ単体でデータベース ログインとデータベース ユーザーの役割を担うものになりますので、サーバー上にログインとしては作成されていません。
以上で設定は完了です。
TestUser でログインしてみます。
接続の時ですが、明示的に接続するデータベースを指定する必要があるようです。
接続するデータベースを [<default>] のように未設定の状態だと、どのデータベースにも接続ができない状態になります。
明示的にデータベースを指定した場合は接続をすることができます。
ただし、[db_owner] の権限を持っていてもデータベースのプロパティを開けないとう制約はあるようです。
# この辺がログインの権限使っていたかがうろ覚えなのですが…。
バックアップなどもちょっと通常と挙動が変わってくるのでこの辺は調査する必要がありそうですね。
■データベースの移動
それでは、先ほど Contained Databases を設定したデータベースをバックアップして、他の Denali の環境に移動 (リストア) したいと思います。
Contained Databases を設定したデータベースをリストアする場合、リストア先のサーバーでも sp_configure で機能を有効にしておく必要があります。
有効にしていない場合は、リストア時に以下のエラーとなります。
設定が行えていればリストアをすることができます。
こちらがリストアが完了して、TestUser で接続をした環境になります。
SQL Server のデータベースを別のサーバーに移動する場合
- CREATE LOGIN を SID を指定して実行して、移動前のサーバーのログインと同じ SID のログインを作成してパスワードを設定
- 異なる SID でログインを作成 (SID 指定なし) してパスワードを設定し、sp_change_users_login でログインを再マップ
といった作業をすると思いますが、Contained Databases の場合は [SQL user with password] で作成したユーザーの情報とパスワードがデータベースに含まれていますので、パスワードの設定は不要です。
# ユーザー情報自体もデータベースに含まれているので SID の変更を意識する必要がありません。
Contained Databases 用の DMV として [sys.dm_db_uncontained_entities] が追加されているのですがこれでどのような情報が取得できるかは調査中です。
Denali がリリースされた際には新しいセキュリティモデルとしてこの辺を考慮する必要が出てきそうですね。
[…] Contained Databases を使用したログインを意識しないデータベースの移動 の続きになります。 […]
Contained Databases を使用した場合の一時テーブルの照合順序について « SE の雑記
7 7月 11 at 20:34
[…] 以前、Contained Databases を使用したログインを意識しないデータベースの移動 という内容の投稿を書きました。 […]
Windows 認証を使用した Contained Database « SE の雑記
31 10月 11 at 23:12