How to: Use Operational Auditing with Change Data Capture

Article
09/22/2010

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

This tutorial demonstrates how to install and use the Change Data Capture (CDC) auditing feature for the SQL Server Modeling Services. In this tutorial, you will accomplish the following objectives:

Use the Mx.exe tool to install a new Modeling Services database instance with the CDC feature enabled.
Explore the CDC-enabled Modeling Services database.
Enable CDC auditing on a new Modeling Services table.
Query changes recorded by the CDC auditing feature.

Enabling the Change Data Capture Feature

You must enable the Change Data Capture feature at the time of installing the Modeling Services database on SQL Server 2008. The following steps use the Mx.exe command-line utility to install a new Modeling Services database with the CDC auditing option enabled. After it is installed, SQL Server Management Studio is used to explore the resulting changes to the database.

To install a CDC-enabled Modeling Services database

On the Start Menu, click All Programs, Microsoft SQL Server Modeling CTP, and open the Microsoft SQL Server Modeling CTP Command Prompt.
Use the following command line to create an empty Repository database named RepositoryCDC.
```
mx.exe create /database:RepositoryCDC
```
Note

The Mx.exe utility uses integrated Windows security to access the target server. The server in this example defaults to (local), which is the SQL Server 2008 default instance on the current machine. The calling user must have appropriate permissions on the target server to allow for the creation of a new database.
Use the following command to deploy the Base Domain Library (BDL) to the RepositoryCDC database. Enable auditing with the RepositoryAuditing (ra) property.
```
mx.exe install Repository.mx /database:RepositoryCDC /property:ra=+
```
Note

Note that the SQL Server Modeling CTP installs other models, such as the System_Runtime and Microsoft.UML2 models. If you want these models in the new database instances, you can use the same Mx.exe command to install their associated image files (*.mx) in the SQL Server Modeling CTP tools directory.

To explore the new CDC-enabled Modeling Services database

On the Start menu, click All Programs, Microsoft SQL Server 2008, and open SQL Server Management Studio.
In the Connect to Server dialog, type the name of the Modeling Services server in the Server name combo box.
Specify the Authentication properties, and then click the Connect button.

Note

Some of the steps in this tutorial require that the user belong to either the RepositoryAdministrator role in the database or the SQL Server sysadmin role.
On the toolbar, click the New Query button. A blank query window should open.
In the blank query window, add the following T-SQL statements. These statements first change the context to the RepositoryCDC database that you created in the previous steps. The query looks at the sys.databases system view to return the enabled state of CDC for the current database.
```
-- Query the current database property for CDC.
use RepositoryCDC
go
select name, is_cdc_enabled
from sys.databases 
where database_id = DB_ID()
go
```
Press the F5 key to run the query. Confirm that the is_cdc_enabled field returns 1 for the RepositoryCDC database.
Installing the Modeling Services database with auditing also adds CDC-based auditing for installed Modeling Services tables. To view the tables that CDC is currently auditing, replace the previous text in the SQL Server Management Studio query window with the following T-SQL statements.
```
use RepositoryCDC
go
exec sys.sp_cdc_help_change_data_capture
go
```
Press the F5 key to run the query. The Results pane should show a list of CDC-audited Modeling Services tables.

Using CDC Auditing

A Modeling Services database installed with the auditing option automatically enables Change Data Capture and applies CDC auditing to tables installed by the Modeling Services. However, any tables directly added to the SQL Server will need not immediately use CDC auditing. The Modeling Services stored procedure, [Repository.Item].[AddAuditing] can enable CDC auditing for a specific table. The following procedures also show an example of how to view the auditing data captured by CDC.

Note

The [Repository.Item].[AddAuditing] stored procedure requires that the caller belong to the db_owner fixed database role. It also requires SQL Server Agent to be running.

For a user to read the auditing data, the user must be a member of the RepositoryChangeDataCaptureReader role.

To audit a new Modeling Services table

In the previously opened SQL Server Management Studio query window, replace the query text with the following statements. This script creates a schema, TestSchema, and a table, PeopleTable.

use RepositoryCDC
go
-- First remove any previously auditted table and schema.
if (OBJECT_ID('[TestSchema].[PeopleChanges]') is not null)
begin
   drop function TestSchema.PeopleChanges
   drop function TestSchema.PeopleNetChanges
end
if (OBJECT_ID('[TestSchema].[PeopleTable]') is not null)
   drop table [TestSchema].[PeopleTable]
go
if (SCHEMA_ID('TestSchema') is not null)
   drop schema TestSchema
go

-- Create the TestSchema schema and the PeopleTable table.
create schema TestSchema
go
create table [TestSchema].[PeopleTable] 
   (Id [bigint] not null, Folder [bigint] not null, Name [nvarchar](max) null
   constraint [PK_TestSchema_PeopleTable_Id] primary key clustered ([Id] asc))
go

Press the F5 key to run the query.
In the query window, replace the text with the following statements. This script calls [Repository.Item].[AddAuditing] to apply CDC-based auditing to the [TestSchema].[PeopleTable] table created in the previous step. The @baseName parameter specifies the name of the table without the Table suffix. Any table participating in auditing should follow Modeling Services design patterns for table naming conventions. For more information, see Table Design Patterns.
```
exec [Repository.Item].[AddAuditing] @schema=N'TestSchema', @baseName=N'People'
```
Press the F5 key to run the query.

To view auditing changes

In SQL Server Management Studio, press F8 to display the Object Explorer.
Verify that the SQL Server Agent is running on your server. If not, right-click the SQL Server Agent node, and then click Start.

In the previously opened SQL Server Management Studio query window, replace the query text with the following statements. This script adds rows to the previously created [TestSchema].[PeopleTable]. It also updates the Name field of row 1 three times. These changes are used to observe associated auditing data for the table.

use RepositoryCDC
go

-- Create a test Folder
insert into [Repository.Item].[Folders]
([Name], [Folder]) values (N'CDCTestFolder', null)

-- Obtain the Id for the new Folder
declare @CDCFolder as int
set @CDCFolder = (select [Repository.Item].[PathsFolder]('CDCTestFolder'))

-- Insert new rows into the [TestSchema].[PeopleTable] table.
insert into [TestSchema].[PeopleTable]
           ([Id] ,[Folder], [Name]) values (1, @CDCFolder, N'Person1')
insert into [TestSchema].[PeopleTable]
           ([Id] ,[Folder], [Name]) values (2, @CDCFolder, N'Person2')
insert into [TestSchema].[PeopleTable]
           ([Id] ,[Folder], [Name]) values (3, @CDCFolder, N'Person3')
go

-- Update one row in the table multiple times.
update [TestSchema].[PeopleTable]
set [Name] = N'Person4' where [Id] = 1
go
update [TestSchema].[PeopleTable]
set [Name] = N'Person5' where [Id] = 1
go
update [TestSchema].[PeopleTable]
set [Name] = N'Person6' where [Id] = 1
go

Press the F5 key to run the query.
In the previously opened SQL Server Management Studio query window, replace the query text with the following statements. The prior call to [Repository.Item].[AddAuditing] generated two functions for viewing auditing events: [TestSchema].[PeopleNetChanges] and [TestSchema].[PeopleChanges]. This script uses the [TestSchema].[PeopleChanges] function to return all CDC auditing events between two log sequence number values. This example gets the minimum and maximum log sequence values to show all changes to the [TestSchema].[PeopleTable] table.
```
declare @min_lsn as binary(10)
declare @max_lsn as binary(10)
set @min_lsn = sys.fn_cdc_get_min_lsn('Capture_TestSchema_People')
set @max_lsn = sys.fn_cdc_get_max_lsn()
select sys.fn_cdc_map_lsn_to_time(__$start_lsn) as StartTime, 
   case __$operationwhen 1 then N'delete'
      when 2 then N'insert'
      when 3 then N'update before update'
      when 4 then N'update'
   end as Operation,
from [TestSchema].[PeopleChanges](@min_lsn, @max_lsn, 'all')
go
```
Although SQL Server 2008 provides similar functions for viewing Change Data Capture events, it is important to use the functions generated by the [Repository.Item].[AddAuditing] procedure. These functions can be called by users in the RepositoryChangeDataCaptureReader role. The functions also provide filtering of the auditing data to show only event data for rows in the source tables that the user has permission to see. As with updatable security views, this filtering is based on Modeling Services Folders and user permissions to read data related to those Folders. For more information, see Security Tasks (Modeling Services).
Press the F5 key to run the query. The Results window should show the three insert and three update operations on this table. Note that the column values are listed for each change.

Note

A common auditing question involves investigating changes during a specific time period. The SQL function sys.fn_cdc_map_time_to_lsn can be used to convert a datetime to a log sequence number for use in the auditing helper functions.