SQL Server 2012: 部分的なものが好ましい場合もある
SQL Server 2012 では、部分的包含データベースがサポートされ、移行と統合に関する問題が幾分緩和されます。
Denny Cherry
部分的包含データベースは、SQL Server 4.2 以来 SQL Server データベースにまつわる統合と移行の大きな問題をいくつか解決します。SQL Server 2012 には多くの新機能が導入されていますが、ありがたいことに、部分的包含データベースもその 1 つです。
部分的包含データベースによって解決される最初の問題は、SQL Server のあるインスタンスから別のインスタンスにデータベースを移動する際に、データベースが使用する SQL Server ログインを特定する必要性がなくなったことです。データベースが使用するログインは、移動先のデータベース インスタンスで作成することも可能です。
SQL Server 2012 は、このために包含 SQL Server ログインを使用します。これは、包含データベース内で作成される SQL Server ユーザーで、対応する SQL Server ログインがないユーザーです。このような包含データベース内の包含ユーザーは、包含データベース内にパスワードを保存しているため、認証は想定どおりに機能します。
解決される 2 つ目の問題は、包含データベース内でのテーブル間の照合順序の競合です。照合順序の競合が解決されることにより、一時テーブルは事実上必要なくなります。SQL Server は、包含データベースが照合されるときに、包含データベースのスコープ内で一時テーブルを自動作成します。したがって、SQL Server の同じインスタンスが異なる照合順序のデータベースをホストできるようになります。このとき、照合順序を指定するために CREATE TABLE ステートメントを変更したり、JOIN ステートメントの構文内で COLLATE ステートメントを指定する必要はありません。
新機能の中にはデータベース互換性レベルを最新の値に設定することを必要とする機能もありますが、部分的包含データベースは、SQL Server 2005 から現バージョンまでサポートされます。ただし、包含データベース機能はまだ完全ではありません。SQL Server 2012 CTP3 のリリースから、2012 年初期～中期の製品版リリースまでの期間に完成予定です。
部分的包含データベースのセットアップ
データベースを非包含から部分的包含に変えるには、まず、sp_configure システム ストアド プロシージャを使用してサーバー設定を変更する必要があります。sp_configure システム ストアド プロシージャを使用して、"contained database authentication" 設定を 0 から 1 に変更します。次に、RECONFIGURE ステートメントを使用して新しい設定をアクティブにします。
EXEC sp_configure 'contained database authentication', 1 RECONFIGURE GO
"contained database authentication" 設定を有効にしたら、特定のデータベースを部分的包含に変更できます。SQL Server Management Studio (図 1 参照) または ALTER DATABASE ステートメントを使用して変更するか、新しいデータベースを包含データベースとして作成することができます。
ALTER DATABASE: USE [master] GO ALTER DATABASE [Cont] SET CONTAINMENT=PARTIAL GO CREATE DATABASE: CREATE DATABASE [Cont1] CONTAINMENT=PARTIAL GO
新しい部分的包含データベースを作成するか、データベースを部分的包含に変更するには、オブジェクト エクスプローラーで SQL Server インスタンスに接続します。インスタンスを右クリックして新しいデータベースを作成するか、既存のデータベースを選択します。(新しいデータベースを作成するか、既存のデータベースを変更するかに応じて) [プロパティ] を選択します。いずれの場合も、[オプション] を選択して、[コンテインメントの種類] を "なし" から "部分" に変更します。
.jpg)
図 1 [データベースのプロパティ] ウィンドウでは、データベースのコンテインメントの設定を変更できる
T/SQL および ALTER DATABASE ステートメントを使用すると、既存のデータベースを非包含から部分的包含に変更できます。データベースのコンテインメント設定を適切に変更するには、ALTER DATABASE ステートメントでデータベース全体を排他的にロックできなければなりません。したがって、そのデータベースを使用している部門では、短期間の稼働停止をスケジューリングする必要があります。
一時テーブルの照合順序エラーを回避する
新しい部分的包含データベースによって、複数の異なる照合順序が SQL Server の同じインスタンス内に存在することが可能になります。一時テーブルを結合するときに、照合順序の問題を心配する必要がなくなります。これをテストするには、Microsoft SQL Server インスタンスを使用して、異なる照合順序の部分的包含データベースを作成します。次に、物理テーブルと一時テーブルをその部分的包含データベース内に作成し、データをこの 2 つのテーブルに読み込んで、両テーブルの結合を試みます。
インスタンスが照合順序として SQL_Latin1_General_CP1_CI_AS を使用しているときに、Albanian_100_CI_AI_KS_WS という照合順序を使用してデータベースを作成します (図 2 参照)。新しいデータベース内に dbo.Employee というテーブルを作成し、3 つの行をテーブルに読み込みます。また、#emp という一時テーブルも作成して、1 行挿入します。クエリの最後に 2 つのテーブルを結合すると、1 行返されます。包含データベース設定を "部分" に設定していない場合は、エラーが返されます。
図 2 部分的包含データベースの作成と使用
use master GO CREATE DATABASE [Cont] CONTAINMENT = PARTIAL ONPRIMARY (NAME = N'Cont', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont.mdf', SIZE = 4096KB, FILEGROWTH= 1024KB) LOGON (NAME = N'Cont_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%) COLLATE Albanian_100_CI_AI_KS_WS GO Use Cont GO Create table Employee (EmployeeId INTPRIMARYKEY, LastName nvarchar(100), FirstName nvarchar(100)) GO Insert into Employee (EmployeeId,LastName,FirstName) values (1,'last1','first1'), (2,'last2','first2'),(3,'last3','first3') GO Create table #emp (LastName nvarchar(100)) GO Insert into #emp (LastName) values ('last1') GO select* from Employee join #emp on Employee.LastName = #emp.LastName
包含ユーザー
包含ユーザーは、従来の SQL Server ログインに似ていますが、包含データベース ユーザーに対応するログインがありません。包含ユーザーを作成するには、SQL Server Management Studio か、CREATE USER T/SQL ステートメントを使用します。
SQL Server Management Studio を使用するには、オブジェクト エクスプローラーで包含データベースをホストするインスタンスに接続します。[データベース]、[<自分の包含データベース>]、[セキュリティ]、[ユーザー] の順に展開します。[ユーザー] フォルダーを右クリックして、コンテキスト メニューで [プロパティ] を選択したら、[ユーザーの種類] で [パスワードを持つ SQL ユーザー] を選択します。[ユーザー名] フィールドと [パスワード] フィールドに入力し (図 3 参照)、必要に応じて既定のスキーマやデータベース ロールのメンバーシップを構成します。
.jpg)
図 3 SQL Server Management Studio で包含 SQL ユーザーを作成
包含ユーザーを作成したら、包含データベース内で、sys.database_principals カタログ ビューを確認できます。カタログ ビューをクエリすると返されるレコードセットでは、値 2 に設定された、authentication_type という新しい列が表示されます。また、ユーザーが包含ユーザーの場合、値が DATABASE に設定された、authentication_type_desc という列も表示されます。
T/SQL CREATE USER ステートメントを使用して包含データベース ユーザーを作成します。以下のように WITH PASSWORD ステートメントを指定することで、ログインに対応するユーザーではなく、包含ユーザーをインスタンス レベルで作成していることが、SQL Server によって認識されます。
CREATE USER MyContainedUser WITHPASSWORD = 'MyContainedUserPassword' GO
包含 SQL ユーザーと同様に、包含 Windows ログインも作成できます。[ユーザーの種類] (図 4 参照) を [Windows ユーザー] に変更して、ログインを選択しないでにユーザー名を入力します。
.jpg)
図 4 SQL Server Management Studio を使用した包含 Windows ログインを作成
次のように、CREATE USER T/SQL ステートメントを使用して包含 Windows ユーザーを作成することもできます。
CREATE USER [CAPT-MAL\test] WITH DEFAULT_SCHEMA = [dbo] GO
従来の (非包含) ユーザーとまったく同様に、包含ログインを指定して包含データベースにログオンします。包含ユーザーに関して重要な点は、接続時に接続文字列にデータベース名を指定しなければならないことです。指定しないと、SQL Server は、従来の SQL Server ログインが接続を試みていると想定します。インスタンス レベルで定義されたユーザー名とパスワードに相当するログインがなければ、接続に失敗します。
データベース名の指定は、SQL Server Management Studio の [サーバーへの接続] ダイアログ ボックスで行います。まず、ログイン ウィンドウの下部にある [オプション] ボタンをクリックします。次に、[接続プロパティ] タブを選択します。[接続プロパティ] タブで、包含データベースのデータベース名を [データベースへの接続] に入力します (図 5 参照)。
SQL Server インスタンスでホストされているデータベースの一覧を、[ログイン] タブで指定した包含ユーザーとしてクエリすることはできません。その包含ユーザーは、ユーザー名とパスワードを保持する包含データベースに対して認証を行わない限り、master データベースに対する権限がありません。このため、データベース名を把握して、[データベースへの接続] に手入力する必要があります。
.jpg)
図 5 SQL Server Management Studio の [サーバーへの接続] ダイアログ ボックスの [接続プロパティ] タブ
これまでに紹介した 2 つの機能からわかるように、SQL Server 2012 のインスタンスへのデータベース インスタンスの統合が非常に簡単になります。データベース アプリケーションが包含データベース機能と完全に互換性があるかどうかを確認するために、時間とテストを少し必要としますが、包含データベース設定を一度有効にすれば、データベースの移行と統合が劇的にスムーズになります。これこそが、包含データベースの真の目的です。
.jpg)
Denny Cherry は、MVP およびフリーのコンサルタントで、Microsoft SQL Server、Hyper-V、vSphere、エンタープライズ ストレージ ソリューションに 10 年以上携わってきた実績があります。現在、マイクロソフト認定資格など、SQL Server 2000 ～ 2008 関連の認定資格をいくつか保有しています。また、SQL Server の管理について、数冊の書籍や多数の技術文書を執筆しています。