Firstly,
Change data capture is available only on the Enterprise, Developer,
and Evaluation editions of SQL Server.
So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions. (As the spec includes "multiple future applications" this may be an real issue for you)
Unlike triggers it is not real time, this is both an advantage and a disadvantage. Using triggers always slow down an update.
I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change.
However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.
If you are restoring a CDC-enabled database onto a different instance, you must ensure the server principal that "owns" the database in question is present on the target instance prior to performing the restore. You must also ensure you use the KEEP_CDC option when restoring the database.
From the MSDN page on restore options:
KEEP_CDC should be used to prevent change data capture settings from being removed when a database backup or log backup is restored on another server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted.
Restoring the database with KEEP_CDC will not create the change data capture jobs. To extract changes from the log after restoring the database, recreate the capture process job and the cleanup job for the restored database. For information, see sys.sp_cdc_add_job (Transact-SQL).
For information about using change data capture with database mirroring, see Change Data Capture and Other SQL Server Features.
I just restored a test CDC-enabled database onto my SQL Server 2012 instance that was created on my test SQL Server 2008R2 instance, without creating the login that owns the database on the 2012 instance. An error was shown during the restore:
Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262
Could not update the metadata that indicates database CDCTest is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_MScdc_ddl_database triggers] 'drop''. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.
The login must also be a member of the sysadmin server role prior to restoring the CDC-enabled database.
You will need to re-create the owner of the database in the target instance using the same SID if the owner is a SQL login (if the owner is a Windows login, the SID will naturally be the same if the Windows login is a domain-member).
You'll need to perform something like the following to restore your database:
/*
obtain the identify of the login that "owns" the database by looking at the
UserName column in the output from the following command:
*/
RESTORE HEADERONLY FROM DISK = 'D:\SQLServer\Temp\CDCTest.bak'
/*
This login is the owner of the database
*/
CREATE LOGIN CDCTestLogin
WITH PASSWORD = 'LozierPituophisUnconsciousShelduck4'
, SID = 0x2ECDACB721D7E84E8A28DCFE1C758799;
/*
Ensure the login is a member of the 'sysadmin' server-level fixed role.
*/
EXEC sp_addsrvrolemember @loginame = 'CDCTestLogin', @rolename = 'sysadmin';
GO
/*
Restore the database, with the KEEP_CDC option
*/
RESTORE DATABASE CDCTest FROM DISK = 'D:\SQLServer\Temp\CDCTest.bak'
WITH MOVE 'CDCTest' TO 'D:\SQLServer\MV2012\Data\CDCTest.mdf'
, MOVE 'CDCTest_log' TO 'D:\SQLServer\MV2012\Logs\CDCTest_log.LDF'
, REPLACE
, KEEP_CDC;
Best Answer
Instead of using triggers on the CDC tables, you should use the built-in CDC functions for returning the changes recorded by CDC. If you only need the latest value for each row, use cdc.fn_cdc_get_net_changes but if you need all intermediate changes as well, use cdc.fn_cdc_get_all_changes.
These functions are created for each table (or 'capture instance') that you enable CDC on and the functions return changes on a table from the earliest LSN to the latest LSN. There is a good article here that delves into CDC and retrieving the data.
To use it in your periodic polling process, you could do something like this:
You could even use SQL Server Integration Services which has built-in tools for working with CDC data.