🎩 🏘️ 📩 SQL Server 2008の変更を追跡する 🍴 👩🏻‍🔬 💆

すべてのDBMS開発者は遅かれ早かれ、データベースアクセスとサーバーイベント全般を追跡するタスクに直面すると思います。もちろん、ツールを選択する（または自分で作成する）前に、DBMS開発者自身が提供するソリューションに注意を払う必要があります。 SQL Server 2008でこの問題を解決した経験を共有したいと思います。

現在、SQL Server 2008にはこのようなソリューションが4つあります（SQL Server 2011のこの分野での大きな変更は予想されていません）。これらのツールの一部は以前のバージョンから、一部は2008年に登場しました。これらのツールは多くの点で重複しているため、特定の問題を解決するために1つ（または複数）のツールを選択するのは容易ではありません。これを支援するために、例を使用して各ツールの概要を説明します。

1. CT（変更追跡）。

多くの場合、CDC（Change Data Capture）と混同されます。しかし、これらのツールは、目的と実装の両方で異なります。 CTは変更の事実（どの行で、どのデータが変更されたか（C R UD））を追跡するように設計されていますが、CDCは変更の履歴（削除された行を含む行のすべてのバージョン）を保持します。実装に関しては、CDCはトランザクションログの読み取りに基づいており（非同期）、CTは同期して動作します。

変更の追跡が有効になっている各テーブルに対して、変更された行のID、変更された列を識別するためのビットマスク、操作の種類が格納されるシステムテーブルが作成されます。

CTを有効にするには、データベースレベルで特定のテーブルに対して有効にする必要があります。

ALTER DATABASE ChangeTracking SET change_tracking = ON ( change_retention = 10 minutes, auto_cleanup = ON ) ALTER TABLE Orders enable change_tracking WITH ( track_columns_updated = ON )

ALTER DATABASE ChangeTracking SET change_tracking = ON <br/> 
      

        
        
        
      

     ( change_retention = 10 minutes, auto_cleanup = ON ) <br/> 
      

        
        
        
      

     <br/> 
      

        
        
        
      

     ALTER TABLE Orders enable change_tracking WITH ( track_columns_updated = ON )

優れた記事の詳細（パラメーターの説明、使用例、詳細情報）。

2. CDC（変更データキャプチャ）

変更されたデータを追跡するためのツール。 CTとの主な違いは、非同期実装（上記の説明）と変更された（C R UD）データのすべてのバージョンの保存です。 CDCは、cdcスキーマのシステムテーブルを使用して、変更されたデータを保存します。 CDCがアクティブになっている各テーブルに対して、タイプcdc.dbo_Orders_CT（テーブルdbo.Orders用）の名前でテーブルが作成されます。

* _lsn-（ログシーケンス番号）-ログ内のトランザクション識別子の一種。

$ operation-操作のタイプ（1-削除、2-挿入、3-更新（更新前の行のバージョン）、4-更新（更新後の行のバージョン）。
$ update_maskは、行内の変更されたセルを指すビットマスクです。
IDと右側のその他すべては、それぞれセルです。変更が追跡されるテーブルの構造。

CDCをアクティブにするには、特定のテーブルのデータベースレベルでアクティブにする必要があります。

EXEC sys. sp_cdc_enable_db EXEC sys. sp_cdc_enable_table @source_schema = N 'dbo' , @source_name = N 'Orders' , @role_name = N 'cdc' , @capture_instance = N 'dbo_Orders' , @supports_net_changes = 1 , @index_name = 'id_idx' , @captured_column_list = null, @ FILEGROUP_NAME = null;

EXEC sys. sp_cdc_enable_db <br/> 
      

        
        
        
      

     <br/> 
      

        
        
        
      

     EXEC sys. sp_cdc_enable_table <br/> 
      

        
        
        
      

     @source_schema = N 'dbo' ,<br/> 
      

        
        
        
      

     @source_name = N 'Orders' ,<br/> 
      

        
        
        
      

     @role_name = N 'cdc' ,<br/> 
      

        
        
        
      

     @capture_instance = N 'dbo_Orders' ,<br/> 
      

        
        
        
      

     @supports_net_changes = 1 ,<br/> 
      

        
        
        
      

     @index_name = 'id_idx' ,<br/> 
      

        
        
        
      

     @captured_column_list = null,<br/> 
      

        
        
        
      

     @ FILEGROUP_NAME = null;

@source_schema-CDCをアクティブ化するテーブルが属するスキーム
@source_name-CDCをアクティブ化するテーブルの名前
@role_name-変更を表示する権限を持つロールの名前（そうでない場合は、自動的に作成されます）
@capture_instance-対応するシステムテーブルに表示される名前の一部と一致します
@supports_net_changes-変更の結果セットを表示する機能のサポート（データの最新バージョンのみ）。これには一意のインデックスが必要です
@index_name-実際には、一意のインデックスの名前
@captured_column_list-変更追跡がアクティブ化されるフィールドのリスト。デフォルトではそれだけです。
@filegroup_name-システムテーブルが配置されるファイルグループ

純粋に実用的な観点からすると、CDCの大きなマイナス点は、変更の著者を修正することが不可能であることです。もちろん、デフォルト値suser_sname（）を使用してcdc.dbo_Orders_CTシステムテーブルに列を追加する手間はありません（実際、これは機能します）。

保存されたデータへのクエリの例と記事の詳細な説明。

3. SQL Server監査

サーバーへのすべてのイベントとリクエスト（selectを含む）を追跡するように設計された強力なツール。このツールの範囲は非常に広く、プロファイリングからセキュリティに関連する問題、データベースの意図しない部分でのユーザーアクティビティの特定までです。

SQL Server Auditでは、監視対象イベントのフィルターを柔軟に構成できます。

監査を使用するには、サーバーレベルでアクティブ化する必要があります。

CREATE server audit ServerAudit TO FILE ( filepath = `D:\Audit\`, maxsize = 1GB ) WITH ( on_failture = CONTINUE ) ALTER server audit ServerAudit WITH ( STATE = ON )

CREATE server audit ServerAudit<br/> 
      

        
        
        
      

     TO FILE ( filepath = `D:\Audit\`, maxsize = 1GB ) <br/> 
      

        
        
        
      

     WITH ( on_failture = CONTINUE ) <br/> 
      

        
        
        
      

     <br/> 
      

        
        
        
      

     ALTER server audit ServerAudit WITH ( STATE = ON )

サーバーレベルで監査仕様（トレース）を作成する例：

CREATE server audit specification ServerAudit_Permissions FOR server audit ServerAudit ADD ( server_principal_change_group ) , ADD ( server_permission_change_group ) , ADD ( server_role_member_change_group ) ; ALTER server audit specification ServerAudit_Permissions WITH ( STATE = ON ) ;

CREATE server audit specification ServerAudit_Permissions<br/> 
      

        
        
        
      

     FOR server audit ServerAudit<br/> 
      

        
        
        
      

     ADD ( server_principal_change_group ) ,<br/> 
      

        
        
        
      

     ADD ( server_permission_change_group ) ,<br/> 
      

        
        
        
      

     ADD ( server_role_member_change_group ) ;<br/> 
      

        
        
        
      

     <br/> 
      

        
        
        
      

     ALTER server audit specification ServerAudit_Permissions<br/> 
      

        
        
        
      

     WITH ( STATE = ON ) ;

データベースレベルで監査仕様を作成する例：

USE MyDb CREATE DATABASE audit specification SA_MyDb_Orders FOR server audit ServerAudit ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. Orders BY PUBLIC ) , ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. OrderDetails BY PUBLIC )

USE MyDb<br/> 
      

        
        
        
      

     CREATE DATABASE audit specification SA_MyDb_Orders <br/> 
      

        
        
        
      

     FOR server audit ServerAudit<br/> 
      

        
        
        
      

     ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. Orders BY PUBLIC ) ,<br/> 
      

        
        
        
      

     ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. OrderDetails BY PUBLIC )

監査を調整するために、SQL Server Management Studioには便利なビジュアルインターフェイスがあります。

また、仕様c2の標準化された監査の手段があることに注意する必要があります（MSDNによると、米国の州の標準、標準へのリンクは見つかりませんでした）。

SP_CONFIGURE 'show advanced options' , 1 ; RECONFIGURE ; SP_CONFIGURE 'c2 audit mode' , 1 ; RECONFIGURE ;

SP_CONFIGURE 'show advanced options' , 1 ;<br/> 
      

        
        
        
      

     RECONFIGURE ;<br/> 
      

        
        
        
      

     <br/> 
      

        
        
        
      

     SP_CONFIGURE 'c2 audit mode' , 1 ;<br/> 
      

        
        
        
      

     RECONFIGURE ;

4. SQL Serverプロファイラー

このユーティリティは長い間誰もが慣れ親しんでいるので、今のところ私はそれにこだわらない。

ありがとう

もちろん、トピックに関心がない限り、各楽器について詳細に記述する計画です。

SQL Server 2008の変更を追跡する

1. CT（変更追跡）。

2. CDC（変更データキャプチャ）

3. SQL Server監査

4. SQL Serverプロファイラー

More articles: