SQL Server のことなら SQL Quality SQL Server パフォーマンス チューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム > 技術情報 > SQL Server 2012 自習書 データ パーティション入門

SQL Server 2012 自習書 「No.19 データ パーティション入門」(HTML 版)

松本美穂と松本崇博が執筆した完全オリジナル SQL Server 2012 自習書シリーズの「No.19 データ パーティション入門」の HTML 版です。 日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2013年12月29日]

目次へ | 前のページへ | 次のページへ

5.1 一部のデータ ファイル破損時の復旧

◆ 一部のデータ ファイル破損時の復旧

データ パーティションは、複数のファイル グループおよびデータ ファイル(.ndf)で構成している場合は、一部のデータ ファイルが破損してもそのまま稼動させることができます。また、破損したデータ ファイル(ファイル グループ)のバックアップを復元するだけで、破損前と同じ状態へ復旧することができます。
このときの作業の流れは、次のとおりです。
1. 事前にファイル グループ単位のバックアップまたはデータベース全体のフル バックアップを実行しておく
2. 一部のデータ ファイルに障害が発生した場合は、SQL Server のログを確認して、障害が発生したデータ ファイル(ファイル グループ)の名前を確認する
3. 手順2 で確認したファイル グループを OFFLINE にする
4. データベースを ONLINE にする
5. ログ末尾(TAIL ログ)をバックアップする
6. 手順1 でバックアップしたファイルから、障害が発生したファイル グループのみを復元する
7. 手順 5 でバックアップしたログ末尾を復元する

◆ Let's Try

それでは、これを試してみましょう。前の Step で利用した「t1」テーブルが格納されている「pTestDB」データベースを利用して、一部のデータ ファイルが破損したときの復旧手順を試してみましょう。

手順1 ファイル グループ単位のバックアップの実行

1.最初の手順は、データベース「pTestDB」を構成する 7つファイル グループ(PRIMARYfg1 fg6)を、それぞれバックアップします。通常の BACKUP ステートメントで、次のように FILEGROUP キーワードを指定することで、ファイル グループ単位でのバックアップを実行することができます。

-- PRIMARY ファイル グループバックアップ
BACKUP DATABASE pTestDB
 FILEGROUP 'PRIMARY'
  TO DISK 'C:\test\fgPrimary.bak' WITH FORMAT
-- fg1 ファイル グループバックアップ
BACKUP DATABASE pTestDB 
 FILEGROUP 'fg1'
  TO DISK 'C:\test\fg1.bak' WITH FORMAT
-- fg2 ファイル グループバックアップ
BACKUP DATABASE pTestDB 
 FILEGROUP 'fg2'
  TO DISK 'C:\test\fg2.bak' WITH FORMAT
-- fg3 ファイル グループバックアップ
BACKUP DATABASE pTestDB 
 FILEGROUP 'fg3'
  TO DISK 'C:\test\fg3.bak' WITH FORMAT
-- fg4 ファイル グループバックアップ
BACKUP DATABASE pTestDB 
 FILEGROUP 'fg4'
  TO DISK 'C:\test\fg4.bak' WITH FORMAT
-- fg5 ファイル グループバックアップ
BACKUP DATABASE pTestDB 
 FILEGROUP 'fg5'
  TO DISK 'C:\test\fg5.bak' WITH FORMAT
-- fg6 ファイル グループバックアップ
BACKUP DATABASE pTestDB 
 FILEGROUP 'fg6'
  TO DISK 'C:\test\fg6.bak' WITH FORMAT

◆ データ ファイル破損のシミュレーション

1.次に、データ ファイルの破損をシミュレーションするために「fg5.ndf」ファイル(fg5 ファイル グループ)を削除します。ファイルを削除するには、まず、SQL Server サービスを停止しておく必要があります。サービスの停止は、次のように「SQL Server 構成マネージャー」ツールから、SQL Server サービスを右クリックして、[停止]をクリックします。

00044

2.サービスが停止されたら、Windows エクスプローラーを起動して、次のように「fg5.ndf」ファイルを右クリックして[削除]をクリックし、直接削除します。

00045

3.ファイルの削除が完了したら、「SQL Server 構成マネージャー」ツールから SQL Server サービスを開始します。

◆ 手順2 SQL Server ログから障害が発生したファイルの確認

1.SQL Server サービスが開始されたら、Management Studio を起動して、SQL Server ログを参照します。データ ファイルへ障害が発生している場合は、次のようにエラーが記録されます。

00046

ログへは、次のように記録されます。

Unable to open the physical file "C:\test\fg5.ndf>". 
Operating system error 2: "2(指定されたファイルが見つかりません。)"

日本語訳だと「物理ファイルが開けません、指定されたファイルが見つかりません」というエラーで、これがファイルに障害が発生した場合に記録されるエラー メッセージです。

2.この状態では、データベースへアクセスすることができないので、これを確認しておきましょう。クエリ エディターを開いて、データベースへ接続してみます。

USE pTestDB

00047

◆ 手順3 破損したファイル グループを OFFLINE へ変更

この状態から復旧するには、まず破損したファイル グループを OFFLINE へ設定します。

1.ファイル グループを OFFLINE へ設定するには、ファイル グループの「論理名」を指定する必要があるので、次のようにバックアップ ファイルに対して RESTORE FILELISTONLY ステートメントを実行して、ファイル グループの論理名を取得します。

RESTORE FILELISTONLY
  FROM DISK 'C:\test\fg5.bak'

00048

結果の LogicalName が論理名です。これにより、破損したファイル(C:\test\fg5.ndf)の論理名が「fg5」であることが分かるので、これを OFFLINE へ設定します。

2.OFFLINE へ設定するには、次のように ALTER DATABASE .. MODIFY FILE ステートメントで OFFLINE を指定します。

ALTER DATABASE pTestDB
  MODIFY FILE (NAME 'fg5'OFFLINE)

00049

◆ 手順4 データベースを ONLINE へ変更

破損したファイル グループを OFFLINE にした後は、データベースを ONLINE へ設定します。

1.データベースを ONLINE へ設定するには、次のように ALTER DATABASE ステートメントを実行します。

ALTER DATABASE pTestDB
  SET ONLINE

00050

これにより、データベースが利用できるようになり、破損していないファイル グループへアクセスできるようになります。

2.データベースが利用できることを確認するために、パーティション 32014年用fg6 ファイル グループを利用)のデータを参照してみましょう。

USE pTestDB
go
-- パーティション 3
SELECT *, $PARTITION.pFunc1(col1As [パーティション番号]
FROM t1
WHERE col1 '2014/01/01'

00051

結果は、問題なくデータを参照することができ、破損したファイルがあるにも関わらず、データベースを利用できたことを確認できます。現在のファイル グループとパーティションの構成は、次のとおりです。

00052

3.次に、パーティション 3 に対して、更新系のステートメントを実行してみましょう。

USE pTestDB
go
-- パーティション データ追加更新削除
INSERT INTO t1 VALUES('2014/01/02')    
UPDATE t1 SET col1='2014/05/02' WHERE col1 '2014/01/02'
DELETE FROM t1 WHERE col1='2014/01/01'

00053

これも問題なく実行できたことを確認できます。更新系のステートメントについても、破損したファイル グループを除いて、実行することができです(更新操作は、トランザクション ログへ記録されています)。

4.次に、破損したファイル グループ(パーティション2)へアクセスしてみましょう。

-- パーティション アクセス
SELECT FROM t1 WHERE col1 '2013/01/01'
INSERT INTO t1 VALUES('2013/01/02')

00054

結果は、どちらもエラーになります。エラーには、破損したファイル グループの名前「fg5」があり、これにアクセスできないというメッセージになっていることを確認できます。

5.次に、テーブル スキャンを実行してみましょう。

SELECT FROM t1

00055

結果は、2012年のデータ(パーティション 1)のデータが表示されて、クエリがエラーで完了します。[メッセージ]タブを開くと、同じエラーが発生していることを確認できます。このように、破損したファイル グループを含んだデータ範囲検索の場合は、そのファイル グループへアクセスするまでの間に取得できたデータを参照することができます。

6.続いて、データベースの状態を確認するために、次のように database_files カタログ ビューを参照してみましょう。

SELECT state_descname*
 FROM sys.database_files

00056

結果は、fg5 のみが OFFLINE であることを確認できます。

◆ 手順5 ログ末尾(TAIL ログ)のバックアップ

ここからは、破損したファイル グループを復旧するための手順です。
前の手順では、オンラインとなっているファイル グループに対して更新操作を行いましたが、これらの操作は、トランザクション ログの末尾(まだバックアップされていないアクティブ ログで、「TAIL ログ」とも呼ばれます)へ記録されています。

1.ログの末尾をバックアップするには、次のように BACKUP LOG ステートメントで WITH NORECOVERY を指定して実行します。

USE master
go
BACKUP LOG pTestDB
 TO DISK 'C:\test\lastLog.bak'
  WITH NORECOVERY, FORMAT

00057

正常に処理されました」と表示されれば、バックアップが完了です。NORECOVERY オプションを指定することで、ログ末尾をバックアップして、かつ「オンライン復元シーケンス」(SQL Server をオンラインにしたままでの復元)を開始することができます。

なお、実行時に「使用中で排他アクセスを獲得できませんでした」エラーが表示される場合には、ログ末尾のバックアップが失敗しています。この場合は、ほかに接続しているユーザーがいないことを確認してから、次のように ALTER DATABASEステートメントを実行して、データベースをSINGLE_USER モードへの切り替えを行ってから、ログ末尾のバックアップを再度実行してみてください。

USE master
go
ALTER DATABASE pTestDB
 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go

2.オンライン復元シーケンスの開始後は、データベースが NOREOVERY 状態(復元中)となり、データベースへのアクセスはできなくなります。

USE pTestDB

00058

このようにデータベースの復元中は、復旧作業を正しく行うために、データベースへは一切アクセスさせないようにして、ユーザーによる更新操作を行わせないように(トランザクション ログへ更新操作が記録されないように)しています。

◆ 手順6 障害の発生したファイル グループのみを復元

1.次に、破損したファイル グループ(fg5)のみをバックアップから復元します。

RESTORE DATABASE pTestDB
 FILEGROUP 'fg5'
  FROM DISK 'C:\test\fg5.bak'
   WITH NORECOVERY

00059

◆ 手順7 ログ末尾(TAIL ログ)のリストア

1.最後に、手順5 で取得したログ末尾のバックアップをリストアします。

RESTORE LOG pTestDB
 FROM DISK 'C:\test\lastLog.bak'
  WITH RECOVERY

00060

2.リストアが完了したら、リストアしたデータを確認してみましょう。

USE pTestDB
go
SELECT *, $PARTITION.pFunc1(col1As [パーティション番号]
FROM t1

00061

障害発生後に、パーティション3へ追加したデータ(2014-05-02)が正しく格納されて、破損したファイル グループ(パーティション2)のデータも復元されていることを確認できます。

3.すべてのファイル グループが ONLINE になっていることも確認しておきましょう。

SELECT state_descname*
 FROM sys.database_files

00062

◆ データ パーティションのまとめ

以上のように、データ パーティションは、運用管理性/保守性の利点が非常に多くあります。特に、本自習書で試した次の特徴は、データ パーティションを利用する大きなメリットになります。

  • 古いデータを定期的にアーカイブ テーブルへ移動する「スライディング ウィンドウ」のシナリオを簡単に実現可能
  • 古いデータ削除時のパフォーマンスが大幅アップ
  • パーティション単位でデータ圧縮が可能
  • パーティション単位でインデックスの再構築と再編成が可能。古いパーティションは、インデックスの再構築が必要ないケースが多いので、テーブル全体を再構築するよりも、大幅に再構築時間を短縮することが可能
  • パーティションを複数のファイル グループで構成しておけば、パーティション単位でバックアップと復元が可能
  • パーティションを複数のデータ ファイル(.ndf)で構成しておけば、一部のデータ ファイルが破損しても、そのまま稼動させることが可能
  • 一部のデータ ファイルが破損しても、破損したファイル グループのみを復元するだけで完全復旧可能

◆ おわりに

最後までこの自習書の内容を試された皆さま、いかがでしたでしょうか?

SQL Server 2012 のデータ パーティション機能には、多くのメリットがあり、大規模データベース環境では非常に重要な機能であることを確認できたのではないでしょうか。今回は「入門編」ということで、データ パーティションの基本的な操作方法のみの紹介になりましたが、データ パーティションのパフォーマンスについては、以下のホワイト ペーパーへ詳しく記載されていますので、ぜひ参考にしてみてください(SQL Server 2005 ベースの情報ですが、SQL Server 2012 でも、多くのことが当てはまります)。

徹底検証シリーズSQL Server 2005 データ パーティション パフォーマンス検証
http://www.microsoft.com/ja-jp/sqlserver/2008/r2/technology/cqi.aspx#a03

目次へ | 前のページへ | 次のページへ

SQL Server 2014 CTP2 インメモリ OLTP 機能の概要
SQL Server 2012 の教科書
SQL Server 2014 CTP2 インメモリ OLTP 機能の概要 (Amazon Kindle 書籍)
SQL Server 2012 の教科書(ソシム)

事例1

MPNロゴ


SQLQualityは執筆とセミナーを通じて技術の啓蒙やエンジニアの育成支援も行っています
最新刊
SQL Server 2014 CTP2 インメモリ OLTP 機能の概要
SQL Server 2014 CTP2 インメモリ OLTP 機能の概要(Amazon Kindle 書籍)

SQL Server 2012 の教科書
SQL Server 2012 の教科書(ソシム)

弊社オリジナル制作の
SQL Server 2012 自習書も
マイクロソフトのサイトで公開中!
ロングセラー
ASP.NET でいってみよう  SQL Server 2000 でいってみよう
ASP.NET でいってみよう
第7刷 16,500 部発行
SQL Server 2000 でいってみよう
第12刷 28,500 部発行


セミナー風景
セミナー風景

弊社執筆の
SQL Server 2012 自習書
マイクロソフトのサイトで公開中
全30冊
ダウンロードはこちら
弊社執筆の
SQL Server 2008 R2 自習書
マイクロソフトのサイトで公開中
全30冊
目次はこちら
松本美穂のコラム
(公開活動などのお知らせ)

第 46 回:Microsoft Power BI for Windows app からの Power BI サイト アクセス
第 45 回:Power Query で取得したデータを PowerPivot へ読み込む方法と PowerPivot for Excel 自習書のご紹介
第44回:「SQL Server 2014 への移行とアップグレードの実践」ドキュメントを作成しました
第43回:SQL Server 2014 インメモリ OLTP 機能の上級者向けドキュメントを作成しました
第42回:Power Query プレビュー版 と Power BI for Office 365 へのクエリ保存(共有クエリ)
第41回:「SQL Server 2014 CTP2 インメモリ OLTP 機能の概要」自習書のお知らせです
第40回: SQL Server 2012 自習書(HTML版)を掲載しました
第39回: Power BI for Office 365 プレビュー版は試されましたか?
第38回: SQL Server 2014 CTP2 の公開
第37回: SQL Server 2014 CTP1 の自習書をご覧ください
第36回: SQL Server 2014 CTP1 のクラスター化列ストア インデックスを試す
第35回: SQL Server 2014 CTP1 のインメモリ OLTP の基本操作を試す
第34回: GeoFlow for Excel 2013 のプレビュー版を試す
第33回: iPad と iPhone からの SQL Server 2012 Reporting Servicesのレポート閲覧
第32回: PASS Summit 2012 参加レポート
第31回: SQL Server 2012 Reporting Services 自習書のお知らせ
第30回: SQL Server 2012(RTM 版)の新機能 自習書をご覧ください
第29回: 書籍「SQL Server 2012の教科書 開発編」のお知らせ
第26回: SQL Server 2012 の Power View 機能のご紹介
第25回: SQL Server 2012 の Data Quality Services
第24回: SQL Server 2012 自習書のご案内と初セミナー報告
第23回: Denali CTP1 が公開されました
第22回 チューニングに王道あらず
第21回 Microsoft TechEd 2010 終了しました
第20回 Microsoft TechEd Japan 2010 今年も登壇します
第19回 SQL Server 2008 R2 RTM の 日本語版が公開されました
第18回 「SQL Azure 入門」自習書のご案内
第17回 SQL Server 2008 自習書の追加ドキュメントのお知らせ
第16回 SQL Server 2008 R2 自習書とプレビュー セミナーのお知らせ
第15回 SQL Server 2008 R2 Reporting Services と新刊のお知らせ
第14回 TechEd 2009 のご報告と SQL Server 2008 R2 について
第13回 SQL Server 2008 R2 の CTP 版が公開されました
第12回 MVP Summit 2009 in Seattle へ参加

技術コミュニティでも活動中

Microsoft MVP for SQL Server

松本崇博 Blog(SQL Server Tips)
松本美穂ブログ(SQL Serverノート)