データベースを作成および管理し、SQL Serverでユーザーを作成します
Mục lục
システムデータを変更する
ユーザーは、システムテーブル、システムストアドプロシージャ、カタログビュー（目次）などのシステムデータベースオブジェクトの情報を直接更新することはできません。ただし、ユーザーは、システム全体を管理し、すべてのユーザーとデータベースオブジェクトを次のように管理できる管理ツールの完全なセットを利用できます。
- SSMS管理機能：SQL Server 2015以降、いくつかのSQLServer管理ユーティリティがSSMSに統合されています。これは、SQLServerインストールのコア管理コンソールです。これにより、完全な高レベルの管理機能、定期的なメンテナンスタスクのスケジュール設定などが可能になります。
- SQL Server管理オブジェクト（SQL-SMO）API ：SQLServerアプリケーションを管理するための完全な機能が含まれています
- Transact-SQLスクリプトとストアドプロシージャ：これらはシステムストアドプロシージャとT-SQLDDLステートメントです。
ユーザー定義データベース
SQL Serverでは、ユーザーはユーザー定義データベースと呼ばれる独自のデータベースを作成し、それらを操作できます。これらのデータベースの目的は、ユーザーデータを保存することです。
各SQLServerインスタンスは最大32767のデータベースを保持でき、各データベースは最大32767のファイルを保持できます。 SQL Serverでデータベースを作成すると、オペレーティングシステムには、データファイルとログファイルの少なくとも2つのファイルがあります。データファイルには、テーブル、インデックス、ストアドプロシージャ、ビューなどのデータとオブジェクトが含まれています。ログファイルは、アーカイブしてデータベースの状態に戻るのを支援する目的で、データベースの変更を記録します
基本的に、SQL Serverデータベースはこれらのファイルだけを気にする必要があります。データベースに属するものはすべてこれらのファイルにカプセル化され、これらのファイルをすべて別のマシンにコピーするだけで、データベースをある場所から別の場所に移動できます（もちろん他にもあります）バックアップ/復元などの方法）、それらをそのサーバー上のSQLServerに接続します。
要約すると、SQLServerデータベースには2つの主要なタイプのファイルがあります。
- .mdfファイルはメインデータベースファイルと呼ばれ、スキーマとデータが含まれています
- .ldfファイルにはログが含まれています
- さらに、データベースは拡張子.ndfの2番目のデータベースファイルを使用できます。
T-SQLでデータベースを作成する（T-SQLでデータベースを作成する）
ドキュメント： https ：//docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database？view = sql-server-ver15
完全な構文（注、[]の句はオプションです：
CREATE DATABASE DATABASE_NAME [ON [PRIMARY] [<filespec>[,...n] [,<filegroup>[,...n]] [LOGON {<filespec[,...n]}] ] [collate collation_name] [;]説明：
- DATABASE_NAME：作成するデータベースの名前
- オン：ファイルがハードドライブのどこに保存されているかを示します。
- PRIMARY：<filespec>に関連付けられてプライマリファイルを定義します
- <filespec>：ファイルの制御.attribute
- <filegroup>：<filegroup>のコントロール。属性
- LOG ON：ログファイルのアーカイブファイルを示します。
- COLLATE：データベースの照合コードを指定します。照合は、ローカルおよび言語固有の基準（通常は必要に応じて照合を選択）に基づいて文字データを比較およびソートするためのルールを定義します。データベースはUnicodeデータを格納できます。
例えば：
CREATE DATABASE [Customer] ON PRIMARY (NAME = 'Customer_DBX', FILENAME = 'C:DATACustomer_DB.mdf') LOG ON (NAME = 'Customer_DB_log', FILENAME = 'C:DATACustomer_DB_log.ldf') COLLATE SQL_Latin1_General_CP1_CI_ASコマンドを実行した後、SQL Serverは、パスC：dataに格納されているハードファイルを使用して新しいデータベースを作成します。
オブジェクトエクスプローラーを更新して、顧客データベースを再度表示します
またはもっと簡潔に：
CREATE DATABASE [CustomerDB] -- Voi cach tao nay duong dan file database se do Microsoft SQL dinh nghia
データベースの変更
ユーザー定義データベースが拡大または縮小すると、データベースサイズは自動または手動で拡大または縮小されます。時間の経過とともに変化する要件に基づいて、データベースの変更が必要になる場合があります。
ALTER DATABASE database_name {<add_or_modify_files> | <add_or_modify_filegroups> | <set_database_options> | MODIFY NAME = new_database_name | COLLATE collation_name } [;]説明：
- database_name：db .name
- MODIFY NAME = new_database_name：変更する新しいデータベース名です
- collate collation_name：dbの照合名
- <add_or_modify_files>：ファイルの追加、ファイルの削除、またはファイルの編集
- <add_or_modify_filegroups>：ファイルグループは、データベースに追加、編集、またはデータベースから削除できます。
- <set_database_options>：データベースごとに設定できるデータベースプロパティに影響を与えるデータベースレベルのオプションです。これらのオプションは各データベースに固有であり、他のデータベースには影響しません。
データベース名を編集する例：
ALTER DATABASE Customer MODIFY NAME = CusDBSQL Serverユーザーの作成（SQL Serverユーザーの作成）
ドキュメント： https ：//docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user？view = sql-server-ver15
例えば：
-- Creates the login toanngo92 with password '1234'. CREATE LOGIN toanngo92 WITH PASSWORD = '1234'; GO -- Creates a database user for the login created above. CREATE USER toanngo92 FOR LOGIN toanngo92; GOデータベース所有者（データベース所有権）
SQL Serverでは、ユーザー定義データベースの所有権を変更できます。システムデータベースの所有権は変更できません。 sp_changedbownerという名前のシステムプロシージャは、データベースの所有権を変更するために使用されます。
構文：
sp_changedbowner [@loginname=]'login'ログインを既存のユーザー名として使用します。
sp_changedbownerが実行された後、新しい所有者（dboと呼ばれる）がデータベースの所有者として選択されます。 Dboには、すべてのデータベースアクションを処理する権限があります。 master、model、tempdb（システムデータベースのdb）の所有者は所有者を変更できません。
例えば：
use 'CusDB' sp_changedbowner 'toanngo92'コマンドの実行後、CusDBデータベースはその所有者を「toanngo92」に変更しました。「toanngo92」という名前のログインアカウントは、CusDBデータベースを直接操作できます。
データベースオプションの設定
データベースレベルのオプションはデータベースのプロパティを指定し、データベースごとに設定できます。これらのオプションは各データベースに固有であり、他のデータベースには影響しません。データベースオプションには、データベースの作成時にデフォルト値が割り当てられます。デフォルト値は、 ALTERDATABASEステートメントのSET句を使用して変更できます。
SQLServerのデータベースオプション
| オプションタイプ | 説明 |
| 自動オプション | データベースの自動動作を制御する |
| カーソルオプション | カーソル動作制御 |
| 回復オプション | データベースのリカバリ（フォールバック）モデルを制御する |
| その他のオプション | ANSI 。標準制御 |
| 状態オプション | オンライン/オフライン、ユーザー接続など、データベースの状態を制御します |
注： sp_configureシステムストアドプロシージャまたはSQLManagementStudioという名前のプロシージャを使用してデータベース設定を構成します。
CusDBデータベースのAUTO_SHRINKオプションをONにする例。オプションをオンにすると、データベースは自動的に縮小/縮小してメモリを節約します（ただし、この機能はお勧めしません）
ドキュメントAUTO_SHRINK： https ://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/set-the-auto-shrink-database-option-to-off?view=sql-server -2017
USE 'CusDB' ALTER DATABASE 'CusDB' SET AUTO_SHRINK ONファイルグループ
SQL Serverでは、ハードドライブ内のデータファイルを使用してデータベースを格納します。データファイルをさらにファイルグループに分割して、パフォーマンスを向上させることができます。各ファイルグループ（関連ファイルをグループ化するために使用されるfilegroup_はデータベースオブジェクトを格納します。すべてのデータベースにはデフォルトでメインファイルグループがあります。このファイルグループにはデータファイルが含まれます。メインファイルグループとデータファイルはデフォルトの属性値で自動的に作成されます。管理時、データ割り当て、および場所の目的。
たとえば、customer_data1.ndf、customer_data2.ndf、customer_data3.ndfという名前の3つのファイルがあり、これらは対応する3つのハードドライブに作成でき、customer_fgroup1という名前の1つのファイルグループに割り当てることができます。次に、customer_fgroup1ファイルグループにテーブルを具体的に作成できます。次に、特にfilgroupcustomer_fgroup1にテーブルを作成できます。テーブルからのデータのクエリは3つのドライブに分散されるため、パフォーマンスがさらに向上します。
現在のデータベースにファイルグループを追加する
ファイルグループは、データベースが最初に作成されたときに作成することも、ファイルがデータベースに挿入された後に作成することもできます。ただし、ファイルがデータベースに含まれた後は、ファイルを別のファイルグループに移動することはできません。
ファイルは、同時に複数のファイルグループのメンバーになることはできません。データベースごとに許可されるファイルグループは最大32,767個です。ファイルグループには、データファイルのみを含めることができます。トランザクションログファイルはファイルグループに属することはできません。
初期化中にファイルグループをデータベースに配置するファイルグループを作成する例：
CREATE DATABASE [SalesDB] ON PRIMARY (NAME = 'SalesDB' , FILENAME = 'C:dataSalesDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), FILEGROUP [MyFileGroup] (NAME = 'SalesDB_FG', FILENAME='C:dataSalesDB_FG.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) LOG ON (NAME = 'SalesDB_log', FILENAME='C:dataSalesDB_log.ldf', SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS
ファイルグループを現在のデータベースに配置するときの構文：
ALTER DATABASE database_name (<add_or_modify_files> |<add_or_modify_filegroups> |<set_database_options> |MODIFY NAME=new_database_name |COLLATE collation_name )[;]例えば：
USE 'CusDB' ALTER DATABASE 'CusDB' ADD FILEGROUP FG_Readonlyコマンドが実行されると、SQL Serverは「コマンドが正常に完了しました」というメッセージを表示し、ファイルグループFG_Readonlyが「CusDB」として現在のデータベースに挿入されます。
デフォルトのファイルグループ
オブジェクトは、データベースで作成されるときにデフォルトのファイルグループに割り当てられます。ファイルグループPRIMARYはデフォルトのファイルグループです。デフォルトのファイルグループは、ALTERDATABASEステートメントを使用して変更できます。システムオブジェクトとテーブルオブジェクトはPRIMARYファイルグループに残りますが、ALTERはまだ新しいファイルグループに入りません。
新しいファイルを作成し、ファイルグループFG_ReadOnlyに入れ、FG_ReadOnlyをデフォルトのファイルグループとして設定する方法の例
USE 'CusDB' ALTER DATABASE 'CusDB' ADD FILE (NAME = CusDB1, FILENAME = 'C:dataCusDB1.ndf') TO FILEGROUP FG_ReadOnly ALTER DATABASE CusDB MODIFY FILEGROUP FG_Readonly Defaultトランザクションログ
SQL Serverのトランザクションログは、各トランザクションによって作成されたデータベース内のすべてのトランザクションと変更を記録します。トランザクションログは、データベースの重要なコンポーネントです。システム障害が発生した場合に最近のデータソースにアクセスする唯一のソリューションになる可能性があります
トランザクションログは、次の操作をサポートします。
- 不完全なトランザクションがロールバックされるか、データベースエンジンがエラーを検出します。ログは、変更をロールバックするために使用されます。
- SQL Serverを実行しているサーバーに障害が発生した場合、データベースは不整合な状態になっている可能性があります。 SQL Serverのインスタンスが開始されると、各データベースの復元が実行されます。
- ハードウェアデータの損失がデータベースファイルに影響を与えた後、データベースを障害点に復元できます。
- Log Reader Agentは、構成された各データベースのトランザクションログを監視して、トランザクションを複製します
- バックアップサーバー、データベースミラーリング、およびログ配布ソリューションは、トランザクションログに依存します。
トランザクションログの操作：
SQL Serverデータベースには、少なくとも1つのデータファイルと1つのトランザクションログファイルがあります。トランザクションログのデータと情報は別々に、できれば別々のドライブに保存されます。これらのファイルはデータベースによって使用されます。
SQL Serverは、各データベースのトランザクションログを使用してトランザクションをロールバックします。トランザクションログは、データベースで発生したすべての変更と、変更を行ったトランザクションを格納するシリアルレコードです。このログには、各トランザクションで行われた変更を元に戻すのに十分な情報が保持されています。トランザクションログには、ページの割り当てと割り当て、および各トランザクションのコミットまたはロールバックが記録されます。この機能により、SQLServerはデータ状態をより柔軟に復元できます。
各トランザクションのロールバックは、次の方法で実装できます。
- トランザクションログが適用されると、トランザクションは先に進みます
- 不完全なトランザクションがバックアップされると、トランザクションはロールバックされます。
データベースにログファイルを追加する
データベースを編集してログファイルを追加するための構文：
ALTER DATABASE database_name ( ... ) [;] <add_or_modify_files>::= {ADD FILE <filespec>[,...n] [TO FILEGROUP {filegroup_name|DEFAULT}] | ADD LOG FILE <filespec>[,...n] | REMOVE FILE logical_file_name | MODIFY FILE <filespec> }SSMSを使用してデータベースを作成する
SSMSを使用してデータベースを作成する手順：
- オブジェクトエクスプローラーで、SQL Serverデータベースエンジンインスタンスに接続し、インスタンスの横にある[+]アイコンをクリックしてインスタンスを展開します。
- 図1.1に示すように、データベースを右クリックし、[新しいデータベース]をクリックします。
- [新しいデータベース]ダイアログボックスで、作成するデータベースの名前を入力します
- データベースがデフォルト値で作成されている場合は、[ OK ]をクリックして終了します。そうでない場合は、引き続きパラメーターを選択し、図1.2に示すようにデータベースを構成します。
- 所有者名を変更するには、[…]ボタンをクリックして別の所有者を選択します
- 一次データとトランザクションログファイルのデフォルト値を変更するには、データベースファイルテーブルで、対応するセルをクリックして値を入力します。
- データベースの照合を変更するには、[オプション]タブをクリックし、図1.3に示すようにリストから照合を選択します。
- リカバリモデルを変更するには、図1.4に示すように、[オプション]タブを選択し、選択ボックスからリカバリモデルを選択します。
- 他のデータベースオプションを変更するには、[オプション]タブの情報を編集します。
- ファイルグループmoiwsを追加するには、[ファイルグループ]タブをクリックします。次のステップは、[追加]ボタンをクリックしてから、図1.5に示すようにlieejファイルグループを入力することです。
- データベースに高いnanagプロパティを追加するには、[拡張プロパティ]タブを選択します
- [名前]列に、extendプロパティの名前を入力します
- [値]列に、extendプロパティの値を入力します。たとえば、データベースを説明するために1.2行を入力できます。
- [ OK]をクリックしてデータベースを作成します
データベースの削除（データベースの削除）
データベースを削除する前に、データベースが重要である場合は、最近のバックアップをいくつか保持していることを確認してください。これがすべての場合のルールです。削除されたデータベースは、バックアップを復元することによってのみ再作成できます。
データベース構文の削除：
DROP DATABASE [databasename]SSMSを使用してデータベースを削除するには、次の手順を実行します。
- オブジェクトエクスプローラーで、SQL Seerverデータベースエンジンインスタンスに接続し、「+」記号を押してインスタンスを展開します。
- 展開後、データベースを選択し、右クリックして[削除]をクリックします
- データベースが選択されていることを確認し、[削除]をクリックします
データベーススナップショットを作成する
データベーススナップショットは、SQL Server 215から導入された機能です。この機能は、SQLデータベースの読み取り専用のsticビューを提供します。ユーザーが間違ったコマンドを操作して実行し、データベースに障害が発生した場合、ソースデータベースはスナップショットが作成されたときの状態に戻ります。 SSMSはスナップショットの作成をサポートしていませんが、これを行うにはT-SQLステートメントを使用する必要があります。
スナップショットの利点：
- 便利で読み取り専用のコピーバージョンのデータを提供する
- 照会すると、パフォーマンスの低下はありません
- スナップショットファイルは軽くてすばやく初期化できます
スナップショットのデメリット：
- スナップショットバックアップを作成できません
- スナップショットは、データベースのソースと同じデータベースサーバーに存在する必要があります
- 新規ユーザーにスナップショットでデータアクセス権を割り当てることはできません
構文：
ドキュメント： https ：//docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database-snapshot-transact-sql？view = sql-server-ver15
CREATE DATABASE database_snapshot_name ON ( NAME = logical_file_name , FILNAME = 'os_file_name' )[,...n] AS SNAPSHOT OF source_database_name [;]説明：
- database_snapshot_name：データベーススナップショットの名前
- ON（NAME = logical_file_new、FILENAME =’os_file_name’）：ソースデータベース内のファイルのリスト。スナップショットを機能させるには、すべてのデータファイルを明確に識別する必要があります
- source_database_nameのスナップショットとして：source_database_nameという名前のソースデータベース
AdventureWorks2019データベースのスナップショットを作成する例：
CREATE DATABASE AdvventureWorks_snapshot on (NAME = AdventureWorks2017, FILENAME = 'C:dataAdventureWorks_snapshot.ss') AS SNAPSHOT OF AdventureWorks2019; GO2019ではなくNAME=AdventureWorks2017のステートメントがエラーになるため、次のドキュメントをお読みください。
結果：
Bài viết liên quan:
Dịch vụ thiết kế Wesbite
