{"id":12321,"date":"2019-05-19T20:46:51","date_gmt":"2019-05-19T20:46:51","guid":{"rendered":"https:\/\/dbtut.com\/?p=12321"},"modified":"2019-05-19T20:46:53","modified_gmt":"2019-05-19T20:46:53","slug":"change-data-capturecdc-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/05\/19\/change-data-capturecdc-in-sql-server\/","title":{"rendered":"Change Data Capture(CDC) in SQL Server"},"content":{"rendered":"
Change Data Capture (CDC) is a technology that can be used in incremental data transfers from OLTP systems to data warehouse systems. By storing the insert, update, and delete operations, it stores the first and last version of the record in a change table. This table where the changes are stored is called a change table.<\/p>\n
It then reflects these changes to the destination that you want to transfer. We can do similar operations using trigger, but since CDC takes the changes from the log file, it both works more efficiently and makes our processes considerably easier.<\/p>\n
Let’s continue with an example as we always do.<\/p>\n
Check CDC Enabled Databases<\/h2>\n
First of all, with the help of the following script, let’s check if there is a database on the instance where CDC is enabled.<\/p>\n
USE master \nGO \nSELECT [name],is_cdc_enabled FROM sys.databases \n<\/pre>\n
Enable CDC<\/h2>\n
Then, enable the CDC for the database we want to use the CDC with the help of the following script.<\/p>\n
USE [AdventureWorks2014] \nGO \nEXEC sys.sp_cdc_enable_db \nGO\n<\/pre>\n
After running the above script, a schema named cdc and some tables will be created in this schema. The list of these tables is as follows. You can see these tables under the system tables in the database in which the CDC is enabled.<\/p>\n
\n
\n\n
\n
cdc.captured_columns<\/td>\n
This table stores the list of captured columns<\/td>\n<\/tr>\n
\n
cdc.change_tables<\/td>\n
This table stores the list of captured tables<\/td>\n<\/tr>\n
\n
cdc.ddl_history<\/td>\n
This table stores the history of all DDL operations after the CDC is enabled<\/td>\n<\/tr>\n
\n
cdc.index_columns<\/td>\n
This table stores the changes of the indexes of the columns captred with CDC.<\/td>\n<\/tr>\n
\n
cdc.lsn_time_mapping<\/td>\n
This table stores the change and time of transactions<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
List CDC Enabled Tables<\/h2>\n
CDC can be enabled based on the table in the database. You can use the following script to list the tables where the CDC was enabled.<\/p>\n<\/div>\n
USE [AdventureWorks2014] \nGO \nSELECT [name]\nFROM sys.tables\nwhere is_tracked_by_cdc=1\n<\/pre>\n
Enable CDC on a Table<\/h2>\n
You can enable CDC on a table with the help of the following script<\/p>\n
If the SQL Server Agent service is not started, you will receive a message as follows. To avoid this error, you must start the SQL Server Agent service. The reason you receive the error is that the CDC creates two jobs and the SQL Server Agent Service is not started, so these jobs cannot be executed. After starting the service, you don’t need to run the script above again.<\/p>\n
SQLServerAgent is not currently running so it cannot be notified of this action.<\/em><\/span><\/p>\n
After running the script, you will see that the CDC creates a new table named “cdc.dbo_Address_CT” under the system tables. In this table, you will see that there are some extra columns except the columns of the “dbo.Address” table.<\/p>\n
<\/p>\n
<\/p>\n
Check if the CDC is working correctly<\/h2>\n
Perform an update as follows to check if the CDC is working correctly.<\/p>\n
USE [AdventureWorks2014]\nGO\nUPDATE [dbo].[Address]\n SET [City] = 'Ankara'\nWHERE City='Seattle' AND AddressID=23\n<\/pre>\n
Check whether the change is reflected to the “change table”.<\/p>\n
USE [AdventureWorks2014]\nGO\nSELECT * FROM [cdc].[dbo_Address_CT]\nGO\n<\/pre>\n
The values you will see in the _$operation column are as follows.<\/p>\n
\n
\n\n
\n
1<\/td>\n
Delete Process<\/td>\n<\/tr>\n
\n
2<\/td>\n
Insert Process<\/td>\n<\/tr>\n
\n
3<\/td>\n
Before Update<\/td>\n<\/tr>\n
\n
4<\/td>\n
After Update<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
When you enable CDC with the script I shared above on the table, it monitors and logs the changes made to all columns in the table. If you want to do this for specific columns in the table, you need to add the columns you want with the @captured_column_list parameter to the end of the script.<\/p>\n
A function called “fn_cdc_get_all_changes_dbo_Address” has been created under the database when the CDC is enabled. You can also get specific results by querying this function. Example use;<\/p>\n
USE AdventureWorks2014\nGO \nDECLARE @start_time DATETIME, @finish_time DATETIME, @first_lsn BINARY(10), @last_lsn BINARY(10); \n--We set the start time to yesterday to list changes in the last 1 day.\nSELECT @start_time = GETDATE()-1, @finish_time = GETDATE(); \n--You can type smallest greater than or equal instead of smallest greater than.\nSELECT @first_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @start_time);\n--You can type largest less than instead of largest less than or equal.\nSELECT @last_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @finish_time); \nSELECT * \nFROM cdc.fn_cdc_get_all_changes_dbo_Address(@first_lsn,@last_lsn,'all') \n<\/pre>\n
Disable CDC<\/h2>\n
Finally, you can disable CDC as follows.<\/p>\n
USE [AdventureWorks2014];\nGO\nEXECUTE sys.sp_cdc_disable_table \n @source_schema = N'dbo', \n @source_name = N'Address',\n @capture_instance = N'dbo_Address';\nGO\n--If you don't want to disable it on a database basis, don't run the following script.\nEXEC sys.sp_cdc_disable_db \nGO<\/pre>\n
You can access Capture Instance by running the following stored procedure.<\/p>\n
sys.sp_cdc_help_change_data_capture<\/p>\n
After enabling the CDC, you can feed dataware house applications with SSIS packages. Below you will find examples.<\/p>\n
<\/p>\n