EDIT: Added some comments from the stuff I was able to find out, in case future onlookers are interested.

I'm completely new to CDC, and am in the process of implementing it to a large DB for the first time. So I'd like a few questions answered I couldn't find through googling around.

In short, the idea is to replace our old triggers by using CDC to audit the full history of all changes to dozens of tables. The DB is very large, so the solution needs to be as optimized as possible, and the changes need to be logged in almost real-time. Maintainability for future DDL changes is also a big consideration, since some of the tracked tables will be modified every now and then. We don't want to spend hours each time just patching, testing etc. all the customized solutions to support the new changes.

Currently the AUDIT log tables store only the tracked table's columns, plus __$start_lsn and __$operation. I created a job to run every 5 minutes, where we fetch the following:

DECLARE @begin_time DATETIME, @end_time DATETIME, @begin_lsn NVARCHAR(256), @end_lsn NVARCHAR(256);
SELECT @begin_time = DATEADD(HOUR, -1, GETDATE()), @end_time = GETDATE(); 
SELECT @begin_lsn = CONVERT(NVARCHAR(256), sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time), 1);
SELECT @end_lsn = CONVERT(NVARCHAR(256), sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time), 1);

These values are then used with the fn_cdc_get_all_changes_ function in a dynamically generated SQL which will modify and insert all results into the AUDIT log where the combination of __$start_lsn, __$operation and PK value don't yet exist.

In addition, I'm going to edit the cdc_cleanup job to add a new first step making sure that any data older than the default 72 hours, will be checked and moved to the AUDIT log (if required), making sure that in case of job failures etc, the CDC_tables will never be cleaned of data that's not yet been recorded.

Now, in case something went wrong there, please let me know.

My questions about this process are as follows:

  1. Is the combination of __$start_lsn, __$operation and PK value reliable enough to make sure no records are omitted? Isn't it theoretically possible that two simultaneous edits to the same row started at the same time, but the other transaction was still ongoing and not logged when the AUDIT job picked up the records from CDC? Basically, this would mean that the records shared identical identifying __$start_lsn values.

EDIT: Found out that __$start_lsn is indeed unique per each transaction. While it is sort of a virtual timestamp, even in the case of absolutely simultaneous transactions, the value is still increased by one for the other to ensure that the transactions show uniquely.

  1. What's the maximum length of BINARY(10) datatype displayed in NVARCHAR form? I seriously don't understand how that hexadecimal string is formed. I assume 256 characters is enough but I'd rather not go overboard.

  2. Considering the need for performance, any ideas to make this faster? The whole point with the __$start_lsn comparison is to avoid pointless table scans when basically I just want to replicate everything that's logged to the CDC tables, to these AUDIT tables albeit formatted somewhat for compatibility with our softwares and requirement specs.

EDIT: By comparing the CDC-table's __$start_lsn to lsn_time_mapping table (use functions if appropriate), you can see that the __$start_lsn doesn't actually signify the start time of the transaction, but the stamp from when the transaction ended . That way, you can for example fetch the MAX last moved __$start_lsn from your AUDIT table, use sys.fn_cdc_increment_lsn function to increment that by one, and then use the CDC functions to fetch all data equal to or newer than that lsn. And no data can exist in between, so you will always get all data since the last time you fetched it. This pretty much reduces pointless scans and general overhead to a minimum.

As always, thanks for all the help and suggestions! The web is filled with tutorials on how to enable CDC and query for data, but there's really nothing there about how to actually use it for business requirements in a large, performance critical DB.

  • 1
    Before you go too deeply down this path, I would suggest making sure CDC meets your needs for schema changes to the tables you're auditing. – Jon Seigel Jul 11 '14 at 13:57
  • Not sure I understand what you mean here. The changes are nothing dramatic, simply removing, adding and modifying existing columns based on software version requirements. – Kahn Jul 14 '14 at 6:59
  • 1
    See this question: stackoverflow.com/questions/14958119 – Jon Seigel Jul 14 '14 at 13:36
  • Hey thanks! I had bumped into that same post while trying to find out more, but I hadn't actually read the link referred to in that post. Lots of useful information there. Also, I've now tested sp_cdc_disable_table, and with that there's no problem regarding schema changes. Just need to drop the CDC-tracking for specific tables, make all adjustments to the other AUDIT objects, and re-enable CDC for the table to automatically update CDC to the currently active table schema. Thanks a lot for the help here! – Kahn Jul 15 '14 at 6:53
  • 1
    Hopefully you noticed that sp_cdc_disable_table drops the change table... Also, note that changes show up in there asynchronously. You'll need to validate that all the current changes have been audited before the schema change process is started, or there might be an auditing gap. – Jon Seigel Jul 15 '14 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.