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.
| 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_timesys.fn_cdc_map_time_to_lsnYou 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. |
 |
|
|
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. |
|
|
|
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. |
|
|
|
|
|
|
|
|
CDC and Performance