{"id":2413,"date":"2022-02-28T08:00:00","date_gmt":"2022-02-28T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2413"},"modified":"2023-10-03T20:23:38","modified_gmt":"2023-10-03T17:23:38","slug":"ti-einai-to-change-data-capture-cdc-kai-pos-energopoite","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-einai-to-change-data-capture-cdc-kai-pos-energopoite\/","title":{"rendered":"What is Change Data Capture (CDC) and how is it enabled in SQL Server"},"content":{"rendered":"

Change Data Capture known as CDC<\/strong> records that DML<\/strong> (insert, update, delete) action is performed on a table of a database. These changes are recorded in a corresponding table that is created. Through system functions we can find the changes made to records.<\/p>\n\n\n\n

Which is used<\/h2>\n\n\n\n

The CDC <\/strong>it is very useful for ETL <\/strong>(Extraction, Transformation and Load) processes where we want a gradual loading of the data in a Data Warehouse. <\/p>\n\n\n\n

In a Data Warehouse we are interested in depicting the changes over time in a different format, so a complete copy of the data would be delayed and would not meet the needs of the application.<\/p>\n\n\n\n

However, it could also be used for necessities auditing <\/strong>but with the main disadvantage that it does not record SELECT <\/strong>which may take place.<\/p>\n\n\n\n

Step by step activation with example<\/h2>\n\n\n\n

We have a table with few entries:<\/p>\n\n\n\n

select * from customer;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To enable CDC on the database:<\/p>\n\n\n\n
USE db_test\nGO  \nEXEC sp_changedbowner 'sqlowner' --\u03a0\u03c1\u03bf\u03b1\u03b9\u03c1\u03b5\u03c4\u03b9\u03ba\u03ac\nGO\nEXEC sys.sp_cdc_enable_db;\nGO <\/pre>\n\n\n\n
We enable CDC in the panel we saw before:<\/p>\n\n\n\n
USE db_test  \nGO  \n  \nEXEC sys.sp_cdc_enable_table  \n@source_schema = N'dbo',  \n@source_name   = N'customer',  \n@role_name     = N'cdc_viewer',  \n@filegroup_name = null,  \n@supports_net_changes = 1  ;\nGO  \n<\/pre>\n\n\n\n
As soon as we activate it we will see that some have been created system boards<\/strong> (01) in the base, two new Jobs in SQL Server Agent<\/strong> (02) and finally one or two functions <\/strong>(03) (depending on the parameter you will read below).<\/p>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
In the parameter @role_name<\/code> we define the role * that we want to give to users to have access to the data (of course they must also have right SELECT <\/strong>in the source table). If this parameter is not set then the data will be readable only by whoever has the role sysadmin <\/strong>or is db_owner<\/strong> on the basis. <\/p>\n\n\n\n
*If the role does not exist, it is created automatically.<\/em><\/p>\n\n\n\n
With the parameter @supports_net_changes = 1<\/code> we declare that we want the function to be created as well cdc.fn_cdc_get_net_changes<\/a>. This allows us to see the records that changed in a certain time period (it returns a unique change for each change in that time period).<\/p>\n\n\n\n
Let's do some DML actions (insert, update, delete) on the table:<\/p>\n\n\n\n
insert into db_test..customer values\n('Marios','Georgopoulos','Male','209285915');\n\nupdate  customer set sex = 'Female' where id = 1;\n\ndelete from customer where id = 3;<\/pre>\n\n\n\n
To find the changes<\/h2>\n\n\n\n
To see the changes that have occurred in the table, Microsoft suggests the use of functions cdc.fn_cdc_get_all_changes<\/a> and cdc.fn_cdc_get_net_changes<\/a> that are created.<\/p>\n\n\n\n
With the cdc.fn_cdc_get_all_changes<\/a> we can see all the changes that have happened:<\/p>\n\n\n\n
DECLARE @from_lsn binary(10), @to_lsn binary(10);\nSET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_customer') \nSET @to_lsn   = sys.fn_cdc_get_max_lsn(); \n\nSELECT * FROM [cdc].[fn_cdc_get_all_changes_dbo_customer](@from_lsn, @to_lsn, N' all update old');  \nGO  <\/pre>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
We see the changes we made before along with some extra columns with metadata. With the column $start_lsn<\/code> we are told the commit LSN that performed the transaction. With the $seqval<\/code> we can enqueue changes made in the same transaction. THE $update_mask<\/code> reports in bits the columns that changed in case of update.<\/p>\n\n\n\n
In $ operation<\/code> report on the type of change made. More specifically:<\/p>\n\n\n\n