本日の Agenda 1. SQL Server の基本構成 2. データ書き込みの基本動作 3. バックゕップの基本動作この 3 点についてデモを交えながらお話させていただきます 2

Size: px

Start display at page:

Download "本日の Agenda 1. SQL Server の基本構成 2. データ書き込みの基本動作 3. バックゕップの基本動作この 3 点についてデモを交えながらお話させていただきます 2"

きみかずらぶり
5 years ago
Views:

1 第 1 回 SQL Server 勉強会いちから勉強 SQL Server の基本構成 / データ書き込み / バックゕップの動作について

2 本日の 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

3 本日のデモ環境 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

4 1.SQL Server の基本構成 4

5 このセクションの内容 1. SQL Server のンスタンスの基本構成 2. SQL Server のデータベースエンジンのサービスの基本構成 3. SQL Server の接続の基本動作 5

6 SQL Server の基本的な構成 SQL Server はマルチンスタンス / マルチデータベース環境で構築することができますマルチンスタンス一つのサーバー内に複数のンスタンス (SQL Server のサービス ) を作成することができますマルチデータベース一つのンスタンスの中に複数のデータベースを作成することができます 6

7 マルチンスタンス / マルチデータベース構成概要図サーバーンスタンス 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

8 システムデータベースの役割データベース名 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 スキーマ内にあるように扱われます

9 ンスタンスの種類ンスタンスには以下の 2 種類があります既定のンスタンスサーバー内で一つのみ作成できるンスタンス接続時には < サーバー名 > で接続が可能名前付きンスタンスサーバー内で複数作成ができるンスタンス接続時には < サーバー名ンスタンス名 > で接続が可能ンスタンスの構成ひとつの既定のンスタンスひとつの名前付きンスタンスひとつの既定のンスタンス + 複数の名前付きンスタンス複数の名前付きンスタンス 9

既定のンスタンスと名前付きンスタンスの違い項目既定のインスタンス名前付きインスタンス接続時の指定サーバー名サーバー名ンスタンス名初期サービスポート 1433 (TCP) 動的ポート (TCP) 専用管理者接続 (DAC) ポート (*) 1434 (TCP) 動的ポート (TCP) 専用管理者接続 (Dedicated admin connection : DAC)

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_schedulers sys.dm_os_memorys-

11 SQL Server のサービスの構成 SQL Server は以下のサービスで構成されますデータベースエンジンのコゕコンポーネントのサービスのみ記載していますサービスの単位サービス名内容ンスタンス単位 SQL Server (< ンスタンス名 >) SQL Server Agent (< ンスタンス名 >) SQL Server データベースエンジンのコゕサービス SQL Server のジョブを起動するサービス共有サービス SQL Server Browser SQL Server の接続情報 ( ンスタンス名に対してのポート番号 ) をクラゕントに返すサービス 11

ユーザーデータベース専用管理者接続 (DAC) Port 1434 (TCP) SQL Server Agent ( ンスタンス A)

12 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

13 既定のンスタンスのポート番号の確認 SQL Server のポート DAC のポート構成マネージャーから確認ログフゔルから確認 13

14 名前付きンスタンスのポート番号の確認 SQL Server のポート DAC のポート構成マネージャーから確認ログフゔルから確認 14

15 SQL Browser サービスの役割既定のンスタンス接続時の概要クラゕント SQL Server 1433 (TCP) 1 サーバー名で接続 2Port 1433 (TCP) で接続 3 接続完了 15

16 SQL Browser サービスの役割名前付きンスタンス接続時の概要クラゕント SQL Server 動的ポート (TCP) 1 サーバー名ンスタンス名で接続 2SQL Browse サービス (1434 (UDP)) にンスタンスの情報を問い合わせ 3SQL Browser サービスがンスタンスの情報を回答 4 回答された接続情報で SQL Server に接続 5 接続完了 16

17 既定のンスタンスのポート番号を変更すると? サーバー名で接続をしようとした場合は Port 1433 (TCP) で接続を試行します既定のンスタンスが 1433 以外で実行されている場合は接続時にポート番号を明記する必要があります SQL Browser を使用して解決してくれたような記憶もあったのですが検証したところ解決できませんでした 17

18 参考 ) 名前付きンスタンスにサーバー名だけで接続サーバー名だけで接続をした場合 Port 1433 (TCP) で接続を試行しますそのため名前付きンスタンスに 1433 を固定で割り当てるとサーバー名だけで接続をすることが可能です 18

19 SQL Browser の接続情報を確認ネットワークキャプチャソフトで SQL Browser サービスとネットワークパケットを確認してみます 19

20 ネットワークキャプチャに使用したソフト Microsoft Network Monitor px?familyid=983b941d-06cb-4658-b7f d062f&displaylang=ja 20

21 2. データ書き込みの基本動作 21

22 このセクションの内容 5 種類の操作の基本確認 1. SELECT 2. INSERT 3. DELETE 4. UPDATE 5. TRUNCATE TABLE この 5 種類の操作を複数の情報から確認してみます概要レベルで記載しているため詳細に見ていくとさらに細かな動作が行われていますあらかじめご了承ください 22

23 SELECT の基本動作 1SELECT ~ SQL Server 3 クラゕントに結果を返すクラゕントデータベースメモリ 2 データフゔルからデータを読み込みデータフゔル (mdf) ログフゔル (ldf) 2 メモリにデータをロード 23

24 SELECT 時の各種状態データバッフゔキャッシュデゖスク I/O DATA: データ行 (BLOB を除く ) TEXT_MIX: LOB ( ラージオブジェクト ) INDEX: ンデックスのエントリ IAM: Index Allocation Map エクステントを結びつけるために使用されるページ PFS: Page Free Space ページの空き容量を管理 DIFF_MAP (DCM): 最後の BACKUP DATABASE ステートメント以降に変更されたエクステント 24

25 SELECT の基本動作 ( ソートあり ) 1SELECT ~ ORDER BY ~ SQL Server 3 クラゕントに結果を返すクラゕント 2 データフゔルからデータを読み込み 2 中間結果を書き込みデータベースデータフゔル (mdf) tempdb ログフゔル (ldf) メモリ 2 メモリにデータをロード 25

26 SELECT 時 ( ソートあり ) の各種状態データバッフゔキャッシュデゖスク I/O 26

27 tempdb が拡張できないとメッセージ 1105 レベル 17 状態 2 行 1 データベース 'tempdb' にオブジェクト 'dbo.sort temporary run storage: ' の領域を割り当てられませんでした 'PRIMARY' フゔルグループがいっぱいです不要なフゔルの削除フゔルグループ内のオブジェクトの削除フゔルグループへの新しいフゔルの追加またはフゔルグループの既存のフゔルの自動拡張の設定のいずれかを行ってデゖスク領域を作成してください大量のメモリを搭載していても tempdb のデゖスクサズが枯渇するとソート等の処理は完了しません 27

28 INSERT の基本動作 1INSERT INTO ~ SQL Server 4 クラゕントからみて処理完了クラゕントデータベースデータフゔル (mdf) メモリ 28 2INSERT データをログに先行書き込みダーテゖーページ : 物理書き込みが行われていないキャッシュ上のデータチェックポント : キャッシュ上のダーテゖーページをデゖスク上のデータにフラッシュログフゔル (ldf) 5 チェックポント発生時ダーテゖーページをデータフゔルに書き込み 3 メモリに INSERT データをロード ( ダーテゖーページ )

29 INSERT 時の各種状態データバッフゔキャッシュトランザクションログ 29

30 DELETE の基本動作 1DELETE FROM~ SQL Server 5 クラゕントからみて処理完了クラゕント 4DELETE 対象を非実在 ( ゴースト ) レコードとしてマーク 6 バックグラウンドのクリーンゕップタスク (GhostCleanup Task) で非実在レコードを削除データベースデータフゔル (mdf) ログフゔル (ldf) 3DELETE データをログに先行書き込みメモリ 2 削除対象を検索しダーテゖーページとしてマーク 30

31 DELETE 時の各種状態データバッフゔキャッシュトランザクションログ 31

32 UPDATE の基本動作 1UPDATE~ SQL Server 4 クラゕントからみて処理完了クラゕントデータベースメモリ 2UPDATE データをログに先行書き込み DisplaySwitch.exe データフゔル (mdf) ログフゔル (ldf) 5 チェックポント発生時ダーテゖーページをデータフゔルに書き込み 3 メモリにデータをロードし UPDATE ( ダーテゖーページ ) 32

33 UPDATE 時の各種状態データバッフゔキャッシュトランザクションログ 33

34 TRUNCATE TABLE の基本動作 1TRUNCATE TABLE~ SQL Server 5 クラゕントからみて処理完了クラゕント 4 テーブルのページを割り当て解除データベースデータフゔル (mdf) ログフゔル (ldf) 3TRUNCATE データをログに先行書き込み ( ビットマップの更新 ) メモリ 2 対象テーブルの割り当て情報を取得 34

35 TRUNCATE 時の各種状態データバッフゔキャッシュトランザクションログ 35

36 データ書き込みの基本動作を確認ソート時のデゖスク / メモリの動作と UPDATE 時のデータ書き込みの動作をのぞいてみます 36

37 3. バックゕップの基本動作 37

38 このセクションの内容 3 種類のバックゕップの説明ページとエクステント差分バックゕップについて 38

39 バックゕップの種類バックゕップの動作は大きく分けて 3 種類 1. 完全バックゕップ 2. 差分バックゕップ 3. トランザクションログバックゕップ SQL Server のバックゕップ / リストゕの基本は上記 3 種類のバックゕップと 3 種類の復旧モデル ( 単純 / 完全 / 一括ログ ) の組み合わせで考えていきます今回は 3 種類のバックゕップについてリストゕと復旧モデルは別の機会に 39

40 各バックゕップの内容バックゕップの種類完全バックゕップ差分バックゕップトランザクションログバックゕップバックゕップの特徴特定のデータベースまたはフゔルグループやフゔルのデータがすべて含まれますさらにデータを復旧するために必要なログも含まれます差分バックゕップは最後に行ったデータの完全バックゕップに基づきます差分バックゕップには差分ベースの作成以降に変更されたデータのみが含まれます復元時には完全バックゕップを先に復元し続いて最新の差分バックゕップを復元します時間の経過と共にデータベースが更新されるにつれて差分バックゕップに含まれるデータ量が増えます完全復旧モデルまたは一括ログ復旧モデルでは通常のトランザクションログバックゕップ ( ログバックゕップ ) が必要になります各ログバックゕップはバックゕップを作成したときにゕクテゖブだったトランザクションログ部分に対応しますまたログバックゕップには以前のログバックゕップ時にバックゕップされなかったすべてのログレコードも含まれます完全復旧モデルおよび場合によっては一括ログ復旧モデルではログチェーンが途切れていなければデータベースを任意の時点に復元できますトランザクションログは通常のログバックゕップ後に毎回切り捨てられます 40

41 初回バックゕップ前後の動作について初回完全バックゕップ前後でトランザクションログの蓄積状況が違うようです初回完全バックゕップ前トランザクションログのバックゕップをしないでもある程度トランザクションログが切り捨てられている初回完全バックゕップ時にトランザクションログの切り捨てがされる初回完全バックゕップ後トランザクションログのバックゕップを取得しないとトランザクションログが蓄積され続ける完全バックゕップを取得してもトランザクションログが切り捨てられない 41

42 バックゕップメージデータベース完全バックゕップ差分バックゕップデータフゔル (mdf) トランザクションログバックゕップログフゔル (ldf) 上図は取得種類のメージ図のため各フゔルを対象として記載しています 42

43 3 種類のバックゕップを利用した基本設定例日月火水木金土 0:00 完全バックゕップ 0:00 0:00 0:00 0:00 0:00 0:00 差分バックゕップ差分バックゕップ差分バックゕップ差分バックゕップ差分バックゕップ差分バックゕップ 1 時間毎 1 時間毎 1 時間毎 1 時間毎 1 時間毎 1 時間毎 1 時間毎ログバックゕップログバックゕップログバックゕップログバックゕップログバックゕップログバックゕップログバックゕップ 43

44 参考 ) リストゕ概要 1. 完全バックゕップ単体でリストゕ可能 (mdf / ndf / ldf フゔルを復元 ) バックゕップ取得時点までのデータをリストゕすることが可能 2. 差分バックゕップ単体でリストゕ不可差分バックゕップ取得前の完全バックゕップと組み合わせてリストゕをするバックゕップ取得時点までのデータをリストゕすることが可能 3. トランザクションログバックゕップ単体でリストゕ不可 44

45 参考 ) リストゕの基本手順 1. 最新の完全バックゕップをリストゕ 2. 最新の差分バックゕップをリストゕ差分バックゕップは前回の完全バックゕップからの差分になりますので複数の差分バックゕップがあっても最新の差分バックゕップをリストゕすればそれまでのデータはリストゕされます 3. 最新の差分バックゕップ以降のトランザクションログバックゕップをリストゕ最新の差分バックゕップ以降の変更をロールフォワードします 45

46 ページとエクステント差分バックゕップは変更されたエクステントが取得されます 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

47 ページとエクステントの概要図ページエクステントページヘッダーテーブル 1 データ行 1 テーブル 1 データ行 2 テーブル 1 データ行 3 テーブル 1 ページ 1 テーブル 1 ページ 2 テーブル 1 ページ 3 テーブル 1 ページ 4 テーブル 1 ページ 5 テーブル 1 ページ 6 テーブル 1 ページ 7 テーブル 1 ページ 8 47

48 参考 ) 混合エクステントテーブル 1 ページ 1 テーブル 1 ページ 2 テーブル 2 ページ 1 テーブル 1 ンデックス 1 テーブル1 ンデックス 2 テーブル 2 ンデックス1 テーブル 3 ページ 1 テーブル 4 ページ 1 前のページは単一エクステントの概要図になります 48

49 差分バックゕップについて差分バックゕップは更新があったエクステントが取得されます DCM によって取得対象が管理されています DCM はエクステントのビットマップ情報を管理しているためエクステント内のページに変更があった場合は変更されていないページの情報も取得対象となります前回の完全バックゕップ取得からテーブルの変更が大きくなると取得対象も多くなるため完全バックゕップを取得しないとバックゕップフゔルも大きくなりますこの動作は完全バックゕップを取得しないと DCM がクリゕされないためです 49

50 差分バックゕップの取得対象ページ 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 バックゕップの基本動作を確認バックゕップ前後のログの蓄積と差分バックゕップの動作を確認してみます 51

52 某都市伝説をご存知でしょうか?? 以下抜粋 K さんはタクシーを飛ばしさっそくサーバールームで調査を開始する原因はデータベースサーバーが障害で停止していた大至急データベースを復旧させシステムを再始動させなければならないがこの時 K さんの顔が青ざめる障害時の復旧をおこなうためのログフゔルが肥大化しデゖスクが一杯になっていたしまった! 気付かないうちにこんなにログが肥大化しているなんて... さらに最悪なことにそのログフゔルが壊れていたデータベースサーバー停止の原因はこれであるログフゔイルが壊れるとデータの復旧ができない毎日夜中にバックゕップを取っていたがデータはバックゕップした状態すなわち昨日の夜の時点に戻ってしまうユーザーがショッピングサト上で行っていた直近の購入情報が損失してしまうのだなるほどログフゔルが壊れるとデータの復旧ができないデータベースがあるんですね 52

53 時間があった時の追加デモログフゔルを壊してデータ復旧に挑戦 53

54 まとめ SQL Server 2005 以降は各種情報を見るための手段が提供されているため何かゕクションを起こした際にどのような挙動が発生しているのかを確認しやすくなっています今回のセッションの内容で SQL Server にさらに興味を持っていただけると幸いです 54

Actual4Test Actual4test - actual test exam dumps-pass for IT exams

Actual4Test Actual4test - actual test exam dumps-pass for IT exams Actual4Test http://www.actual4test.com Actual4test - actual test exam dumps-pass for IT exams Exam : FM0-308 日本語版 Title : Developer Essentials for FileMaker 13 Vendor : FileMaker Version : DEMO 1 / 4 Get

本日の Agenda 1. SQL Server の基本構成 2. データ書き込みの基本動作 3. バックゕップの基本動作 この 3 点についてデモを交えながらお話させていただきます 2

本日の Agenda 1. SQL Server の基本構成 2. データ書き込みの基本動作 3. バックゕップの基本動作この 3 点についてデモを交えながらお話させていただきます 2