SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 CDC and Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asqldeveloper
Starting Member

17 Posts

Posted - 08/22/2013 :  08:00:21  Show Profile  Reply with Quote
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

USA
15678 Posts

Posted - 08/22/2013 :  15:34:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 08/23/2013 :  20:36:57  Show Profile  Reply with Quote
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 - 08/23/2013 :  20:37:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000