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

ホーム＞技術情報＞ SQL Server 2012 自習書セキュリティ

SQL Server 2012 自習書「No.18 セキュリティ」（HTML 版）

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

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

7.1　変更データキャプチャ（CDC： Change Data Capture）

◆ 変更データキャプチャ

変更データキャプチャ（CDC： Change Data Capture）は、UPDATE や INSERT、DELETE ステートメントによる更新履歴を保管できる（変更データをキャプチャできる）機能です。これにより、Oracle 11g における Total Recall 機能のように、指定した時間の過去のデータを参照したり、オペレーションミス時のデータ回復などで利用できるようになります。変更データキャプチャは、悪意のあるユーザーによって、データが改ざんされた場合などに、改ざんされる前のデータを復旧する目的としても利用することができます。

◆ Let's Try

それでは、これを試してみましょう。

1．変更データキャプチャは、内部的には SQL Server Agent サービス機能を利用しているので、まずは SQL Server Agent サービスを開始しておく必要があります。SQL Server Agent サービスを開始するには、Management Studio から次のように操作します。

00194

2．次に、変更データキャプチャ機能を試すために「CDCtestDB」という名前のデータベースを作成します。

CREATE DATABASE CDCtestDB

3．次に、このデータベースに対して「sp_cdc_enable_db」ストアドプロシージャを実行して、変更データキャプチャ（CDC）を有効に設定します。

USE CDCtestDB
EXEC sys.sp_cdc_enable_db

00195

4．続いて、NorthwindJ データベースの「商品」テーブルをもとに、CDCtestDB データベース内へ「商品」テーブルを作成します。

USE CDCtestDB
-- 商品テーブルの作成
SELECT * INTO 商品 FROM NorthwindJ..商品
-- データの確認
SELECT * FROM 商品

00196

5．次に、作成した「商品」テーブルに対して、「sp_cdc_enable_table」ストアドプロシージャを実行して、CDC を有効に設定します。

USE CDCtestDB
go
EXEC sys.sp_cdc_enable_table
        @source_schema = N'dbo'
       ,@source_name = N'商品'
       ,@role_name = N'cdc_Admin'

00197

以上で、変更データキャプチャの設定が完了です。

Note： SQL Server Agent サービスを開始していない場合のエラー
もし、SQL Server Agent サービスを開始していない場合は、上記のステートメントを実行したときに、次のエラーが返されます。
00198

必ずサービスを開始しておくようにしましょう。
なお、サービス開始後に、再度 sp_cdc_enable_table を実行したときに、次のエラーが出る場合があります。
00199

この場合は、sp_cdc_disable_table を利用して、一度テーブルに対する変更データキャプチャを無効にした後に、再度 sp_cdc_enable_table を実行してください。
00200

◆ 更新履歴の参照（スキーマ名_テーブル名_CT テーブル）

変更データキャプチャを有効化すると、「dbo_商品_CT」のように「スキーマ名_テーブル名_CT」という名前のテーブルが自動的に作成されて、このテーブルへ更新履歴が記録されていくようになります（スキーマについては、本自習書シリーズの「ログイン認証とオブジェクト権限」で詳しく説明しています）。

00201

それでは、これを確認してみましょう。まずは、UPDATE／INSERT／DELETE ステートメントを実行して、商品テーブルのデータを更新します

-- データの変更
UPDATE 商品 SET 商品名 = 'XXX' WHERE 商品コード = 1
-- データの追加
INSERT INTO 商品(商品コード, 商品名, 生産中止) VALUES (199, 'AAAAAA', 1)
-- データの削除
DELETE FROM 商品 WHERE 商品コード = 2
-- データが更新されたことを確認
SELECT * FROM 商品

次に、更新履歴が格納されているテーブル「dbo_商品_CT」（cdc スキーマ）を参照します。

SELECT * FROM cdc.dbo_商品_CT

このように「dbo_商品_CT」テーブルの Operation 列を参照することで、更新前データか更新後データなのか、INSERT／DELETE されたデータなのかを確認することができます。また、変更データキャプチャは、トランザクションログへ記録された更新情報をもとにした機能なので、start_lsn と end_lsn など LSN（Log Sequence Number：ログ順序番号）によって、更新時刻（や順序）を管理しています。

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