Administrator & Monitoring Change Data Capture in SQL Server 2008
How to Enable CDC for a SQL Server 2008 Database Table
1. Now let’s create a Currency Table in the ChangeDataCapture Database by executing the following TSQL Query:
Create table Currency
CurrencyKey Int Identity(1,1) Primary Key NOT NULL,
2. Once the Currency table is successfully created, a DBA needs to make sure that the SQL Server Agent Service is running. In order for the CDC to be successful the SQL Server Agent should be running.
3. Enable CDC for the table Currency by executing the following TSQL:
EXEC sp_cdc_enable_table ‘dbo’, ‘Currency’, @role_name = NULL, @supports_net_changes =1
4. Execute the query below to check whether the table is enabled for CDC:
Select [name], is_tracked_by_cdc from sys.tables
The value of 1 for the is_tracked_by_cdc column means that the CDC is enabled for the table and the value of 0 for is_tracked_by_cdc column means that the CDC is disabled.
5. Once you have enabled CDC for the Currency table, another table is created for keeping changed data and the information about the changes in the source table. The new table created will have the cdc.dbo_Currency_CT name as highlighted in the above snippet:
How to disable CDC for a SQL Server 2008 Database Table
Database Administrators can run the TSQL Query below to disable CDC on a table:
EXEC sys.sp_cdc_disable_table ‘dbo’, ‘Currency’, ‘all’
How to disable CDC for a SQL Server 2008 Database
Database Administrators can run the TSQL Query below to disable CDC on a database. The SQL Server Agent should be running when DBA a decides to disable CDC for the database. Once the TSQL Query below has been executed successfully you could see that the SQL Server Agent Jobs which monitor the changes happening to table by reading the transaction log are deleted and also all the system tables related to CDC are also dropped.
Growth of Change Data Capture System Tables
Once in every three days there is an automatic cleanup process that occurs. For more intense environments you can leverage the manual method using the system stored procedure: sys.sp_cdc_cleanup_change_table. When you execute this system procedure you specify the low LSN and any change records occurring before this point are removed and the start_lsn is set to the low LSN you specified.
Change Data Capture System Tables
Once the DBA has enabled CDC for the Currency table, SQL Server creates the tables below along with a schema named CDC. CDC enables change tracking on tables so that Data Manipulation Language (DML) and Data Definition Language (DDL) changes made to the tables can be captured. The system tables that store information used by change data capture operations are mentioned below:
|cdc.<capture_instance>_CT||It returns one row for each change made to a captured column in the CDC enabled source table|
|cdc.captured_columns||It returns one row for each column tracked in a capture instance|
|cdc.change_tables||It returns one row for each change table in the database|
|cdc.ddl_history||All the Data Definition Language (DDL) change made to tables that are enabled for CDC are captured|
|cdc.lsn_time_mapping||It returns one row for each transaction having rows in a change table. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed|
|cdc.index_columns||It has information about all the indexes which are associated to a CDC enabled table|
|cdc.cdc_jobs||This table is created in MSDB database and has the configuration parameters for CDC agent jobs|
|dbo.systranschemas||This table is used to track schema changes in articles published in transactional and snapshot publications. This table is stored in both publication and subscription databases|
Change Data Capture System Stored Procedures
Change data capture enables change tracking on tables so that Data Manipulation Language (DML) and Data Definition Language (DDL) changes made to the tables can be captured. The following stored procedures help DBA to implement, monitor, and maintain change data capture:
Change Data Capture Dynamic Management Views
Database Administrators can monitor the change data capture process to determine if changes made are being written correctly and with a reasonable latency to the change tables. The following below mentioned DMV can be used to identify if there are any errors:
Change Data Capture System Functions
Change data capture records DDL & DML activity applied to SQL Server 2008 tables, supplying the details of the changes in an easily consumed relational format. Column information that mirrors the column structure of a tracked source table is captured for the modified rows, along with the metadata needed to apply the changes to a target environment. The following functions are used to return information about the changes:
4. sys.fn_cdc_get_column_ordinal ( ‘capture_instance’ , ‘column_name’ )
Change Data Capture SQL Server Agent Jobs
There are two SQL Server Agent Jobs which are created when CDC is enabled for a particular table.
Database Administrators can use this feature to monitor DDL and DML changes which are happening to tables.
Pages: 1 2