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:
- 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.
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.
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.