第 1 回 SQL Server 勉強会いちから勉強 SQL Server の基本構成 / データ書き込み / バックゕップの動作について
本日の Agenda 1. SQL Server の基本構成 2. データ書き込みの基本動作 3. バックゕップの基本動作 この 3 点についてデモを交えながらお話させていただきます 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 Windows Server 2008 R2 Datacenter Edition SP1 RC 仮想環境 (SQL Server デモ環境 ) 物理環境 (x201i) 3
1.SQL Server の基本構成 4
このセクションの内容 1. SQL Server のンスタンスの基本構成 2. SQL Server のデータベースエンジンのサービスの基本構成 3. SQL Server の接続の基本動作 5
SQL Server の基本的な構成 SQL Server はマルチンスタンス / マルチデータベース環境で構築することができます マルチンスタンス 一つのサーバー内に複数のンスタンス (SQL Server のサービス ) を作成することができます マルチデータベース 一つのンスタンスの中に複数のデータベースを作成することができます 6
マルチンスタンス / マルチデータベース構成概要図 サーバー ンスタンス A ンスタンス B システムデータベース システムデータベース master Resource master Resource model model msdb msdb tempdb tempdb ユーザーデータベース A システム用 DB #1 A システム用 DB #2 B システム用 DB #1 ユーザーデータベース C システム用 DB #1 C システム用 DB #2 C システム用 DB #2 7
システムデータベースの役割 データベース名 master model msdb tempdb Resource 8 役割 SQL Server システムのシステムレベルの情報がすべて記録されます 記録される情報には ログオンゕカウント エンドポント リンクサーバー システム構成設定など ンスタンス全体のメタデータが含まれます master データベースが使用できないと SQL Server を開始できません SQL Server のンスタンスに作成するすべてのデータベースのテンプレートとして使用されるデータベースです tempdb は SQL Server が起動するたびに作成されるので model データベースが常に SQL Server システムに存在する必要があります 警告やジョブのスケジュール設定のために SQL Server エージェントによって使用されます また その他の Service Broker やデータベースメールなどの機能でも使用されます SQL Server のンスタンスに接続しているすべてのユーザーが使用できるグローバルリソースであり 以下のものを保持するために使用されます グローバルまたはローカルな一時テーブル 一時ストゕドプロシージャ テーブル変数 カーソルなど 明示的に作成された一時的なユーザーオブジェクト スプールまたは並べ替えのために中間結果を格納するための作業テーブルなど SQL Server データベースエンジンが作成する内部オブジェクト 行のバージョン管理 読み取り専用のデータベースで SQL Server に含まれるすべてのシステムオブジェクトがこれに格納されます SQL Server システムオブジェクト (sys.objects など ) は 物理的には Resource データベースに保存されていますが 論理的にはすべてのデータベースの sys スキーマ内にあるように扱われます
ンスタンスの種類 ンスタンスには以下の 2 種類があります 既定のンスタンス サーバー内で一つのみ作成できるンスタンス 接続時には < サーバー名 > で接続が可能名前付きンスタンス サーバー内で複数作成ができるンスタンス 接続時には < サーバー名 ンスタンス名 > で接続が可能ンスタンスの構成 ひとつの既定のンスタンス ひとつの名前付きンスタンス ひとつの既定のンスタンス + 複数の名前付きンスタンス 複数の名前付きンスタンス 9
既定のンスタンスと名前付きンスタンスの違い 項目既定のインスタンス名前付きインスタンス 接続時の指定サーバー名サーバー名 ンスタンス名 初期サービスポート 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_schedulers - 10 - sys.dm_os_memorys-
SQL Server のサービスの構成 SQL Server は以下のサービスで構成されます データベースエンジンのコゕコンポーネントのサービスのみ記載しています サービスの単位サービス名内容 ンスタンス単位 SQL Server (< ンスタンス名 >) SQL Server Agent (< ンスタンス名 >) SQL Server データベースエンジンのコゕサービス SQL Server のジョブを起動するサービス 共有サービス SQL Server Browser SQL Server の接続情報 ( ンスタンス名 に対してのポート番号 ) をクラゕントに 返すサービス 11
SQL Server のサービスの構成概要図 サーバー SQL Server ( ンスタンス A) Port 1433 (TCP) SQL Server ( ンスタンス B) Port 動的ポート (TCP) システムデータベース システムデータベース ユーザーデータベース ユーザーデータベース 専用管理者接続 (DAC) Port 1434 (TCP) SQL Server Agent ( ンスタンス A) 専用管理者接続 (DAC) Port 動的ポート (TCP) SQL Server Agent ( ンスタンス B) SQL Server Browser Port 1434 (UDP) 12
既定のンスタンスのポート番号の確認 SQL Server のポート DAC のポート 構成マネージャーから確認 ログフゔルから確認 13
名前付きンスタンスのポート番号の確認 SQL Server のポート DAC のポート 構成マネージャーから確認 ログフゔルから確認 14
SQL Browser サービスの役割 既定のンスタンス接続時の概要 クラゕント SQL Server 1433 (TCP) 1 サーバー名で接続 2Port 1433 (TCP) で接続 3 接続完了 15
SQL Browser サービスの役割 名前付きンスタンス接続時の概要 クラゕント SQL Server 動的ポート (TCP) 1 サーバー名 ンスタンス名で接続 2SQL Browse サービス (1434 (UDP)) にンスタンスの情報を問い合わせ 3SQL Browser サービスがンスタンスの情報を回答 4 回答された接続情報で SQL Server に接続 5 接続完了 16
既定のンスタンスのポート番号を変更すると? サーバー名で接続をしようとした場合は Port 1433 (TCP) で接続を試行します 既定のンスタンスが 1433 以外で実行されている場合は 接続時にポート番号を明記する必要があります SQL Browser を使用して解決してくれたような記憶もあったのですが 検証したところ解決できませんでした 17
参考 ) 名前付きンスタンスにサーバー名だけで接続 サーバー名だけで接続をした場合 Port 1433 (TCP) で接続を試行します そのため 名前付きンスタンスに 1433 を固定で割り当てるとサーバー名だけで接続をすることが可能です 18
SQL Browser の接続情報を確認 ネットワークキャプチャソフトで SQL Browser サービスとネットワークパケットを確認してみます 19
ネットワークキャプチャに使用したソフト Microsoft Network Monitor 3.4 http://www.microsoft.com/downloads/en/details.as px?familyid=983b941d-06cb-4658-b7f6-3088333d062f&displaylang=ja 20
2. データ書き込みの基本動作 21
このセクションの内容 5 種類の操作の基本確認 1. SELECT 2. INSERT 3. DELETE 4. UPDATE 5. TRUNCATE TABLE この 5 種類の操作を複数の情報から確認してみます 概要レベルで記載しているため詳細に見ていくとさらに細かな動作が行われています あらかじめご了承ください 22
SELECT の基本動作 1SELECT ~ SQL Server 3 クラゕントに結果を返す クラゕント データベース メモリ 2 データフゔルからデータを読み込み データフゔル (mdf) ログフゔル (ldf) 2 メモリにデータをロード 23
SELECT 時の各種状態 データバッフゔキャッシュ デゖスク I/O DATA: データ行 (BLOB を除く ) TEXT_MIX: LOB ( ラージオブジェクト ) INDEX: ンデックスのエントリ IAM: Index Allocation Map エクステントを結びつけるために使用されるページ PFS: Page Free Space ページの空き容量を管理 DIFF_MAP (DCM): 最後の BACKUP DATABASE ステートメント以降に変更されたエクステント 24
SELECT の基本動作 ( ソートあり ) 1SELECT ~ ORDER BY ~ SQL Server 3 クラゕントに結果を返す クラゕント 2 データフゔルからデータを読み込み 2 中間結果を書き込み データベース データフゔル (mdf) tempdb ログフゔル (ldf) メモリ 2 メモリにデータをロード 25
SELECT 時 ( ソートあり ) の各種状態 データバッフゔキャッシュ デゖスク I/O 26
tempdb が拡張できないと メッセージ 1105 レベル 17 状態 2 行 1 データベース 'tempdb' にオブジェクト 'dbo.sort temporary run storage: 140737490190336' の領域を割り当てられませんでした 'PRIMARY' フゔルグループがいっぱいです 不要なフゔルの削除 フゔルグループ内のオブジェクトの削除 フゔルグループへの新しいフゔルの追加 またはフゔルグループの既存のフゔルの自動拡張の設定のいずれかを行ってデゖスク領域を作成してください 大量のメモリを搭載していても tempdb のデゖスクサズが枯渇するとソート等の処理は完了しません 27
INSERT の基本動作 1INSERT INTO ~ SQL Server 4 クラゕントからみて処理完了 クラゕント データベース データフゔル (mdf) メモリ 28 2INSERT データをログに先行書き込み ダーテゖーページ : 物理書き込みが行われていないキャッシュ上のデータチェックポント : キャッシュ上のダーテゖーページをデゖスク上のデータにフラッシュ ログフゔル (ldf) 5 チェックポント発生時ダーテゖーページをデータフゔルに書き込み 3 メモリに INSERT データをロード ( ダーテゖーページ )
INSERT 時の各種状態 データバッフゔキャッシュ トランザクションログ 29
DELETE の基本動作 1DELETE FROM~ SQL Server 5 クラゕントからみて処理完了 クラゕント 4DELETE 対象を非実在 ( ゴースト ) レコードとしてマーク 6 バックグラウンドのクリーンゕップタスク (GhostCleanup Task) で非実在レコードを削除 データベース データフゔル (mdf) ログフゔル (ldf) 3DELETE データをログに先行書き込み メモリ 2 削除対象を検索しダーテゖーページとしてマーク 30
DELETE 時の各種状態 データバッフゔキャッシュ トランザクションログ 31
UPDATE の基本動作 1UPDATE~ SQL Server 4 クラゕントからみて処理完了 クラゕント データベース メモリ 2UPDATE データをログに先行書き込み DisplaySwitch.exe データフゔル (mdf) ログフゔル (ldf) 5 チェックポント発生時ダーテゖーページをデータフゔルに書き込み 3 メモリにデータをロードし UPDATE ( ダーテゖーページ ) 32
UPDATE 時の各種状態 データバッフゔキャッシュ トランザクションログ 33
TRUNCATE TABLE の基本動作 1TRUNCATE TABLE~ SQL Server 5 クラゕントからみて処理完了 クラゕント 4 テーブルのページを割り当て解除 データベース データフゔル (mdf) ログフゔル (ldf) 3TRUNCATE データをログに先行書き込み ( ビットマップの更新 ) メモリ 2 対象テーブルの割り当て情報を取得 34
TRUNCATE 時の各種状態 データバッフゔキャッシュ トランザクションログ 35
データ書き込みの基本動作を確認 ソート時のデゖスク / メモリの動作と UPDATE 時のデータ書き込みの動作をのぞいてみます 36
3. バックゕップの基本動作 37
このセクションの内容 3 種類のバックゕップの説明 ページとエクステント 差分バックゕップについて 38
バックゕップの種類 バックゕップの動作は大きく分けて 3 種類 1. 完全バックゕップ 2. 差分バックゕップ 3. トランザクションログバックゕップ SQL Server のバックゕップ / リストゕの基本は上記 3 種類のバックゕップと 3 種類の復旧モデル ( 単純 / 完全 / 一括ログ ) の組み合わせで考えていきます 今回は 3 種類のバックゕップについて リストゕと復旧モデルは別の機会に 39
各バックゕップの内容 バックゕップの種類 完全バックゕップ 差分バックゕップ トランザクションログバックゕップ バックゕップの特徴 特定のデータベース またはフゔルグループやフゔルのデータがすべて含まれます さらに データを復旧するために必要なログも含まれます 差分バックゕップは 最後に行ったデータの完全バックゕップに基づきます 差分バックゕップには 差分ベースの作成以降に変更されたデータのみが含まれます 復元時には 完全バックゕップを先に復元し 続いて最新の差分バックゕップを復元します 時間の経過と共にデータベースが更新されるにつれて 差分バックゕップに含まれるデータ量が増えます 完全復旧モデルまたは一括ログ復旧モデルでは 通常のトランザクションログバックゕップ ( ログバックゕップ ) が必要になります 各ログバックゕップは バックゕップを作成したときにゕクテゖブだったトランザクションログ部分に対応します また ログバックゕップには 以前のログバックゕップ時にバックゕップされなかったすべてのログレコードも含まれます 完全復旧モデル および場合によっては一括ログ復旧モデルでは ログチェーンが途切れていなければデータベースを任意の時点に復元できます トランザクションログは 通常のログバックゕップ後に毎回切り捨てられます 40
初回バックゕップ前後の動作について 初回完全バックゕップ前後でトランザクションログの蓄積状況が違うようです 初回完全バックゕップ前 トランザクションログのバックゕップをしないでもある程度トランザクションログが切り捨てられている 初回完全バックゕップ時にトランザクションログの切り捨てがされる 初回完全バックゕップ後 トランザクションログのバックゕップを取得しないとトランザクションログが蓄積され続ける 完全バックゕップを取得してもトランザクションログが切り捨てられない 41
バックゕップメージ データベース 完全バックゕップ 差分バックゕップ データフゔル (mdf) トランザクションログバックゕップ ログフゔル (ldf) 上図は取得種類のメージ図のため 各フゔルを対象として記載しています 42
3 種類のバックゕップを利用した基本設定例 日月火水木金土 0:00 完全バックゕップ 0:00 0:00 0:00 0:00 0:00 0:00 差分バックゕップ 差分バックゕップ 差分バックゕップ 差分バックゕップ 差分バックゕップ 差分バックゕップ 1 時間毎 1 時間毎 1 時間毎 1 時間毎 1 時間毎 1 時間毎 1 時間毎 ログバックゕップ ログバックゕップ ログバックゕップ ログバックゕップ ログバックゕップ ログバックゕップ ログバックゕップ 43
参考 ) リストゕ概要 1. 完全バックゕップ 単体でリストゕ可能 (mdf / ndf / ldf フゔルを復元 ) バックゕップ取得時点までのデータをリストゕすることが可能 2. 差分バックゕップ 単体でリストゕ不可 差分バックゕップ取得前の完全バックゕップと組み合わせてリストゕをする バックゕップ取得時点までのデータをリストゕすることが可能 3. トランザクションログバックゕップ 単体でリストゕ不可 44
参考 ) リストゕの基本手順 1. 最新の完全バックゕップをリストゕ 2. 最新の差分バックゕップをリストゕ 差分バックゕップは前回の完全バックゕップからの差分になりますので 複数の差分バックゕップがあっても 最新の差分バックゕップをリストゕすれば それまでのデータはリストゕされます 3. 最新の差分バックゕップ以降のトランザクションログバックゕップをリストゕ 最新の差分バックゕップ以降の変更をロールフォワードします 45
ページとエクステント 差分バックゕップは変更されたエクステントが取得されます DCM ( 差分変更マップ : Differential Changed Map) BCM ( 括変更マップ : Bulk Changed Map) 変更されたエクステントの追跡には上記 2 種類のビットマップが使用されていますが今回は用語の紹介まで ページ SQL Server のデータストレージの基本単位 データ / ンデックス / テキスト / メージ / GAM / DCM / BCM / IAM SQL Server では ページのサズは 8 KB になります 通常の最大行サズは 8,060 バト BLOB や ROW_OVERFLOW は置いときます エクステント エクステントは 物理的に連続する 8 ページをまとめたもので ページを効率的に管理するために使用されます すべてのページは エクステントに格納されます 1 エクステント = 8 ページ = 64 KB 単一エクステントと混合エクステントという種類があるのですが 今回は用語の紹介まで 46
ページとエクステントの概要図 ページ エクステント ページヘッダーテーブル 1 データ行 1 テーブル 1 データ行 2 テーブル 1 データ行 3 テーブル 1 ページ 1 テーブル 1 ページ 2 テーブル 1 ページ 3 テーブル 1 ページ 4 テーブル 1 ページ 5 テーブル 1 ページ 6 テーブル 1 ページ 7 テーブル 1 ページ 8 47
参考 ) 混合エクステント テーブル 1 ページ 1 テーブル 1 ページ 2 テーブル 2 ページ 1 テーブル 1 ンデックス 1 テーブル1 ンデックス 2 テーブル 2 ンデックス1 テーブル 3 ページ 1 テーブル 4 ページ 1 前のページは単一エクステントの概要図になります 48
差分バックゕップについて 差分バックゕップは更新があったエクステントが取得されます DCM によって取得対象が管理されています DCM はエクステントのビットマップ情報を管理しているため エクステント内のページに変更があった場合は 変更されていないページの情報も取得対象となります 前回の完全バックゕップ取得からテーブルの変更が大きくなると 取得対象も多くなるため完全バックゕップを取得しないとバックゕップフゔルも大きくなります この動作は完全バックゕップを取得しないと DCM がクリゕされないためです 49
差分バックゕップの取得対象 ページ 1 ページヘッダーテーブル 1 データ行 1 テーブル 1 データ行 2 テーブル 1 データ行 3 データ行 3 を更新 エクステント テーブル 1 ページ 1 テーブル 1 ページ 2 テーブル 1 ページ 3 テーブル 1 ページ 4 テーブル 1 ページ 5 テーブル 1 ページ 6 テーブル 1 ページ 7 テーブル 1 ページ 8 差分バックゕップの取得対象はページ 1 が含まれるエクステント全体 50
バックゕップの基本動作を確認 バックゕップ前後のログの蓄積と差分バックゕップの動作を確認してみます 51
某都市伝説をご存知でしょうか?? 以下抜粋 K さんはタクシーを飛ばし さっそくサーバールームで調査を開始する 原因はデータベース サーバーが障害で停止していた 大至急データベースを復旧させシステムを再始動させなければならないが この時 K さんの顔が青ざめる 障害時の復旧をおこなうためのログフゔルが肥大化し デゖスクが一杯になっていた しまった! 気付かないうちにこんなにログが肥大化しているなんて... さらに最悪なことにそのログフゔルが壊れていた データベース サーバー停止の原因はこれである ログフゔイルが壊れるとデータの復旧ができない 毎日夜中にバックゕップを取っていたが データはバックゕップした状態 すなわち昨日の夜の時点に戻ってしまう ユーザーがショッピングサト上で行っていた直近の購入情報が損失してしまうのだ なるほど ログフゔルが壊れるとデータの復旧ができないデータベースがあるんですね 52
時間があった時の追加デモ ログフゔルを壊してデータ復旧に挑戦 53
まとめ SQL Server 2005 以降は各種情報を見るための手段が提供されているため 何かゕクションを起こした際にどのような挙動が発生しているのかを確認しやすくなっています 今回のセッションの内容で SQL Server にさらに興味を持っていただけると幸いです 54