SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2012 自習書 DWH 関連の新機能

SQL Server 2012 自習書シリーズ（HTML 版）
新機能編 No.2「AlwaysOn による可用性の向上」

松本美穂と松本崇博が執筆した SQL Server 2012 自習書シリーズの「新機能編 No.2 AlwaysOn による可用性の向上」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2014年12月26日]

目次へ | 前のページへ | 次のページへ

2.12　包含データベースによるログインに依存しない DB ユーザー

◆ 包含データベースによるログインに依存しない DB ユーザー

包含データベース（包含データベース）は、SQL Server 2012 から提供された新機能で、ログイン アカウントや照合順序に依存しないデータベースを作成できる機能です。これを利用すれば、ログインアカウントとは完全に独立したデータベース（DB）ユーザーを作成できるようになるので、可用性グループでも役立ちます。

◆ 包含データベースを利用しない場合の DB ユーザーの問題

ここでは、まず包含データベースを利用しなかった場合にどういった問題が発生するのかを試してみましょう。今回作成した AGTestDB データベースは、包含データベースを利用していないので、これで試します。

1．まずは、SQL Server 認証用のログインアカウントを作成するので、SQL Server の認証モードを混合モードへ設定します。認証モードを設定するには、次のように Management Studio でサーバー名を右クリックして［プロパティ］をクリックします。

00176

［サーバーのプロパティ］ダイアログが表示されたら、［セキュリティ］ページを開いて、「SQL Server 認証モードと Windows 認証モード」をチェックして、［OK］ボタンをクリックします。ダイアログを閉じたら、SQL Server を再起動することで、変更が有効になります。

この操作は、SERVER1、SERVER2、SERVER3 で行ってください。

2．次に、プライマリ（SERVER1）へ接続して、ログインアカウントとデータベースユーザーを作成します。ログインアカウントを作成するには、次のように［セキュリティ］フォルダーの［ログイン］フォルダーを右クリックして、「新しいログイン」をクリックします。

00177

［ログイン - 新規作成］ダイアログが表示されたら、ログイン名に「testlogin1」など任意の名前を入力して、「SQL Server 認証」を選択、［パスワード］と［パスワードの確認入力］に「P@ssword」など任意のパスワードを設定します。

3．次に、［ユーザーマッピング］ページを開いて、「AGTestDB」データベースをチェックして、このログインアカウントにマッピングされたデータベースユーザーを作成します。

00178

次に、「db_datareader」ロールをチェックして、［OK］ボタンをクリックします。これでデータベースに対する読み取り操作が可能になります。

4．次に、AGTestDB データベースを展開して、［セキュリティ］フォルダーの［ユーザー］フォルダーを展開すると、testlogin1 ユーザーが作成されていることを確認できます。

00179

このユーザーのプロパティを開くと、［ログイン名］に testlogin1 ログインアカウントが表示されて、ログインアカウントにマッピングされたユーザーであることを確認できます。

5．次に、ツールバーの［データベースエンジンクエリ］ボタンをクリックして、testlogin1 ログインアカウントで接続します。

00180

［認証］で「SQL Server 認証」を選択、［ログイン］に「testlogin1」、パスワードに設定したパスワードを入力して、［接続］ボタンをクリックします。

6．接続完了後、次のように SELECT ステートメントを実行して、t1 テーブルのデータが参照できることを確認します。

00181

7．次に、SERVER2（セカンダリ）へ接続して、AGTestDB データベースを展開し、［セキュリティ］フォルダーの［ユーザー］フォルダーを展開すると、testlogin1 ユーザーが作成されている（複製されている）ことを確認できます

00182

しかし、ユーザーのプロパティを開くと、［ログイン名］が表示されない、マッピングの切れたユーザーとなっていることを確認できます。

8．次に、ツールバーの［データベースエンジンクエリ］ボタンをクリックして、testlogin1 ログインアカウントで SERVER2 への接続を試みます。

00183

しかし、結果はエラーとなります。

00184

SERVER2 には、ログインアカウントを作成していないため、ログインすることができません。

9．次に、SERVER2 へログインアカウントを作成します。

00185

［ログイン名］に「testlogin1」と入力して、「SQL Server 認証」を選択し、任意のパスワードを設定します。

10．次に、［ユーザーマッピング］ページを開いて、「AGTestDB」データベースをチェックして、［OK］ボタンをクリックします。

00186

しかし、AGTestDB データベースは、セカンダリに設定されているデータベースなので、読み取り専用として設定されているので、データベースを更新できない主旨のエラーが返ります。

11．次に、［AGTestDB］データベースのチェックを外して、［OK］ボタンをクリックします。

00187

今度は、「testlogin1 は既に存在します」というエラーが発生します。これは、先ほどのエラーになったときに、ログインアカウントの作成については成功している（データベースユーザーの作成は失敗している）ために発生しています。

12．次に、作成されたログインアカウント（testlogin1）を確認します。

00188

13．次に、もう一度、testlogin1 ログインアカウントで SERVER2 への接続を試みます。

00189

今度は接続できます。

14．接続後、USE ステートメントを実行して、AGTestDB データベースへ接続します。

00190

結果は、エラーとなります。これは、SERVER2 上に作成した「testlogin1」ログインアカウントと AGTestDB データベース内のデータベースユーザー「testlogin1」のマッピングが切れているために発生しています。これでは、せっかく読み取り可能なセカンダリを作成していても、ユーザーがデータベースへ接続できないことになってしまいます。

15．マッピングの切れたユーザーは、次のように sp_change_users_login ストアドプロシージャを実行して確認することができます（このステートメントは、Administrator としてログインした状態で実行してください）。

00191

16．マッピングの切れたユーザーは、Update_One を指定して sp_change_users_login ストアドプロシージャを実行することで、再マッピングする（マッピングを修復する）ことができるのですが、これも次のようにエラーとなります。

00192

セカンダリデータベースは、読み取り専用に設定されるため、マッピング情報の更新（データベース内部のデータ更新）は許可されないのです。

したがって、このステートメントが実行できるのは、セカンダリがプライマリに役割変更されたときだけです。これでは、障害発生時に、（このステートメントが実行されるまで）ユーザーがログインできないことになってしまいます（ダウンタイムが長くなってしまいます）。また、このステートメントを実行しても、内部的な SID を新しいもの（SERVER2 側で作成したログインアカウントの SecurityID）へ置き換えるだけなので、逆の役割変更があった場合（次に SERVER1 が再びプライマリになった場合）には、再度マッピングが切れたユーザー（SERVER1 上のログインアカウントの SID と SERVER2 上の同じログインアカウントの SID が異なるので、再度マッピングが切れる）が発生することにもなります。

したがって、sp_change_users_login ストアドプロシージャによるマッピング更新は、最良の解決策とは言えません。これを解決するための方法が、次の 2つです。

KB（Knowledge Base）918992 で提供される「sp_help_revlogin」ストアドプロシージャを利用して、同じ SID／同じパスワードのログインアカウントをすべてのサーバー上で作成する。この KB は、以下の URL で提供されている
http://support.microsoft.com/kb/918992/ja
SQL Server 2012 から提供された新機能「包含データベース」を利用して、ログインアカウントに依存しないデータベースユーザーを作成する

◆ 包含データベースを利用したログインに依存しない DB ユーザー

包含データベース（Contained Database）機能を利用すると、ログインアカウントに依存しない（マッピングが不要な）データベースユーザーを作成することができます（独立したデータベースユーザーをデータベース内に含められることから Contained と名付けられています）。このようにデータベースユーザーが独立していれば、可用性グループでの役割変更時にも問題なくそのユーザーを利用することができ、またデータベースの移行時（ハードウェアリプレイス時のデータベース移行時や、別のサーバーへデータベースを移動したい場合、開発機から本番機へデータベースを移動したい場合など）にも役立ちます。

それでは、包含データベースを利用して、可用性グループを構成してみましょう。

1．包含データベースを利用するには、まずは sp_configure を利用して、SQL Server の構成オプションで Contained Database Authentication（包含データベース認証）を有効化（1へ設定）します。これは次のように実行します。

EXEC sp_configure 'contained database authentication', 1
RECONFIGURE

00193

このオプションは、可用性グループを構成するすべてのサーバー上で実行しておく必要があるので SERVER2 および SERVER3 でも実行しておきます。

00194

00195

2．次に、AGTestDB データベースを包含データベースへ設定します。包含データベースへ設定するには、次のようにデータベースのプロパティを開いて、［オプション］ページから［コンテインメントの種類］で「部分」を選択します。

00196

Note：［コンテインメントの種類］の「完全」？
SQL Server 2012 では、［コンテインメントの種類］は「部分」と「なし」のみが選択できますが、SQL Server の将来バージョンでは、「完全」が提供されて、さまざまなサーバーオブジェクトに関してもデータベース内に包含したものが作成できるようになる予定です（SQL Server 2012 では、ログインアカウントと照合順序のみが包含できるため、完全ではなく、部分的な包含という位置付けになっています）。

Note： ALTER DATABASE ステートメントで包含データベースを設定する場合
GUI 操作ではなく、ALTER DATABASE ステートメントで包含データベースを設定する場合には、次のように SET オプションで CONTAINMENT=PARTIAL を指定して実行します。

USE master
go
ALTER DATABASE AGTestDB
SET CONTAINMENT = PARTIAL
WITH NO_WAIT
go

次に、データベース内にユーザーを作成します。AGTestDB データベースを展開して、［セキュリティ］フォルダーの［ユーザー］を右クリックして、［新しいユーザー］をクリックします。

00197

［データベースユーザー - 新期］ダイアログが表示されたら、［ユーザーの種類］で「パスワードを持つユーザー」を選択して、［ユーザー名］に「testuser1」、［パスワード］と［パスワードの確認入力］に任意のパスワード（P@ssword など）を設定します。

3．次に、［メンバーシップ］ページを開いて、「db_datareader」ロールをチェックして、［OK］ボタンをクリックします。これで、データベース内のオブジェクトに対する読み取り操作が行えるようになります。

00198

これで、データベースの中に包含されたデータベースユーザーの作成が完了です。

Note： SQL ステートメントで包含データベース内に DB ユーザーを作成する場合
SQL ステートメントを利用して、包含データベース内にデータベースユーザーを作成する場合には、次のように CREATE USER ステートメントを実行します。

USE AGTestDB
go
CREATE USER testuser1
WITH PASSWORD = 'P@ssword'

WITH PASSWORD でパスワードを設定することで、データベースの中に包含されたデータベースユーザーを作成することができます。

4．次に、作成したユーザー（testuser1）で SERVER1 へ接続します。

00199

［認証］で「SQL Server 認証」を選択、［ログイン］に「testuser1」、［パスワード］に設定したパスワード（P@ssword）を入力して［オプション］ボタンをクリックします。

5．次のように［接続プロパティ］タブが表示されたら、［データベースへの接続］へ「AGTestDB」と入力して［接続］ボタンをクリックします。

00200

これで、testuser1 ユーザーとして AGTestDB データベースへ接続することができます。

6．接続完了後、t1 テーブルのデータを参照します。

00201

ステータスバーには、接続しているユーザーと接続先のデータベース名が表示されるので、これで正しく接続できたことを確認できます。

7．次に、SERVER2 に対して、testuser1 で接続します。

00202

［サーバー名］に「SERVER2」、［認証］で「SQL Server 認証」を選択、［ログイン］に「testuser1」、［パスワード］に設定したパスワード（P@ssword）を入力して［オプション］ボタンをクリックします。

8．［接続プロパティ］タブが表示されたら、［データベースへの接続］へ「AGTestDB」と入力して［接続］ボタンをクリックします。

00203

9．接続完了後、t1 テーブルのデータを参照します。

00204

何の問題もなくデータが参照できることを確認できます。

このように包含データベースを利用すれば、データベースユーザーがデータベース内に包含されるので、マッピングの切れたユーザーが発生することはありません。

◆ tempdb データベースの照合順序に依存しない一時テーブルの作成

包含データベースは、tempdb の照合順序に依存しない一時テーブルの作成ができることも大きなメリットです。従来のバージョンでは、CREATE TABLE ステートメントを利用して作成した一時テーブルの照合順序は tempdb の照合順序を継承するため、データベース移行時に、移行元と移行先で tempdb の照合順序が異なる場合に、照合順序の不一致が発生するという問題がありました。

この問題は、次のように包含データベースではない通常のデータベースを作成することで確認することができます。

-- 包含データベースではない通常のデータベースを照合順序 Japanese_CS_AS で作成
CREATE DATABASE testDB
COLLATE Japanese_CS_AS
go

-- テーブル t1 の作成
USE testDB
CREATE TABLE t1 ( a varchar(100) )
INSERT INTO t1 VALUES('aaa')
SELECT * FROM t1

-- 一時テーブル #t2 の作成
CREATE TABLE #t2 ( a varchar(100) )
INSERT INTO #t2 VALUES('aaa')
SELECT * FROM #t2

このステートメントでは、通常のデータベースを照合順序 Japanese_CS_AS で作成しているので、t1 テーブルの文字列データ型の列は、Japanese_CS_AS として作成されます。一方、tempdb の照合順序は Japanese_CI_AS（既定値）へ設定しているので、一時テーブル ＃t２ の文字列データ型の列は Japanese_CI_AS として作成されます。このように異なる照合順序の列がある場合に、この列を JOIN キーとして利用すると、次のようにエラーが発生してしまいます。

00205

このように、包含データベースではない通常のデータベースの場合には、移行元と移行先での tempdb の照合順序に注意する必要がありました。これに対して包含データベースでは、tempdb の照合順序に依存しない一時テーブルの作成が可能です。一時テーブルの照合順序は、包含データベースに設定された照合順序を継承するようになっているため、移行元と移行先で tempdb の照合順序が異なっている場合でも、データベースを問題なく動作させることが可能です。

このように、SQL Server 2012 では、包含データベースが提供されたことによって、データベースの移行（バックアップ／リストアやデタッチ／アタッチによるデータベース移動）が非常に簡単に行えるようになりました。これにより、ハードウェアリプレイス時のデータベース移行時や、別のサーバーへデータベースを移動したい場合、開発機から本番機へデータベースを移動したい場合などで大変役立ちます。

また、可用性グループを構成する場合にも、包含データベースを利用することで、データベースユーザーを何の問題もなく利用できるようになるので、大変便利です。

目次へ | 前のページへ | 次のページへ