Good day @Nimai Ahluwalia
What are the impacts on the Cost and Memory when we apply CDC on SQL or Azure?
The impact on Azure SQL Database is an error massage, since Change Data Capture is not supported in Azure SQL Database
The error returned was 22845: 'Cannot enable change data capture in this edition of SQL Server.'. Use the action and error to determine the cause of the failure and resubmit the request.
Change Data Capture is supported on SQL Server and Azure SQL Managed Instance
----------
My 2 cents about performance
Change Data Capture is based on the data which is stored in the transaction log. This make the procedure reliable (not like using triggers for example). CDC reads the log and adds information about changes to the tracked table's associated change table. This means that we have latency between the time that the data is changed and the time that the change appears within its associated change table.
There are four parameters you can use to control the performance using the sys.sp_cdc_change_job stored procedure. You can read more in this document.
(1) If latency becomes too high, then try to increase maxscans and/or maxtrans
(2) Specifying the @captured_column_list parameter using sys.sp_cdc_enable_table and track only the columns taht you need! Do not track all the columns in the table as in default configuration.
(3) Disable net changes if not needed by setting @supports_net_changes to 0 when you use sys.sp_cdc_enable_table
(4) Remember the UPDATE cost twice than INSER or DELETE since it stores 2 rows and acted as DELETE+INSERT
(5) Monitor the size of the log file using DBCC SQLPERF(LOGSPACE) ! Remember that when CDC is enabled, then operations that would normally be minimally logged are fully logged
(6) manual clean up the CDC if really needed as explain here.
Note! In general CDC can impact IO since it write a lot of "extra" data, it might impact the size of the transaction log a lot, It can lead to locks and it might raise the memory use... and yet it is considered as very useful and with minimal impact (if it is needed) since it is based on the transaction log and the management actions are done in the background.
