SQL Server 2005 　Online Piecemeal Restore　シナリオによる可用性向上

マイクロソフトの植田です。

今回はパーティション・テーブルを利用したバックアップ/リストアに関する話題をご紹介したいと思います。

https://blogs.msdn.com/sqlcat/archive/2005/10/27/how-can-sql-server-2005-online-piecemeal-restore-improve-availability.aspx

注：下記内容に関する詳細の確認をご希望される場合は上記のブログを参照いただけますようお願いします。

本ドキュメントは以下の方を対象としております。

l 開発者、テストエンジニア、データベース・アドミニストレータ

l データベース、および、Microsoft SQL Serverについて基本的な知識をお持ちの方

SQL Server 2005のオンラインでの段階的な部分復元（Online Piecemeal Restore）シナリオによる可用性向上

 

質問1：どのようにしてSQL Server 2005の「オンラインでの段階的な部分復元シナリオ」は可用性を向上させることができるのでしょうか？

質問2：1つのテーブルや、パーティション分割されたテーブルの一つのパーティションなどの単体オブジェクトを復元することは可能でしょうか？

回答：

その質問は、大規模データベースにおいて、どのようにして最大限の柔軟性、管理容易性、可用性、および、復元性能を得るかというものです。ある時データベースのリストアが必要になった場合、そのデータベースに属する全てのファイルグループ中の全てのファイルは同じ時点で復元されなければいけません。数テラバイトのデータベースをリストアすることは長時間の業務停止を招き、特に全体のデータベースに比べて復元しなければならないデータが比較的小さな場合は重大な損失となります。SQL Server 2000では、ファイルグループ単位でのリストア機能を提供していましたが、そのデータベース中の全てのファイルグループがアクセス可能である必要がありました。

大規模データベースの運用シナリオにもっと柔軟性を持たせるために、SQL Server 2005の新しい機能であるオンラインでの段階的な部分復旧シナリオを利用すると、特定のファイルグループのバックアップを使用して、復元したい対象のデータのみ含む小規模な作業データベースを作成することができます。あるテーブル（またはパーティション分割されたテーブルの個別のパーティション）を特定のファイルグループに配置することにより、データベースの一部のテーブル単位または単一のパーティション単位といった粒度の細かい、短時間で実行できる、バックアップ/リストア計画を考えることができます。また、特定の時間、トランザクション・マーク、またはログのシーケンス番号（LSN）により指定されるトランザクション・ログを利用して、ある特定の時間へのトランザクション・ロール・フォワードを行うことも可能です。以下にデモンストレーションを示します。

サンプルDBとしてProdDBを用います。ProdDBは以下のセカンダリ・ファイルグループを持っています：

l Customer： FGCustomers

l Current year orders (2005)： FGOrders

l Historical orders (2000-2004)： FGOrderHistory

PordDBはCustomers、OrdersそしてOrderDetailesという3つの大きなユーザ・テーブルを持っています。CustomerテーブルはファイルグループFGCustomersファイルグループ上に配置されています。Ordersテーブルは年ごとにパーティション分割されています。2005年の注文はFGOrdersファイルグループ上に配置されています、一方2000-2004年の注文はFGOrderHistoryファイルグループ上に配置されています。ProdDBのレイアウトは以下の通りです：

プライマリ・ファイルグループ	FGCustomers (RW)	FGOrders (RW)	FGOrderHsitory (RO)
システム・テーブルのみが含まる、ユーザ・テーブルはなし	Customers data	2005 Orders Partitions OrderDetails	2000-2004 Orders partitions

ファイルグループ・バックアップとトランザクション・ログによるロール・フォワードにより、ある時点におけるデータベースの復元を行うことができます。例えば、あるバッチ処理で2005年のデータが不正になり、データを復元するためにFGOrders（上記網掛けの部分）の部分的ファイルグループ・リストアを行わなければならない状況を想定してください。

ある時点への部分的ファイルグループ・リストアの手順

1. ProdDBのプライマリ・ファイルグループ、FGCustomers、FGOrders、FGOrderHistoryをバックアップする

2. バッチ処理を走らせる（バッチ処理により2005年のデータが壊れたと仮定）

3. 最新のトランザクション・ログをバックアップする（ログ中のある時点に対するロール・フォワードが必要だと仮定）

4. 段階的な部分復元を実施する。PartialDBと言う名前の新しい作業データベースを作成するためにProdDBのプライマリ、および、特定のファイルグループ（FGOrders）のバックアップを使用する。トランザクション・ログを用いてある時点（データが壊れるバッチ処理の直前）へのロール・フォワードを行う。Ordersテーブルの2005年のデータが存在するパーティションはFGOrdersファイルグループから復旧。

5. ProdDB上の不正なデータを削除する

6. 作業データベースPartialDBからProdDBへ、リストアされたパーティション、または、テーブルをコピーする

ファイルグループおよびトランザクション・ログのバックアップ、不正なバッチ処理、オンライン逐次リストアの例

1. ProdDBファイルグループ、トランザクション・ログのバックアップ

BACKUP DATABASE ProdDB

FILEGROUP = 'PRIMARY'

to disk = 'C:\ProdDB_FG_PRIMARY_SQL.BAK' with init

-- SQL FGCustomers ファイルグループのバックアップ

BACKUP DATABASE ProdDB

FILEGROUP = 'FGCustomers'

to disk = 'C:\ProdDB_FG_FGCustomers_SQL.BAK' with init

-- SQL FGOrders ファイルグループのバックアップ

BACKUP DATABASE ProdDB

FILEGROUP = 'FGOrders'

to disk = 'C:\ProdDB_FG_FGOrders_SQL.BAK' with init

-- SQL FGOrderHistory ファイルグループのバックアップ

BACKUP DATABASE ProdDB

FILEGROUP = 'FGOrderHistory'

to disk = 'C:\ProdDB_FG_FGOrderHistory_SQL.BAK' with init

-- SQL ログバックアップ

BACKUP log ProdDB

to disk = 'C:\ProdDB_LOG_SQL1.BAK' with init

2. バッチ処理により2005年のデータの一部が壊れたと想定

-- バッチ処理が誤って2005年のデータを更新、または、削除したとする

DELETE from [ProdDB]..Orders

where OrderID in (select top 50 OrderID from [ProdDB]..Orders where OrderDate > '2005-01-30')

3. 最新のログのバックアップ

-- 最新のトランザクション・ログをバックアップ（この中のある時点にロール・フォワードする必要があるとする）

BACKUP log ProdDB

to disk = 'C:\ProdDB_LOG_SQL2.BAK' with init

4. オンライン段階的な部分復元により作業データベース、PartialDB、を作成

-- PartialDB はProdDB のバックアップより作成される

-- PartialDB のファイルを作成するのにMOVE オプションを使用する

RESTORE DATABASE PartialDB

from disk = 'C:\ProdDB_FG_PRIMARY_SQL.BAK'

WITH NORECOVERY, REPLACE, PARTIAL – partial:FG全てをリストアするわけではない

,MOVE 'ProdDB_data' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBPri1dat.mdf'

,MOVE 'SProdDB_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBlog.ldf'

-- 最新の2005年のデータのみリストア、FGOrdersHistory中の古いデータが格納されているパーティションはリストアしない

RESTORE DATABASE PartialDB

from disk = 'C:\ProdDB_FG_FGOrders_SQL.BAK'

WITH NORECOVERY

       ,MOVE 'FGOrders_file1' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\PartialDBOrdFi1dt.ndf'

-- ある時点まで一つ目のログをリストア。 トランザクション・マークやLSNを使ってリストアすることも可能

RESTORE log PartialDB

from disk = 'C:\ProdDB_LOG_SQL1.BAK'

WITH NORECOVERY, STOPAT = 'Oct 26, 2005 11:00 PM'

-- ある時点まで二つ目のログをリストア。 トランザクション・マークやLSNを使ってリストアすることも可能

RESTORE log PartialDB

from disk = 'C:\ProdDB_LOG_SQL2.BAK'

WITH RECOVERY, STOPAT = 'Oct 26, 2005 11:00 PM'

-- NOTE: 段階的な部分復元。　PartialDB の一部は復旧され、オンライン

select file_id, name, physical_name, state, state_desc

from sys.database_files

もし復旧点をトランザクション・マークによって指定する場合は、トランザクションが書き込みを行う処理でなければならないことに注意してください。例えば、トランザクションが「INSERT」、「UPDATE」、「DELETE」のどれかを含んでいれば、そのトランザクション・マークはログに書き込まれます。「SELECT」文のみのトランザクション・マークは書き込みを行うトランザクションではありません。トランザクション・マークを利用した復元に関してはBooks Onlineの「マークされたトランザクションの使用（完全復旧モデル）」が参考になります。

新しく作成された作業データベースPartialDBは2つのファイルグループで、3つのファイルを持ちます。プライマリ・ファイルグループのバックアップはProdDB_data（システムカタログ・テーブルを含む）ファイルとトランザクション・ログであるSProdDB_logファイルをリストアします。セカンダリのファイルグループ、FGOrdersはFGOrders_file1を再作成するのに使用されます。FGOrdersは2005年のOrdersテーブルのデータのみを持っています。

File_id	名前	物理ファイル名	状態	状態の記述
1	ProdDB_data	C:\Program Files\Microsoft SQL Server\MSSQL.1 \MSSQL\DATA\PartialDBPri1dat.mdf	0	ONLINE
2	SProcDB_log	C:\Program Files\Microsoft SQL Server\MSSQL.1 \MSSQL\DATA\PartialDBlog.ldf	0	ONLINE
3	FGCustomers_file1	C:\Program Files\Microsoft SQL Server\MSSQL.1 \MSSQL\DATA\PartialDBCustFi1dt.ndf	3	RECOVERY_PENDING
4	FGOrders_file1	C:\Program Files\Microsoft SQL Server\MSSQL.1 \MSSQL\DATA\PartialDBOrdFi1dt.ndf	0	ONLINE
5	FGOrderHistory_file1	C:\Program Files\Microsoft SQL Server\MSSQL.1 \MSSQL\DATA\PartialDBOrdHistFi1dt.ndf	3	RECOVERY_PENDING

PartialDBに関する特別な注意点

プライマリ・ファイルグループはデータベースのオブジェクトを定義するシステム・テーブルを持っています。ベスト・プラクティスとして、リストア処理を迅速に行うためにユーザ・オブジェクトはプライマリのファイルグループではなく、セカンダリのファイルグループに配置することを提案します。いったんプライマリ・ファイルグループがリストアされると、実際には全てのオブジェクトをリストアしないかもしれませんが、システム・テーブルはそのデータベース上の全てのオブジェクトのメタデータを保持します。

ONLINE状態の全てのファイルグループではデータを参照することができますが、パーティション分割されたテーブルなど、いくつかのオブジェクトは複数のファイルグループにまたがって存在することを覚えておいてください。もし、パーティション分割されたテーブルにおいて、いくつかのパーティションのみリストアされた場合、以下のようなテーブル・スキャン（フルスキャン）を行うクエリ「SELECT * FROM Orders」や、復元されていないオブジェクトに対するクエリは以下のエラーを受けて失敗するでしょう：

Msg 679, Level 16, State 1, Line 1

One of the partitions of index '' for table 'dbo.Orders'(partition ID 72057594038452224) resides on a filegroup that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

5. ProdDB中の不正なデータを削除

ProdDBにデータをリストアする前に、以下のような手法を用いてProdDBの不正なデータを除去しなければいけません。

a. テーブル削除（「SELECT INTO」を使用してオブジェクトを再作成する場合）

b. テーブルをトランケイト（切り詰め）して全ての行を削除、または、「WHERE」句を使って特定の行のみ削除する

c. スイッチ・アウト（パーティション分割されたテーブルにおいて）。元のテーブルでは新しいデータをスイッチ・インする前に、（スイッチ・アウトを使って）データ挿入先パーティションを空にしておかなければいけません。スイッチ・アウトをすると不正なデータを削除できます。

6. PartialDBからProdDBへパーティション、または、テーブルをコピー

コピーの手法を使ってProdDBにデータをリストアします。コピーしたオブジェクトに対してインデックスを再作成し、制約を加える、などの処理を行います。考えられるコピーの方法をいくつか記します：

a. SELECT * INTO ProdDB..OrderDtails FROM PartialDB..OrderDetails (テーブル全体をリストアし、ProdDBにおいて不正なデータを含むテーブル全体を削除した場合)

b. INSERT INTO ProdDB..Orders SELECT * FROM PartialDB..Orders WHERE OrderDate >= ‘2005-01-30’

c. 正しいデータを持ったファイルグループから作業テーブルを作成、データをコピー、スイッチ・イン(パーティション分割されたテーブルの場合)。注意：作業用ソース・テーブルおよびリストア対象のパーティション分割されたテーブルの定義は正確に一致している必要があります。データ挿入先のパーティションは空である必要があります。（スイッチ・インで使用される）ソース・テーブルは、データ挿入先のパーティションと同じファイルグループに属している必要があります。

パーティション・スイッチに関しては「データのサブセットを管理するためのパーティション設計」、および、「パーティションの切り替えを利用した効率的なデータ転送」が参考になります。

ある特定の時点における、あるオブジェクトをリストアした場合、参照整合性に関する影響を考慮しなければならないでしょう。他のオブジェクトとの関係が損なわれていないか確かめるための参照整合性のチェックを行わなければならないかもしれません。

まとめ：

結論として、SQL Server 2005は、大規模なデータベースを運用するシナリオにおいて特に有用である、柔軟なファイルグループ・バックアップ手法を提供します。テーブル（パーティション分割された個々のパーティションを含む）を特定のファイルグループに配置することにより、データベースの一部を小規模な作業用のデータベースに短時間でリストアするといった、粒度の細かいバックアップ/リストア計画を立てることができます。上記で述べた手法を使うことにより、テーブル全体、または、特定のテーブル・パーティションを復旧することができます。トランザクション・マークを併用することで、ある特定の時点におけるデータベースの状態を復元することができます。作業データベースにオブジェクトがリストアされた後、最終手順でそのデータを本番データベースにコピーします。コピー後は、作業データベースは削除可能です。

コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。