CDC - SQL Server Change Data capture DDL Changes.

Posted on 2012-08-22
Medium Priority
Last Modified: 2012-08-27

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

Question by:codedeveloper1
1 Comment
LVL 13

Accepted Solution

dwkor earned 1000 total points
ID: 38324970

1. You can set the retention for up to 100 years. Hopefully it's big enough retention interval.

2. If I architected solution based on CDC I'd do the following. First, I would not use CDC tables to keep audit data. I'll move the data to another set of the tables (perhaps at the future even to the new server). I would do it in background based on some schedules (it also would solve the problem with retention you asked above). Next, I'd implement DDL trigger for ALTER TABLE statement and send the service broker message when table is altered. In the activation proc I'll process the "backlog" (data which has not been moved from CDC to permanent tables) and recreate the captured instances.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question