最終更新 2010/12/04
SQL Server 2008 のインストールと構成

トラブルシューティング

SQL Server2008 のエディション

エラー情報の検出

SQL Server2008 の主要な機能

データベースの保守

データベースエンジンサービスのインスタンス

トランザクション管理

SQL Server 2008 のインストール

トランザクションの分離

サーバ構成オプション

パフォーマンスの監視とリソースの管理
データベース作成

システムモニタのパフォーマンス監視システムデータベースとユーザデータベース

SQL Server Profiler によるイベントの収集と再生

データのエクスポート・インポート

SQL トレースを使用した情報の収集

異なる照合順序を持つテーブルの結合

チューニングアドバイザ

インデックス管理

動的管理オブジェクト

インデックスの断片化

データコレクションによるパフォーマンスデータの収集

データパーティション

リソースガバナによるリソース管理

データ圧縮

高可用性の構成
セキュリティ管理

ログの配布

データベースエンジンのセキュリティモデル

データベースミラーリング

ログインサーバと認証モード

フェールオーバークラスタ化

ロールベースの権限管理

レプリケーション

セキュリティスキーマ
権限の設定

サーバー

データベースｽｷｰﾏ･ｵﾌﾞｼﾞｪｸﾄ

システムテーブル関連メモ

実行コンテキストの制御

カタログビュー

監査

動的管理オブジェクト

トリガー

ストアドプロシージャ

透過的暗号化組み込み関数グローバル変数

インスタンスの管理
SQL Server エージェント

SET コマンドDBCC コマンドSQL Server エージェントのジョブ構成
SQL Server エージェントの警告の構成
ポリシーベースの管理

データベースの管理
トランザクションログによる復旧
データベースのバックアップ
データベースの復元
データの整合性チェック
システムデータベースの管理
Authoring Documents by kita
http://vre.sakura.ne.jp/

■SQL Server 2008 のインストールと構成
SQL Server2008 のエディション
エディション

CPU

MEM

容量

使用できない機能

Enterprise / Developer

-

-

-

全ての機能が使用できるStandard / Web

4

-

-

スナップショット、パーティション、データ圧縮、リソースガバナ

Workgroup

2

4GB

-

上記＋Analysis Services 等のデータ統合/分析/活用コンポネート

Express

1

1GB

10GB

上記＋ログ配布

SQL Server2008 の主要な機能
データベースエンジン

オンライントランザクション処理や大規模なデータウェアハウス環境の為の高速で拡張性の高サービス

いリレーショナルデータベースを提供する。サービス名は SQL Server (インスタンス名)

フルテキスト検索

テーブルに格納された大量のテキストデータに対してキーワードベースのクエリを高速に実行出来るようにする機能

SQL Server

データベース間のデータの複製と同期を行うための機能で、サーバへの負荷を軽減する分散デー

レプリケーション

タベース環境を安易に構築運用できる

Reporting Services

各種リレーショナルデータベースや Analysis Services が提供する OLAP データベースに蓄積されたデータをデータソースとして利用しテーブル形式のレポート、クロス集計表、グラフといったインターフェースを持つレポートをデザイナから作成しサーバープロセスに配置できる

Integration

構造やフォーマット形式が異なる DB やテキストファイルから、統合の対象となるデータを抽出

Service

変換し、DWH のような「分析用」データベースへロードするためのパッケージ作成運用が可能

Analysis Services

RDB は 2 次元のテーブルにデータを格納されるのに対し、OLAP キューブと呼ばれる多次元構造体に集計値が格納される

管理ツール

SQL Server2000 までの SQL Server Enterprise Manager、クエリアナライザ、分析マネージャな
どの管理機能は SQL Server Management Studio に統合

開発ツール

Reporting Services、Integration Services、Analysis Services といった BI コンポーネントの開発環境は Business Intelligence Development Studio に統合

データベースエンジンサービスのインスタンス
データベースエンジンサービス、Analysis Services、Reporting Services は 1 台のコンピュータで複数稼働でき、それぞれのサービスが 1 つのインスタンスとして識別される。
1 つだけ名前を付けないインスタンス（既定のインスタンス）をインストール可能で「コンピュータ名」で接続する。
その他のインスタンスは全て名前付きインスタンスと呼ばれ「コンピュータ名\インスタンス名」という名前で識別される。

データベースのデータベース の構造
データファイル（.mdf / .ndf）テーブルや INDEAX といった DB のオブジェクトが格納されている
トランザクションログファイル（.ldf）DB オブジェクトに対する変更履歴が格納されている

データファイルは、ページと呼ばれる 8kb の単位に分割されている。ページは I/O の最小単位。
連続した 8 個のページを 64kb のエクステントと言う。
ランダム I/O はページ単位。シーケンシャル I/O はエクステント単位で行われる。

データベースエンジンサービスのデータベースエンジンサービス の基本動作①

検索クエリの実行で実行プランを生成され実行プランはプロシージャキャッシュに格納される②

ストレージエンジンは生成されたプランから対象となるページをデータファイルから読み取りメモリにロードする③

読み込まれたメモリはバッファキャッシュに格納される④

結果セットの生成にソート等などが必要な場合、クエリワークメモリを使用する⑤

クエリワークメモリに収まらない場合は、tempdb システムデータベースに置かれる⑥

結果セット生成後、クライアントに結果を返す

次回以降、検索した場合、参照データがバッファキャッシュに存在する限り物理 I/O は発生しない（論理読み込み）⑦

更新クエリーの実行でも実行プランが生成されプロシージャキャッシュに格納される⑧

バッファキャッシュ内のデータページを変更する⑨

変更操作（ログレコード）をログキャッシュに格納し即座にログファイルへ書き出す（ログの先行書き込み）⑩

チェックポイントが発生したタイミングでログレコードがデータファイルに書きだす

SQL Server 2008 のインストール
Setup.exe を実行し「SQL Server の新規スタンドアロンインストールまたは既存のインストールへの機能追加」
ページ

説明

機能の選択

インストールする機能を選択する

インスタンスの構成

既定インスタンスか、名前付きインスタンスを選択。
インスタンス ID と、インストール先フォルダを指定

サーバー構成各サービスが起動するユーザーを指定する。インストール後も構成マネージャから変更可能

照合順序

データの比較と並べ替えを決める一連の規則

アカウントの準備

Windows 認証か、Windows 認証と SQL Server 認証の両方できるモードのどちらか選択

データディレクトリ

インスタンスが格納するデータファイルのディレクトリを指定

FILESTREAM

有効にすると、通常はファイルシステム上のファイルとして格納されるテキスト/イメージ/ビデオなどを varbinary(max)データ型の列に格納できるようになる。
Win32API や、リモートからのアクセスも許容するにはサーバーオプションの filestream_access_level に 2 をセットする必要がある

Analysis Services 構成

使用するアカウントと、データディレクトリを設定

Reporting Services 構成

ネイティブモード：レポートサーバーインスタンスのインストールと構成両方が行われる。
SharePoint 統合モード：レポートサーバーが Windows SharePoint Service 等と統合される為、生成したレポートのバージョン履歴を保存できるようになる。
レポートサーバーを構成せず：リモートのレポートサーバーDB に接続する場合に選択する

サーバ構成オプション
インスタンスのプロパティ⇒「詳細設定」項目から設定できるものもある。
exec sp_configure 'show advanced option' , '1'
RECONFIGURE

全てのオプション表示を有効にする

コンフィグの設定を有効にする

exec sp_configure

コンフィグ設定内容を確認する

オプション(紫は再起動・要)説明

AD Hoc Distributed Querites

OPENROWSET()関数､OPENDATASOUCE()関数を使用したｱﾄﾞﾎｯｸな分散ｸｴﾘの実行を許可するか

affinity I/O mask

複数プロセッサ環境でインスタンスがディスク I/O で使用するプロセッサの割り当て

affinity mask

複数プロセッサ環境でインスタンスが使用するプロセッサの割り当て

Agent XPsSQL Server エージェントの拡張ストアドプロシージャの使用

awe enabled

32bitOS で AWE マップメモリの有効化（Windows2000 では再起動が必要）

backup compression default

インスタンスレベルの圧縮バックアップの既定値

blocked process threshold

ブロックされたプロセスのレポートを生成するための閾値（秒）clr enabled

インスタンスのインプロセスで共通言語ランタイム（CLR）の使用

cost threshold for parallelism

シングルプランで実行した場合、指定した時間(秒)以上掛かる場合のみ並列処理を行う

common criteria

情報セキュリティ国際評価基準（Common Criteria）への準拠の有効化

compliance enabled
cross db ownership chaining

複数 DB の組み合わせ所有権の有効化

Database Mail XPs

SMTP を使用するデータベースメール機能の有効化

default trace enabled

既定のトレースログファイルの有効化

filestream access level

1：Transact-SQL アクセスに対する FILESTREAM が有効
2：Transact-SQL アクセス及び Win32 ストリームアクセスに対して FILESTREAM が有効

fill factor (%)

インスタンスの既定のインデックス充填率

max degree of parallelism

パラレルクエリーの上限

max server memory (MB)バッファプールの最大サイズ

max worker threads

インスタンスが使用できる最大ワーカースレッド数。0 は環境に応じて自動で最適に調整

nested triggers

AFTER トリガの連鎖実行の設定

network packet size (B)ネットワークで使用するパケットサイズ

priority boost

WindowsOS スケジューラに対する SQL Server インスタンスの実行優先度

query governor cost limit

クエリ実行時間の上限設定。クエリコスト＝実行完了までの予測時間（秒単位）

query wait (s)タイムアウトになるまでのクエリのリソース待ち時間

recovery interval (min)SQL Server インスタンスが DB を復旧するのに必要な時間の最大値

remote access

SQL Server ｲﾝｽﾀﾝｽが実行されているﾛｰｶﾙｻｰﾊﾞまたはﾘﾓｰﾄｻｰﾊﾞからｽﾄｱﾄﾞﾌﾟﾛｼｰｼﾞｬの実行

remote admin connections

リモート専用管理者接続の許可

SQL Mail XPs

MAPI ベースのメール機能を提供する SQL Mail の有効化

user connections

インスタンスに対する同時ユーザ接続の最大数

user options

接続ユーザに対するグローバルな既定値の指定

xp_cmdshell

ｲﾝｽﾀﾝｽ内から OS ｺﾏﾝﾄﾞを実行可能にする xp_cmdshell ｽﾄｱﾄﾞﾌﾟﾛｼｰｼﾞｬの許可

ネットワークライブラリのネットワークライブラリ の確認
SQL Server 構成マネージャにてライブラリの有効/無効や、TCP ポート番号の設定が可能。サービス再起動が必要。
TCP/IP

インターネット経由でインスタンスに接続可能

名前付きパイプ

LAN 経由でインスタンスに接続可能

共有メモリ

ローカルからのみインスタンスに接続可能

VIA

仮想インターフェースをサポートしているハードのみ動作可能

リモートから接続するには TCP/IP または名前付きパイプを有効にする必要がある。既定は TCP ポート 1433 を使用。
名前付きインスタンスは SQL Server が起動時に動的にポート番号が付与される。同じ TCP ポートは使用できない。
名前付きインスタンスは起動する度にポート番号が変わる為、SQL Server Browser を起動しておくことで、サーバ名を指定した際、自動でポートを取得して接続できるsqlcmd -S インスタンス名

でサーバに接続できる（インスタンス名はコンピュータ名\インスタンス）

オプション

説明

-U

ログイン ID を指定（省略した場合、Windows 認証になる）-P

パスワードを指定（省略した場合、Windows 認証になる）-S

接続先のインスタンスを指定（省略時は既定のインスタンス）-H

ホスト名（IP アドレス）を指定-d

接続先のデータベースを指定（省略した場合は master）-i

入力ファイル名を指定。SQL ファイルを実行する場合などに使用■データベース作成
システムデータベースとユーザデータベース
masterSQL Server のインスタンスレベルのオブジェクトやシステム情報を格納する

msdb

警告/ジョブ/スケジュールの設定情報や各種の履歴情報など、主にエージェントが使用するデータを格納

model

ユーザデータベースを作成する際のテンプレートとして使用される

tempdb

一時オブジェクトの格納や SQL Server の内部的な作業領域

resource

システムオブジェクトが格納される読み取り専用の DB。隠し DB の為、管理ツールには表示されない

上記をシステムデータベースと呼び、ユーザーデータベースはアプリケーションで使用する DB オブジェクトを格納する

ファイルグループ
ファイルグループ
ユーザデータベースで格納するオブジェクトはデータファイルを指定するのではなく、複数のデータファイルで構成されたファイルグループを指定する。（Oracle で言う表領域）
特定のファイルに格納させたい場合は、CREATE 文の ON 句で ﾌｧｲﾙｸﾞﾙｰﾌﾟ名 を指定する（後で変更できない）
既定では「PRIMARY」という名前のファイルグループに格納される。PRIMARY は DB に必須のファイルグループ。

データのエクスポート・インポート
テーブルのデータをテキスト形式のファイルでエクスポート/インポートを行う。
bcp DB.ｽｷｰﾏ.ﾃｰﾌﾞﾙ名 { out | in } ﾌｧｲﾙ名 ｵﾌﾟｼｮﾝ
ﾃｰﾌﾞﾙ名

データベース名からのフルパスで記述する。AdventureWorksLT2008.dbo.BuildVersion{out | in} ﾌｧｲﾙ名

テーブルのエクスポートまたはインポートを指定

format nul

フォーマットファイル（Oralce の SQL Loade で言うコントロールファイル）を作成（例）bcp AdventureWorksLT2008.dbo.BuildVersion format nul -f format.xml -c -x -T-f ﾌｧｲﾙ名

フォーマットファイルを指定（インポート先のテーブル構造が同じであれば不要）-S ｲﾝｽﾀﾝｽ名

接続先の SQL サーバーインスタンス名-T

現在使用している Windows アカウント情報による信頼関係接続の要求-c

文字データとして保存-t ﾃﾞﾐﾘﾀ

列の区切り文字を指定。デフォルトはタブ-x

XML フォーマットを作成

データベースを右クリック⇒[タスク]⇒[データのインポート] or [データーのエクスポート]でも実行可能。
エクスポート⇒インポートを一括で行う。異なる DB 間であればテーブルがなくても新規作成しインポートされる。
直ぐに実行せず、SSIS パッケージとしてファイルシステムに保存した場合、以下のコマンドでインポート行う。
DTEXEC -f SSIS ﾊﾟｯｹｰｼﾞ名 [ /decrypt ﾊﾟｯｹｰｼﾞ作成の際に指定したﾊﾟｽﾜｰﾄﾞ ]異なる照合順序を持つテーブルの結合
インスタンス、DB レベルで指定可能で、オブジェクトのプロパティから確認できる。
{ CREATE | ALTER } DATABASE データベース名 COLLATE 照合順序
照合順序

説明

Japanese_CI_AS（既定）

大文字小文字を区別しない。アクセントは区別するJapanese_CS_AI

大文字小文字を区別する。

アクセントは区別しない

異なる照合順序のテーブル同士を結合（以下の例だと TAB1 と TAB2）した場合、エラーになる。
エラーを回避するには ON 句に COLLATE 句を追加して、照合順序を指定する。
（例）select * from DB1.dbo.TBL1 A inner join DB2.dbo.TBL2 B on A.name = B.name CLOLLATE Japanese_CS_AI
指定できるのは文字列データ型（char / varchar / text / nchar / nvarchar / ntext）のみ

インデックス管理
CREATE [ CLUSTERED ] INDEX ｲﾝﾃﾞｯｸｽ名 ON ﾃｰﾌﾞﾙ名(列名[,列名])[ INCLUDE(ﾘｰﾌﾉｰﾄﾞに含めたい列名 1, 列名 2, …) ][ WHERE 条件 ]‥‥不可列 INDEX 作成‥‥フィルタ列指定（この条件に一致した列のみ INDEX を作成する）[ WITH ( FILLFACTOR = 充填率(%)[,PAD_INDEX = ON|OFF] )[ ON { ﾊﾟｰﾃｨｼｮﾝ関数(ｶﾗﾑ名) | ﾌｧｲﾙｸﾞﾙｰﾌﾟ } ]]‥‥充填率、非リーフノードでも充填率を有効‥‥格納する場所を指定する

クラスタ化インデックスと非クラスタ化インデックスがあり、クラスタ化インデックスが作成されていないテーブル構造をヒープ構造と呼ぶ。
ヒープ構造は挿入された順にデータが挿入される為、ORDER BY で並べ替えを行うと、SORT のコストが発生する。

Product テーブルのインデックス構造を確認するする例。
select * from sys.dm_db_partition_stats where OBJECT_NAME(object_id) = 'Product'
カラム名

説明

index_id

0:INDEX なし(ﾋｰﾌﾟ構造) 1:ｸﾗｽﾀ化ｲﾝﾃﾞｯｸｽ構造 2 以上:非ｸﾗｽﾀ化ｲﾝﾃﾞｯｸｽ構造

in_row_data_page_count

実際に使用されているデータのページ数

in_row_used_page_count

実際に使用されているページ数（データ＋IAM＋INDEX ルートページの合計）

in_row_reserved_page_count

当該テーブルが何ページ分使用しているか（予約で確保しているページ数）

IAM（Index Allocation Map）はヒープ全体を管理するページ。ファイル ID＋ページ番号の一覧が格納されている。

SET STATISTICS IO ON

で SQL 実行時にページの I/O が確認できる。

DBCC DROPCLEANBUFFERS

ディスク I/O を行わせる為に、バッファキャッシュを削除する。

物理読み取り数、先行読み取り数はデータファイルからバッファキャッシュ領域に読み取ったページ数。
論理読み取り数はストレージエンジンがバッファキャッシュ領域から読み取ったページ数を示す。

クラスタ化
クラスタ化インデックス
sys.indexes の index_id 列が 1 になっている。
INDEX カラムの数値が 1～100 はページ 100、101～200 はページ 200‥‥というようにソートされた状態でツリーを作成。
一番上位をルートノードと言う。一番下位をリーフノード。その間にあるのを中間ノード。
カラムの値で 142 のものを検索する場合、最初に 101～200 の間にあるページ 200 を参照し、続いて中間ノードで更に細分化された索引を辿り、最終的にリーフノードの値に辿り着く。

テーブルスキャン（全てのデータ行を読み取る）より効率が良い。
クラスタ化インデックスはリーフノードがデータ行そのものとして編集され、ヒープは削除される。
リーフノードにデータ行を持っている為、TABLE につき 1 つしか作成できない。
インデックスは既に順序付けられている為、ORDERBY 句で SORT を行っても SORT のコストは発生しない。

列に PRIMARY KEY 制約を付与した場合、自動的にクラスタ化 INDEX が作成される。
既にクラスタ化 INDEX がある場合は非クラスターINDEX が作成される。

非クラスタ化
クラスタ化 インデックス
sys.indexes の index_id 列が 2 以上の値。249 個まで作成可能。一番大きい値がルートノードになる。
リーフレベルには「非クラスタ化キーの値」と「行ロケータ」が含まれる。
「行ロケータ」はクラスター化インデックスがあるテーブルの場合、クラスター化インデックスのキー値を格納。
クラスタ化インデックスがないテーブルでは、
データ行へのポインタである行識別子 (ファイル ID ＋ページ番号＋行 ID) が入っている。（下図）

インデックスのリーフに格納された行識別子を使用してデータ行を取得する操作を RID Lookup と言う。
RID Lookup はランダムアクセスの為、非クラスター化インデックスを使用した範囲検索の場合、何度も RID Lookup を行い効率が悪くなる。Scan の方が効率良いと判断した場合は INDEX を使用しない。

複合 INDEX「姓」、「社員番号」、「名」の 3 つの複合 INDEX を作成した場合、リーフレベルに「姓」、「社員番号」、「名」が含まれる。
SELECT 社員番号, 姓, 名 FROM 社員 WHERE 姓 = 'Aoki' という検索を行った場合、リーフレベルだけで必要な情報が全て取得できる為、RID Lookup を行わない。
一番左へ指定した列でツリー構造が作成されるので WHERE 句の検索条件で指定される列を一番左へ指定する。
１つのインデックスにカラムは 16 個まで。インデックスの 1 レコード最大サイズは 900 バイトまで。

不可列 INDEX
リーフノードのみにカラム（非キー列）を追加させる。
CLUSTER INDEX には使用できない。（リーフノードに全カラムのデータを保持している為）

通常の複合 INDEX と違い以下の特徴がある。
・

非キー列に text 型や image 型などの LOB データ型の列を含めることが出来る

・

非キー列を組み合わせることにより 1 つの INDEX に最大 16 列、900 バイトまでという制限を回避できる

フィルタ選択
フィルタ選択された
選択 された INDEX
sys.dm_db_partition_statsの row_count にてレコード数が解る。

INDEX のプロパティ「フィルタ」内に「カラム名 IS NOT NULL」と入力すると、指定したカラム名に対し
NULL 以外のみ INDEX として持つというフィルタを設定できる。（NULL のレコードは INDEX 作成しない）

インデックスの断片化
インデックスページ番号 100 に 10 件のデータで使用率が 100%だとする。
この状態で新たに 1 件データを挿入した場合、新たにインデックスページ番号(200 とする)を作成し、ページ番号 100 から 50%のデータが移動され、新規に作成された INDEX もそこに追加される。
100 には 50%の空き。200 も 50%の空き。こうした断片化を内部断片化と言う。
100⇒200 は論理的には連続しているが物理的には別領域にデータが格納されている為、IO 効率が悪くなる。
こうした断片化を外部断片化と言う。

INDEX 作成時の空き容量（充填率）はオプション内の FILL FACTOR の項目。SLQ の場合は FILLFACTOR = 充填率
充填率を指定できるのはリーフノードのみ。非リーフノードでも充填率を適用したい場合は、「インデックスを埋め込む」のチェックボックスを ON にする。SQL の場合は PAD_INDEX = ON

断片化の
断片化 の検出sys.dm_db_index_physical_stats 動的管理関数にて取得する
引数：(db_id, object_id, index_id, ﾊﾟｰﾃｨｼｮﾝ番号, 'ﾓｰﾄﾞ') ※省略したい時はそれぞれ NULL と指定する
モード

説明

LIMITED

スキャンするページ数が最小で最も高速。リーフレベルより上のページをスキャンする

SAMPLEDINDEX またはヒープ構造について全ページの 1%をサンプルした統計値が返される
10000 ページに満たない場合は DETAILED モードが実行される

DETAILED

リーフページを含む全てのページがスキャンされ、全ての統計値が返される

カラム

説明

index_depth

インデックスのレベル。（何階層のツリー構成になっているか）1 はヒープ

index_level

インデックスの現在のレベル。0 はリーフレベル
非リーフレベル(1 以上)は DETAILED オプション時の時のみ表示される
index_level はインデックスのルートレベルで最大になる

partition_number

1 から始まるパーティション番号。分割されていれば 2 以上の数値が入る

avg_fragmentation_in_percent

インデックスの論理的な断片化。複数のファイルが考慮される

page_count

実際に使用しているページ数

断片化の
断片化 の解消
INDEX 作成中は該当 TABLE の INDEX にアクセスできなくなる。（ONLINE = ON を設定すればアクセス可能）
クラスタ化 INDEX はリーフレベルにデータが入ってるので再構築中はテーブルに一切アクセスできなくなる。
ページ数も多いため再構築に時間も掛かる。
ALTER INDEX { ｲﾝﾃﾞｯｸｽ名| ALL } ON ﾃｰﾌﾞﾙ名 { REBUILD [ WITH (ｵﾌﾟｼｮﾝ[,ｵﾌﾟｼｮﾝ]) ] | REORGANIZE }REBUILD

再構築を行う。新しいページに再作成を行う。断片化 30%以上の時に行うべき。

REORGANIZE

既存のページを使用し並び替えを行う。実行中もユーザはアクセス可能。
再編成は断片化率が大きいと処理に非常に時間が掛かる為、断片化 30%未満の場合に行う

ONLINE = ON

オンラインで再構築を行う。オンラインは既存の INDEX を保持したまま別領域に INDEX を作成するのでデータファイルの容量に注意する

SORT_IN_TEMPDB = ON

オンライン再構築の INDEX 作成に tempdb を利用する

FILLFACTOR = 充填率

INDEX 充填率を指定する。REORGANIZE の時は使用できない

XML インデックス
XML ドキュメントを xml データ型の列に格納する際には XML インスタンスを内部的にバイナリデータオブジェクト（BLOB）
として格納する。この xml データ型の列にインデックスを設定したのが XML インデックス。
テーブルの主キーにクラスタ化インデックスが作成されている必要がある。

空間インデックス
空間 インデックス
geometry データ型：ユークリッド座標系内の幾何データ（点/線/多角形）をサポートする平面空間データ値を格納できる
geogrphy データ型：地球上の領域を表す地理オブジェクト（地点/地上/土地など）を格納できる
空間インデックスを作成するにはテーブルに PRIMARY KEY 制約を定義しておく必要があるデータパーティション

パーティション関数と、パーティション構成を紐付け、テーブルのデータ格納をファイルグループではなくパーティション構成を指定することで、パーティションキーの値によりデータの格納先を振り分ける。
テーブル名を右クリック⇒[ストレージ]⇒[パーティショ
ンの作成] or [パーティションの管理]① １つまたは複数のファイルグループを作成。
CREATE DATABASE DB 名
ON PRIMARY ( NAME='論理名' ,FILENAME='ﾌｧｲﾙ名' ,SIZE=ｻｲｽﾞ) [,(...n)] ][ ,FILEGROUP ﾌｧｲﾙｸﾞﾙｰﾌﾟ名 ( NAME='論理名' ,FILENAME='ﾌｧｲﾙ名' ,SIZE=ｻｲｽﾞ) [,(...n)] ]LOG ON ( NAME='論理名' ,FILENAME='ﾌｧｲﾙ名' ,SIZE=ｻｲｽﾞ)
追加削除オペレーション

SQL 文

ファイルグループ

ALTER DATABASE DB 名 { ADD | REMOVE } FILEGROUP ﾌｧｲﾙｸﾞﾙｰﾌﾟ

ファイル

ALTER DATABASE DB 名 { ADD | REMOVE } FILE
( NAME = '論理名', FILENAME = 'ﾌｧｲﾙ名' , SIZE =ｻｲｽﾞ ) TO FILEGROUP ﾌｧｲﾙｸﾞﾙｰﾌﾟ

② パーティション関数の作成
パーティション関数は、パーティションを分割する際の境界を定義する
CREATE PARTITION FUNCTION 関数名 (分割するﾃﾞｰﾀ型)
AS RANGE [ RIGHT | LEFT ] FOR VALUES ('境界 1' [,'境界 2']… )分割するﾃﾞｰﾀ型

time、date、datetime、char、shinint、smallint、int、bigint、float、real 等が使用できる

RIGHT | LEFTRIGHT は右側のパーティションの開始位置、LEFT は、左側のパーティションの終了位置。
LEFT ('2006/01/01'‥‥と指定すると 2006 年 1 月 1 日のデータが前のパーティションに含まれてしまう為、RIGHT にすべき。RIGHT は ORACLE の HIGH VALUE と一緒

境界

最大 999 個(=1000 パーティション)まで作成可能③ パーティション構成の作成
パーティション関数により分割されたテーブルやインデックスをファイルグループに関連付ける
CREATE PARTITION SCHEME ﾊﾟｰﾃｨｼｮﾝ構成名 AS PARTITION 関数名 TO ( ﾌｧｲﾙｸﾞﾙｰﾌﾟ 1[,ﾌｧｲﾙｸﾞﾙｰﾌﾟ 2] … )ファイルグループは境界の数＋１（パーティションの数）以上、指定する。
同じファイルグループを繰り返し指定しても良い。

④ テーブルと INDEX のパーティション分割
TABLE や INDEX 作成時に、③で作成したパーティション構成名を ON 句で指定することでパーティション化される
CREATE TABLE ﾃｰﾌﾞﾙ名 ( 列名 ﾃﾞｰﾀ型 [,列名 ﾃﾞｰﾀ型] … ) ON ﾊﾟｰﾃｨｼｮﾝ構成名( ﾊﾟｰﾃｨｼｮﾝｷｰ列 )CREATE INDEX INDEX 名 ON ﾃｰﾌﾞﾙ名(ｶﾗﾑ) ON ﾊﾟｰﾃｨｼｮﾝ構成名( ﾊﾟｰﾃｨｼｮﾝｷｰ列 )ON 句にファイルグループ名を指定した場合は、単純に指定したファイルグループに全てのデータが格納されるだけになる。
無指定の場合は既定（PRIMARY）のファイルに格納される。
パーティションキー列で指定する列のデータ型は②で作成した分割するデータ型と一致している必要がある

⑤ パーティション状態を確認する
・該当テーブルのデータが、どのパーティションに格納されたか確認する
SELECT *, $PARTITION.ﾊﾟｰﾃｨｼｮﾝ関数名(ﾊﾟｰﾃｨｼｮﾝｷｰ) FROM ﾃｰﾌﾞﾙ名・パーティション毎に何件格納されているか確認する
SELECT $PARTITION.ﾊﾟｰﾃｨｼｮﾝ関数名(ﾊﾟｰﾃｨｼｮﾝｷｰ) ,COUNT(*) from ﾃｰﾌﾞﾙ名
group by $PARTITION.ﾊﾟｰﾃｨｼｮﾝ関数名(ﾊﾟｰﾃｨｼｮﾝｷｰ)その他、役に立ちそうなテーブル
sys.dm_db_partition_stats

パーティションごとに、ページ数と行数の情報を返す

sys.data_spaces

ファイルグループや、パーティション構成などの情報

sys.partition_functions

パーティション関数一覧

sys.partition_schemes

パーティション構成一覧

sys.partition_range_values

パーティション関数の範囲境界値一覧

sys.destination_data_spaces

パーティション構成のファイルグループ紐付け一覧[データベース名]⇒[ストレージ]にてパーティション関数と、パーティション構成の一覧が取得できる⑥ パーティション操作
パーティション単位のデータ移動させる
ALTER TABLE 元表 SWITCH PARTITION ﾊﾟｰﾃｨｼｮﾝ番号 TO 移動先の表 PATITION ﾊﾟｰﾃｨｼｮﾝ番号
・

元表と移動先の構造が全く同じであること

・

移動元のパーティション移動先のパーティションが同じファイルグループに配置されていること

・

移動先のパーティションが空であること

パーティション構成にファイルグループを追加
ALTER PARTITION SCHEME ﾊﾟｰﾃｨｼｮﾝ構成名 NEXT USED ﾌｧｲﾙｸﾞﾙｰﾌﾟ名
パーティション構成で単一のファイルグループのみで構成している場合、この SQL 文は省略できる

SPLIT で分割、MARGE で指定した境界値を削除する
ALTER PARTITION FUNCTION 関数名() [ SPLIT | MEARGE ] RANGE('境界値')

データ圧縮ALTER TABLE ﾃｰﾌﾞﾙ名 REBUILD PARTITION = { ALL | ﾊﾟｰﾃｨｼｮﾝ番号 }WITH ( DATA_COMPRESSION = { ROW | PAGE | NONE } )ROW は行圧縮、PAGE は、行圧縮～ディクショナリ圧縮まで行う。システムテーブルは圧縮できない。
圧縮の種類

説明

行圧縮

固定長のデータを可変長にする。8byte の固定長に 3byte しかデータが入ってない場合、5byte の空白を削除して 3byte の容量にする

ﾌﾟﾚﾌｨｯｸｽ圧縮

格納されている値の共通する文字列が列ごとに識別され圧縮情報(CI)構造にプレフィックス値として格納される。実際のデータはプレフィックス値への参照に置き換えられる。
20080601,20080602 の場合、200806 を①というプレフィックス値として格納し実際のデータは①01,①02 のように格納される。

ﾃﾞｨｸｼｮﾅﾘ圧縮

繰り返し出現する値を任意に抽出し、それらを CI に格納する。
CI に格納された任意の値への参照に置き換えられる

テーブル名を右クリック⇒[ストレージ]⇒[圧縮管理]■セキュリティ管理
データベースエンジンのセキュリティモデル
用語説明

プリンシパル

リソースを使用する主体の総称。Windows ユーザ、Windows グループ、ログイン、DB ユーザ、ロールなど。sys.server_principals

や

sys.database_principals

データベース

データベースに作成されたテーブル、ストアドプロシージャ、ビューなどのこと

オブジェクトsys.all_objects

リソース

オブジェクトと、オブジェクトを格納するサーバー（インスタンス）、データベース、スキーマな
どのコンテナ構造

プリンシパル

作成する SQL 文

定義の範囲

Windows レベル

create login

Windows ドメインログイン、Windows ローカルログイン

サーバーレベル

create login

SQL Server ログイン

DB レベルcreate { user|role }データベースユーザー、データベースロール、アプリケーションロール

ログインサーバと認証モード
モード

説明

Windows 認証

Windows に登録されたユーザやグループをログインと関連付ける。
SQL Server で行った操作ログなど個別のアカウントで識別可能

SQL Server 認証

master システムデータベースに作成したログイン名とパスワードを使用する為、利用者を個別に特定できない。大量の接続が発生するアプリケーションではコネクションプーリングを利用できる為、パフォーマンス上の利点がある

Windows 認証

master に作成

CREATE LOGIN [ログイン名] FROM WINDOWS
ログイン名[ WITH DEFAULT_DATABASE=DB 名 ][ドメイン名\ドメインローカルグループ名]または[PC 名\グループ名(ユーザー名)]\ や - などの文字が入ってる場合は、[ ] で括る必要がある

DEFAULT_DATABASE=DB 名

接続を許可する既定の DB を指定。省略すると master になる

sys.server_principals でログイン名やサーバーロール一覧を取得可能。

SQL Server 認証
CREATE LOGIN

master に作成

ログイン名

WITH PASSWORD='パスワード' [ UNLOCK ] [ MUST_CHANGE ] [,DEFAULT_DATABASE = DB名 ]WITH オプション

説明

MUST_CHANGE

最初のログイン時にパスワードの変更を要求。
CHECK_POLICY ON と CHECK_EXPIRATION が ON の場合に使用する

UNLOCK

認証ログインがロックされたのを解除できる。ALTER 文でのみ指定可能。

CHECK_POLICY={ON|OFF}

パスワードの複雑さポリシー（既定は ON）

CHECK_EXPIRATION

パスワードの有効期限ポリシー（SQL で作成の場合、既定は OFF、SMS の場合は ON）{ ON | OFF }CHECK_POLICY が ON の場合のみ指定可能

ログインの変更及び削除の SQL

説明DROP LOGIN ログイン名

ログインを削除

ALTER LOGIN ログイン名 DISABLE

ログインを無効化

ALTER LOGIN ログイン名 WITH NAME=新ログイン名

ログイン名を変更

データベースユーザ

各データベースに作成

CREATE USER ユーザー名

FOR LOGIN ログイン名[ WITH DEFAULT_SCHEMA = スキーマ名 ]FOR 文はユーザー名とログイン名が同じ、または Windows 認証用のログイン名の場合、省略可能。
DEFAULT_SCHEMA を省略した場合、既定のスキーマは dbo になる。
既定のスキーマはデータベース ユーザー用のオブジェクトの名前を解決するときにサーバーで最初に検索されるスキーマ。

sys.database_principals でデータベースユーザーや、データベースロール一覧取得。
name 列の dbo は sysadmin 固定サーバーロールのメンバに登録されたログインに関連付けられる。
name 列の guest はデータベースユーザーが作成されていないログインユーザーに関連付けられる。（既定は無効）

SQL インスタンスに作成された DB をバックアップし異なるインスタンスに復元した場合、ログインの SID と DB ユーザーの SID が不一致となり DB 間の関連づけが損なわれる。
SID を別のログインの SID に一致するように変更することで、ユーザーを別のログインに再マップする。
ALTER USER ユーザー名

WITH LOGIN = ログイン名

ロールベースの権限管理
ロールの種類説明

固定ｻｰﾊﾞｰﾛｰﾙ

SQL Server レベル（エンドポイント、ログイン、データベース）

固定ﾃﾞｰﾀﾍﾞｰｽﾛｰﾙ

DB レベル（DB ユーザ、ロール、アプリケーションロール、スキーマ等）

ﾕｰｻﾞｰ定義のﾃﾞｰﾀﾍﾞｰｽﾛｰﾙ

固定ロールの場合は DB 内で対象となる全てのオブジェクトに権限が付与されてしまう。
テーブル単位など細かい単位で権限を付与することができる

ｱﾌﾟﾘｹｰｼｮﾝﾛｰﾙ

特定のアプリケーションから接続しているユーザーに対してのみ、特定のデータへのアクセスを有効にできる固定サーバーロール
固定 サーバーロール
EXEC sp_addsrvrolemember 'ログイン名', 'ロール名'
ロール名

説明

sysadminシステム管理者権限で全ての操作が可能。インストール時の管理者と sa に付与されている

serveradmin

環境設定オプションの設定が可能。サーバーのシャットダウンも可能

setupadmin

リンクサーバーと起動プロシージャを管理

securityadmin

ログインとログインのプロパティを管理。
CREATE DATABASE 権限の管理、エラーログの読み取りパスワードの変更やリセットができる

processadmin

SQL Server 内で実行されるプロセスを終了できる

bulkadmin

BULK INSERT ステートメントを実行可能

diskadmin

ディスクファイルを管理

dbcreator

データベースの作成、変更、削除が可能

public全てのログインが含まれる特殊なロール。削除不可

sp_helpsrvrolemember でログイン名の与えられたサーバー権限一覧取得

固定データベースロール
固定 データベースロール
EXEC { sp_addrolemember | sp_droprolemember } 'ロール名', 'ユーザー名'
ロール名

説明（データベース単位で一括許可）

db_owner

データベースでのすべての構成作業とメンテナンス作業を実行でき、データベース削除も可能

db_securityadmin

ロールのメンバシップを変更し、権限を管理可能

db_accessadmin

Windows ログイン、Windows グループ、および SQL Server ログインのデータベースに対するアクセスを追加または削除できる

db_backupoperator データベースをバックアップできる。DBCC / CHECKPOINT / BACKUP
db_ddladmin

データベース内で、すべての DDL (データ定義言語) コマンドを実行できる
GRANT / REVOKE / DENY は実行出来ない

db_datawriter

データベース内のテーブルのデータを追加、削除、または変更できる

db_datareader

データベース内のテーブルからすべてのデータを読み取ることができる

db_denydatawriter データベース内のテーブルのデータを追加、変更、または削除することができない
db_denydatareader データベース内のテーブルのデータを読み取ることができない
dbm_monitor

データベースミラーリングセッションの状態を監視できる

sp_helprolemember でデータベースユーザーに与えられた権限一覧取得

ユーザー定義
ユーザー定義の定義 のデータベースロール
ロールを作成CREATE ROLE ロール名

ロールに権限を与える

GRANT { 権限 } ON OBJECT::オブジェクト名 TO ロール名
権限：SELECT,INSERT,UPDATE,VIEW DEFINITION

ユーザーにロールを付与する

EXEC sp_addrolemember 'ロール名', 'ユーザー名'

メタ情報一覧取得
メタ 情報一覧取得の情報一覧取得の許可
メタ情報はユーザーが所有しているオブジェクトか、そのユーザーに権限が許可されているオブジェクトのみ参照可能。
オブジェクトを使用させずに定義情報の参照のみを行わせたい場合は VIEW DEFINITION 権限を追加する。
GRANT VIEW ANY DEFINITION TO { ログイン名 | ユーザー定義 DB ロール名 }GRANT VIEW DEFINITION TO { DB ユーザー名 | ユーザー定義 DB ロール名 }メタデータ表示のスコープはサーバー、データベース、セキュリティスキーマ、個別のオブジェクトで可能

セキュリティスキーマ
オブジェクトを省略しない場合は サーバー.データベース.スキーマ.オブジェクト と完全修飾名で記載する。
サーバ、データベース、スキーマは省略可能。省略した場合、現在ログインしているサーバー、データベース、
スキーマは既定のスキーマとなる。見つからない場合は dbo を使用する。

スキーマを作成する

CREATE SCHEMA

スキーマ名 [ AUTHORIZATION { DB ﾕｰｻﾞｰ名 | DB ﾛｰﾙ } ]※AUTHORIZATION でスキーマの所有者を指定する。省略すると dbo になるスキーマを変更する

ALTER SCHEMA

新スキーマ名所有者を変更する

ALTER AUTHORIZATION ON

TRANSFER

既存スキーマ.オブジェクト

既存スキーマ.オブジェクト TO { DB ﾕｰｻﾞｰ名 | DB ﾛｰﾙ }SalesLT スキーマの所有ユーザーが Sales とした場合、Sales ユーザーは、SalesLT.TABLE へのアクセス権限がある。
他スキーマが所有している dbo.TABLE へのアクセスはできない。（GRANT 文等により権限付与が必要）
ところが、所有者を変更、ALTER AUTHORIZATION ON dbo.TABLE TO Sales を行うことで、
dbo.TABLE の所有者が Sales ユーザーとなり、Sales ユーザーにてアクセスできるようになる。

権限の設定
許可

GRANT

権限 [ ON ｾｷｭﾘﾃｨ保護可能なｵﾌﾞｼﾞｪｸﾄ ] TO プリンシパル [ WITH GRANT OPTION ]削除

REVOKE 権限 [ ON ｾｷｭﾘﾃｨ保護可能なｵﾌﾞｼﾞｪｸﾄ ] FROM プリンシパル

拒否

DENY

権限 [ ON ｾｷｭﾘﾃｨ保護可能なｵﾌﾞｼﾞｪｸﾄ ] TO プリンシパル「拒否」は権限の使用を禁止することで「許可」よりも優先される。
セキュリティ保護可能なオブジェクトの指定は以下のように指定する。
ON OBJECT::テーブル名|

ON SCHEMA::スキーマ名|

ON DATABASE::データベース名

WITH GRANT OPTION そのプリンシパル権限を他のプリンシパルにも許可できる（Oracle の ADMIN オプション）
権限の確認は sys.server_permissions

サーバースコープのサーバースコープの権限設定

[一覧]

GRANT

TO { ログイン名 | ユーザー定義 DB ロール }サーバースコープ権限

master データベースにて実行する

サーバースコープ権限許可される内容

左記権限を含む

ADMINISTER BULKBULK INSERT または SELECT * FROM OPENROWSET(BULK...)ステー

CONTROL SERVEROPERATIONS

トメントを使用した一括インポート

ALTER ANY CONNECTIONSQL Server への接続

CREDENTIAL

作成・修正・削除

資格情報

DATABASE

作成・修正・削除

データベースENDPOINT

作成・修正・削除

エンドポイント

EVENT NOTIFICATION

作成・修正・削除

イベント通知LINKED SERVER

作成・修正・削除

リンクサーバー

LOGIN

作成・修正・削除SQL Server ログイン

ALTER RESOURCES

データベースファイルの構成管理

SERVER STATE

バッファプール・統計情報などの変更

SETTINGS

サーバーレベルの構成オプションの変更

TRACE

トレース取得・再生

AUTHENTICATE SERVER

インスタンス全体での信頼設定

CONNECT SQLSQL Server への接続

CONTROL SERVER

sysadmin 固定サーバーロールのメンバシップと同等

EXTERNAL ACCESS ASSEMBLY

アセンブリ署名に使用した証明書または非対称キーに基づき外部リソースへのアクセス許可

UNSAFE ASSEMBLY

アセンブリ署名に使用した証明書または非対称キーに基づきアンマネージドコードの呼び出しを許可

SHUTDOWNSQL Server 停止

VIEW ANY DEFINITION

リソースメタデータの表示

CREATE ANY DATABASE

作成・修正

データベースALTER ANY DATABASEDDL EVENT NOTIFICATION

ｻｰﾊﾞｰｽｺｰﾌﾟ(ON SERVER)の DDL ｽﾃｰﾄﾒﾝﾄのｲﾍﾞﾝﾄ通知設定

ALTER ANY EVENTTRACE EVENT NOTIFICATION

トレースイベントの通知作成

NOTIFICATIONENDPOINT

エンドポイントの作成

ALTER ANY ENDPOINT

データベースのメタデータ表示

VIEW ANY DEFINITION

サーバースコープの動的管理ビューと動的管理関数の使用

ALTER SERVER STATE

VIEW ANY DATABASESERVER STATE

データベーススコープのデータベーススコープ の権限設定[一覧]

各データベースにて実行する

GRANT ﾃﾞｰﾀﾍﾞｰｽ権限[,～n] TO { DB ﾕｰｻﾞｰ名 | ﾕｰｻﾞｰ定義 DB ﾛｰﾙ } [,～n]データベース権限許可される内容

左記権限を含む

ALTER

各種リソースの作成・変更・削除

CONTROLALTER ANY

作成・変更・削除ALTER

APPLICATION ROLE

アプリケーションロールASSEMBLY

アセンブリ

ASYMMETRIC KEY

非対称キー

CERTIFICATE

証明書

DATABASE DDL TRIGGER

DDL トリガDATABASE EVENT NOTIFICATION

データベーススコープ(ON DATABASE)のイベント通知

DATASPACE

パーティション関数、パーティション構成

FULLTEXTCATALOG

フルテキストカタログ

CONTRACT

Service Broker コントラクト

MESSAGE TYPE

Service Broker メッセージ型

REMOTESERVICE BINDING

Service Broker リモートサービスバインド

SERVICE

Service Broker サービス

ROLE

データベースロール

ROUTE

Service Broker ルート

SCHEMA

スキーマ

SYMMETRIC KEY

対象キー

USER

データベースユーザー

AUTHENTICATE

データベース間の信頼関係の設定

BACKUP DATABASEBACKUP DATABASE ステートメントの実行

BACKUP LOG

BACKUP LOG ステートメントの実行

CHECKPOINTCHECKPOINT ステートメントの実行

CONTROLdbo または dbo_owner 固定ロールのメンバシップと同等

CONNECT REPLICATION

パブリッシャーに対する接続

CONNECTSQL Server に対する接続

CONNECT REPLICATIONCREATE AGGREGATE

ALTER 権限を持つスキーマに対する集約関数の作成

ALTER

DEFAULT

ALTER 権限を持つスキーマに対するデフォルト

FUNCTION

ALTER 権限を持つスキーマに対する関数

PROCEDURE

ALTER 権限を持つスキーマに対するプロシージャ

SYNONYM

ALTER 権限を持つスキーマに対するシノニム

TABLE

ALTER 権限を持つスキーマに対するテーブル

TYPE

ALTER 権限を持つスキーマに対するユーザー型

VIEW

ALTER 権限を持つスキーマに対するビュー

XML SCHEMA COLLECTION

ALTER 権限を持つスキーマに対する XML ｽｷｰﾏｺﾚｸｼｮﾝ

QUEUE

Service Broker キュー

ASSEMBLY

データベースアセンブリ

ASYMMETRIC KEY

非対象キー

CERTIFICATE

証明書

CONTROLALTER ANY ～CONTRACT

Service Broker コントラクト

MESSAGE TYPE

Service Broker メッセージ型

ROUTE

Service Broker のルート

REMOTE SERVICE BINDING

Service Broker リモートサービスバインド

SERVICE

Service Broker のサービス

DATABASE DDL EVENT NOTIFICATION

データベーススコープ(ON DATABASE)のイベント通知

FULLTEXT CATALOG

フルテキストカタログ

ROLE

データベースロール

SCHEMA

スキーマ

SYMMETRIC KEY

対象キー

DATABASE

データベースの作成

DELETE

DB 内全ｽｷｰﾏのｵﾌﾞｼﾞｪｸﾄに対する DELETE 実行

INSERT

DB 内全ｽｷｰﾏのｵﾌﾞｼﾞｪｸﾄに対する INSERT の実行

SELECT

DB 内全ｽｷｰﾏのｵﾌﾞｼﾞｪｸﾄに対する SELECT の実行

UPDATE

DB 内全ｽｷｰﾏのｵﾌﾞｼﾞｪｸﾄに対する UPDATE の実行

EXECUTE

DB 内全ｽｷｰﾏのﾌﾟﾛｼｰｼﾞｬ、ｽｶﾗ変数、集計関数の実行

REFERENCES

DB 内全ｵﾌﾞｼﾞｪｸﾄ､ｱｾﾝﾌﾞﾘ､型に対する REFERENCES ﾊﾞｲﾝﾄﾞ

TAKE OWNERSHIP

DB 内全ｵﾌﾞｼﾞｪｸﾄの所有権のプリンシパル間の転送

SHOWPLAN

プランの表示SUBSCRIBE QUERY NOTIFICATIONS

ｸｴﾘ通知ｻﾌﾞｽｸﾘﾌﾟｼｮﾝ要求を含むコマンドの実行

VIEW DATABASE STATE

DB スコープの動的管理ビューと動的管理関数の使用

VIEW DEFINITION

セキュリティ保護可能なリソースのメタデータ表示

CONTROL

スキーマ・
スキーマ・オブジェクトスコープのオブジェクトスコープ の権限設定
GRANT 権限 [,～n] ON SCHEMA::スキーマ TO { DB ﾕｰｻﾞｰ名 | ﾕｰｻﾞｰ定義 DB ﾛｰﾙ } [,～n]GRANT 権限 [,～n] ON OBJECT::[ｽｷｰﾏ.]ｵﾌﾞｼﾞｪｸﾄ[(列,･･･)] TO { DB ﾕｰｻﾞｰ名 | ﾕｰｻﾞｰ定義 DB ﾛｰﾙ } [,～n]権限許可される内容（スキーマまたは、オブジェクト単位）

CONTROL

オブジェクトに対する全ての操作とリソース権限

TAKE OWNERSHIP

オブジェクトの所有者に対するプリンシパル間の転送

ALTER

オブジェクトの変更

EXECUTE

プロシージャ、スカラ変数、集計関数の実行

INSERT

オブジェクトに対する INSERTDELETE

オブジェクトに対する DELETE

UPDATE

オブジェクトに対する UPDATESELECT

オブジェクトに対する SELECTREFERENCES

オブジェクト、アセンブリ、型に対する REFERENCES バインド

VIEW DEFINTION

オブジェクトに対するメタデータの表示

VIEW CHANGE TRACKINGON OBJECT 句でのみ使用可能

実行コンテキストの制御
A さんが、B さんのストアドプロシージャを実行したとする。
ストアドプロシージャが、B さん所有のオブジェクトを参照する場合、A さんの select 権限はチェックされない
ストアドプロシージャが、B さん以外の所有するオブジェクトを参照する場合、A さんの select 権限がチェックされる
VIEW や PROCEDURE を作成する際に EXECUTE AS を使用することで、それ以降の処理は指定のユーザーで実行される
CREATE PROCEDURE ﾌﾟﾛｼｰｼﾞｬ名[ (@変数名 データ型) ] WITH EXECUTE AS 引数 AS ソース
引数

説明

CALLER

モジュールを実行しているユーザー本人のコンテキストで動作する（デフォルト）

OWNER

モジュールを所有しているユーザー本人のコンテキストで動作する
DDL トリガまたはログオントリガには指定できない'DB ユーザー名'

指定した DB ユーザのコンテキストで実行される。
サーバースコープの DDL トリガまたはログオントリガには指定できない'ログイン名'指定したログインのコンテキストで実行される。
サーバースコープの DDL トリガまたはログオントリガにのみ指定できる

プロシージャのソース内で EXECUTE AS を使用することで動的にコンテキストを変更できる
プロシージャのソース内で REVERT を使用すると元のコンテキスト（WITH 句で指定したコンテキスト）に戻る

監査
ログインの
ログインの監査既定の設定ではインスタンスのログインの失敗を監査している。
[サーバーのプロパティ]⇒「セキュリティの項目」で変更可能。
ログは Windows のイベントログ及び、SQLServer ログに保存される。

SQL Server 監査の監査の構成
監査オブジェクトで出力先（FILE or イベントログ）を定義し、監査の仕様オブジェクトでサーバーまたは DB レベルで監査の対象となるアクション及びアクショングループを定義。
定義されたアクションが発生すると、監査オブジェクトで指定したファイルにログを出力する。
[インスタンス]⇒[セキュリティ]⇒[監査]を右クリック⇒[監査ログの表示]でログが確認できる。

監査の
監査 の SQL

master データベースにて実行する[インスタンス]⇒[セキュリティ]⇒[監査]を右クリック⇒[新しい監査]

CREATE SERVER AUDIT(

監査ログファイル名

TO FILEFILEPATH = 'ディレクトリのフルパス'[ ,MAXSIZE = ファイルサイズ { MB | GB | TB } ][ ,MAX_ROLLOVER_FILES = 最大ローテーション][ ,RESERVE_DISK_SPACE = { ON | OFF }]…事前にログファイルの容量を割り当てるか否か) [ WITH (QUEUE_DELAY = キューの遅延(ms),ON_FAILURE = { CONTINUE | SHUTDOWN }) ]…監査に失敗した場合の動作サーバーまたは
サーバーまたはデータベースレベル
または データベースレベルのデータベースレベルの監視仕様
サーバーまたはデータベースの [セキュリティ]⇒[サーバー監査の仕様]を右クリック⇒[新しい監査]

CREATE { SERVER | DATABASE } AUDIT SPECIFICATIONFOR SERVER AUDIT

監視仕様の名前

監査ログファイル名

ADD (監査アクション) [,ADD･･･]‥‥SERVER レベルの場合

ADD (監査アクション ON ｾｷｭﾘﾃｨ保護可能なｵﾌﾞｼﾞｪｸﾄ by プリンシパル) [,ADD･･･]‥‥DATABASE レベルの場合[ WITH ( STATE = { ON | OFF } ) ]

監査、
監査 、監査仕様の監査仕様の開始・
開始 ・終了ALTER SERVER AUDIT

監査ログファイル名

WITH ( STATE = { ON | OFF } )ALTER { SERVER | DATABASE } AUDIT SPECIFICATION

監視仕様の名前

WITH (STATE = { ON | OFF } )トリガー
ログオントリガー
ユーザーが SQL にログインした際に定義済みのバッチやストアドプロシージャを実行させることが出来る。
CREATE TRIGGER トリガー名 ON{ ALL SERVER | DATABASE } FOR LOGON AS

IF 条件 BEGIN SQL ステートメント END
条件の設定例説明

ORIGINAL_LOGIN() = 'A'

ログオン名が A の場合(CAST(GETDATE() AS TIME)) < '9:00:00'

現在時刻が 9:00:00 以前の場合

SQL ステートメント

説明

ROLLBACK

接続を拒否する

DML トリガー
トリガー
INSERT、UPDATE、DELETE ステートメントが実行された後に AS 句以降の SQL が実行される。
CREATE TRIGGER トリガー名 ON オブジェクト名 AFTER INSERT, UPDATE, DELETE AS SQL ステートメント
SQL ステートメントに PRINT '表示させたい文字列' と記述すると、指定した文字列が表示される。

DDL トリガー
CREATE、ALTER、DROP、その他の DDL ｽﾃｰﾄﾒﾝﾄ、および DDL に類似した処理を実行するｼｽﾃﾑ ｽﾄｱﾄﾞ ﾌﾟﾛｼｰｼﾞｬを操作する
CREATE TRIGGER トリガー名 ON{ALL SERVER | DATABASE} {FOR | AFTER} DDL イベント AS SQL ステートメント

DDL イベントは、CREATE_TABLE や、DROP_SYNONYM など。

透過的暗号化
データベース単位（master は透過的暗号化できない）でデータファイルとログファイルを暗号化する。
データページの暗号化/複合化はページ I/O 時にリアルタイムに行われる。
①データベースマスターキーの作成（master データベースに作成）
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'パスワード'②サーバー証明書の作成（master データベースに作成）
CREATE CERTIFICATE

証明書名

WITH SUBJECT = 'コメント'

③データベース暗号化キーの作成（暗号化したいデータベースに作成）
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = 暗号化ｷｰ

ENCRYPTION BY SERVER CERTIFICATE 証明書名

暗号化キーには、AES_128、AES_192、AES_256、TRIPLE_DES_3KEY の何れかを指定④データベース暗号化キーに使用した証明書及び、秘密キーのバックアップ
BACKUP CERTIFICATE

証明書名

TO FILE = 'フルパスでのファイル名'

WITH PRIVATE KEY (FILE = 'フルパスでのファイル名' ,ENCRYPTION BY PASSWORD = 'パスワード')⑤透過的な暗号化を有効にする
ALTER DATABASE

暗号化キーを作成したデータベース名SET ENCRYPTION ON■インスタンスの管理
SQL Server エージェント
SQL Server データベースエンジンサービスで発生する様々なイベントの監視、管理作業を自動化するためのサービス。
主要なコンポネートは左記の 3 つ。
定期的に行う作業はジョブに登録。
警告はエラーが発生した際に起動するジョブを登録可能。
オペレータはメッセージの送信先

データベースメールのデータベースメールの 作成
メールの送信元。SQL Server からオペレータ宛にメールを送信する。
SMTP サーバー経由でメールを送信するデータベースメールと、MAPI ベースの SQL Mail の 2 種類から選択可能。
[管理]⇒[データベースメール]を右クリック⇒[データベースメールの構成][次のタスクを実行してデータベースメールをセットアップする]を選択し次の画面へ遷移。
任意のプロファイル名（SMTP メールアカウント、セキュリティ、パラメータを定義したもの）を設定する。
SMTP 入力項目

説明

アカウント名

このアカウント名が下記の情報が定義されたものになる
1 つのプロファイルに複数のアカウントを定義できる

電子メールアドレス

DB から送信される電子メールアドレス

表示名

DB から送信される送信者の表示名

サーバー名

SMTP サーバーのアドレスを入力。OP25B の場合、587 番ポートを使用する

SMTP 認証

SMTP サーバーとの認証情報を選択する

オペレータのオペレータの 設定
ジョブの完了時や警告の発生時に通知メッセージを受け取る個人またはグループのメールアドレスに対する別名。
メールの通知は、「ジョブ」や「警告」のプロパティにある「応答」や「通知」から送信するオペレータを設定する。
[SQL Server エージェント]⇒[オペレータ]を右クリック⇒[新しいオペレータ]
全般

説明

名前

オペレータの名前（ジョブでの応答や警告の通知で表示される名前）

有効のチェック

チェックを外した場合、通知先にこのオペレータが設定されていてもメールを送信しない

電子メールアドレス

メールを送信する相手の電子メールアドレス

メールを送信させるには[SQL Server エージェント]の[プロパティ]にて「警告システム」のメールプロファイルを有効、
メールシステム（データベースメールか SQL Mail）及び、プロファイルを選択した後、SQL Server エージェントを再起動する必要がある。

SQL Server エージェントのジョブ構成
ジョブで実行可能なのは、ActiveX スクリプト、PowerShell スクリプト、Transact-SQL、SQL Server Integration Services
実行可能なプログラムまたは OS コマンド、Analysis Services のコマンド及びクエリ、各種レプリケーションタスク。
ジョブにオペレータを定義することで、ジョブの実行状態をメールで通知できる。

Transact-SQL を実行するジョブステップでは、ジョブ所有者のコンテキストが使用される。
それ以外のステップは既定で SQL Server エージェントのサービスアカウントが使用される。
SQL Server エージェントがローカルシステムやローカルアカウントの場合、ネットワークリソースへアクセスはできない。
SQL Server 構成マネージャから、SQL Server エージェントのログオンアカウントを変更できる。
サービスアカウントに権限を付与した場合、全てのエージェントジョブに権限が拡張される。
ジョブステップ毎にプロキシアカウントを使用することで必要最小限の権限を追加するだけで済む。
プロキシアカウントは、SQL エージェントの[プロキシ]から作成できる。

ジョブ設定項目

設定内容

全般

ジョブ名、ジョブの所有者、カテゴリを指定（カテゴリはジョブ内容を解りやすく分類する為に使用）

ステップ「全般」項目にて、「新規作成」から実際に行うアクションを定義する。複数ステップ登録可能。
「詳細設定」項目では成否後のアクション、ログ出力（ファイル or テーブル）の設定を行う。
履歴にステップ出力を含めるにチェックすると、ジョブを右クリック⇒[履歴]を表示した際のログにス
テップのログが含まれる。（通常はジョブ開始と終了のみで途中のステップはログに出力されない）

スケジュール「新規作成」にスケジュールを作成するか、「選択」にてスケジュール管理（ジョブを右クリック⇒[ス
ケジュール管理]）から作成済みのスケジュールから選択することで任意の時間にジョブが実行できる。
「スケジュール種類」の CPU アイドル時は、エージェントのプロパティ、詳細設定で指定する

警告

SQL Server データベースエンジンで発生する特定のイベントに対して、予め定義した操作を実行する。
また警告のオプションから指定したオペレータに対してメールの通知の設定ができる

通知

ジョブの成功、完了、失敗時の何れかの時、オペレータを指定しメールを通知したり、
Windows のログに書き込んだりすることが可能。ジョブの実行後、ジョブの削除を行うこともできる

対象サーバ

ジョブを実行するサーバー

SQL Server エージェントの警告の構成
SQL エージェント⇒[警告]から設定する。
SQL Server データベースエンジンで発生する特定のイベントに対して、予め定義した操作を実行する機能。
イベントはイベントログ(アプリケーション)で記録され、ログを読み取り定義された警告と比較する。
定義された警告と一致していた場合に関連付けられた応答を行う。パフォーマンスの閾値を超えた時に応答させる事も可。
重要度が 0～10 は情報レベル。19～24 がクリティカルなエラー。

SQL Server エージェントのプロパティ、警告システムのメールプロファイルを有効にする。
「緊急時のオペレータを有効にする」は指定した全てのオペレータに通知が成功しない場合、他のオペレータにメッセージを送信できる。緊急時のオペレータを利用できるのは警告の通知先オペレータがポケットベルを使用している場合に限るポリシーベースの管理
1 つ以上の SQL Server 2008 インスタンスを「ポリシー」を使って管理するためのフレームワーク。
ポリシーは事前に定義された一連のフォセット（インスタンス・データベース・オブジェクトなどの管理対象の動作または特性をモデル化した論理プロパティの集合）に基づき定義する。

ポリシーの評価タイミングは評価モードで設定する。
評価モード

説明

サポートされるフォセット

要求時

ユーザーが直接指定（ポリシーを右クリック⇒評価）した場合

全てのフォセット

ｽｹｼﾞｭｰﾙで実行

SQL Server エージェントのジョブを使用し定期的にポリシーを評価

全てのフォセット変更中:回避ポリシーの違反時に DDL トリガによって変更がキャンセルされる。

DDL ｽﾃｰﾄﾒﾝﾄがﾄﾗﾝｻﾞｸｼｮﾝを

このポリシーは要求時に実行することも可能

ｻﾎﾟｰﾄしているﾌｫｾｯﾄ

関連する変更が行われた時にログが記録されイベント通知される。

ﾌｫｾｯﾄのﾌﾟﾛﾊﾟﾃｨ値の変更をｲﾍﾞ

このポリシーは要求時に実行することも可能

ﾝﾄとしてｷｬﾌﾟﾁｬできるﾌｫｾｯﾄ

変更中:ﾛｸﾞのみ

サーバーに
サーバーに対 するポリシー
するポリシーのポリシーの定義
インスタンスの[管理]⇒[ポリシー]⇒[フォセット]にて定義できるフォセット一覧が確認できる。
先頭に Server と名前が付与しているのがサーバーに関連するフォセット。プロパティで設定可能な値の説明が見れる。
Server Configuration では sp_configure に対応する値、Surface Area Configuration(※)では DB エンジンサービスがサポートする機能のうち、外部からのアクセス領域を拡張するセキュリティ(DB メール機能の有効/無効など)が含まれる。
※DB エンジンサービスだけではなく、Analysis Services、Reporting Services に対応するフォセットも提供されている

①

条件の定義

[管理]⇒[条件]を右クリック[新しい条件]にてフォセットを選択し、式の条件を羅列していく。

②

ポリシーの定義[管理]⇒[ポリシー]を右クリック⇒[新しいポリシー]にて①で作成した条件を選択。
[条件 1] AND [条件 2]のように複数組み合わせことも可能。

③

ポリシーの評価[管理]⇒[ポリシー]を右クリック⇒[評価]にて②で作成されたポリシーの結果を確認する。
ポリシー違反となったものはその場で変更できる。異なるインスタンスからでもポリシーの評価が可能。
ポリシーの評価ダイアログで、ソースから②を作成したインスタンスを選択すれば評価できる。
[表示]メニュー⇒[登録済みサーバー]⇒[Local Server Groups]から、右クリックで評価すると、[Local Server Groups]に登録されているインスタンスを一度に評価できる。

■データベースの管理
トランザクションログによる復旧
バッファキャッシュ内でデータが変更されたものがデータファイルに反映される（CHECK POINT）前に障害が発生した場合、先行書き込みされているトランザクションログに書き込まれている内容とデータファイルの内容に差違が発生する。
このような場合でも SQL Server インスタンスを開始すれば整合の取れた状態に自動復旧する。
ロールフォワード：データページに反映されていない更新をログシーケンス番号の順番に適用する
ロールバック：ロールフォワードにより適用された更新の中でｺﾐｯﾄされていないﾄﾗﾝｻﾞｸｼｮﾝがあれば更新を取り消す

トランザクションログのトランザクションログ の管理方法（データベースのプロパティにある[オプション]から管理方法を選択）
管理方法
完全

トランザクションログに書き込まれる変更履歴はバックアップを実行するまで切り捨てられない

単純

既にデータファイルに更新が反映されている不要なトランザクション領域をチェックポイント実行時に切り捨てる。単純以外はトランザクションログをバックアップしない限り切り捨ては行われない。
切り捨てを行わないとトランザクションログの容量が溢れエラーとなってしまう
トランザクションログのバックアップが行えない為、時間指定の復元はできない

一括ログ

一括操作（一括インポートや、テーブルデータのコピー、インデックスの DDL 操作など）は大量の変更
履歴が書き込まれログが肥大化されてしまう。一括ログにすると一括操作に関しては変更されたエクス
テントの情報のみがトランザクションログに書き込まれることでログの肥大化を防ぐことができる
トランザクションログに一括操作が含まれている場合、時間指定の復元が出来なくなる

データベースのバックアップ
sysadmin、db_owner、db_backupoperator のロールメンバがバックアップの実行ができる。
種類

説明

完全バックアップ

データファイルとバックアップ中に行われたデータページへの変更履歴をバックアップ。
バックアップ完了時点でコミットされてないトランザクションは変更履歴を使用しロールバック
BACKUP DATABASE DB 名 TO { ﾊﾞｯｸｱｯﾌﾟﾃﾞﾊﾞｲｽ | DISK = 'ﾌｧｲﾙ' } [WITH ｵﾌﾟｼｮﾝ]

差分バックアップ

完全バックアップ以降、更新されたデータページのみがエクステント単位で記録される。
前回の差分バックアップ以降の差分バックアップではないことに注意！
差分バックアップは全てのエクステントが保存される為、トランザクションログも含まれる。
BACKUP DATABASE DB 名 TO { ﾊﾞｯｸｱｯﾌﾟﾃﾞﾊﾞｲｽ | DISK = 'ﾌｧｲﾙ' } WITH DIFFERENTIAL

トランザクション

完全バックアップまたは差分バックアップ以降に発生したﾄﾗﾝｸｻﾞｸｼｮﾝﾛｸﾞのみバックアップ。

ログバックアップ

ログバックアップをする事でコミット済みの変更履歴がﾄﾗﾝｸｻﾞｸｼｮﾝﾛｸﾞから切り捨てられる。
トランザクションの管理方法が、完全、一括ログでないと実行出来ない。
BACKUP LOG DB 名 TO { ﾊﾞｯｸｱｯﾌﾟﾃﾞﾊﾞｲｽ | DISK = 'ﾌｧｲﾙ' } [WITH ｵﾌﾟｼｮﾝ]

WITH オプション

説明

INIT

バックアップメディア内の既存のバックアップデータが上書きされる

NOINIT

バックアップメディアに追記する（デフォルト）

COPY_ONLY

バックアップの履歴を残さずバックアップする。
本来履歴は msdb の backupset、backupmediafamily に書き込まれる

NORECOVERY

データベースは復元中の状態になり、ログ末尾のバックアップの後にデータベースが変化しない
ことが保障される（復元操作の直前には以下のコマンドでログ末尾のバックアップが必要）
BACKUP LOG データベース名 TO バックアップデバイス名 WITH NORECOVERYNO_TRUNCATE

トランザクションログのバックアップ時にトランザクションログの切り捨てを行わない

COMPRESSION

バックアップファイルを圧縮する

NAME

バックアップセットの説明。backupset の name 列に反映される

バックアップデバイス
物理デバイス名（Z:\back.bac など）の他に論理デバイス名の指定が可能。
exec sp_addumpdevice 'disk | tape', '論理デバイス名','物理デバイス名'
デバイスの削除は、sp_dropdevice '論理デバイス名'

デバイスの一覧は sys.backup_devices

データベースの復元
sysadmin、dbcreator の固定サーバーロール及び DB 所有者(dbo)が復元操作可能
復元

バックアップされたデータをディスクにコピーし、トランザクションログに記録されたトランザクションをデータファイルに適用する処理。トランザクションの適用をロールフォワードと言う

復旧

DB を一貫性のある状態にするための処理。復元を行った直後はコミットされてないトランザクションが含まれている為、一貫性が失われており復旧を行ってない DB は「復元しています」の状態になり開けない。
復旧を行うことでコミットされてないトランザクションがロールバックされて開けるようになる

RESTORE [ DATABASE | LOG ] DB 名 [PAGE='ﾌｧｲﾙ ID:ﾍﾟｰｼﾞ ID'] FROM { ﾊﾞｯｸｱｯﾌﾟﾃﾞﾊﾞｲｽ | DISK = 'ﾌｧｲﾙ' }[ WITH FILE=ﾌｧｲﾙ位置, MOVE '論理ﾌｧｲﾙ名' TO '物理ﾌｧｲﾙ名' ,ﾘｶﾊﾞﾘｵﾌﾟｼｮﾝ ]各種オプション

説明

PAGE

対象のページのみ修復する。CHECK 後に破損がある場合、msdb.dbo.suspect_pages に情報あり

FILE = position

該当するﾊﾞｯｸｱｯﾌﾟｾｯﾄとﾌｧｲﾙを検出するために使用されるﾊﾞｯｸｱｯﾌﾟｾｯﾄの位置を指定。
msdb.dbo.backupset、position カラムにて確認。
省略した場合はﾊﾞｯｸｱｯﾌﾟﾃﾞﾊﾞｲｽ内で最初に見つけた位置になる？

MOVE

MOVE '論理ファイル名' TO '物理ファイル名'復元するデータファイルの位置を変更する。デフォルトは BACKUP した際の物理デバイスに復元
sys.database_files

RECOVERYに論理ファイル名と、物理ファイル名の情報がある（デフォルト）復元と復旧の両方を行う。復旧を行うと後続のバックアップセットの適用ができない
為、一番最後のバックアップセットのみ RECOVERY を行う

NORECOVERY

復旧処理を行わず復元中の状態になる。バックアップファイルが複数ある場合、途中のファイルに対して RECOVERY を指定すると後続のファイルが適用されなくなってしまう為

STANDBY

復元中の状態にするが、データベースの読み取りだけはできる

STOPAT datatime

指定した時間の状態に DB を復元する

REPLACE

通常、復元によって実行されるいくつかの重要な安全性チェックが無効になる
別 DB から取得したバックアップで既存の DB 名を上書きや、ログ末尾のバックアップチェック等

データの整合性チェック
データベースプロパティの「オプション」⇒「ページ確認」にてチェック方法を選択。
CHECKSUM
DB がページに書き込む際、ページ全体の CHECKSUM をページヘッダーに記録し、読み込む際に CHECKSUM を再計算することで完全な整合性を確認することができる。

TORN_PAGE_DETECTION
DB が 8KB のページをディスクに書き込む際に 512byte のセクタ毎にビット情報をページヘッダーに格納する。
ディスクから読み取る際はページヘッダーに格納されたセクタ毎のビットパターンとページ内の情報を比較する。
512 バイト以下の破損は検知できない。Windows Server 2000 以前のデフォルトオプション。

ページ破損の確認
DBCC パラメータ ( 'チェック対象'[,引数] ) [ WITH オプション ]パラメータ

チェックする項目

CHECKALLOC

データベースのディスク領域の割り当てと、ページ使用一貫性

CHECKTABLE

TABLE 及び INDEX 付きビューのページ割り当てと構造的整合性

CHECKFILEGROUP

ファイルグループ内にある全 TABLE 及び INDEX 付きビューに対してページ割り当てと構造的整合性

CHECKDB

DB 内にある全 TABLE 及び INDEX 付きビューに対してページ割り当てと構造的整合性。
検出されたエラーに対する修復を引数で指定可能(対象 DB がシングルユーザーモードの必要あり)
REPAIR_ALLOW_DATA_LOSS：破損したページを切り捨ててエラーを修復、データが失われる可能性有REPAIR_REBUILD：データ損失の可能性がない修復を行う
WITH EXTENDED_LOGICAL_CHECKS を指定すると互換性レベルが 100 (SQL Server 2008) 以上の場合、
INDEX 付きビュー、XML インデックス、空間インデックスの論理的な一貫性チェックが出来る

CHECKCATALOG

データベースのシステムカタログに格納されているメタデータの一貫性

UPDATEUSAGE

TABLE またはインデックスパーティションについて、行、使用済みページ、予約済みページ、
リーフページ、データベースのカウント情報に不一致がある場合、それらのレポートと修正行う

システムデータベースの管理
master データベースに障害が発生した場合は、シングルユーザーモードで DB 起動してリカバリを行う
net start [mssql$]インスタンス名 /m
名前付きインスタンスの場合はインスタンス名の前に mssql$を付与する。
シングルでも起動できない場合は、setup.exe の引数に/ACTION=REBUILDDATABASE を指定し DB の再構築を行う
model と msdb は DB 破損していてもインスタンス起動が可能なので通常のリカバリを行う
tempdb はインスタンスを再起動することで model データベースを雛形に再作成されるデータベーススナップショット
CREATE DATABASE ｽﾅｯﾌﾟｼｮｯﾄ名 ON( NAME = 元 DB の論理ファイル名,FILENAME = '物理ファイル名' ) [ ,( NAME= ～)...n ]AS SNAPSHOT OF 元の DB 名
スナップショット作成時点の DB 内容を読み取り専用で保持する。
スナップショットのテーブルを参照してもソースのテーブルに対してロックはかからない。
元 DB が更新されると更新前のデータがスナップショットに書き込まれ旧データが維持される。
リモートサーバーにスナップショットを作成することはできない。
データベースのディレクトリ情報が書き込まれるだけなので容量の節約になるが、ソースとなる DB のデータファイルが破損するとスナップショットも使用できなくなる。

スナップショットからのデータベース復元
RESTORE DATABASE データベース名 FROM DATABASE_SNAPSHOT='スナップショット名'

■トラブルシューティング
エラー情報の検出
SQL Server のデータベースエンジンサービスログ
重大度レベル 19～25 エラー、DB 復旧状態、バックアップ/復元操作、DBCC CHECKDB ステートメント、サーバーレベルの各種メッセージなどが記録される。SMS の SQL Server ログか、～\インスタンス名\MSSQL\LOG\ERRORLOG ファイルで確認できる

SQL Server エージェントサービスログ
サービスレベルのエラーと警告を含むメッセージが記録される
SQL Server ログと同じフォルダで、SQLAGENT.OUT ファイルに記録されている

データベースの保守
データベースの状態は、sys.databases カタログビューの state_desc 列か、DATABASEPROPERTYEX 関数で参照できる
ステータス

説明

ONLINE

正常な状態。プライマリファイルグループのファイルがオンラインであれば、セカンダリファイルグループ
がオフラインでも DB にアクセスできる

OFFLINE

意図的に DB をオフラインにし使用できない状態

RESTORING復元中。RESTORE ステートメントにて、NORECOVERY オプションを指定するとなる

RECOVERING

復旧中。インスタンス起動時と、RESOTRE ステートメントにて RECOVERY オプションを指定するとなる。
正常に復旧したら ONLINE。復旧に失敗したら SUSPECT になる

RECOVERY

復旧中にリソース関連のエラーが検出された。

PENDING

ファイルが見つからない、リソース制限に引っ掛かった等で DB が開けない状態

SUSPECT

プライマリファイルグループのファイルに問題があり復旧に失敗した

EMERGENCY

読み取り専用のシングルユーザーモード。SUSPECT 状態の時、ALTER DATABASE にて、この状態に遷移できる

データベースの状態を変更する
ALTER DATABASE DB 名 SET { ONLINE | OFFLINE | EMERGENCY } [ WITH ｵﾌﾟｼｮﾝ ]OFFILNE 移行は、トランザクションのコミットが完了するまで待たされる。
WITH オプション

説明

ROLLBACK IMMEDIATE

未確定のトランザクションを直ちにロールバックさせ OFFLINE に移行

NO_WAIT

ロールバックも待たずに即座に OFFLINE に移行

データベースのデータベース の移動（
移動（ MOVE～
MOVE～ TO）
TO）①

データベースをバックアップする
BACKUP DATABASE DB 名 TO バックアップデバイス②

別名でデータベースを復元する
RESTORE DATABASE DB 名 FROM バックアップデバイス WITH
MOVE '論理ファイル名' TO '物理ファイル名' [ ,MOVE ～データファイルの数分 MOVE を記述 ]③

データベース名を変更
ALTER DATABASE 変更前 DB 名 MODIFY NAME = 変更後 DB 名

データベースのデータベース の移動（
移動（ MODIFY FILE オプション）
オプション）①

データベースを OFFLINE にする
ALTER DATABASE DB 名 SET OFFLINE②

データファイルとトランザクションファイルの配置場所を変更する
ALTER DATABASE DB 名 MODIFY FILE ( NAME = '論理ファイル名' FILENAME = '物理ファイル名' )③

物理ファイルを②で指定した物理ファイル名に移動（またはコピー）させデータベースをオンラインにする

データベースのデータベース の移動（
移動（ デタッチ/
デタッチ/アタッチ）
アタッチ）①

稼働中のインスタンスからデータファイルを切り離す（デタッチ）OFFLINE にする必要なし。
sp_detach_db 'DB 名'②

デタッチされたファイルをインスタンスに接続する（ファイルの数分繰り返す）
sp_attach_db 'DB 名','物理ファイル名'SQL Server によるトランザクション管理
BEGIN TRANSACTION [トランザクション名]
BIGIN～COMMIT or ROLLBACK 間の書かれた TSQL をトランザクションとして処理する。ロックを獲得する
COMMIT TRANSACTION [トランザクション名] | ROLLBACK TRANSACTION [トランザクション名]
ロック種類

説明

S：共有ロック

SELECT 等によるデータ参照する際に、別プロセスからの変更を禁止する。
データ行の読み取り完了時にロックを解除する

U：更新ロック

where 句による UPDATE や DELETE は対象の行を検索する際に別プロセスからの変更を禁止する。
更新する行が見つかると排他ロックに変更する

X：排他ロック

INSERT、UPDATE、DELETE によりデータ更新中の状態。トランザクション完了まで保持。
更新が掛かってない行に対しては、更新・読み取り共に可能

既定ではロックはタイムアウトしない為、SET LOCK_TIMEOUT タイムアウトまでの時間

で設定する

ロック状態
ロック 状態の状態の 確認（確認（代表的な
代表的 な 3 種類）
種類）①

ロック状態は、インスタンスを右クリック⇒「利用状況モニタ」から「タスクの状態」の「SUSPENDED」②

DB 名を右クリック、「レポート」⇒「標準レポート」⇒「ブロックしているすべてのトランザクション」③

dm_tran_locks 動的管理ビュー、sp_lock（下位互換の為に残っているので今後削除される）

デットロックのデットロック の分析
デットロック時、ロールバックにコストの掛からないトランザクションを実行しているセッションが強制終了される。
トレースフラグ「1204」と「1222」を設定することでデットロック発生時に ERRORLOG に情報を出力する。
1204：ノード毎にデットロックの情報がレポートされる
1222：最初にプロセス毎、次にリソース毎にデットロックの情報がレポートされる

DBCC { TRACEON | TRACEOFF } ( 1204, 1222, -1 )でトレース取得の有効無効を設定する。

最後に-1 を指定すればサーバー上全てで有効になる。指定しない場合はセッション単位。
SQL Server 構成マネージャの詳細設定にある「起動時オプション」に「;-T1204;-1222」と記述することでも有効になる

トランザクションの分離
論理的な 1 つの作業単位がトランザクションとして有効である為には ACID と呼ばれる 4 つのプロパティが必要
ACID

説明

Atomicity（原子性）

分離できない 1 つの作業単位（更新処理のロックメカニズムで一貫性を保持）

Consistency（一貫性） トランザクション完了時のデータの一貫性（更新処理のロックメカニズムで一貫性を保持）
Isolation（分離性）

別トランザクションからの独立性
ロックを減らせば同時実行性が向上し分離性が低下、データの一貫性が失われるリスクが高くなる
ロックを増やせば同時実行性が低下し分離性が向上、データの整合性が維持される

Durability（持続性）

システム障害が発生しても完了したトランザクションは保持されなければならない

リソースに対するロックが不十分な状態で処理を同時実行された場合、タイミングなどにより 4 種類の問題が発生する
問題

説明

更新データの損失先に実行される更新処理が、後から実行される更新処理によって上書きされてしまう

ダーティリード

トランザクション更新中のデータがコミットされる前に、他の処理が参照できてしまうこと

反復不可能読み取り

トランザクション中で同じデータを参照した時に、値が一貫せず変化してしまう
A が参照しているデータに対して B が更新してしまう場合に発生

ファントム値

トランザクション中で参照してたデータ行が削除される、または存在してなかった行が追加される
A が参照しているデータに対して B がデータの削除や挿入を行った場合に発生

トランザクション分離
トランザクション分離レベル
分離 レベル
分離レベル

更新ﾃﾞｰﾀ損失

ﾀﾞｰﾃｨﾘｰﾄﾞ

反復不可能読み取り

ﾌｧﾝﾄﾑ値

同時実行制御READ UNCOMMITTED

×

○

○

○

物理排他（悲観的ﾛｯｸ）

READ COMMITTED（既定）

×

×

○

○

物理排他（悲観的ﾛｯｸ）

REPEATABLE READ

×

×

×

○

物理排他（悲観的ﾛｯｸ）

SERIALIZABLE

×

×

×

×

物理排他（悲観的ﾛｯｸ）

READ COMMITTED SNAPSHOT

×

×

○

○

論理排他（楽観的ﾛｯｸ）

SNAPSHOT

×

×

×

×

論理排他（楽観的ﾛｯｸ）

ロックの種類説明

悲観的ロック

行を変更する為の参照を行った時点でロックを掛ける。READ UNCOMMITTED 以外、
行変更をするステートメントを含むトランザクションが完了するまでロックを維持。
頻繁に行更新が行われる場合に適用する。

楽観的ロック

行を変更する為の参照を行った時点でロックは掛けない。
行変更ステートメントが発生した時点でロックを掛ける
殆ど行更新が行われない場合に適用する。

分離レベルの変更
SET TRANSACTION ISOLATION LEVEL { 悲観的ロックの分離レベル | SNAPSHOT }READ_COMMITTED_SNAPSHOT は DB レベルで、SNAPSHOT は、DB レベルとトランザクションレベルの両方で設定が必要。
ALTER DATABASE DB 名 SET { READ_COMMITTED_SNAPSHOT | SNAPSHOT } { ON | OFF }現在の分離レベルを確認するには、DBCC USEROPTIONS■パフォーマンスの監視とリソースの管理
システムモニタのパフォーマンス監視
SQL Server をインストールすると SQL Server の構成要素を監視する為のｵﾌﾞｼﾞｪｸﾄ(SQL Server:ｵﾌﾞｼﾞｪｸﾄ名)が追加される
構成要素

オブジェクト名

説明

メモリ

Buffer Manager

メモリの使用量、物理 I/O、バッファキャッシュヒット率

Memory Manager

クエリワークメモリ領域、現在割り当てられているロック構造の総数

Plan Cache

プランを格納するプロシージャキャッシュに冠する情報

DB

Databases

一括ｺﾋﾟｰ操作、ﾄﾗﾝｻﾞｸｼｮﾝﾛｸﾞの空きﾃﾞｨｽｸ容量､DB 内のｱｸﾃｨﾌﾞなﾄﾗﾝｻﾞｸｼｮﾝ数

実行状況

Loocks

ロックタイムアウトやデットロックなど

SQL Statistics

ｸｴﾘのｺﾝﾊﾟｲﾙと再ｺﾝﾊﾟｲﾙの回数、ｲﾝｽﾀﾝｽが受信するﾊﾞｯﾁの数に関する情報

General Statistics

インスタンスに接続しているユーザー数や、サーバ全体の一般的な使用状況

Exec Statistics

分散クエリ、DTC、拡張ストアドプロシージャなどの実行回数と時間

Access Methods

DB 内のデータへのアクセス方法を監視するためのカウンタ「コントロールパネル」⇒「管理ツール」⇒「パフォーマンス」でツールを起動。
左のシステムモニタをクリック、右側の「＋」ボタンで見たいカウンタを追加する。

SQL Server Profiler によるイベントの収集と再生
・

インスタンスで実行されているあらゆるイベントを収集できる・

リモートコンピュータから SQL Server インスタンスに接続することで、トレースデータ収集に伴う実稼働環境へのオー
バーヘットを減らすことが出来る

・

トレースをファイルに保存する場合、ロールオーバーを有効にすれば最大ﾌｧｲﾙｻｲｽﾞに達した後、別名に切り替わる

・

テーブルに保存の場合、1000 行単位で最大行数を指定。最大行数に達したら保存されなくなる

イベントクラス及
イベントクラス 及び、 イベントクラスがイベントクラスが表示する
表示 する情報する情報（
情報 （カラム）カラム ）を設定する
設定 する
カテゴリ

イベントクラス

説明

StoredSP:Starting

ストアドプロシージャの実行開始

Procedures

SP:StmtStarting

ストアドプロシージャ内の各種ステートメントを表示

TSQL

SQL:BatchStarting

Transact-SQL のバッチ処理が開始

SQL:StmtStarting

Transact-SQL のバッチ処理内の各種ステートメントを表示

Showplan All

コスト見積もり列リストなどを含む実行プランを表示

Showplan Statistics

各操作で渡された実際の行数を含む実行プランを表示

Deprecation Announcement

将来のバージョンで削除される予定の機能を使うと発生

Deprecation Final Support

次回のメジャーリリースで削除される予定の機能を使うと発生

Performance

Deprecation

列フィルタを設定する
TextData など、like または not like で条件一致したトレースのみ取得できる。
不要なトレースを取得しない。

SQL トレースを使用した情報の収集
SQL Server Profiler より DB への負荷が少ないという利点がある。
sys.traces でトレース ID の確認

シスアド/関数

実行するタスク

fn_trace_geteventinfo(id)

トレースに含まれるイベントに関する情報を返す

fn_trace_getinfo(id)指定されたトレースまたは全ての既存のトレース(0 を指定)に関する情報を返す

fn_trace_getfilterinfo(id)

トレースに適用されたフィルタに関する情報を返す

sp_trace_create

トレース定義を作成する（新しいトレースは停止状態）

sp_trace_generateevent

ユーザー定義イベント作成

sp_trace_setevent

イベントクラスまたはデータ列をトレースに追加/削除する

sp_trace_setfilter

新しいフィルタまたは変更されたフィルタをトレースに適用する

sp_trace_setstatus

トレースを開始/停止/削除する。
sp_trace_setstatus トレース ID,{ 0(停止) | 1(開始) | 2(削除) }トレースを作成するスクリプトは、SQL Server Profiler のメニュー「ファイル」⇒「エクスポート」にて作成できる。
作成されたファイル内の sp_trace_create の 3 番目の引数に出力させたいトレースファイル名を指定する。
sqlcmd -i SQL ファイルにてトレースの作成～開始まで実行。詳細は作成されたファイルの中身参照

チューニングアドバイザ
トレースデータをデータベースエンジンチューニングアドバイザに読み込んで分析することで、パフォーマンス向上に効果があるインデックス定義を生成できる。
チューニングアドバイザを行うにはイベントクラスのデータ列に Event Class と Test Data が含まれている必要がある

ワークロードとはチューニングするデータベースに対して実行する Transact-SQL ステートメントのセット。
SQL ファイルや、トレースファイル、XML ファイルなどを指定できる。
ワークロード分析用のデータベースに、ワークロードで指定した SQL が実行出来る DB を選択する。
チューニングするデータベースとテーブルを選択し、分析の開始を実行する。
分析結果の推奨設定を行う SQL は、メニュー「アクション」⇒「推奨設定の保存」で保存できる。

動的管理オブジェクト
パフォーマンスに関する情報を提供する組み込みのビュー及び関数。動的管理ビューと、動的管理関数がある。
システムのカタログビューと区別する為に dm_ というプレフィックスが付与されている
動的管理オブジェクト

説明

sys.dm_exec_connections

サーバーに接続している現在の全てのセッションに関する情報

sys.dm_exec_requests

セッションごとに実行しているコマンドの処理要求に関する情報

sys.dm_exec_cached_plans

プロシージャキャッシュに格納されたクエリプラン情報
sys.dm_exec_text_query_plan(plan_handle,0,-1)で 実行プラン取得
上記で取得した query_plan を.sqlplan で保存し開くとプラン表示できる

sys.dm_exec_query_stats

プロシージャキャッシュに格納されたクエリプランのパフォーマンス統計

sys.dm_exec_sql_text

sys.dm_exec_sql_text(sql_handle) で 元の SQL を取得
sql_handle は、sys.dm_exec_cached_plans、sys.dm_exec_query_statssys.dm_exec_requests

から取得できる

sys.dm_os_memory_pools

システム内に割り当てられている各メモリオブジェクトの情報

sys.dm_os_wait_stats

実行中のすべてのスレッドで検出された待機に関する統計

sys.dm_tran_locks

現在システム内で許可または要求されているロックに関する情報sys.dm_db_index_usage_stats

インデックスの使用情報

sys.dm_db_index_physical_stats

インデックスの断片化の情報

sys.dm_db_partition_stats

現在のデータベースの各パーティションに含まれるページ数と行数の情報

データコレクションによるパフォーマンスデータの収集
SQL Server 2008 でサポートされたパフォーマンスデータ収集のための機能。
ウィザードを使用し管理データウェアハウスを作成後、データコレクションを有効化することで、「ディスク使用量」「サーバーの利用状況」「クエリ統計」といった定義済みのコレクションセットが使用できる。

システムデータコレクションセットによるシステムデータコレクションセット による主
による 主な収集内容
コレクションセット

概要

ディスク使用量

データファイル、トランザクションログファイルサイズ

サーバー利用状況

リソース利用状況を４つの一般的な領域（CPU、ディスク I/O、メモリ、ネットワーク）で追跡

クエリ統計

クエリの統計情報（CPU 使用率が高いクエリや、実行時間の長いクエリ）を表示① 管理データウェアハウス
管理 データウェアハウスの
データウェアハウスの作成[インスタンス]⇒[管理]⇒[データコレクション]を右クリックし[管理データウェアハウスの構成]を選択する。
「ストレージの構成」画面にて「新規作成」を行い新しいデータウェアハウスを作成。任意の名前をつけて OK ボタン。
その他は既定値のまま次へ進み、完了させることで、管理データウェアハウスが作成される。

② データコレクションのデータコレクション の有効化
データ収集したいインスタンスの[管理]⇒[データコレクション]を右クリックし[管理データウェアハウスの構成]を選択。
「構成タスクの選択」画面で「データコレクションをセットアップする」オプションを選択し次へ。
「ストレージ構成」画面にて①で作成した管理データウェアハウスのインスタンス及びデータウェアハウス名を選択。

[管理]⇒[データコレクション]⇒[システムデータコレクションセット]からコレクションの確認ができる。

複数のインスタンスでデータコレクションをセットアップし、共通する管理データウェアハウスを指定することで、パフォーマンスデータを単一の管理データウェアハウスにアップロードできる。
※一度作成したデータコレクションは削除できない

システムデータコレクションセットがシステムデータコレクションセット が使用する
使用 する SQL Server エージェントジョブ
エージ ェントジョブ
コレクションセット

SQL Server エージェントジョブ

ディスク使用量

collection_set_1_noncached_collect_and_upload

サーバー利用状況

collection_set_2_collection

collection_set_2_upload

クエリ統計

collection_set_3_collection

collection_set_3_upload

管理データウェアハウス
管理 データウェアハウスへ
データウェアハウスへ収集した収集 したデータ
したデータの
データ のアップロード
アップロードモード

説明

キャッシュモード

データ収集とアップロードが別々のスケジュールで実行される。
収集されたデータはファイルキャッシュに保存される

非キャッシュモード

データ収集とアップロードが同じスケジュールで実行される。
収集されたデータはキャッシュを介さず管理データウェアハウスへ格納される

デフォルトのデフォルトの 設定内容
コレクションセット

収集時間

アップロード

保持日数

ディスク使用量

キャッシュなし

6 時間

730 日

サーバー利用状況

60 秒

15 分

14 日

クエリ統計

60 秒

15 分

14 日

[管理]⇒[データコレクション]⇒[システムデータコレクションセット]の各コレクションセットのプロパティから、「全般」でアップロードモード、保持日数。「アップロード」でアップロード時間を行うスケジュールを選択する。
コレクションを選択し右クリックから、手動で今すぐ収集してアップロードを行うことも可能。

アップロード先 TBL収集されるデータ内容

snapshots.disk_usage

データファイルの使用状況

snapshots.log_usage

ログファイルの使用状況

snapshots.performance_counter_values

パフォーマンスカウンタ

snapshots.query_stats

クエリ統計

監視対象インスタンスの[管理]⇒[データコレクション]ら右クリック[レポート]⇒[管理データウェアハウス]から、管理データウェアハウスに保存されたパフォーマンスデータに基づくレポートが参照できる。

リソースガバナによるリソース管理
分類関数でグループ分けを行う。

グループは特定のリソースプールと関連付けられる。

リソースプールの設定によりグループが使用できるリソースを制限することができる
オブジェクト説明

リソースプール

特定のワークロードグループと関連付けられる。
リソースプールの CPU とメモリの使用率（最小、最大）を指定する。
既定の Internal と Default は最小 0%、最大 100%固定で変更できない。
ユーザー定義のプールを複数作成した場合、最小の合計は 100%未満にする必要がある。
最大を 80%にしたとしても全てのプールの最小値合計が 50%の場合、実際の最大は 50%になる。
sys.resource_governor_resource_pools

ワークロード

1 つのセッションからの要求に割り当て可能なリソースを指定できる。

グループ

ワークメモリや CPU 時間の最大値、リソースのタイムアウト、最大接続数等を指定する。
既定で Internal（システムプロセス）と Default（ユーザープロセス）がある。
sys.resource_governor_workload_groups

分類関数

ログイン名やアプリケーション名などの接続情報を使用してワークロードグループへ分類する関数。
接続情報取得には、HOST_NAME()、APP_NAME()、SUSER_NAME()、SUSER_SNAME()、IS_SRVROLEMEMBER()
IS_MEMBER()の各システム関数を使用し取得できる。master データベースに作成する必要がある
sys.resource_governor_configuration①

リソースプールの作成{ CREATE | ALTER } RESOURCE POOL リソースプール名 WITH(オプション)
オプション

説明

MIN_CPU_PERCENT = 数値(%)

最小 CPU 使用率（全てのプールの合計は 100%未満にする必要がある）

MAX_CPU_PERCENT = 数値(%)

最大 CPU 使用率

MIN_MEMORY_PERCENT = 数値(%)

最小メモリ使用率（全てのプールの合計は 100%未満にする必要がある）

MAX_MEMORY_PERCENT = 数値(%)

最大メモリ使用率②

ワークロードグループ作成、リソースプールの関連付け{ CREATE | ALTER } WORKLOAD GROUP グループ名[ WITH オプション ]USING リソースプール名

オプション

説明

IMPORTANCE = { LOW | MEDIUM | HIGH }ワークロード グループでの要求の相対的な重要度

REQUEST_MAX_MEMORY_GRANT_PERCEN = 数値(%)

１つの要求にプールから割り当てられる最大メモリ量。既定は 25%
関連付けられたリソースプールの MAX_MEMORY_PERCENT が基準になる

REQUEST_MAX_CPU_TIME_SEC = 秒

要求が使用できる最大 CPU 時間を指定。0 で無制限

REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 秒

メモリ許可 (作業バッファー メモリ) が使用可能になるのをクエリが待機できる最大時間

MAX_DOP = 数値

並列要求の最大 DOP (並列処理の次数) を指定

GROUP_MAX_REQUESTS

ワークロード グループで実行を許可する同時要求の最大数③②または③で変更した構成を { 適用 | 無効 } する

ALTER RESOURCE GOVERNOR { RECONFIGURE | DISABLE }④

分類関数の作成

CREATE FUNCTION 関数名([ @変数名 データ型 ]) RETURNS データ型 WITH SCHEMABINDING
BEGIN
DECLARE @変数名 データ型

オブジェクト名を格納する sysname を使用する

IF (SUSER_NAME() = 'sa')
SET @grp_name = 'GroupAdmin'
ELSE IF (APP_NAME() LIKE '%REPORT%')

任意の SQL 文を自由に記述できる。
今回は、ユーザー名や、アプリケーション名により異なるグループ名を戻すコードを記述

SET @grp_name = 'GroupReports'
RETURN @変数名;
END⑤

分類関数をリソースガバナに登録

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = スキーマ.分類関数名 )■高可用性の構成
SQL Server 2008 の各エディションでサポートされる高可用性機能
高可用性機能Web

WorkgroupStandard

EnterPrise

フェールオーバークラスタリング

××

2 ノードまで

○

ログ配布

○

○

○

○

データベースミラーリング

×

×

○

○

サブスクライバ

サブスクライバ○○

レプリケーション

ログの配布
プライマリでトランザクションログをバックアップし、セカンダリへ転送する。
セカンダリでトランザクションログを復元することで、プライマリ⇔セカンダリ間で同期の取れた DB ができる。
サーバーの種類説明

プライマリ

トランザクションログをバックアップする本番系サーバ。（1 台）
ﾌﾟﾗｲﾏﾘがﾀﾞｳﾝした場合､ﾛｸﾞ配布も行われない為､ｾｶﾝﾀﾞﾘをﾌﾟﾗｲﾏﾘに昇格する必要がある

セカンダリ

バックアップされたトランザクションログから復元する待機系サーバ（複数台）

監視サーバー

ジョブの実行履歴を監視することも可能（オプション）

プライマリ設定
プライマリ設定の設定 の事前準備・データベースプロパティの「オプション」タブにて復旧モデルが「完全」か「一括ログ」にする。
・OS 上でトランザクションログをバックアップするフォルダを作成し共有にする
共有フォルダはプライマリサーバのバックアップジョブ実行ユーザーが読み書きできる権限が必要

プライマリデータベースのプライマリデータベース の設定
データベースプロパティの「トランザクションログの配布」タブにて「ログ配布構成のプライマリデータベースとして有効にする」をチェック。
「バックアップの設定」ボタンを押下し、バックアップフォルダの指定を行う。
ネットワークパス

トランザクションログをバックアップするネットワークのフォルダを指定する。
プライマリサーバと同じフォルダの場合もネットワークのパスで記述する必要がある。
(例) \\192.168.11.2\Share

ローカルパス※IP アドレスはホスト名でも良い

プライマリサーバと同じディスクの場合、フルパスを記入する。
省略した場合は、ネットワークパスが自動的に設定される。
両方記述した場合は、ローカルパスが優先される。(例) C:\Share

バックアップジョブ

ジョブ（トランザクションログのバックアップ）のスケジュールを設定

圧縮「バックアップを圧縮する」を選択すると、プライマリ⇒セカンダリへログ転送する際のトランザクションログファイルを最小化できる

セカンダリデータベースのセカンダリデータベース の設定
セカンダリデータベースセクションにある「追加」ボタンを押下。
「接続」ボタンにてセカンダリデータベースに接続後、復元先のデータベースを指定する。
セカンダリデータ

同期の取れた DB があれば「いいえ」

ベースの初期化

同期の取れた DB を最初から作成する場合は「はい」を選択する。

ファイルのコピー

セカンダリサーバーが保存するバックアップファイルの保存場所を指定。
プライマリサーバのジョブ実行ユーザーが、セカンダリサーバの共有ディスクへ読み書き可
能な権限があること。
スケジュールボタンにて、復元間隔のジョブスケジュールを設定する。

トランザクション

バックアップ復元時のデータベース状態を設定する。

ログの復元

復旧モードなし

NORECOVERY オプションを使用して復元される。

スタンバイモード

STANDBY オプションを指定して復元される。

監視サーバインスタンス
監視 サーバインスタンスのサーバインスタンスの設定（
設定 （任意）任意）「監視サーバインスタンスを使用する」にチェックを入れる。
「設定」ボタンを押下、「接続」から任意のインスタンスを選択する。

データベースミラーリング
ログ配布と同様、トランザクションログのコピー/復元をベースにした高可用性機能。
ログ配布が障害発生時に自動でフェイルーバーしないのに対しミラーリングは自動フェールオーバーする。
サーバーの種類説明

プリンシパルサーバー

ソース DB を持つ本番サーバー

ミラーサーバー

ソース DB の変更をトランザクションログとして受信する（構成できるのは 1 つのｲﾝｽﾀﾝｽのみ）

監視サーバー

サーバー状態を監視し、障害発生時に自動フェールオーバーさせることが出来る

TCP エンドポイント
データベースミラーリングセッションに参加するインスタンス間の通信に使用する。
プリンシパル⇒ミラー間で直接トランザクションログを転送する。つまりファイルを使用しない。

ミラーリングさせる
ミラーリング させる為させる為 の準備
・プリンシパルとなるデータベースの復旧モデルを「完全」に設定する・プリンシパルのソース DB をバックアップし、「NORECOVERY」オプションを指定してミラーサーバーに復元する。

ミラーサーバーのミラーサーバーの構築①

ソース DB を右クリック⇒[タスク]⇒[バックアップ]のバックアップの種類にて「完全」と「トランザクション」の両方を取得する。

②

ミラーサーバーのデータベースを右クリック⇒「データベースの復元」
ページ

説明

全般

・復元用のソースにて「デバイスから」にチェックし①にてバックアップしたファイルを選択・復元するバックアップセットは「完全」と「トランザクションログ」の両方にチェックする
・復元先データベースに①でバックアップした DB を選択する

オプション

・「既存のデータベースを上書き」をチェックする
・復元先のファイルを任意の場所に変更する（既定はﾊﾞｯｸｱｯﾌﾟと同じ場所に復元してしまう）

データベースミラーリングセッションのデータベースミラーリングセッションの設定①

プリンシパルサーバーのソース DB を右クリック⇒[タスク]⇒[ミラー]⇒[セキュリティの構成]からウィザード形式で
設定する

②

ページ

説明

ﾐﾗｰﾘﾝｸﾞ監視ｻｰﾊﾞを含める「はい」にチェック

構成するｻｰﾊﾞｰ「ミラーリング監視サーバーインスタンス」にチェック

ﾌﾟﾘﾝｼﾊﾟﾙｻｰﾊﾞｰｲﾝｽﾀﾝｽ

既定のまま。

ﾐﾗｰｻｰﾊﾞｲﾝｽﾀﾝｽ

ミラー化したいインスタンスを選択し「接続」

ﾐﾗｰﾘﾝｸﾞ監視ｻｰﾊﾞｰｲﾝｽﾀﾝｽ

監視サーバーにしたいインスタンスを選択し「接続」

ｻｰﾋﾞｽｱｶｳﾝﾄ既定のまま動作モードの設定を行う
動作モード

説明

非同期

自動フェールオーバーしない。負荷は低い。

同期

自動フェールオーバーする（監視サーバーを使用してることが条件）
負荷は高い（2 フェーズコミットで同期する為）パッチ適用時パッチ 適用時など
適用時 など、など、ダウンタイムを
ダウンタイム を最小限にする
最小限 にする手順にする手順①

データベースミラーリングセッションを停止②

ミラーサーバに修正プログラムを適用③

データベースミラーリングセッションを再開④

手動でフェイルオーバーする⑤

データベースミラーリングセッションを停止⑥

現在のミラーサーバ（元のプリンシパルサーバー）にパッチを適用する⑦

データベースミラーリングセッションを再開する

フェールオーバークラスタ化
複数のサーバーノードが共有ディスクをマウントし、クライアントは仮想 IP に対して接続する。
ノードダウンなどによりフェールオーバーが発生すると MSCS が新しいノードに仮想ネットワークアドレスと、仮想ネットワーク名を登録する。これによりクライアントからのトラフィックは利用可能なノードにルーティングされるようになる。
1 つのクラスタで最大 16 ノードまでサポートしている。

クラスタ構成時
クラスタ構成時にまれるクラスタリソースの構成時 に組み 込まれるクラスタリソース
クラスタリソースの 種類
リソース

説明

物理ディスク

クラスタに参加する物理的に共有されるストレージ

クォーラム

クラスタの管理データ、構成データ、復旧ログなどが保存される物理ディスク。
クラスタ構成が使用しているネットワーク間で通信が行えなくなった場合、クォーラムリソースを所有しているノードがクラスタリソースの所有権を引き継ぐ（調停）IP アドレス

クラスタ構成で共有される仮想的な IP アドレス。クライアントの接続先 IP アドレスになる

ネットワーク名

クラスタ構成で共有される仮想的なホスト名。IP アドレスリソースに関連付けられるフェールオーバークラスタ
フェ ールオーバークラスタでールオーバークラスタで SQL Server を 稼働される
稼働される場合される 場合の場合の 注意点①

サービスアカウントの設定

MSCS と SQL Server で使用するサービスアカウントは異なるユーザーアカウントを使用する。

そうしないとクラスタのコマンドを利用して MSCS に設定したアカウントのパスワード変更ができない。
MSCC のサービスアカウントにはインスタンスに接続する権限が必要。

②

暗号化の設定SQL Server はサーバー証明書を使用して SSL 通信プロトコルを暗号化できる。
SQL Server 構成マネージャ⇒ネットワーク構成⇒プロパティ⇒強制的に暗号化に「はい」
暗号化を設定する場合、フェールオーバークラスタに参加する全てのノードに SQL Server の仮想ネットワーク名を完全修正
DNS 名で記述したサーバー証明書をインストールする必要がある。
node1.example.com と node2.example.com で構成されたクラスタに cluster という仮想ネットワーク名を構成した場合、
cluster.example.com という名前が記述された証明書を取得し両方のノードにインストールする必要がある。

③

自動フェールバックの禁止

クラスタ構成で自動フェールバックが有効になっていると障害発生ノードが復旧した際に元の優先ノードにリソースが移動することでダウンタイムが発生してしまう。

レプリケーション
複数のサーバー間でテーブルのデータをコピーし複製をネットワーク上に配置することで、通信回線やサーバーに掛かる負荷を分散させる仕組み。

構成要素

説明

パブリッシャ

オリジナルデータとして 1 つ以上のパブリケーション（出版物）を保持しており、パブリケーション（出版社）とアーティクル（記事）の論理的な関連付けやデータ変更に対する監視を行う

ディストリビュータ

パブリッシャから送られてきたパブリケーションをスケジュールに基づき各サブスクライバに配布。

（流通業者）

パブリッシャと同一のサーバに置く（ローカルディストリビュータ）のが一般的だが、図のように別サーバ（リモートディストリビュータ）に置くことも出来る

サブスクライバ

レプリケートされたデータを受信するデータベースインスタンス。

（購読者）

複数のパブリッシャ及びパブリケーションからデータを受信できる。
通常は読み取り専用だがパブリッシャにデータ変更を戻したり、他のサブスクライバにパブリッシュ
するこも出来るアーティクルパブリケーションに含まれる 1 つのデータベースオブジェクト（テーブル/ビュー/ストアドプロシー（記事）ジャ）を表す。テーブルをアーティクルとして使用する場合主キーが必要。

パブリケーション

単一の DB から取得した 1 つ以上のアーティクルの集合。

（出版物）

複数のアーティクルをパブリケーションにグループ化することでパブリケーション単位でレプリケートが出来るサブスクリプション

サブスクライバに配信されるパブリケーションのコピー。

（定期購読）

パブリケーションの受信のタイミングや方法を定義する。

レプリケーショントポロジの種類
種類

説明

スナップ

パブリケーションで定義されたデータの特定の時間の情報を、そのまま一括コピーする。

ショット

データに対する更新は監査されない為、負荷が少ない。
配信データが大量で更新が少ない場合に最適。

トランザ

トランザクションを利用して DB 間の同期を行う。

クション

初期同期はスナップショットレプリケーションのメカニズムにて行われる。
以降、パブリッシャでデータが変更される度にトランザクションログとしてサブスクライバに配信される為、データの変更が頻繁に行われる場合に有効。
サブスクライバは既定では読み取り専用になるがサブスクライバでの更新を許可するオプションも提供さ
れている。

マージ

パブリッシャとサブスクライバの両方でデータ及びスキーマの変更ができる。
初期同期はスナップショットレプリケーションのメカニズムにて行われる。
以降、パブリッシャまたはサブスクライバで行われたデータ、スキーマの変更はトリガを使って追跡される。
サブスクリプションが有効期限切れになると、そのサブスクリプションのメタデータが削除される

パブリケーションのサブスクライブ方法
方法

説明

プッシュ

サブスクライバからの要求なしにパブリッシャの変更をサブスクライバと同期する方式。
同期のタイミングは「要求時」、「連続的」、「スケジュール」の何れか。
・常時接続しているネットワークで連続的、定期的なスケジュールで頻繁にデータ同期を取りたい
・遅延の少ないデータ同期を必要とする・ディストリビュータとして動作する PC の性能に余裕がある
・スナップショットレプリケーション、トランザクションレプリケーションで常に同期する場合

プル

サブスクライバからの要求で同期を行う方式。同期のタイミングをユーザーが指定できる。
・要求時またはスケジュールに基づいて同期を行いたい
・パブリケーションに対してサブスクライバが多い場合
・リソース消費が大きく、ディストリビュータで全てのエージェントを実行できない場合
・サブスクライバが常時接続されてない場合（モバイルデバイスなど）

システムテーブル関連メモ
カタログビュー

取得できる情報sys.all_objects

オブジェクト一覧。name 列を like で検索して目的のオブジェクトを探す

sys.columns

カラム一覧。

sys.backup_devices

論理デバイス情報（sp_helpdevice）

sys.database_files

DB のデータファイル情報（sp_helpfile）

sys.partition_functions

パーティション関数

sys.partition_schemes

パーティション構成

sys.partition_range_values

パーティション関数の範囲境界値

sys.destination_data_spaces

パーティション構成のファイルグループ

sys.data_spaces

ファイルグループや、パーティション構成などの情報

sys.server_principals

ﾛｸﾞｲﾝﾕｰｻﾞｰ、ｻｰﾊﾞｰﾛｰﾙ（sp_helplogins / sp_helpsrvrolemember）

sys.database_principals

DB ユーザー及び DB ロール（sp_helpuser / sp_helprolemember）

sys.server_permissions

権限

sys.server_file_audits

監査ファイルsys.server_audit_specifications

サーバー監査仕様

sys.database_audit_specifications

データベース監査仕様

sys.resource_governor_resource_pools

リソースガバナのリソースプール

sys.resource_governor_workload_groups

リソースガバナのワークロード

sys.resource_governor_configuration

リソースガバナの分類関数

object_id = OBJECT_ID('TABLE 名')

動的管理オブジェクト

説明

sys.dm_exec_connections

サーバーに接続している現在の全てのセッションに関する情報

sys.dm_exec_requests

セッションごとに実行しているコマンドの処理要求に関する情報

sys.dm_exec_cached_plans

プロシージャキャッシュに格納されたクエリプラン情報
sys.dm_exec_sql_text(sql_handle) で 元の SQL を取得
sys.dm_exec_text_query_plan(plan_handle,0,-1)で 実行プラン取得
上記で取得した query_plan を.sqlplan で保存し開くとプラン表示できる

sys.dm_exec_query_stats

プロシージャキャッシュに格納されたクエリプランのパフォーマンス統計

sys.dm_os_memory_pools

システム内に割り当てられている各メモリオブジェクトの情報

sys.dm_os_wait_stats

実行中のすべてのスレッドで検出された待機に関する統計

sys.dm_tran_locks

現在システム内で許可または要求されているロックに関する情報

sys.dm_db_index_usage_stats

インデックス使用頻度。user_xxx 列で確認

sys.dm_db_index_physical_stats

インデックスの断片化情報

sys.dm_db_partition_stats

パーティション毎にページ数と行数の情報を返す（データ及びインデックス）※ _stats となっているのは SQL Server を起動してからの統計情報。再起動すると空になる。

ストアドプロシージャ

説明

sp_help [ ｵﾌﾞｼﾞｪｸﾄ ]オブジェクトに対する全ての情報

sp_helpindex TABLE 名

インデックスに関する情報

sp_helpconstraint TABLE 名

制約情報

sp_helpfile

DB で使用しているファイル情報

sp_helpdevice

論理デバイス情報

sp_helplogins [ ﾛｸﾞｲﾝ名 ]ログイン情報sp_helpuser [ DB ﾕｰｻﾞｰ名 ]DB ユーザー情報

sp_helpsrvrolemember

サーバーロールメンバー情報

sp_helprolemember

DB ロールメンバー情報

sp_configure ['ｵﾌﾟｼｮﾝ','値']

サーバーの設定値取得・設定

sp_addsrvrolemember 'ﾛｸﾞｲﾝ名', 'ﾛｰﾙ名'

ログインにロールを付与

sp_addumpdevice 'disk | tape', '論理ﾃﾞﾊﾞｲｽ名','物理ﾃﾞﾊﾞｲｽ名'

論理デバイス追加

sp_attach_db 'DB 名','物理ファイル名'

DB をアタッチする

sp_detach_db 'DB 名'

DB をデタッチする

xp_cmdshell 'OS コマンド'OS コマンドの実行

組み込み関数

取得する値

OBJECT_NAME(object_id)

オブジェクト名

FILE_NAME(file_id)

論理ファイル名

COL_NAME(object_id,column_id)

カラム名取得

DB_NAME()

データーベース名

ORIGINAL_LOGIN()

ログインしたユーザー名

CURRENT_USER

現在接続中のスキーマー

グローバル変数

戻す値@@ERROR直前に実行したクエリのエラー状態を保持@@LOCK_TIMEOUT

デットロックのタイムアウト時間（秒）@@ROWCOUNT

直前に実行したクエリの件数@@SERVICENAME

インスタンス名SET コマンド説明

SET STATISTICS IO ON

SQL 実行時にページの I/O を確認

SET STATISTICS TIME ON

SQL 実行時にページの経過時間等が確認できる

SET SHOWPLAN_ALL ON

実行プランの表示。ALL の替わりに XML/TEXT もある

DBCC コマンド

説明

DBCC DROPCLEANBUFFERS

ディスク I/O を行わせる為に、バッファキャッシュを削除する

目次が、ハイパーリンク対応した Word 版が欲しい方はメールで連絡下さい。