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つファイルグループ（PRIMARY、fg1 ～ 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．データベースが利用できることを確認するために、パーティション 3（2014年用、fg6 ファイルグループを利用）のデータを参照してみましょう。

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

00051

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

00052

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

USE pTestDB
go
-- パーティション 3 へデータの追加、更新、削除
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）へアクセスしてみましょう。

-- パーティション 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_desc, name, *
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(col1) As [パーティション番号]
FROM t1

00061

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

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

SELECT state_desc, name, *
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

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