956
Part VII: Monitoring and Auditing
Enabling the Database
To enable the database, execute the sys.sp_cdc_enable_db system stored procedure in
the current database. It has no parameters:EXEC sys.sp_cdc_enable_dbYou can use the is_cdc_enabled column in sys.databases to determine which data-
bases have CDC enabled on them:SELECT *
FROM sys.databases
WHERE is_cdc_enabled = 1This procedure creates six system tables in the current database:■ cdc.captured_columns: Stores metadata for tracked table’s columns■ (^) cdc.change_tables: Stores metadata for tracked tables
■ cdc.ddl_history: Tracks DDL activity
■ (^) cdc.index_columns: Tracks table indexes
■ cdc.lsn_time_mapping: Used for calculating clean-up time
■ (^) dbo.systranschemas: Tracks schema changes
These are listed in Object Explorer under the Database ➪ Tables ➪ System tables node.
Enabling Tables
After the database has been prepared for CDC, tables may be set up for CDC using the sys
.sp_cdc_enable_table stored procedure, which has several options:
■ (^) @source_schema: The tracked table’s schema
■ @source_name: The name of the table to be tracked
■ (^) @role_name: The role with permission to view CDC data
The last six parameters are optional:
■ (^) @capture_instance: May be used to create multiple capture instances for the
table. This is useful if the schema is changed.
■ (^) @supports_net_changes: Allows seeing just the net changes and requires a
unique index or primary key. The default is true.
■ (^) @index_name: The name of the unique index, if there’s no primary key for the
table. (But you’d never do that, right?)
■ (^) @captured_column_list: Determines which columns are tracked. The default is
to track all columns.
c41.indd 956c41.indd 956 7/31/2012 10:17:21 AM7/31/2012 10:17:21 AM
http://www.it-ebooks.info