変更の追跡の構成および管理
このトピックでは、変更の追跡を有効化、無効化、および管理する方法について説明します。また、セキュリティを構成する方法、および変更の追跡を使用する場合のストレージとパフォーマンスへの影響を判断する方法について説明します。
データベースの変更の追跡の有効化
変更の追跡を使用するには、あらかじめデータベース レベルで変更の追跡を有効にしておく必要があります。次の例では、ALTER DATABASE を使用して変更の追跡を有効にする方法を示します。
ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
変更の追跡は、SQL Server Management Studio で [データベースのプロパティ] ([変更の追跡] ページ) ダイアログ ボックスを使用して有効にすることもできます。
変更の追跡を有効にするときに、CHANGE_RETENTION および AUTO_CLEANUP オプションを指定できます。これらの値は、変更の追跡を有効にした後いつでも変更できます。
change retention 値は、変更追跡情報を保持する期間を指定します。この期間を過ぎると、変更追跡情報は定期的に削除されます。この値を設定する場合は、アプリケーションとデータベース内のテーブルを同期する間隔を考慮する必要があります。保有期間は、同期間隔と同じかそれ以上の長さに指定する必要があります。アプリケーションが変更を取得する間隔の方が長い場合、変更情報の一部が削除済みである可能性があるので、正しくない結果が返されることがあります。正しくない結果を取得しないようにするために、アプリケーションでは、CHANGE_TRACKING_MIN_VALID_VERSION システム関数を使用して、同期間隔が長すぎないかどうかを判断できます。
AUTO_CLEANUP オプションは、古い変更追跡情報を削除するクリーンアップ タスクを有効または無効にするために使用できます。これは、アプリケーションを同期できない一時的な問題が発生しており、保有期間を過ぎた変更追跡情報を削除するプロセスを問題が解決されるまで一時停止する必要がある場合に役立ちます。
変更の追跡を使用するデータベースについて、以下の点に注意してください。
変更の追跡を使用するには、データベースの互換性レベルを 90 以上に設定する必要があります。データベースの互換性レベルが 90 未満の場合でも、変更の追跡は構成できます。ただし、変更追跡情報の取得に使用される CHANGETABLE 関数からエラーが返されます。
スナップショット分離を使用すると、すべての変更追跡情報の一貫性を最も簡単に確保できます。このため、データベースのスナップショット分離を ON に設定することを強くお勧めします。詳細については、「変更の追跡の使用」を参照してください。
テーブルの変更の追跡の有効化
変更の追跡は、追跡するテーブルごとに有効にする必要があります。変更の追跡を有効にすると、DML 操作の影響を受けるテーブル内のすべての行に関する変更追跡情報が保持されます。
次の例では、ALTER TABLE を使用してテーブルの変更の追跡を有効にする方法を示します。
ALTER TABLE Person.Contact
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
テーブルの変更の追跡は、SQL Server Management Studio で [テーブルのプロパティ] ([変更の追跡] ページ) ダイアログ ボックスを使用して有効にすることもできます。
TRACK_COLUMNS_UPDATED オプションが ON に設定されている場合、SQL Server データベース エンジンによって、どの列が更新されたかという追加情報が内部変更追跡テーブルに格納されます。列追跡を行うと、アプリケーションは更新された列のみを同期できます。これにより、効率とパフォーマンスが向上します。ただし、列追跡情報を保持すると追加のストレージ オーバーヘッドがかかるので、このオプションは既定では OFF に設定されています。
変更の追跡の無効化
まず変更の追跡の対象になっているすべてのテーブルの変更の追跡を無効にして、次にデータベースの変更の追跡を OFF に設定する必要があります。データベース内で変更の追跡が有効になっているテーブルを確認するには、sys.change_tracking_tables カタログ ビューを使用します。
次の例では、ALTER TABLE を使用してテーブルの変更の追跡を無効にする方法を示します。
ALTER TABLE Person.Contact
DISABLE CHANGE_TRACKING;
データベース内のテーブルで変更が追跡されていなければ、データベースの変更の追跡を無効にすることができます。次の例では、ALTER DATABASE を使用してデータベースの変更の追跡を無効にする方法を示します。
ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = OFF
変更の追跡の管理
ここでは、変更の追跡の管理に関連するカタログ ビュー、権限、および設定の一覧を示します。
カタログ ビュー
変更の追跡が有効になっているテーブルおよびデータベースを確認するには、次のカタログ ビューを使用します。
また、sys.internal_tables カタログ ビューには、ユーザー テーブルの変更の追跡を有効にしたときに作成された内部テーブルが表示されます。
セキュリティ
変更追跡関数を使用して変更追跡情報にアクセスするには、プリンシパルに次の権限が必要です。
少なくともクエリ対象テーブルへの変更の追跡対象テーブルの主キー列に対する SELECT 権限。
変更が取得されるテーブルに対する VIEW CHANGE TRACKING 権限。VIEW CHANGE TRACKING 権限は、次の理由のため必要です。
変更追跡レコードには、削除された行に関する情報 (特に削除された行の主キー値) が含まれます。機密データが削除された後の変更の追跡対象テーブルに対する SELECT 権限がプリンシパルに付与されている可能性があります。この場合、そのプリンシパルが変更の追跡を使用して、削除済みの情報にアクセスできることは望ましくありません。
変更追跡情報には、更新操作によってどの列が変更されたかという情報が格納される場合があります。プリンシパルは、機密情報を含む列に対する権限を拒否されている場合があります。ただし、変更追跡情報は参照できるので、列の値が更新されたことは確認できますが、列の値を確認することはできません。
変更の追跡のオーバーヘッドについて
テーブルの変更の追跡を有効にすると、一部の管理操作が影響を受けます。次の表に、考慮する必要がある操作と影響を示します。
操作 |
変更の追跡が有効になっている場合 |
|---|---|
DROP TABLE |
削除するテーブルのすべての変更追跡情報が削除されます。 |
ALTER TABLE DROP CONSTRAINT |
PRIMARY KEY 制約を削除しようとすると失敗します。PRIMARY KEY 制約を削除する前に、変更の追跡を無効にする必要があります。 |
ALTER TABLE DROP COLUMN |
削除する列が主キーの一部である場合、変更の追跡に関係なく列は削除できません。 削除する列が主キーの一部ではない場合、列の削除は成功します。ただし、このデータの同期を実行しているアプリケーションへの影響についてまず理解しておく必要があります。テーブルで列の変更の追跡が有効になっている場合、削除した列がまだ変更追跡情報の一部として返される場合があります。削除した列の処理は、アプリケーションで行う必要があります。 |
ALTER TABLE ADD COLUMN |
変更の追跡対象のテーブルに新しい列を追加する場合、その列の追加は追跡されません。新しい列に加えられた更新および変更のみが追跡されます。 |
ALTER TABLE ALTER COLUMN |
主キー列以外の列のデータ型の変更は追跡されません。 |
ALTER TABLE SWITCH |
いずれかまたは両方のテーブルで変更の追跡が有効になっている場合、パーティションの切り替えは失敗します。 |
DROP INDEX または ALTER INDEX DISABLE |
主キーを設定するインデックスは削除または無効化できません。 |
TRUNCATE TABLE |
テーブルの切り捨ては、変更の追跡が有効になっているテーブルに対して実行できます。ただし、この操作によって削除される行は追跡されず、有効な最小バージョンが更新されます。アプリケーションがそのバージョンをチェックすると、バージョンが古すぎるため再初期化が必要であることが示されます。これは、テーブルの変更の追跡を無効にして再度有効にした場合と同じです。 |
変更の追跡を使用すると、DML 操作の一部として格納される変更追跡情報が原因で、DML 操作のオーバーヘッドが多少増加します。
DML への影響
変更の追跡は、DML 操作のパフォーマンスのオーバーヘッドを最小限に抑えるように最適化されています。テーブルに対する変更の追跡の使用に関連するパフォーマンスのオーバーヘッドの増加は、テーブルにインデックスを作成して維持する必要がある場合に発生するオーバーヘッドに似ています。
DML 操作で変更された行ごとに、行が内部変更追跡テーブルに追加されます。DML 操作に関連するこの影響は、次のようなさまざまな要因によって異なります。
主キー列の数
ユーザー テーブル行で変更されるデータの量
トランザクションで実行される操作の数
スナップショット分離を使用している場合も、変更の追跡が有効になっているかどうかに関係なく、すべての DML 操作のパフォーマンスに影響します。
ストレージへの影響
変更追跡データは、次の種類の内部テーブルに格納されます。
内部変更テーブル
変更の追跡が有効になっているユーザー テーブルごとに 1 つずつ内部変更テーブルがあります。
内部トランザクション テーブル
データベースごとに 1 つずつ内部トランザクション テーブルがあります。
これらの内部テーブルは、ストレージ要件に次のような影響を与えます。
ユーザー テーブル内の各行が変更されるごとに、行が内部変更テーブルに追加されます。この行によって、一定のわずかなオーバーヘッドおよび主キー列のサイズと同じ可変のオーバーヘッドが生じます。この行には、アプリケーションによって設定されるオプションのコンテキスト情報が含まれる場合があります。また、列の追跡が有効になっている場合、列が変更されるごとに追跡テーブルで 4 バイトが必要になります。
トランザクションがコミットされるごとに、行が内部トランザクション テーブルに追加されます。
その他の内部テーブルと同様に、変更追跡テーブルに使用される領域は、sp_spaceused ストアド プロシージャを使用して確認できます。内部テーブルの名前は、次の例に示すように、sys.internal_tables カタログ ビューを使用して取得できます。
sp_spaceused 'sys.change_tracking_309576141'
sp_spaceused 'sys.syscommittab'