Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 CDC and Performance

Author  Topic 

asqldeveloper
Starting Member

17 Posts

Posted - 2013-08-22 : 08:00:21
We have about a lot of tables where we consider them to hold PII and PHI information. The security team wants to track any changes at column level.I know we can achieve this using CDC. We have a software which queries this CDC system tables and logs them. Unfortunately all it can do it send a timestamp and say give me all changes from this timestamp.

Questions:
1. If we are capturing CDC on about 150 tables out of about 400 tables, what is the performance impact on that? Granted, some of these out of 150, are frequently updated.

2. I cant find anywhere how to get changes only based on a timestamp. I know it has beginlsn, endlsn and other functions, but this doesnt help me to query based on timestamp. I was thinking of creating a table and store all the lsn for each table, so that way I know when was the last record sent from each cdc table, is this the only way? Any other approaches?

Any suggestions/recommendations are welcome ASAP. Thanks in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-08-22 : 15:34:32
There are functions to convert LSN to timestamp and vice versa:

sys.fn_cdc_map_lsn_to_time
sys.fn_cdc_map_time_to_lsn

You can use them with cdc.fn_cdc_get_all_changes_ or cdc.fn_cdc_get_net_changes_ to retrieve changed data, and log the associated time and LSN like you suggested.
Go to Top of Page

yelouati
Starting Member

10 Posts

Posted - 2013-08-23 : 20:36:57
Well CDC is certainly faster than triggers. The only way to gage whether the performance hit is acceptable or not is to run a load test.

While CDC is great, please read up on the limitations.

As far as the lsn to time conversion, I suggest you do not use the function but join against the cdc lsn/time table.
Go to Top of Page

yelouati
Starting Member

10 Posts

Posted - 2013-08-23 : 20:37:36
Well CDC is certainly faster than triggers. The only way to gage whether the performance hit is acceptable or not is to run a load test.

While CDC is great, please read up on the limitations.

As far as the lsn to time conversion, I suggest you do not use the function but join against the cdc lsn/time table.
Go to Top of Page
   

- Advertisement -