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

データベースの作成

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

データベースの変更

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

SQL Server Management Studioのオブジェクトエクスプローラの[データベース]フォルダを右クリックして、[プロパティ]を選択する。
[データベースのプロパティ]画面が開くので、画面左の[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負荷の分散を考慮する。

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

セキュリティ
適切なアクセス権限を設定する。

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

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

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

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

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

概要

データベースの作成

基本構文 (クエリ)

SQL Server Management Studio

データベースの作成

データベースの変更

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

ファイル関連オプション

SIZE (初期サイズ)

MAXSIZE (最大サイズ)

FILEGROWTH (成長増分)

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

DEFAULT

CONTAINS FILESTREAM

CONTAINS MEMORY_OPTIMIZED_DATA

使用例

データベースオプション

可用性オプション

カーソルオプション

リカバリモデル

統計情報関連

使用例

その他のオプション

データベースの作成例

基本的なデータベース

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

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

メモリ最適化テーブル

ファイルストリーム

推奨されるパターン

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

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

適切な照合順序の指定

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

アンチパターン

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

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

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

不適切なサイズ設定

案内メニュー

検索