SQL Server のファイル・グループ のバックアップの現在との差分(No.1)

[ トップ ]   [ 新規 | 一覧 | 単語検索 | 最終更新 | ヘルプ ]

• バックアップ一覧
• 差分 を表示
• ソース を表示
• バックアップ を表示
• SQL Server のファイル・グループ へ行く。
  • 1 (2016-05-09 (月) 16:53:38)
  • 2 (2016-05-09 (月) 17:23:12)
  • 3 (2016-05-14 (土) 16:00:59)
  • 4 (2016-05-14 (土) 16:02:32)
  • 5 (2016-05-18 (水) 09:42:37)
  • 6 (2017-01-12 (木) 14:40:31)

---

• 追加された行はこの色です。
• 削除された行はこの色です。

[[Open棟梁Project>http://opentouryo.osscons.jp/]] - [[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]
「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。

-[[戻る>SQL Server]]

* 目次 [#hae0f628]
#contents

*概要 [#wd15b07e]
-「データ ファイル」は、１つの「プライマリ データ ファイル」と複数の「セカンダリ ファイル」に分割することができる。また、「ファイル グループ」を作成し、「ファイル グループ」毎にファイルの配置位置を選択することができる。
「ファイル グループ」（Oracleでは「表領域」と呼ぶ）を作成し、~
「ファイル グループ」毎にファイルの物理的な配置位置を選択することができる。

-デフォルトで「プライマリ ファイル グループ」が存在する。これに「ユーザ定義ファイル グループ」を追加、複数のパス（HDD）に「データ ファイル（テーブル、インデックス）」を分割して作成することができるようになる。
-構成
--「データ ファイル」を、
---１つの「プライマリ データ ファイル」と
---複数の「セカンダリ ファイル」に

>分割することができる。

*「ファイル グループ」の構成要素 [#t91bbcd1]
--また、「ファイル グループ」を作成し、~
「ファイル グループ」毎にファイルの配置位置を選択することができる。

---デフォルトで「プライマリ ファイル グループ」が存在する。
---これに「ユーザ定義ファイル グループ」を追加することで、~
複数のパスに「データ ファイル（テーブル、インデックス）」を分割して作成することができるようになる。

-効果
--簡易[[RAID]]（ストライピングに相当）
--また、部分的なバックアップ・リストア、段階的なリストアなど、運用系の時間短縮も可能。

*「ファイル グループ」による性能の向上 [#xdd38c8c]
SQL Server 2000では、ファイル グループを使用することでマシン リソースをフル活用して処理を実行する
-「[[簡易ストライピング>#le542327]]」と、
-「[[並列クエリ>#z5226928]]」が

サポートされていたが、

SQL Server 2005から、より高度な機能として
-「[[段階的リストア>SQL Server のバックアップ#w226d4d6]]」
-「[[パーティション分割>SQL Server パーティション分割]]」

がサポートされた。

これらの機能も、内部的には「ファイル グループ」のメカニズムを使用している。

**簡易ストライピング [#le542327]
「ファイル グループ」を使用して、「データ ファイル」、「トランザクション ログ ファイル」を複数のファイルに分割し、これを独自の「ディスク コントローラ」を持つディスクに配置する。~
この場合、クエリは複数ディスクにわたって展開されるため、性能が向上する（読み込み、書き込みともに性能が向上する）。

#ref(Filegroup.png,left,nowrap,SQL Server のファイルの配置)

※　RAID0のデバイスに１つのファイルを作成した場合と同じ効果がある。

-SQL Server 2000 チューニング全工程（2）：~
動的ディスク管理でのチューニングポイント (3/3) - ＠IT~
http://www.atmarkit.co.jp/ait/articles/0409/25/news011_3.html

***データ ファイルの充填 [#ue55a340]
-テーブルにデータを追加する場合、
--「ファイル グループ」内の最初のファイルが一杯になるまでファイルにデータを追加するのではなく、
--「ファイル グループ」内のすべてのファイルを対象に、~
空き領域に比例したエクステントを割り当て、そこに、データを追加するという動きになる。

-例えば、
--「ファイル グループ」が、「ファイル１」と「ファイル２」から構成されるとする。
--「ファイル１」の空き領域が100 MBで、「ファイル２」の空き領域が200 MBの場合、
--「ファイル１」のエクステント１つと「ファイル２」のエクステント２つが割り当てられる。
--これにより、「ファイル１」と「ファイル２」は、ほぼ同時に一杯になる。

***データ ファイルの拡張 [#z6f890a5]
データベースが自動拡張されるように設定されている場合、~
「ファイル グループ」内のすべてのファイルが一杯になると、~
ファイルが１つずつラウンドロビン方式で自動拡張される。

**並列クエリ [#z5226928]
-ファイル・グループ（ファイル）毎にI/Oスレッドを起動、

-これによって、
--I/Oスレッドを増大させることによって~
初回検索時の大幅な性能向上が期待できるが、これは、基本的に
---適切に設計されたOLTP向けの性能向上を期待するものではない（OLAP向け）。
---I/Oネックにならない様にファイル・グループやRAIDでI/O性能を向上させておく必要がある。

-なお、バックアップ・リストアについては、~
バックアップ デバイス数と同数のスレッドが使用される。

#ref(ParallelQuery.png,left,nowrap,並列クエリ)

***次数 [#x582f183]
並列クエリの次数に影響を与えるオプション・"しきい値"は、公開されていない。

なお、複数のユーザが、クエリを実行する場合、~
プランの次数は、その処理開始時点の各CPUの処理量を考慮する。

***参考 [#h783e84d]
-並列クエリの例~
https://msdn.microsoft.com/ja-jp/library/ms175097.aspx
-並列処理の次数~
https://msdn.microsoft.com/ja-jp/library/ms188611.aspx

*構成要素 [#oaef86ea]
**「ファイル グループ」の構成要素 [#t91bbcd1]
「ファイル グループ」には、
-「プライマリ ファイル グループ」
-「ユーザ定義ファイル グループ」がある。

**「プライマリ ファイル グループ」 [#j90b188c]
「プライマリ ファイル グループ」には、「プライマリ データ ファイル」と、他の「ファイルグループ」に割り当てられていない「プライマリ ファイル グループ」上の全てのファイルが含まれる。システムDBの テーブル用のすべてのファイルは「プライマリ ファイル グループ」に割り当てられている。
***「プライマリ ファイル グループ」 [#j90b188c]
-「プライマリ ファイル グループ」には、
--「プライマリ データ ファイル」と、
--他の「ファイルグループ」に割り当てられていない「プライマリ ファイル グループ」上の全てのファイル

**「ユーザ定義ファイル グループ」 [#w8c86f01]
>が含まれる。

-システムDBの テーブル用のすべてのファイルは「プライマリ ファイル グループ」に割り当てられている。

***「ユーザ定義ファイル グループ」 [#w8c86f01]
「ユーザ定義ファイル グループ」は、「CREATE DATABASE」または「ALTER DATABASE」ステートメント内で「FILEGROUP」キーワードを使用して作成できる。

**「データ ファイル」の構成要素 [#tbc98bab]

*「データ ファイル」の構成要素 [#tbc98bab]
***「テーブル」・「インデックス」 [#a092a68d]
「テーブル」・「インデックス」は「データ ファイル」に含まれる。

**「プライマリ データ ファイル」 [#jdccafad]
「プライマリ データ ファイル」は「データ ファイル」の開始点で、DB内のほかのファイルを指し示す。1 つのDBに 1 つ「プライマリ データ ファイル」が必要。拡張子は「mdf」が推奨される。
***「プライマリ データ ファイル」 [#jdccafad]
-「プライマリ データ ファイル」は「データ ファイル」の開始点で、DB内のほかのファイルを指し示す。
-1 つのDBに 1 つ「プライマリ データ ファイル」が必要。

**「セカンダリ データ ファイル」 [#ae355fe9]
「セカンダリ データ ファイル」は「プライマリ データ ファイル」以外のすべての「データ ファイル」である。DBは、「セカンダリ データ ファイル」がない場合と、複数の「セカンダリ データ ファイル」がある場合がある。拡張子は「ndf」が推奨される。
-拡張子は「mdf」が推奨される。

***「セカンダリ データ ファイル」 [#ae355fe9]
-「セカンダリ データ ファイル」は「プライマリ データ ファイル」以外のすべての「データ ファイル」である。

-データベースには、
--「セカンダリ データ ファイル」がない場合と、
--複数の「セカンダリ データ ファイル」がある場合がある。

-拡張子は「ndf」が推奨される。

**「トランザクション ログ ファイル」 [#z8b516b8]
「トランザクション ログ ファイル」は「ファイル グループ」に追加できない。

-「データ ファイル」と同様に複数のファイルを作成できる。
-「データ ファイル」のような「プライマリ」、「セカンダリ」を区別しない。

-拡張子は「ldf」が推奨される。

*規則と推奨 [#o07b24e5]
**規則 [#k1eb8dfd]
-１つの「ファイル グループ」を複数のデータベースで使用できない。~
「ファイル グループ」は、１つの「データベース スキーマ」内に定義するため。

-１つの「データ ファイル」は１つの「ファイル グループ」にしか所属できない。~
「データ ファイル」は、１つの「ファイル グループ」内に定義するため。

-「トランザクション ログ ファイル」を「ファイル グループ」に格納できない。~
「トランザクション ログ ファイル」は「データ ファイル」と扱いが異なるため。

**推奨 [#fed77f19]
***作成のポイント [#ud2d855b]
大部分のデータベースは、通常、
-１つの「データ ファイル」と
-１つの「トランザクション ログ ファイル」で

正常に機能するが、必要に応じて、

以下の「ファイル グループ」の導入を検討する。

-「データ ファイル」を複数のファイルに分割する場合は、
--プライマリの「ファイル グループ」の他に、
--追加ファイル用のセカンダリの「ファイル グループ」を作成し、

>これを既定の「ファイル グループ」に設定する。

-これにより、
-*プライマリの「ファイル グループ」にはシステム オブジェクト だけが格納される。~
ストアド プロシージャ、拡張ストアド プロシージャ、関数、ビューなど。
-セカンダリの「ファイル グループ」にはユーザ オブジェクト だけが格納される。~
テーブル、インデックス、列、別名データ型、.NET Framework 共通言語ランタイム (CLR) ユーザ定義型など。

この分割方法は、「[[段階的リストア>SQL Server のバックアップ#w226d4d6]]」や「[[パーティション分割>SQL Server パーティション分割]]」で活用できる。

***性能向上のポイント [#a947c158]
-特定の物理ディスク上にデータ ファイル（テーブル・インデックス）を配置できる。~
これにより、負荷の高い特定のテーブルをディスクI/O性能の高い特定の物理ディスクにマップできる。

-「トランザクション ログ ファイル」は、ディスクI/O性能を考慮して、~
「データ ファイル」と同じ物理ディスク上に配置しない（分散することが望ましい）。

-「ファイル グループ」により、物理ディスクにファイルを分散させることで、~
簡易ストライピングによるディスクI/O性能の向上が期待できる。

--ただし、RAIDコントローラを用いてストライピング可能であれば不要。

--アクセス頻度が高いテーブルと、そのテーブルに属する「非クラスタ化インデックス」を、~
別の「ファイル グループ」にマップし、ディスクI/Oを分散するという方法を採ることもできる。

--同じ結合クエリで使用する各テーブルは~
別の「ファイル グループ」にマップすることにより、ディスクI/Oを分散して処理できる。

***保守性の向上 [#d6119cb4]
大きな記憶域を占有する可能性のあるオブジェクトは、~
別々の「ファイル グループ」に配置することで「[[段階的リストア>SQL Server のバックアップ#w226d4d6]]」による~
柔軟なデータのバックアップ・リストアなど、メンテナンス操作に関してメリットがある。

*例 [#nea2a80d]
次の例では、

DBを作成（CREATE DATABASEステートメント）する際に
-「プライマリ ファイル グループ」と
-「ユーザ定義ファイル グループ」を

作成し、

-１つの「プライマリ データ ファイル」を「プライマリ ファイル グループ」
-２つの「セカンダリ データ ファイル」を「ユーザ定義ファイル グループ」

に設定、

-「ユーザ定義ファイル グループ」をデフォルトの「ファイル グループ」に設定している。
-その後、「ユーザ定義ファイル グループ」を指定してテーブル（または、インデックス）を作成する。

 USE master
 GO
 -- Create the database with the default data filegroup and the log file.
 -- Specify the growth increment and the max size for the primary data file.
 CREATE DATABASE MyDB -- DBの「プライマリ データ ファイル」を「プライマリ ファイル グループ」に配置
 ON PRIMARY
   ( NAME='MyDB_Primary',
    FILE NAME=
       'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_Prm.mdf',
    SIZE=4,
    MAXSIZE=10,
    FILEGROWTH=1),
 FILEGROUP MyDB_FG1 -- DBの「セカンダリ データ ファイル」を「ユーザ定義ファイル グループ」に配置
   ( NAME = 'MyDB_FG1_Dat1',
    FILE NAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10,
    FILEGROWTH=1),
   ( NAME = 'MyDB_FG1_Dat2',
    FILE NAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10,
    FILEGROWTH=1)
 LOG ON -- DBの「トランザクション ログ ファイル」を「任意のパス」に配置
   ( NAME='MyDB_log',
    FILE NAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL\data\MyDB.ldf',
    SIZE=1,
    MAXSIZE=10,
    FILEGROWTH=1)
 GO
 ALTER DATABASE MyDB -- 「ユーザ定義ファイル グループ」をデフォルトの「ファイル グループ」に設定
 MODIFY FILEGROUP MyDB_FG1 DEFAULT
 GO
 
*「トランザクション ログ ファイル」 [#z8b516b8]
「トランザクション ログ ファイル」も「データ ファイル」と同様に複数のファイルを作成できる。ただし、「データ ファイル」のようなプライマリ、セカンダリを区別しない。拡張子は「ldf」が推奨される。
 -- Create a table in the user-defined filegroup.
 -- このテーブルは、「ユーザ定義ファイル グループ」に作成される。
 USE MyDB
 CREATE TABLE MyTable
   ( cola      int   PRIMARY KEY,
    colb      char(8) )
 ON MyDB_FG1
 GO

**参考 [#qd40d160]
-MSDN - Microsoft
--データベース ファイルとファイル グループ~
https://msdn.microsoft.com/ja-jp/library/ms189563.aspx
--ファイルとファイル グループのアーキテクチャ~
https://technet.microsoft.com/ja-jp/library/ms179316.aspx

*「ファイル グループ」による性能の向上施策 [#xdd38c8c]
**「ディスク コントローラ」を分割 [#le542327]
「ファイル グループ」を使用して、「データ ファイル」、「トランザクション ログ ファイル」を複数のファイルに分割し、これを独自の「ディスク コントローラ」を持つディスクに配置する。この場合、クエリは複数ディスクにわたって展開されるため、性能が向上する（読み込み、書き込みともに性能が向上する）。

※　RAID0のデバイスに１つのファイルを作成した場合と同じ効果がある。
-[[SQL Server のデータベースの構成について改めて勉強 その 1 at SE の雑記>http://blog.engineer-memo.com/2011/10/14/sql-server-%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%AE%E6%A7%8B%E6%88%90%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6%E6%94%B9%E3%82%81%E3%81%A6%E5%8B%89%E5%BC%B7-%E3%81%9D/]]

----
Tags: [[:データアクセス]], [[:SQL Server]]

     

Site admin: dotNetDevelopmentInfrastructure

PukiWiki 1.4.7 Copyright © 2001-2006 PukiWiki Developers Team. License is GPL.
Based on "PukiWiki" 1.3 by yu-ji. Powered by PHP 5.3.3. HTML convert time: 0.016 sec.