第1回 SQL Server 勉強会
いちから勉強。
SQL Serverの基本構成 / データ書き込み
/ バックゕップの動作について
本日の Agenda
1.
SQL Serverの基本構成
2.
データ書き込みの基本動作
3.
バックゕップの基本動作
この 3 点についてデモを交えながらお話させていただ
きます。
2010/11/6 DB友の会 勉強会資料 2本日のデモ環境
SQL Server デモ環境
Hyper-V 上のゲスト OS
CPU : 4Core
Memory : 2GB
Windows Server 2008 R2 Datacenter Edition
SQL Server 2008 R2 Datacenter Edition x64 + CU4
物理環境 (Hyper-V のホスト環境)
ThinkPad x201i
CPU : Intel Core i3 M330 (2.13 GHz 4Core)
Memory : 4GB
HD : 250 GB
1.SQL Server の基本構成
2010/11/6 DB友の会 勉強会資料 4
このセクションの内容
1.
SQL Server のンスタンスの基本構成
2.
SQL Server のデータベースエンジンの
サービスの基本構成
SQL Server の基本的な構成
SQL Server はマルチンスタンス / マルチデータ
ベース環境で構築することができます。
マルチンスタンス
一つのサーバー内に複数のンスタンス (SQL Server の
サービス) を作成することができます。
マルチデータベース
一つのンスタンスの中に複数のデータベースを作成す
ることができます。
2010/11/6 DB友の会 勉強会資料 6ンスタンス B
ンスタンス A
マルチンスタンス / マルチデータベース構成概要図
サーバー
ユーザー データベース ユーザー データベース master システム データベース model msdb tempdb Resource master システム データベース model msdb tempdb Resourceシステムデータベースの役割
2010/11/6 DB友の会 勉強会資料 8 データベース名 役割 master SQL Server システムのシステム レベルの情報がすべて記録されます。 記録される情報には、ログオン ゕカウント、エンドポント、リンク サーバー、システ ム構成設定など、ンスタンス全体のメタデータが含まれます。 master データベースが使用できないと、SQL Server を開始できません。 model SQL Server のンスタンスに作成するすべてのデータベースのテンプレートとして使用 されるデータベースです。tempdb は SQL Server が起動するたびに作成されるので、model データベースが常 に SQL Server システムに存在する必要があります。 msdb 警告やジョブのスケジュール設定のために SQL Server エージェントによって使用され ます。 また、その他の Service Broker やデータベース メールなどの機能でも使用されます。 tempdb SQL Server のンスタンスに接続しているすべてのユーザーが使用できるグローバル リソースであり、以下のものを保持するために使用されます。 • グローバルまたはローカルな一時テーブル、一時ストゕド プロシージャ、テーブル変数、カーソルな ど、明示的に作成された一時的なユーザー オブジェクト。 • スプールまたは並べ替えのために中間結果を格納するための作業テーブルなど、SQL Server データ ベース エンジンが作成する内部オブジェクト。 • 行のバージョン管理。 Resource • 読み取り専用のデータベースで、SQL Server に含まれるすべてのシステム オブジェクトがこれに格 納されます。
• SQL Server システム オブジェクト (sys.objects など) は、物理的には Resource データベースに保 存されていますが、論理的にはすべてのデータベースの sys スキーマ内にあるように扱われます。
ンスタンスの種類
ンスタンスには以下の 2 種類があります。
既定のンスタンス
サーバー内で一つのみ作成できるンスタンス
接続時には <サーバー名> で接続が可能
名前付きンスタンス
サーバー内で複数作成ができるンスタンス
接続時には <サーバー名¥ンスタンス名> で接続が可能
ンスタンスの構成
既定のンスタンスと名前付きンスタンスの違い
2010/11/6 DB友の会 勉強会資料 10 項目 既定のインスタンス 名前付きインスタンス 接続時の指定 サーバー名 サーバー名¥ンスタンス名 初期 サービス ポート 1433 (TCP) 動的ポート (TCP) 専用管理者接続 (DAC) ポート (*) 1434 (TCP) 動的ポート (TCP) ※専用管理者接続 (Dedicated admin connection : DAC)実行中のサーバーにゕクセスして、診断関数や Transact-SQL ステートメントを実行したり、サーバー上の問題の トラブルシューテゖングを行ったりすることができます。
このことは、サーバーがロックされていたり、異常状態で実行されたりしていて、SQL Server データベース エン ジン接続に応答していない場合も同じです
[VISIBLE ONLINE (DAC)] という状態のスケジューラーが専用管理者接続用のスケジューラーになります。 (sys.dm_os_schedulers/sys.dm_os_schedulers 動的管理ビューから確認可能)
- sys.dm_os_memorys- - sys.dm_os_schedulers -
SQL Server のサービスの構成
SQL Server は以下のサービスで構成されます。
データベースエンジンのコゕコンポーネントのサービス
のみ記載しています。
サービスの単位 サービス名 内容 ンスタンス単位 SQL Server (<ンスタンス名>) SQL Server データベースエンジンのコゕ サービスSQL Server Agent (<ンスタンス名>) SQL Server のジョブを起動するサービス 共有サービス SQL Server Browser SQL Server の接続情報 (ンスタンス名
に対してのポート番号) をクラゕントに 返すサービス
システム データベース ユーザー データベース
SQL Server のサービスの構成概要図
サーバー
SQL Server (ンスタンス A) Port 1433 (TCP) SQL Server Agent (ンスタンス A)
SQL Server Browser Port 1434 (UDP) システム データベース ユーザー データベース SQL Server (ンスタンス B) Port 動的ポート (TCP) SQL Server Agent (ンスタンス B)
2010/11/6 DB友の会 勉強会資料 12 専用管理者接続 (DAC) Port 1434 (TCP) 専用管理者接続 (DAC) Port 動的ポート (TCP)既定のンスタンスのポート番号の確認
SQL Server のポート
構成
マネージャー
から確認
DAC のポート
名前付きンスタンスのポート番号の確認
2010/11/6 DB友の会 勉強会資料 14 SQL Server のポート
構成
マネージャー
から確認
DAC のポート
ログフゔルから確認
SQL Browser サービスの役割
既定のンスタンス接続時の概要
SQL Server
1433 (TCP)
クラゕント
①サーバー名で接続 ②Port 1433 (TCP) で接続 ③接続完了SQL Browser サービスの役割
2010/11/6 DB友の会 勉強会資料 16
名前付きンスタンス接続時の概要
SQL Server
動的ポート (TCP)
クラゕント
①サーバー名¥ンスタンス名で接続 ②SQL Browse サービス (1434 (UDP)) にンスタンスの情報を問い合わせ ③SQL Browser サービスがンスタンスの情報を回答 ④回答された接続情報で SQL Server に接続 ⑤接続完了接続先の SQL Server のポート番号を明示的に
指定すれば SQL Browser サービスが
停止していてもンスタンスに接続することが可能です。
既定のンスタンスのポート番号を変更すると？
サーバー名で接続をしようとした場合は Port 1433 (TCP) で
接続を試行します。
既定のンスタンスが 1433 以外で実行されている場合は、
接続時にポート番号を明記する必要があります。
SQL Browser を使用して解決してくれたような記憶もあったの
ですが、検証したところ解決できませんでした。
参考) 名前付きンスタンスにサーバー名だけで接続
2010/11/6 DB友の会 勉強会資料 18
サーバー名だけで接続をした場合、Port 1433 (TCP) で
接続を試行します。
そのため、名前付きンスタンスに 1433 を固定で割り
当てるとサーバー名だけで接続をすることが可能です。
SQL Browser の接続情報を確認
ネットワークキャプチャソフトで SQL Browser
ネットワークキャプチャに使用したソフト
2010/11/6 DB友の会 勉強会資料 20
Microsoft Network Monitor 3.4
http://www.microsoft.com/downloads/en/details.as
px?FamilyID=983b941d-06cb-4658-b7f6-3088333d062f&displaylang=ja
このセクションの内容
2010/11/6 DB友の会 勉強会資料 22
5 種類の操作の基本確認
1.SELECT
2.INSERT
3.DELETE
4.UPDATE
5.TRUNCATE TABLE
この 5 種類の操作を複数の情報から確認してみます。
概要レベルで記載しているため詳細に見ていくとさらに細かな動
作が行われています。あらかじめご了承ください。
SELECT の基本動作
クラゕント
SQL Server
①SELECT ～
データベース
メモリ
データフゔル
(mdf)
ログフゔル
(ldf)
②データフゔルから
データを読み込み
②’メモリにデータを
③クラゕントに
結果を返す
SELECT 時の各種状態
2010/11/6 DB友の会 勉強会資料 24
データバッフゔキャッシュ
デゖスク I/O
DATA： データ行 (BLOB を除く) TEXT_MIX： LOB (ラージオブジェクト) INDEX： ンデックスのエントリ IAM：Index Allocation Map
エクステントを結びつけるために 使用されるページ
PFS：
Page Free Space
ページの空き容量を管理 DIFF_MAP (DCM)：
最後の BACKUP DATABASE ス テートメント以降に変更されたエ クステント
SELECT の基本動作 (ソートあり)
クラゕント
SQL Server
①SELECT ～
ORDER BY ～
データベース
メモリ
データフゔル
(mdf)
ログフゔル
(ldf)
②データフゔルから
データを読み込み
③クラゕントに
結果を返す
②’メモリにデータを
データバッフゔキャッシュ
デゖスク I/O
SELECT 時 (ソートあり) の各種状態
2010/11/6 DB友の会 勉強会資料 26
tempdb が拡張できないと
メッセージ1105、レベル17、状態2、行1
データベース'tempdb' にオブジェクト'dbo.SORT
temporary run storage: 140737490190336' の領域を割り
当てられませんでした。'PRIMARY' フゔルグループがいっ
ぱいです。不要なフゔルの削除、フゔルグループ内のオブ
ジェクトの削除、フゔルグループへの新しいフゔルの追加、
またはフゔルグループの既存のフゔルの自動拡張の設定の
いずれかを行ってデゖスク領域を作成してください。
大量のメモリを搭載していても、tempdb のデゖス
INSERT の基本動作
2010/11/6 DB友の会 勉強会資料 28クラゕント
SQL Server
①INSERT INTO ～
データベース
メモリ
データフゔル
(mdf)
ログフゔル
(ldf)
②INSERT データを
ログに先行書き込み
③メモリにINSERT
データをロード
(ダーテゖーページ)
⑤チェックポント発生時
ダーテゖーページを
データフゔルに書き込み
④クラゕントから
みて処理完了
ダーテゖーページ： 物理書き込みが行われていない キャッシュ上のデータ チェックポント： キャッシュ上のダーテゖーページを デゖスク上のデータにフラッシュINSERT 時の各種状態
データバッフゔキャッシュ
DELETE の基本動作
2010/11/6 DB友の会 勉強会資料 30クラゕント
SQL Server
①DELETE FROM～
データベース
メモリ
データフゔル
(mdf)
ログフゔル
(ldf)
③DELETE データを
ログに先行書き込み
②削除対象を検索し
ダーテゖーページ
としてマーク
④DELETE 対象を非実在
(ゴースト)レコードとして
マーク
⑤クラゕントから
みて処理完了
⑥バックグラウンドの
クリーンゕップタスク
(GhostCleanup Task)で
非実在レコードを削除
DELETE 時の各種状態
データバッフゔキャッシュ
UPDATE の基本動作
2010/11/6 DB友の会 勉強会資料 32クラゕント
SQL Server
①UPDATE～
データベース
メモリ
データフゔル
(mdf)
ログフゔル
(ldf)
②UPDATE データを
ログに先行書き込み
③メモリにデータを
ロードし UPDATE
(ダーテゖーページ)
⑤チェックポント発生時
ダーテゖーページを
データフゔルに書き込み
④クラゕントから
みて処理完了
DisplaySwitch.exe
データバッフゔキャッシュ
トランザクションログ
TRUNCATE TABLE の基本動作
2010/11/6 DB友の会 勉強会資料 34クラゕント
SQL Server
①TRUNCATE TABLE～
データベース
メモリ
データフゔル
(mdf)
ログフゔル
(ldf)
③TRUNCATE データを
ログに先行書き込み
(ビットマップの更新)
②対象テーブルの
割り当て情報を取得
④テーブルのページを
割り当て解除
⑤クラゕントから
みて処理完了
TRUNCATE 時の各種状態
データバッフゔキャッシュ
データ書き込みの基本動作を確認
2010/11/6 DB友の会 勉強会資料 36
ソート時のデゖスク / メモリの動作と UPDATE 時
このセクションの内容
2010/11/6 DB友の会 勉強会資料 38
3 種類のバックゕップの説明
ページとエクステント
差分バックゕップについて
バックゕップの種類
バックゕップの動作は大きく分けて 3 種類
1.
完全バックゕップ
2.
差分バックゕップ
3.
トランザクションログ バックゕップ
SQL Server のバックゕップ / リストゕの基本は上記 3 種
類のバックゕップと 3 種類の復旧モデル (単純 / 完全 / 一
各バックゕップの内容
2010/11/6 DB友の会 勉強会資料 40 バックゕップの種類 バックゕップの特徴 完全バックゕップ 特定のデータベース、またはフゔル グループやフゔルのデータがすべて含 まれます。 さらに、データを復旧するために必要なログも含まれます。 差分バックゕップ 差分バックゕップは、最後に行ったデータの完全バックゕップに基づきます。 差分バックゕップには、差分ベースの作成以降に変更されたデータのみが含ま れます。 復元時には、完全バックゕップを先に復元し、続いて最新の差分バックゕップ を復元します。 時間の経過と共にデータベースが更新されるにつれて、差分バックゕップに含 まれるデータ量が増えます。 トランザクションログ バックゕップ 完全復旧モデルまたは一括ログ復旧モデルでは、通常のトランザクション ログ バックゕップ (ログ バックゕップ) が必要になります。 各ログ バックゕップは、バックゕップを作成したときにゕクテゖブだったトラ ンザクション ログ部分に対応します。 また、ログ バックゕップには、以前のログ バックゕップ時にバックゕップされ なかったすべてのログ レコードも含まれます。 完全復旧モデル、および場合によっては一括ログ復旧モデルでは、ログ チェー ンが途切れていなければデータベースを任意の時点に復元できます。 トランザクション ログは、通常のログ バックゕップ後に毎回切り捨てられます。初回バックゕップ前後の動作について
初回完全バックゕップ前後でトランザクションログ
の蓄積状況が違うようです。
初回完全バックゕップ前
トランザクション ログのバックゕップをしないでもある程度ト
ランザクションログが切り捨てられている。
初回完全バックゕップ時にトランザクションログの切り捨てが
される。
初回完全バックゕップ後
トランザクションログのバックゕップを取得しないとトランザ
クションログが蓄積され続ける。
完全バックゕップ
バックゕップメージ
2010/11/6 DB友の会 勉強会資料 42差分バックゕップ
データベース
データフゔル
(mdf)
トランザクションログ バックゕップ
ログフゔル
(ldf)
※上図は取得種類のメージ図のため、各フゔルを対象として記載しています。
3 種類のバックゕップを利用した基本設定例
日
月
火
水
木
金
土
0:00 完全 バックゕップ 0:00 差分 バックゕップ 0:00 差分 バックゕップ 0:00 差分 バックゕップ 0:00 差分 バックゕップ 0:00 差分 バックゕップ 0:00 差分 バックゕップ参考) リストゕ概要
2010/11/6 DB友の会 勉強会資料 441.
完全バックゕップ
単体でリストゕ可能 (mdf / ndf / ldf フゔルを復元)
バックゕップ取得時点までのデータをリストゕすること
が可能
2.
差分バックゕップ
単体でリストゕ不可
差分バックゕップ取得前の完全バックゕップと組み合わ
せてリストゕをする
バックゕップ取得時点までのデータをリストゕすること
が可能
3.
トランザクションログ バックゕップ
単体でリストゕ不可
参考) リストゕの基本手順
1.
最新の完全バックゕップをリストゕ
2.
最新の差分バックゕップをリストゕ
※
差分バックゕップは前回の完全バックゕップからの差分
になりますので、複数の差分バックゕップがあっても、
最新の差分バックゕップをリストゕすれば、それまでの
データはリストゕされます。
3.
最新の差分バックゕップ以降のトランザクション
ログバックゕップをリストゕ
ページとエクステント
2010/11/6 DB友の会 勉強会資料 46
差分バックゕップは変更されたエクステントが取得されます。
DCM (差分変更マップ : Differential Changed Map) BCM (括変更マップ : Bulk Changed Map)
変更されたエクステントの追跡には上記 2 種類のビットマップが使用されていますが今回は用語の紹 介まで。
