Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Difference in CDC vs Trigger Expand / Collapse
Author
Message
Posted Tuesday, November 20, 2012 12:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 336, Visits: 1,147
What is the Difference in CDC vs Trigger?
and which one is performance wise a good one to go.

Thanks!

Post #1386707
Posted Tuesday, November 20, 2012 3:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:14 AM
Points: 167, Visits: 682
Change Data Capture (CDC) is leveraging the changes applied to the logs as opposed to the actual database, as a result it will "no" impact on your DB performance so it's "better".
One of the downside is that you have to enable it (per DB, per table if memory serves me well) and then get your process up and running.
Triggers will have an impact on the DB performance, due to resources sharing, locks, etc.

CDC is the way to go.

Links:
http://channel9.msdn.com/Blogs/ashishjaiman/CDC-Change-Data-Capture-SQL-Server-2008
http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-(cdc)-in-sql-server-2008/
http://www.sqlservercentral.com/articles/Change+Data+Capture+(CDC)/64289/
http://www.databasejournal.com/features/mssql/implementing-sql-servers-change-data-capture.html

HTH,

B
Post #1386771
Posted Tuesday, November 20, 2012 5:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:06 AM
Points: 1,947, Visits: 3,215
Although note if you want to capture information like who changed the data, CDC can't do that.
Post #1386868
Posted Tuesday, November 20, 2012 7:58 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
CDC is A way to go, not necessarily the way to go.

It has some limitations. And, as far as having zero impact, that's partially correct. It doesn't extend the duration of row-locks, but it does require CPU cycles and I/O bandwidth. So it's not really "zero impact", but the impact is often lower than comparable triggers.

As already mentioned, it has limits on being able to do things like capture the user ID, application, connection string, SPID, etc. If all you need is an audit trail of data changes, then CDC can be excellent for this.

CDC also has some limitations with regards to replication, recovery models, and disallows things like truncates on logged tables (note that triggers can't capture truncates either). It also requires that SQL Agent be running (to enable scheduled log-scans). Triggers don't have most of those limitations. (They have other limitations, of course.)

CDC also won't automatically add new columns to a log, while it's relatively easy to set up a DDL trigger to modify a logging trigger. Not an issue if you don't add columns often enough for that to matter, but it can catch people by surprise.

So, which one is better really depends on the purpose of the audit log, and the requirements of the database being audited. If you need Simple Recovery, for whatever reason, then CDC won't do it, but triggers will. If you need zero-impact on locking, then CDC can do that but triggers can't. And so on.

Define the purpose of the audit trail, and you'll get a lot closer to defining which one is better for your needs.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1386929
Posted Friday, August 8, 2014 12:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:48 AM
Points: 9, Visits: 379
If you need Simple Recovery, for whatever reason, then CDC won't do it

Just a correction for what it is worth: CDC is supported with Simple Recovery.
Post #1601312
Posted Monday, August 11, 2014 2:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
If Change Tracking is enough for what you need it, use it, as it's less overhead than either CDC or a trigger. And it doesn't require Enterprise Edition.

SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1602063
Posted Tuesday, August 12, 2014 5:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 54, Visits: 70
CDC is easier to setup and it is only available in SQL Server enterprise edition. CDC do not require creating triggers and storing data.

Triggers are more flexible as user can audit server users and applications etc. Triggers require more planning and management to design triggers and how to store them.
Post #1602222
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse