Geekly Articles each Day

SQL Server 2011-スタンドアロンデータベース

近年、Microsoftは多くの興味深い技術を導入しており、これらの技術は開発者の武器にしっかりと含まれています。 SQL Server 2005には大幅な変更が加えられ、その後、SQL Server 2008が開発され、成功が強化されました。 Denaliは、多くの新しいツールと既存のツールの拡張機能を提供します。 この記事では、データベース開発者にアピールするはずの新しいツールの1つを詳しく見ていきます。 このツールの機能- スタンドアロンデータベース （包含データベース）。 それらが何であるか、それらをどのように使用するか、そして他に適用できるものを考えてみましょう。

現在のデータベースの何が問題になっていますか？


独立したデータベースの本質の説明に進む前に、それらが発明された理由と、現在の実装が開発者に適さないものを考えてみましょう。

主な問題の一部を次に示します。

既存のデータベースの主な欠点を特定したら、新しいタイプの説明に進みます。


スタンドアロンデータベース


すでに、名前と古い基地の記述された欠点に基づいて、新しい種類の基地の魅力は何かを推測できると思います。 自律型データベースには、作業と構成に必要なすべての情報がそれ自体に保存されます。 このようなデータベースは、SQLサーバーの設定から完全に独立しており、外部依存関係がなく、すべての認証メカニズムが含まれています。 サーバーでどの言語設定が設定されているかは関係ありません。

テーブル、関数、プロシージャ、制限、スキーム、タイプ、ライブラリ、ビュー、ログイン、SQL Serverエージェントタスク、システム設定、リンクサーバー-すべてはデータベースに保存されます。

当然、このようなベースの主な利点は、展開と転送の容易さです。 ベースを展開するだけで十分で、すぐに作業の準備ができています。 ユーザー、その役割、エージェントなどのスクリプトは忘れられなくなります。

覚えておくべき用語


アプリケーション 境界 -サーバーインスタンスとアプリケーションコードの間の境界。 アプリケーションコードとは、プロセスに必要なすべてのオブジェクトを含むデータベース全体を意味します。

アプリケーション モデル - アプリケーションの境界内には、アプリケーションの開発と管理が行われている場所があります。

含まれる -アプリケーションの境界内に完全に含まれるユーザーエンティティ。

非包含 （非包含 ）-アプリケーションの境界を越えるユーザーエンティティ。

非包含データベース—プロパティ包含= NONEのデータベース 。 ベースは、サーバーインスタンスに属するいくつかのオブジェクトに依存します。

完全包含 データベース （スタンドアロンデータベース）-オブジェクトまたは関数がアプリケーションの境界を越えることを許可しないデータベース。

部分的包含 データベース -一部のオブジェクトがアプリケーションの境界を越えて動作できるようにするデータベース。 CTP 1で利用可能。

包含 ユーザー （オフラインユーザー）

ユーザーには2つのタイプがあります。


スタンドアロンデータベースを作成する4つの手順


現時点では、そのようなデータベースがどのように機能するかについての理論的知識と概念はすでに十分にあり、もう少し「現場で」自分自身を伸ばすときだと思います。 次の4つの手順では、スタンドアロンデータベースを作成する方法について説明します。

手順1.サーバーレベルでスタンドアロンデータベースの使用を許可します。

手順2.データベースを作成し、自律モードを部分モードに設定します。 CONTAINMENTプロパティはPartialでなければなりません。

手順3.新しいデータベースにオフラインユーザーを作成します。

手順4.オフラインユーザーアカウントで新しいデータベースに移動します。

次に、各ステップを詳細に、写真で検討します。

手順1.サーバーレベルでスタンドアロンデータベースの使用を許可します。


新しいSQL Server 2011のインスタンスに参加し、オブジェクトエクスプローラー（オブジェクトエクスプローラー）からサーバーのコンテキストメニューを呼び出します。 コンテキストメニューで、[ プロパティ]項目を選択します 。



[ 詳細]ページに移動し、[ 含まれるデータベースを有効にする]プロパティの値をTRUEに設定する必要があります。



スクリプトを使用しても同じことが実現できます。
 --Enabled Advanced options --       Advanced sp_configure 'show advanced', 1; RECONFIGURE WITH OVERRIDE; Go --Enabled Database Containment --     sp_configure 'contained database authentication', 1; RECONFIGURE WITH OVERRIDE; go


ステップ2.データベースを作成し、自律モードを部分に設定します


新しいデータベースを作成し、 TestContainedDBと呼びます 。

作成後、コンテキストメニューからそのプロパティを開きます



[ オプション ]タブを開き、[ 包含 タイプ： ] オプションの [ 部分 ]プロパティを選択します。



スクリプトを使用しても同じことが実現できます。
 使用[マスター]
行く

 CREATE DATABASE [TestContainedDB]
 封じ込め=一部
 主に
ログオン
行く

 ALTER DATABASE [TestContainedDB] SET COMPATIBILITY_LEVEL = 110
行く


手順3.新しいデータベースにオフラインユーザーを作成します。


新しいデータベースで、[ セキュリティ]ノード、[ ユーザー ]の順に移動し、コンテキストメニューを使用して新しいユーザーを作成します。



アカウント名とパスワードを設定します。 たとえば、testuser \ testuserとします。



ユーザーがデータベースの所有者になることを示します。 これを行うには、「 メンバーシップ 」ページでdb_ ownerにチェックマークを付けます。

TSqlを使用して同じアクションを実行できます
 使用[TestContainedDB]
行く
ユーザーの作成[TestUser]
パスワード=「testuser」、
 DEFAULT_SCHEMA = [dbo]
行く

説明したアクションが完了するとすぐに、ユーザーがシステムに表示されることを確認できます。



手順4.オフラインユーザーアカウントで新しいデータベースに移動します。


手順を実演するには、SSMSで作業を完了し、説明されている手順に従って再度ログインする必要があります。





ユーザー名とパスワードのフィールドに、オフラインデータベースのユーザーを作成するときに指定した情報を入力します。 私たちの場合、これはtestuser \ testuserです。



その後、[ オプション ]ボタンをクリックして、[ 接続 プロパティ ]タブに移動します。



このタブでは、参加するデータベースを指定する必要があります。 この場合、 TestContainedDBです。

これで、[ 接続 ]ボタンをクリックできるようになり、ベースの自律的な環境にいることがわかります。



ベースからオフラインへの変換


自律型データベースの利点とその作成方法を説明した後、既存のデータベースをオフラインにできるかどうかを考えたと思います。 できます。 次に、このようなプロセスを示します。 デモはテストベースで行われるため、以下のスクリプトを使用して初心者向けに作成します。
 使用[マスター]
行く

 CREATE DATABASE [NonContainedDB]
 封じ込め=なし
 主に
行く

 ALTER DATABASE [NonContainedDB] SET COMPATIBILITY_LEVEL = 110
行く

 IF（1 = FULLTEXTSERVICEPROPERTY（ 'IsFullTextInstalled'））
始める
 EXEC [NonContainedDB]。[Dbo]。[Sp_fulltext_database] @action = 'enable'
終わり
行く

次に、データを含むテーブルを作成します。
  -テーブルが存在する場合は削除します
 -テーブルが存在する場合は削除します
存在する場合（SELECT * FROM sys.objects WHERE name = N'tbl_Players 'AND type =' U '）
     DROP TABLE tbl_Players
行く
 ANSI_NULLSをオンに設定
行く
 -テーブルを作成する
 -テーブルを作成する
 CREATE TABLE tbl_Players（
	 PlayerID INT IDENTITY、
	 PlayerName VARCHAR（15）、
	 BelongsTo VARCHAR（15）、
	 MatchPlayed INT、
	 RunsMade INT、
	 WicketsTaken INT、
	 FeePerMatch NUMERIC（16.2）
 ）

 -レコードを挿入する
 -テーブルにエントリを作成する
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'A. Wonder'、 'India'、10,440,10、1,000,000）
 tbl_Playersに挿入（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（「A. Cricket」、「India」、10、50、17、400000）
 tbl_Playersに挿入（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（「B. Dhanman」、「India」、10,650,0,3600000）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'C. Barsat'、 'India'、10,950,0,5000000）
 tbl_Playersに挿入（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（「A. Mirza」、「India」、2,3,38、3,600,000）

 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'M. Karol'、 'US'、15,44,4、2,000,000）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'Z. Hamsa'、 'US'、3,580,0、400）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'K. Loly'、 'US'、6,500,12,800000）
 tbl_Playersに挿入（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（「S. Summer」、「US」、87、50、8、1230000）
 tbl_Playersに挿入（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（「J.June」、「US」、12,510.9、4988000）

 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'A.Namaki'、 'Australia'、1,4,180、999999）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'Z. Samaki'、 'Australia'、2,6,147、888888）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'MS。Kaki'、 'Australia'、40,66,0,1234）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'S. Boon'、 'Australia'、170,888,10,890）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'DC。Shane'、 'Australia'、28,39,338、4444499）

 tbl_Playersに挿入（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（「S. Noami」、「Singapore」、165、484、45、5678）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'Z. Biswas'、 'Singapore'、73,51,50、22222）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'K. Dolly'、 'Singapore'、65,59,1,99999）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'S. Winter'、 'Singapore'、7,50,8,12）
 INSERT INTO tbl_Players（PlayerName、BelongsTo、MatchPlayed、RunsMade、WicketsTaken、FeePerMatch）VALUES（ 'J. August'、 'Singapore'、9,99,98、890）



図を完成させるには、ストアドプロシージャを追加します。
 存在する場合（名前* 'usp_SelectRecordsByPlayerName'およびタイプ= 'P'のsys.objectsから選択*）
    ドロッププロシージャusp_SelectRecordsByPlayerName
行く
 -ストアドプロシージャを作成する
プロシージャの作成[dbo] [Usp_SelectRecordsByPlayerName]
 （@PlayerID int）
として
始める
	選択してください
		プレイヤーID
		 PlayerName
		 、BelongsTo
		マッチプレイ済み
		 RunsMade
		 WicketsTaken
		 、FeePerMatch
	から
	 tbl_Players
	ここでPlayerId = @PlayerID
終了



最後に、データベース、テーブル、およびストアドプロシージャが必要です。 これでデータベースは通常の依存モードで動作し、私たちの目標はそれをスタンドアロンのデータベースに変えることです。

ステップ1


最初のステップでは、データベースのサーバーおよびユーザーレベルで新しいユーザーを作成する必要があります。 これは、次のスクリプトを使用して実行できます。
  -サーバーにログインを作成する
 -サーバーレベルでユーザーを作成する
 CREATE LOGIN NonContainedUser
 WITH PASSWORD = 'somepassword @ 123'

 -サーバーにログインするための「非包含」ユーザーを作成する
 -オフラインユーザーを作成する
 NonContainedDBを使用する
行く
ログイン用のユーザーNonContainedUserを作成NonContainedUser
行く


ステップ2


次に、どのオブジェクトがリンクされたデータベースに属しているかを判断する必要があります。 これを行うには、次のコードを実行できます
  NonContainedDBを使用する
行く

選択
	 class_desc
	 、feature_name
	 、feature_type_name
 FROM sys.dm_db_uncontained_entities



結果は、次のスクリーンショットのようになります。



ROUTEは無視できます。 そのため、スクリプトによると、リンクされたデータベースに2つのオブジェクトがあります。

サーバーに属するユーザーを判別するには、次のスクリプトを実行できます。
  NonContainedDBを使用する
行く
 SELECT dp.name
 FROM sys.database_principals dp
 sys.server_principals spをdp.sid = sp.sidに参加
 WHERE dp.authentication_type = 1
   AND sp.is_disabled = 0



実行の結果として私たちに何が得られますか



ステップ3


NonContainedDBに基づいて、コンテキストメニューを呼び出して[ プロパティ ]を選択する必要があります 。 次に、[ オプション ]タブに移動し、[ 封じ込めの 種類]プロパティで[ 部分 ]を選択します。

説明されたアクションの別の方法は、スクリプトを書くことです
 使用マスター
行く
 ALTER DATABASE NonContainedDB SET CONTAINMENT = PARTIAL;
行く



設定を設定したら、ユーザーを自律型データベースに移行する必要があります。
  NonContainedDBを使用する
行く
 EXEC sp_migrate_user_to_contained @username = N'NonContainedUser '、
                                   @rename = N'keep_name '、
                                   @disable_login = N'disable_login '



ユーザーをスタンドアロンデータベースに移行するには、sp_ migrate_ user_ to_に 含まれる プロシージャが必要です。 サーバーレベルのユーザーを自律データベースのユーザーに変換します。 手順の後、依存ユーザーを再度定義するスクリプトを実行できます。
  NonContainedDBを使用する
行く
 SELECT dp.name
 FROM sys.database_principals dp
 sys.server_principals spをdp.sid = sp.sidに参加
 WHERE dp.authentication_type = 1
   AND sp.is_disabled = 0



そして結果：



NonContainedUserが表示されなくなったことを確認できます。 これは、ユーザーが変更され、アカウントがサーバーレベルでロックされたことを意味します。

ステップ4


現在のサーバーから切断し、接続フォームを呼び出します。 ユーザー名とパスワードのフィールドに、オフラインデータベースに移行したユーザーのデータを入力します。 次に、接続オプションで、上記の自律データベースへの接続セクションですべてを指定します。

接続後、データベースに関する行に同様のものが表示されます。



自律データベースをバックアップします。


通常のデータベースのアーカイブと同じ方法で実行されます。 したがって、このプロセスは、ユーザーインターフェイスから、またはスクリプトを使用して実行できます。

インターフェース


SSMSを使用してデータベースに参加し、オブジェクトエクスプローラー（オブジェクトエクスプローラ）で目的のデータベースを見つけます。 コンテキストメニューで、 [タスク]> [バックアップ ]に移動します。



スクリプト


簡単なスクリプトを使用して、データベースのバックアップを作成できます。
 バックアップデータベースTestContainedDB
 TO DISK = '<ファイルパス> \ TestContainedDB.bak'

アーカイブデータベースの回復


繰り返しますが、ユーザーインターフェイスを使用する方法とスクリプトを使用する方法の2つがあります。

インターフェース


インターフェイスを通じて、すべてがアーカイブと同様の方法で行われます。

SSMSを使用してデータベースに参加し、オブジェクトエクスプローラー（オブジェクトエクスプローラ）で目的のデータベースを見つけます。 コンテキストメニューで、 [タスク]> [復元 ]に移動します。

スクリプト


 データベースの復元TestContainedDB
 FROM DISK = '<ファイルパス> \ TestContainedDB.bak'

データベースの回復中に、次のメッセージを受け取ることができます。

メッセージ12824、レベル16、状態1、行1

包含データベースを復元するには、sp_configure値「包含データベース認証」を1に設定する必要があります。 value_in_useを設定するには、RECONFIGUREを使用する必要がある場合があります。

メッセージ3013、レベル16、状態1、行1

RESTORE DATABASEが異常終了しています。

ここから、サーバーインスタンスレベルでSQL Server設定のContained Database Authenticationオプションをアクティブにする必要があることが明らかになります。 この設定はデフォルトでオフになっています。 以下のスクリプトを実行して問題を修正します。
  -有効な詳細オプション
 -オプションを編集する機能を有効にする
 sp_configure 'show advanced'、1;
オーバーライドで再構成します。
行く
 -有効なデータベースの包含
 -オフラインデータベースの認証を有効にする
 sp_configure 'contained database authentication'、1;
オーバーライドで再構成します。
行く


スタンドアロンデータベースに関する詳細情報


自律型データベースでサポートされている認証方法は同じままです。

データベースの変更が変更されました。 CREATE / ALTER DATABASEの 動作が異なるようになりました。 データベースの 変更 < データベース 名>式は機能しなくなりました。 データベース名の代わりに、サービスワードCURRENTを指定する必要があります。
  ALTER DATABASE CURRENT



自律データベースに関する追加情報は、 ここで見つけることができます 。

これはニラドリビスワスの記事の翻訳です 。 彼は、新しいSQL Server 2011に関するより興味深い記事を持っています。翻訳が気に入ったら、残りの翻訳を掲載できます。 記事は非常に大きく、それらを壊す方が良いです。 その結果、4〜5個の部品がリリースされます。

サイクルからの転送：
MS SQL Server 2011： スタンドアロンデータベース 、 新しいシーケンスオブジェクト 、 オフセットステートメント 、 エラー処理 、 結果セット構成 、 SSMSの新機能 。

Source: https://habr.com/ru/post/J123345/

More articles:


All Articles