CDC - SQL Server Change Data capture DDL Changes.
Posted on 2012-08-22
I have proposed CDC as an auditing solution to my company software. It is an MVC application that uses web API feature. I have following conditions that i need to meet in order to use CDC as an auditing tool.
1. We want to keep audited data forever or atleast up to our will. We dont want to delete the captured data. There is a CDC clean up job that deletes data after certain time period. Can we stop that service or is there an alternative that we can do to preserve captured data.
2. There is a pretty good chance that there will be many ddl changes to a table. Currently if a new column is added to a table that is already running CDC. The newly added column in source table is manually captured on a second or different captured instance and as SQL server can run only 2 captured instances of a single table what would happen if there is one more column added to the source table needs to be captures. Do we have to drop any one of existing captured instances ? If dropping any of exisiting captured is the solution then what will happen to audited data of previosuly captured instances. It is really important that none of the captured data is lost.
Please advise on both points asap. It is really a very high priority issue for me..