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:
Use ChangeDataCapture
Go
Create table Currency
(
CurrencyKey Int Identity(1,1) Primary Key NOT NULL,
CurrencyAlternateKey varchar(5),
CurrencyName varchar(25)
)
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:
Use ChangeDataCapture
Go
EXEC sp_cdc_enable_table ‘dbo’, ‘Currency’, @role_name = NULL, @supports_net_changes =1
Go
4. Execute the query below to check whether the table is enabled for CDC:
Use ChangeDataCapture
Go
Select [name], is_tracked_by_cdc from sys.tables
GO
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:
Use ChangeDataCapture
Go
EXEC sys.sp_cdc_disable_table ‘dbo’, ‘Currency’, ‘all’
Go
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.
Use ChangeDataCapture
Go
EXEC sys.sp_cdc_disable_db
Go
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:
| Table Name | Description |
| 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:
1. sys.sp_cdc_add_job
2. sys.sp_cdc_change_job
3. sys.sp_cdc_cleanup_change_table
4. sys.sp_cdc_dbsnapshotLSN
5. sys.sp_cdc_disable_db
6. sys.sp_cdc_disable_table
7. sys.sp_cdc_drop_job
8. sys.sp_cdc_enable_db
9. sys.sp_cdc_enable_table
10. sys.sp_cdc_generate_wrapper_function
11. sys.sp_cdc_get_captured_columns
12. sys.sp_cdc_get_ddl_history
13. sys.sp_cdc_help_change_data_capture
14. sys.sp_cdc_help_jobs
15. sys.sp_cdc_restoredb
16. sys.sp_cdc_scan
17. sys.sp_cdc_start_job
18. sys.sp_cdc_stop_job
19. sys.sp_cdc_vupgrade
20. sys.sp_cdc_vupgrade_databases
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:
1. sys.dm_cdc_log_scan_sessions
2. sys.dm_cdc_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:
1. cdc.fn_cdc_get_all_changes_<capture_instance>
2. cdc.fn_cdc_get_net_changes_<capture_instance>
3. sys.fn_cdc_decrement_lsn
4. sys.fn_cdc_get_column_ordinal ( ‘capture_instance’ , ‘column_name’ )
5. sys.fn_cdc_get_max_lsn
6. sys.fn_cdc_get_min_lsn
7. sys.fn_cdc_has_column_changed
8. sys.fn_cdc_increment_lsn
9. sys.fn_cdc_is_bit_set
10. sys.fn_cdc_map_lsn_to_time
11. sys.fn_cdc_map_time_to_lsn
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.
Conclusion
Database Administrators can use this feature to monitor DDL and DML changes which are happening to tables.
]]>