SQL Server - データベースの作成

概要

SQL Serverでデータベースを作成するには、SQL Server Management StudioまたはTransact-SQLを使用する。
ここでは、SQL Server Management Studioを使用したデータベースの作成を行う。

データベースの作成

基本構文 (クエリ)

 CREATE DATABASE <データベース名>
 [ CONTAINMENT = { NONE | PARTIAL } ]
 [ ON [ PRIMARY ] 
    [ <filespec> [ ,...n ] ]
    [ , <filegroup> [ ,...n ] ] ]
 [ LOG ON { <filespec> [ ,...n ] } ]
 [ COLLATE collation_name ]
 [ WITH <database_option> [ ,...n ] ]

SQL Server Management Studio

データベースの作成

1. SQL Server Management Studioのオブジェクトエクスプローラの[データベース]フォルダを右クリックして、[新しいデータベース]を選択する。
2. [新しいデータベース]画面が開くので、画面右の[データベース名]項目を入力して、[OK]ボタンを押下する。
   各種設定は後から設定可能であるため、名前を付けるのみにする。
3. SQL Server Management Studioのオブジェクトエクスプローラから、上記で追加したデータベースがあるか確認する。

データベースの変更

作成済みのデータベースは、後から変更することができる。

1. SQL Server Management Studioのオブジェクトエクスプローラの[データベース]フォルダを右クリックして、[プロパティ]を選択する。
2. [データベースのプロパティ]画面が開くので、画面左の[File]を選択すると、該当データベースのファイルを変更できる。

   初期設定では、自動拡張するように設定されている。

   また、ファイル名およびファイルのパスもここで確認することができる。

CONTAINMENT (封じ込めオプション)

• NONE (デフォルト設定)

  従来型のデータベース

  サーバレベルの照合順序に依存する。

• PARTIAL

  部分的な包含データベース

  データベースの独立性を高めて、サーバ間の移行を容易にする。

  クラウド移行やサーバ間移行が予定されている場合に有効である。

 CONTAINMENT = { NONE | PARTIAL }

 /* 使用例 */
 
 CREATE DATABASE SampleDB
 CONTAINMENT = PARTIAL
 COLLATE Japanese_CI_AS

ファイル関連オプション

 /* ファイル仕様 (<filespec>) オプション */
 <filespec>::=
 (
    NAME     = logical_file_name,
    FILENAME = 'os_file_name'
    [ , SIZE       = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE    = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
 )

SIZE (初期サイズ)

データベースファイルの初期サイズを指定する。

サイズが小さい場合は頻繁な自動拡張が発生して、パフォーマンスに影響する。
大規模なデータベースの場合、予測される1年分の成長を考慮することが推奨される。

 SIZE = size [ KB | MB | GB | TB ]

MAXSIZE (最大サイズ)

ファイルの成長限界を設定する。

UNLIMITEDの指定は、ディスク容量の枯渇リスクがあるため、非推奨である。

運用管理の面では、適切な上限設定が必要となる。

 MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED }

FILEGROWTH (成長増分)

ファイルの自動拡張時の増分を指定する。

パーセント指定は、予測が困難で管理が複雑になるため、非推奨である。

推奨設定例を以下に示す。

• データファイル

  100[MB] - 1[GB]

• ログファイル

  50[MB] - 500[MB]

 FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ]

 /* 使用例 */
 
 CREATE DATABASE SampleDB
 ON PRIMARY
 (
    NAME       = 'SampleDB_Data',
    FILENAME   = 'D:\Data\SampleDB.mdf',
    SIZE       = 1GB,    /* 十分な初期サイズ */
    MAXSIZE    = 50GB,   /* 明確な上限設定 */
    FILEGROWTH = 500MB   /* 適度な成長増分 */
 )
 LOG ON
 (
    NAME       = 'SampleDB_Log',
    FILENAME   = 'E:\Logs\SampleDB.ldf',
    SIZE       = 500MB,
    MAXSIZE    = 25GB,
    FILEGROWTH = 250MB
 )

ファイルグループオプション

 /* ファイルグループ (<filegroup>) オプション */
 
 <filegroup>::=
 FILEGROUP filegroup_name
    [ [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ] ]
    [ DEFAULT ]
    [ READ_ONLY ]
    <filespec> [ ,...n ]

DEFAULT

プライマリでないファイルグループをデフォルトとして指定する。

テーブル作成時に明示的な指定がない場合、このファイルグループが使用される。

CONTAINS FILESTREAM

BLOBデータ (画像、文書等) 向けのファイルグループを指定する。

ファイルシステムへの直接アクセスが可能である。

CONTAINS MEMORY_OPTIMIZED_DATA

インメモリOLTPのデータ向けのオプションである。

高性能が要求されるトランザクション処理で有効である。

使用例

 CREATE DATABASE SampleDB
 ON PRIMARY
 (
    NAME = 'SampleDB_Pri',
    FILENAME = 'D:\Data\SampleDB_pri.mdf',
    SIZE = 100MB
 ),
 FILEGROUP ArchiveFG
 (
    NAME = 'SampleDB_Arc',
    FILENAME = 'D:\Data\SampleDB_arc.ndf',
    SIZE = 1GB
 ) DEFAULT,  /* アーカイブ用ファイルグループをデフォルトにする */
 FILEGROUP FSGROUP CONTAINS FILESTREAM
 (
    NAME = 'SampleDB_FS',
    FILENAME = 'D:\Data\SampleDB_FS'
 )

データベースオプション

可用性オプション

• ENABLE_BROKER
• DISABLE_BROKER
• NEW_BROKER
• ERROR_BROKER_CONVERSATIONS
• HONOR_BROKER_PRIORITY

カーソルオプション

• CURSOR_CLOSE_ON_COMMIT

  ON または OFF

• CURSOR_DEFAULT

  LOCAL または GLOBAL

リカバリモデル

• FULL

  完全なポイントインタイムリカバリが可能

  本番環境では推奨される。

• BULK_LOGGED

  大量データロード時のログ領域を節約する。

• SIMPLE

  ログの切り捨てが自動的に行われる。

  開発環境向けで使用される。

 RECOVERY { FULL | BULK_LOGGED | SIMPLE }

• TORN_PAGE_DETECTION

  ON または OFF

• PAGE_VERIFY

  CHECKSUM または TORN_PAGE_DETECTION または NONE

統計情報関連

• ON

  ONが推奨される。

  クエリオプティマイザのパフォーマンスが向上する。

• OFF

  完全に制御された環境等、特殊な場合のみ設定する。

 AUTO_CREATE_STATISTICS { ON | OFF }
 AUTO_UPDATE_STATISTICS { ON | OFF }

使用例

 CREATE DATABASE SampleDB
 ON PRIMARY
 (
    NAME       = 'SampleDB_Pri',
    FILENAME   = 'D:\Data\SampleDB.mdf',
    SIZE       = 2GB,
    MAXSIZE    = 100GB,
    FILEGROWTH = 1GB
 ),
 FILEGROUP DataFG
 (
    NAME       = 'SampleDB_Data',
    FILENAME   = 'E:\Data\SampleDB_data.ndf',
    SIZE       = 5GB,
    MAXSIZE    = 500GB,
    FILEGROWTH = 1GB
 ) DEFAULT
 LOG ON
 (
    NAME       = 'SampleDB_Log',
    FILENAME   = 'F:\Logs\SampleDB.ldf',
    SIZE       = 1GB,
    MAXSIZE    = 50GB,
    FILEGROWTH = 500MB
 )
 COLLATE Japanese_CI_AS
 WITH
    RECOVERY FULL,
    AUTO_CLOSE OFF,
    AUTO_SHRINK OFF,
    AUTO_CREATE_STATISTICS ON,
    AUTO_UPDATE_STATISTICS ON,
    PAGE_VERIFY CHECKSUM,
    READ_COMMITTED_SNAPSHOT ON

その他のオプション

• AUTO_CLOSE

  ON または OFF

• AUTO_CREATE_STATISTICS

  ON または OFF

• AUTO_SHRINK

  ON または OFF

• AUTO_UPDATE_STATISTICS

  ON または OFF

• AUTO_UPDATE_STATISTICS_ASYNC

  ON または OFF

• CHANGE_TRACKING

  ON または OFF

• COMPATIBILITY_LEVEL

  150

  140

  130

  120

  110

  100

• DB_CHAINING

  ON または OFF

• ENCRYPTION

  ON または OFF

• MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT

  ON または OFF

• PARAMETERIZATION

  SIMPLE または FORCED

• READ_COMMITTED_SNAPSHOT

  ON または OFF

• RECURSIVE_TRIGGERS

  ON または OFF

• SERVICE_BROKER
• TRUSTWORTHY

  ON または OFF

データベースの作成例

基本的なデータベース

 CREATE DATABASE SampleDB
 ON PRIMARY
 (
    NAME       = 'SampleDB_Data',
    FILENAME   = 'D:\Data\SampleDB.mdf',
    SIZE       = 100MB,
    MAXSIZE    = 10GB,
    FILEGROWTH = 100MB
 )
 LOG ON
 (
    NAME       = 'SampleDB_Log',
    FILENAME   = 'E:\Logs\SampleDB.ldf',
    SIZE       = 50MB,
    MAXSIZE    = 5GB,
    FILEGROWTH = 50MB
 )

複数のファイルグループを持つデータベース

 CREATE DATABASE SampleDB
 ON PRIMARY
 (
    NAME     = 'SampleDB_Primary',
    FILENAME = 'D:\Data\SampleDB_Primary.mdf'
 ),
 FILEGROUP SampleData_FG
 (
    NAME     = 'SampleDB_Data1',
    FILENAME = 'D:\Data\SampleDB_Data1.ndf'
 ),
 FILEGROUP Archive_FG
 (
    NAME     = 'SampleDB_Archive',
    FILENAME = 'D:\Data\SampleDB_Archive.ndf'
 )
 LOG ON
 (
    NAME     = 'SampleDB_Log',
    FILENAME = 'E:\Logs\SampleDB.ldf'
 )

高度なオプションを使用したデータベース

 CREATE DATABASE SampleDB
 CONTAINMENT = NONE
 ON PRIMARY
 (
    NAME     = 'SampleDB_Data',
    FILENAME = 'D:\Data\SampleDB.mdf'
 )
 LOG ON
 (
    NAME     = 'SampleDB_Log',
    FILENAME = 'E:\Logs\SampleDB.ldf'
 )
 COLLATE Japanese_CI_AS
 WITH 
    RECOVERY FULL,
    DB_CHAINING OFF,
    TRUSTWORTHY OFF,
    COMPATIBILITY_LEVEL = 150,
    AUTO_CLOSE OFF,
    AUTO_SHRINK OFF,
    AUTO_CREATE_STATISTICS ON,
    AUTO_UPDATE_STATISTICS ON

メモリ最適化テーブル

 CREATE DATABASE InMemoryDB
 WITH
    MEMORY_OPTIMIZED_DATA
    CONTAINS MEMORY_OPTIMIZED_FILEGROUP

ファイルストリーム

 CREATE DATABASE DocumentDB
 ON PRIMARY
 (
    NAME     = 'DocDB_Data',
    FILENAME = 'D:\Data\DocDB.mdf'
 ),
 FILEGROUP FS_Group CONTAINS FILESTREAM
 (
    NAME     = 'DocFS',
    FILENAME = 'D:\FileStream\DocFS'
 )

推奨されるパターン

ファイルサイズと成長設定を明示的に指定

 CREATE DATABASE SampleDB
 ON PRIMARY 
 (
    NAME       = 'SampleDB_Data',
    FILENAME   = 'D:\Data\SampleDB.mdf',
    SIZE       = 100MB,
    MAXSIZE    = 10GB,
    FILEGROWTH = 100MB
 )
 LOG ON
 (
    NAME       = 'SampleDB_Log',
    FILENAME   = 'E:\Logs\SampleDB.ldf',
    SIZE       = 50MB,
    MAXSIZE    = 5GB,
    FILEGROWTH = 50MB
 )

データファイルとログファイルを別のドライブに配置

パフォーマンス、I/O分散、障害復旧の点からも推奨される。

適切な照合順序の指定

 CREATE DATABASE SampleDB
 COLLATE Japanese_CI_AS

複数のファイルグループを使用 (大規模データベース向け)

 CREATE DATABASE SampleDB
 ON PRIMARY 
    (NAME = 'Primary', FILENAME = 'D:\Data\Primary.mdf'),
 FILEGROUP Sample_FG
    (NAME = 'Sales1', FILENAME = 'D:\Data\Sample1.ndf'),
    (NAME = 'Sales2', FILENAME = 'D:\Data\Sample2.ndf')

アンチパターン

デフォルト設定のみで作成

 CREATE DATABASE SampleDB

これは、初期サイズが小さすぎる可能性がある。
また、成長設定が最適化されていないことやファイルの配置場所が制御できない。

不適切なファイル成長設定

パーセント指定は予測困難である。

 CREATE DATABASE SalesDB
 ON PRIMARY 
 (
    FILEGROWTH = 1%
 )

データとログを同じドライブに配置

 CREATE DATABASE SalesDB
 ON PRIMARY 
 (
    FILENAME = 'C:\Data\SalesDB.mdf'
 )
 LOG ON
 (
    FILENAME = 'C:\Data\SalesDB.ldf'  -- 同じドライブ
 )

不適切なサイズ設定

 CREATE DATABASE SalesDB
 ON PRIMARY 
 (
    SIZE = 5MB,  -- 小さすぎる初期サイズ
    MAXSIZE = UNLIMITED  -- 制限なし
 )

推奨される事柄を以下に示す。

• 事前計画

  予想される成長率を考慮する。

  適切な初期サイズを設定する。

  業務要件に基づいた最大サイズを設定する。

• パフォーマンス考慮

  ファイルの物理的な配置を計画する。

  I/O負荷の分散を考慮する。

  適切なファイル成長値を設定する。

• セキュリティ

  適切なアクセス権限を設定する。

  セキュアな場所へのファイルを配置する。

• 監視と管理

  成長率の監視計画を行う。

  バックアップ戦略を策定する。

  メンテナンス計画を策定する。