CDC - SQL Server Change Data capture DDL Changes.

Posted on 2012-08-22
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


    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

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

         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 …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now