Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am constructing a database that needs to keep track of historical data. It is a Electronic medical system. All data needs to be updated in real time so the user can see what changes happened in the application.

Would CDC be the way to go or should I just have triggers on the database that pushes the information into a Audit tables

share|improve this question

closed as too localized by Jon Seigel, Paul White, Max Vernon, Mark Storey-Smith, RolandoMySQLDBA Jun 24 '13 at 18:49

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
Doesn't your previous question answer this one? Presumably the application should not be wiping out historical data if you want to do schema updates. –  Jon Seigel Jun 22 '13 at 20:14
    
CDC is used more for auditing purposes, not to keep track of a lot of data version changes. –  StanleyJohns Jun 24 '13 at 17:43
    
Depends on what you want to do. CDC is used more for auditing/data security purposes. If this is what you need, then CDC is better than using triggers. –  StanleyJohns Jun 24 '13 at 18:05
    
You say "All data needs to be updated in real time so the user can see what changes happened in the application." CDC is not real time! –  Ian Ringrose Apr 1 at 10:49

1 Answer 1

There is no definitive answer here because each solution has its own pros and cons.

CDC

  • Easier to setup and you don’t need to worry about creating triggers and storing trigger data

  • Available only in SQL Server Enterprise edition so price may be an issue

Triggers

  • More flexibility to adopt this to your needs (you can audit sql
    server users, applications and more…)
  • More management and planning will be required upfront. You need to design the triggers, figure out how to store history data, how to do reporting and probably more…

One of these two is enough – you don’t need to implement both.

If you decide to use triggers I’d recommend you find a third party tool that can generate triggers for you, create reports and such. I know that ApexSQL Audit can do this but note that it’s a premium tool (has a free trial though).

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.