データベースを別のサーバー インスタンスで使用できるようにする場合のメタデータの管理 (SQL Server)

このトピックは、次の状況で関連します。

• Always On 可用性グループの可用性レプリカを構成すること。

• データベースのミラーリングを設定する。

• ログ配布構成でプライマリ サーバーとセカンダリ サーバーの間でロールを変更する準備をする場合。

• データベースを別のサーバー インスタンスに復元する。

• データベースのコピーを別のサーバー インスタンスにアタッチする。

一部のアプリケーションは、単一ユーザー データベースのスコープ外にある情報、エンティティ、オブジェクトに依存します。 通常、アプリケーションには master データベースと msdb データベース、およびユーザー データベースに対する依存関係があります。 ユーザー データベースの外部に格納される (ユーザー データベースを正しく機能させるために必要な) すべてのデータは、対象のサーバー インスタンスで使用できるようにする必要があります。 たとえば、アプリケーションのログインは メタデータとしてマスター データベースに格納され、移行先サーバーで再作成する必要があります。 アプリケーションまたはデータベースのメンテナンス プランが、 msdb データベースに格納されているメタデータを持つ SQL Server エージェント ジョブに依存している場合は、移行先サーバー インスタンスでそれらのジョブを再作成する必要があります。 同様に、サーバー レベルのトリガーのメタデータは マスターに格納されます。

アプリケーションのデータベースを別のサーバー インスタンスに移動する場合は、ターゲット サーバー インスタンス上の master および msdb 内の依存エンティティとオブジェクトのすべてのメタデータを再作成する必要があります。 たとえば、データベース アプリケーションでサーバー レベルのトリガーを使用する場合、新しいシステムでデータベースをアタッチまたは復元するだけでは不十分です。 マスター データベースでこれらのトリガーのメタデータを手動で再作成しない限り、データベースは期待どおりに動作しません。

ユーザー データベースの外部に格納されている情報、エンティティ、およびオブジェクト

このトピックの残りの部分では、別のサーバー インスタンスで使用できるデータベースに影響を与える可能性がある潜在的な問題についてまとめます。 次の一覧に示す情報、エンティティ、またはオブジェクトの種類を 1 つ以上再作成する必要がある場合があります。 概要を表示するには、アイテムのリンクをクリックします。

• サーバーの構成設定

• 資格情報

• 複数データベース間のクエリ

• データベースの所有権

• 分散クエリ/リンク サーバー

• 暗号化されたデータ

• ユーザー定義エラー メッセージ

• イベント通知と Windows Management Instrumentation (WMI) イベント (サーバー レベル)

• 拡張ストアド プロシージャ

• SQL Server プロパティのフルテキスト エンジン

• 仕事

• ログイン

• アクセス許可

• レプリケーションの設定

• Service Broker アプリケーション

• スタートアップ プロシージャ

• トリガー (サーバー レベル)

サーバー構成の設定

SQL Server 2005 以降のバージョンでは、主要なサービスと機能を選択的にインストールして起動します。 これは、システムの攻撃可能な領域を減らすのに役立ちます。 新規インストールの既定の構成では、多くの機能が有効になっていません。 データベースが既定でオフになっているサービスまたは機能に依存している場合は、移行先サーバー インスタンスでこのサービスまたは機能を有効にする必要があります。

これらの設定と有効化または無効化の詳細については、「 サーバー構成オプション (SQL Server)」を参照してください。

[上]

資格情報

資格情報は、SQL Server 外部のリソースへの接続に必要な認証情報を含むレコードです。 ほとんどの資格情報は、Windows ログインとパスワードで構成されます。

この機能の詳細については、「 資格情報 (データベース エンジン)」を参照してください。

[上]

複数データベースにまたがるクエリ

DB_CHAININGおよび TRUSTWORTHY データベース オプションは、既定では OFF です。 元のデータベースに対してこれらのいずれかが ON に設定されている場合は、移行先サーバー インスタンス上のデータベースで有効にする必要がある場合があります。 詳細については、「 ALTER DATABASE (Transact-SQL)」を参照してください。

アタッチとデタッチの操作により、データベース間の所有権の連鎖が無効になります。 チェーンを有効にする方法については、「 クロス db 所有権チェーン サーバー構成オプション」を参照してください。

詳細については、「信頼できるプロパティを使用するようにミラー データベースを設定する (Transact-SQL)」も参照してください。

[上]

データベースの所有権

データベースが別のコンピューターに復元されると、復元操作を開始した SQL Server ログインまたは Windows ユーザーが自動的に新しいデータベースの所有者になります。 データベースが復元されると、システム管理者または新しいデータベース所有者がデータベースの所有権を変更できます。

分散クエリとリンク サーバー

OLE DB アプリケーションでは、分散クエリとリンク サーバーがサポートされています。 分散クエリは、同じコンピューターまたは異なるコンピューター上の複数の異種データ ソースからのデータにアクセスします。 リンク サーバーの構成により、SQL Server はリモート サーバー上の OLE DB データ ソースに対してコマンドを実行できます。 これらの機能の詳細については、「 リンク サーバー (データベース エンジン)」を参照してください。

[上]

暗号化されたデータ

別のサーバー インスタンスで使用できるようにするデータベースに暗号化されたデータが含まれており、データベース マスター キーが元のサーバーのサービス マスター キーによって保護されている場合は、サービス マスター キーの暗号化を再作成することが必要な場合があります。 データベース マスター キーは、暗号化されたデータベース内の証明書と非対称キーの秘密キーを保護するために使用される対称キーです。 データベース マスター キーを作成すると、Triple DES アルゴリズムとユーザー指定のパスワードを使用して暗号化されます。

サーバー インスタンスでデータベース マスター キーの自動復号化を有効にするには、サービス マスター キーを使用してこのキーのコピーを暗号化します。 この暗号化されたコピーは、データベースと マスターの両方に格納されます。 通常、master に格納されているコピーは、 マスター キーが変更されるたびに自動的に更新されます。 SQL Server はまず、インスタンスのサービス マスター キーを使用してデータベース マスター キーの暗号化を解除しようとします。 その暗号化解除が失敗した場合、SQL Server は資格情報ストアで、マスター キーが必要なデータベースと同じファミリ GUID を持つマスター キー資格情報を検索します。 その後、SQL Server は、復号化が成功するか、それ以上資格情報が存在しない限り、一致する資格情報ごとにデータベース マスター キーの暗号化を解除しようとします。 サービス マスター キーによって暗号化されていないマスター キーは、OPEN MASTER KEY ステートメントとパスワードを使用して開く必要があります。

暗号化されたデータベースが SQL Server の新しいインスタンスにコピー、復元、またはアタッチされている場合、サービス マスター キーによって暗号化されたデータベース マスター キーのコピーは、移行先サーバー インスタンスの マスター に格納されません。 移行先サーバー インスタンスで、データベースのマスター キーを開く必要があります。 マスター キーを開くには、OPEN MASTER KEY DECRYPTION BY PASSWORD ='password' ステートメントを実行します。 その後、ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY というステートメントを実行して、データベース マスター キーの自動暗号化解除を有効にすることをお勧めします。 この ALTER MASTER KEY ステートメントは、サービス マスター キーで暗号化されたデータベース マスター キーのコピーを使用してサーバー インスタンスをプロビジョニングします。 詳細については、 OPEN MASTER KEY (Transact-SQL) および ALTER MASTER KEY (Transact-SQL) を参照してください。

ミラー データベースのデータベース マスター キーの自動復号化を有効にする方法については、「 暗号化されたミラー データベースのセットアップ」を参照してください。

詳細については、以下も参照してください。

• 暗号化階層

• 暗号化されたミラー データベースの設定

• 2 台のサーバーに同一の対称キーを作成する

[上]

ユーザー定義エラー メッセージ

ユーザー定義エラー メッセージは 、sys.messages カタログ ビューに存在します。 このカタログ ビューは マスターに格納されます。 データベース アプリケーションがユーザー定義エラー メッセージに依存していて、データベースが別のサーバー インスタンスで使用できる場合は、 sp_addmessage を使用して、それらのユーザー定義メッセージを宛先サーバー インスタンスに追加します。

[上]

イベント通知と Windows Management Instrumentation (WMI) イベント (サーバー レベル)

Server-Level イベント通知

サーバー レベルのイベント通知は msdb に格納されます。 そのため、データベース アプリケーションがサーバー レベルのイベント通知に依存している場合は、そのイベント通知を宛先サーバー インスタンスに再作成する必要があります。 サーバー インスタンスでイベント通知を表示するには、 sys.server_event_notifications カタログ ビューを使用します。 詳細については、「 イベント通知」を参照してください。

さらに、イベント通知は Service Broker を使用して配信されます。 受信メッセージのルートは、サービスを含むデータベースには含まれません。 代わりに、明示的なルートは msdb に格納されます。 サービスで msdb データベース内の明示的なルートを使用して受信メッセージをサービスにルーティングする場合は、別のインスタンスにデータベースをアタッチするときに、このルートを再作成する必要があります。

Windows Management Instrumentation (WMI) イベント

WMI Provider for Server Events を使用すると、Windows Management Instrumentation (WMI) を使用して SQL Server のイベントを監視できます。 データベースが依存する WMI プロバイダーを介して公開されるサーバー レベルのイベントに依存するアプリケーションは、移行先サーバー インスタンスのコンピューターを定義する必要があります。 WMI イベント プロバイダーは、 msdb で定義されているターゲット サービスを使用してイベント通知を作成します。

SQL Server Management Studio を使用して WMI アラートを作成するには

• WMI イベント アラートを作成する

ミラー化されたデータベースのイベント通知のしくみ

ミラー化されたデータベースがフェールオーバーされる可能性があるため、ミラー化されたデータベースを含むイベント通知のデータベース間配信は、定義上はリモートです。 Service Broker は、ミラー化されたルートの形式で、ミラー化 されたデータベースに対して特別なサポートを提供します。 ミラー化されたルートには、プリンシパル サーバー インスタンス用とミラー サーバー インスタンス用の 2 つのアドレスがあります。

ミラー化されたルートを設定すると、Service Broker のルーティングでデータベース ミラーリングが認識されます。 ミラー化されたルートを使用すると、Service Broker は会話を現在のプリンシパル サーバー インスタンスに透過的にリダイレクトできます。 たとえば、ミラー化されたデータベースによってホストされているサービス Service_A Database_Aについて考えてみます。 Service_Aとのダイアログを作成するには、Database_Bによってホストされている別のサービス Service_Bが必要であるとします。 このダイアログを可能にするには、Database_B Service_Aのミラー化されたルートが含まれている必要があります。 さらに、Database_Aには、Service_Bへのミラー化されていない TCP トランスポート ルートが含まれている必要があります。これは、ローカル ルートとは異なり、フェールオーバー後も有効なままです。 これらのルートがフェールオーバー後に ACK が返されるようにします。 送信側のサービスには常に同じ方法で名前が付けられているため、ルートでブローカー インスタンスを指定する必要があります。

ミラー化されたルートの要件は、ミラー化されたデータベース内のサービスがイニシエーター サービスかターゲット サービスかに関係なく適用されます。

• ターゲット サービスがミラー化されたデータベース内にある場合、イニシエーター サービスには、ターゲットへのミラー化されたルートが必要です。 ただし、ターゲットはイニシエーターへの通常のルートを持つことができます。

• イニシエーター サービスがミラー化されたデータベース内にある場合、受信確認と応答を配信するには、ターゲット サービスにイニシエーターへのミラー化されたルートが必要です。 ただし、イニシエーターはターゲットへの通常のルートを持つことができます。

[上]

拡張ストアド プロシージャ

拡張ストアド プロシージャは、SQL Server 拡張ストアド プロシージャ API を使用してプログラミングされます。 sysadmin 固定サーバー ロールのメンバーは、拡張ストアド プロシージャを SQL Server のインスタンスに登録し、ユーザーにプロシージャを実行する権限を付与できます。 拡張ストアド プロシージャは、 マスター データベースにのみ追加できます。

拡張ストアド プロシージャは、SQL Server のインスタンスのアドレス空間で直接実行され、メモリ リークやその他の問題が発生して、サーバーのパフォーマンスと信頼性が低下する可能性があります。 参照されるデータを含むインスタンスとは別の SQL Server のインスタンスに拡張ストアド プロシージャを格納することを検討する必要があります。 また、分散クエリを使用してデータベースにアクセスすることも検討する必要があります。

詳細については、「 GRANT オブジェクト権限 (Transact-SQL)、 DENY オブジェクト権限 (Transact-SQL)、 および REVOKE オブジェクト権限 (Transact-SQL)」を参照してください。

[上]

SQL Server のプロパティの Full-Text エンジン

プロパティは、 sp_fulltext_serviceによって Full-Text エンジンで設定されます。 移行先サーバー インスタンスに、これらのプロパティに必要な設定があることを確認します。 これらのプロパティの詳細については、「 FULLTEXTSERVICEPROPERTY (Transact-SQL)」を参照してください。

さらに、 ワード ブレーカーとステマー コンポーネントまたは フルテキスト検索フィルター コンポーネントのバージョンが元のサーバー インスタンスと移行先サーバー インスタンスで異なる場合、フルテキスト インデックスとクエリの動作が異なる場合があります。 また、 類義語辞典 はインスタンス固有のファイルに格納されます。 これらのファイルのコピーを移行先サーバー インスタンス上の同等の場所に転送するか、新しいインスタンスで再作成する必要があります。

詳細については、以下も参照してください。

• Full-Text カタログとインデックスのバックアップと復元

• データベース ミラーリングと Full-Text カタログ (SQL Server)

[上]

仕事

データベースが SQL Server エージェント ジョブに依存している場合は、移行先サーバー インスタンスで再作成する必要があります。 ジョブはその環境に依存します。 移行先サーバー インスタンスで既存のジョブを再作成する場合は、移行先サーバー インスタンスを元のサーバー インスタンス上のジョブの環境と一致するように変更する必要がある場合があります。 次の環境要因が重要です。

• ジョブによって使用されるログイン

  SQL Server エージェント ジョブを作成または実行するには、まず、ジョブに必要なすべての SQL Server ログインを移行先サーバー インスタンスに追加する必要があります。 詳細については、「 SQL Server エージェント ジョブを作成および管理するためのユーザーの構成」を参照してください。

• SQL Server エージェント サービスのスタートアップ アカウント

  サービス スタートアップ アカウントは、SQL Server エージェントが実行される Microsoft Windows アカウントとそのネットワーク アクセス許可を定義します。 SQL Server エージェントは、指定されたユーザー アカウントとして実行されます。 エージェント サービスのコンテキストは、ジョブとその実行環境の設定に影響します。 アカウントには、ジョブで必要なネットワーク共有などのリソースへのアクセス権が必要です。 サービスのスタートアップ アカウントを選択および変更する方法については、「 SQL Server エージェント サービスのアカウントを選択する」を参照してください。

  正しく動作するには、正しいドメイン、ファイル システム、レジストリのアクセス許可を持つサービス スタートアップ アカウントを構成する必要があります。 また、ジョブには、サービス アカウント用に構成する必要がある共有ネットワーク リソースが必要になる場合があります。 詳細については、「 Windows サービス アカウントとアクセス許可の構成」を参照してください。

• SQL Server の特定のインスタンスに関連付けられている SQL Server エージェント サービスには独自のレジストリ ハイブがあり、そのジョブは通常、このレジストリ ハイブの 1 つ以上の設定に依存します。 意図したとおりに動作するには、ジョブにこれらのレジストリ設定が必要です。 スクリプトを使用して別の SQL Server エージェント サービスでジョブを再作成すると、そのレジストリにそのジョブの正しい設定が設定されていない可能性があります。 再作成されたジョブが移行先サーバー インスタンスで正しく動作するには、元の SQL Server エージェント サービスと移行先 SQL Server エージェント サービスのレジストリ設定が同じである必要があります。

  注意事項

  再作成されたジョブを処理するために移行先 SQL Server エージェント サービスのレジストリ設定を変更すると、現在の設定が他のジョブで必要な場合に問題が発生する可能性があります。 さらに、レジストリを誤って編集すると、システムが深刻な損傷を受ける可能性があります。 レジストリを変更する前に、コンピューター上の値のデータをバックアップすることをお勧めします。

• SQL Server エージェント プロキシ

  SQL Server エージェント プロキシは、指定されたジョブ ステップのセキュリティ コンテキストを定義します。 ジョブを移行先サーバー インスタンスで実行するには、必要なすべてのプロキシをそのインスタンスで手動で再作成する必要があります。 詳細については、「 SQL Server エージェント プロキシの作成 」および「 プロキシを使用するマルチサーバー ジョブのトラブルシューティング」を参照してください。

詳細については、以下も参照してください。

• ジョブ を実装する

• ロールの切り替え後のログインとジョブの管理 (SQL Server) (データベース ミラーリング用)

• Windows サービス アカウントとアクセス許可を構成 する (SQL Server のインスタンスをインストールする場合)

• SQL Server エージェントを構成する (SQL Server のインスタンスをインストールする場合)

• SQL Server エージェントセキュリティ を実装する

既存のジョブとそのプロパティを表示するには

• ジョブアクティビティ監視

• sp_help_job (Transact-SQL)

• ジョブステップ情報を表示

• dbo.sysjobs (Transact-SQL)

ジョブを作成するには

• ジョブの作成

• ジョブの作成

スクリプトを使用してジョブを再作成するためのベスト プラクティス

まず、単純なジョブをスクリプト化し、他の SQL Server エージェント サービスでジョブを再作成し、ジョブを実行して意図したとおりに動作するかどうかを確認することをお勧めします。 これにより、非互換性を特定し、それらを解決しようとします。 スクリプトジョブが新しい環境で意図したとおりに動作しない場合は、その環境で正常に動作する同等のジョブを作成することをお勧めします。

[上]

ログイン

SQL Server のインスタンスにログインするには、有効な SQL Server ログインが必要です。 このログインは、プリンシパルが SQL Server のインスタンスに接続できるかどうかを確認する認証プロセスで使用されます。 対応する SQL Server ログインが定義されていないか、サーバー インスタンスで正しく定義されていないデータベース ユーザーは、インスタンスにログインできません。 このようなユーザーは、そのサーバー インスタンスのデータベースの 孤立ユーザー と呼ばれます。 データベースを復元、アタッチ、または SQL Server の別のインスタンスにコピーした後に、データベース ユーザーが孤立する可能性があります。

データベースの元のコピーにある一部またはすべてのオブジェクトのスクリプトを生成するには、スクリプトの生成ウィザードを使用し、[ スクリプト オプションの選択 ] ダイアログ ボックスで[ スクリプト ログイン ] オプションを True に設定します。

[上]

権限

データベースが別のサーバー インスタンスで使用可能になると、次の種類のアクセス許可が影響を受ける可能性があります。

• システムオブジェクトに対する権限：GRANT、REVOKE、DENY

• サーバー インスタンスに対する GRANT、REVOKE、または DENY アクセス許可 (サーバー レベルのアクセス許可)

システム オブジェクトに対する GRANT、REVOKE、DENY 権限

ストアド プロシージャ、拡張ストアド プロシージャ、関数、ビューなどのシステム オブジェクトに対する権限は マスター データベースに 格納され、移行先サーバー インスタンスで構成する必要があります。

データベースの元のコピーの一部またはすべてのオブジェクトのスクリプトを生成するには、スクリプトの生成ウィザードを使用し、[ スクリプト オプションの選択] ダイアログ ボックスで、[ スクリプト Object-Level 権限 ] オプションを True に設定します。

システム オブジェクトは、 sys.system_objects カタログ ビューに表示されます。 システム オブジェクトに対する権限は、マスター データベースの sys.database_permissions カタログ ビューに表示されます。 これらのカタログ ビューにクエリを実行し、システム オブジェクトのアクセス許可を付与する方法については、「 GRANT System Object Permissions (Transact-SQL)」を参照してください。 詳細については、「 システム オブジェクトの権限の取り消し (Transact-SQL) 」および「 DENY System Object Permissions (Transact-SQL)」を参照してください。

サーバー インスタンスに対する GRANT、REVOKE、DENY 権限

サーバー スコープのアクセス許可は マスター データベースに格納され、移行先サーバー インスタンスで構成する必要があります。 サーバー インスタンスのサーバーのアクセス許可については、 sys.server_permissions カタログ ビューに対してクエリを実行し、サーバー プリンシパルのクエリに関する情報については 、sys.server_principalsのカタログ ビューに対してクエリを実行し、サーバー ロールのメンバーシップについては 、sys.server_role_members カタログ ビューに対してクエリを実行します。

詳細については、「 GRANT Server Permissions (Transact-SQL)、 REVOKE Server Permissions (Transact-SQL)、 DENY Server Permissions (Transact-SQL)」を参照してください。

Server-Level 証明書または非対称キーのアクセス許可

サーバー レベルのアクセス許可を証明書または非対称キーに直接付与することはできません。 代わりに、特定の証明書または非対称キー専用に作成されたマップされたログインに、サーバー レベルのアクセス許可が付与されます。 そのため、サーバー レベルのアクセス許可を必要とする各証明書または非対称キーには、独自の 証明書マップログイン または 非対称キーマップログインが必要です。 証明書または非対称キーに対するサーバー レベルのアクセス許可を付与するには、マップされたログインに対するアクセス許可を付与します。

マップされたログインとそのアクセス許可は両方とも マスターに存在します。 証明書または非対称キーが master 以外のデータベースに存在する場合は、 masterで再作成 し、ログインにマップする必要があります。 データベースを別のサーバー インスタンスに移動、コピー、または復元する場合は、コピー先サーバー インスタンスの マスター データベースにその証明書または非対称キーを再作成し、ログインにマップし、ログインに必要なサーバー レベルのアクセス許可を付与する必要があります。

証明書または非対称キーを作成するには

• 証明書を作成する (Transact-SQL)

• 非対称キーを作成する（Transact-SQL）

証明書または非対称キーをログインにマップするには

• CREATE LOGIN (Transact-SQL)

マップされたログインにアクセス許可を割り当てるには

• GRANT (サーバーの権限の許可) (Transact-SQL)

証明書と非対称キーの詳細については、「 暗号化階層」を参照してください。

[上]

レプリケーションの設定

レプリケートされたデータベースのバックアップを別のサーバーまたはデータベースに復元する場合、レプリケーション設定を保持することはできません。 この場合は、バックアップの復元後にすべてのパブリケーションとサブスクリプションを再作成する必要があります。 このプロセスを簡単にするには、現在のレプリケーション設定用のスクリプトと、レプリケーションの有効化と無効化用のスクリプトを作成します。 レプリケーション設定を再作成するには、これらのスクリプトをコピーし、移行先サーバー インスタンスで機能するようにサーバー名の参照を変更します。

詳細については、「 レプリケートされたデータベースのバックアップと復元、 データベース ミラーリングとレプリケーション (SQL Server)、 ログ配布とレプリケーション (SQL Server)」を参照してください。

[上]

Service Broker アプリケーション

Service Broker アプリケーションの多くの側面がデータベースと共に移動します。 ただし、アプリケーションの一部の側面は、新しい場所で再作成または再構成する必要があります。

[上]

スタートアップ プロシージャ

スタートアップ プロシージャは、自動実行のマークが付けられたストアド プロシージャであり、SQL Server が起動するたびに実行されます。 データベースがスタートアップ プロシージャに依存している場合は、対象のサーバー インスタンスでデータベースを定義し、起動時に自動的に実行されるように構成する必要があります。

[上]

トリガー (サーバー レベル)

DDL トリガーは、さまざまなデータ定義言語 (DDL) イベントに応答してストアド プロシージャを起動します。 これらのイベントは、主に、キーワード CREATE、ALTER、DROP で始まる Transact-SQL ステートメントに対応します。 DDL に似た操作を実行する特定のシステム ストアド プロシージャでも、DDL トリガーを起動できます。

この機能の詳細については、「 DDL トリガー」を参照してください。

[上]

こちらもご覧ください

包含データベース
他のサーバーへのデータベースのコピー
データベースのデタッチとアタッチ (SQL Server)
ログ配布のセカンダリへのフェールオーバー (SQL Server)
データベース ミラーリング セッション中のロールの切り替え (SQL Server)
暗号化されたミラー データベースの設定
SQL Server 構成マネージャー
孤立したユーザーのトラブルシューティング (SQL Server)