January 2, 2014 at 5:33 am
Hi,
Could someone, suggest me which would be the best to Implement for tracking the DML Activites and DATA changes,
Either CDC or Any DDL Triggers.
More Details:
Database Size: 102,400 Kilo Bytes (Only a Small DB)
FileGrowth:
compatibility_level:90
collation_name:SQL_Latin1_General_CP1_CI_AS
user_access: 0
state: ONLINE
recovery_model: SIMPLE
page_verify_option: CHECKSUM
is_auto_create_stats_on:1
is_auto_update_stats_on:1
is_fulltext_enabled:1
is_broker_enabled:0
log_reuse_wait:0
log_reuse_wait_desc:NOTHING
is_cdc_enabled:0
is_encrypted:0
replica_id: NULL
No:of Tables: Around 20
No:of Procedures: Around 30
Thanks,
Prabhu
Thanks,
Prabhu
January 2, 2014 at 6:00 am
Here's a good explanation on the topic:
http://www.sqlservercentral.com/Forums/Topic1386707-391-1.aspx
Read the last post from GSquared.
Mark
January 2, 2014 at 7:37 am
In general, CDC is a good way to track data changes over time. It does add overhead to the system, but it has fairly reliable performance. Whereas, you have to write the triggers. And you have to get them right or you can miss data, cause performance headaches, all sorts of things. But, as Gus said in his response to the other question, it really depends on what you're auditing and why to determine which solution is best for you. Be aware of the possibility of tracking query calls (including parameter values) by capturing them using extended events. It's another method for auditing, again, depending on what you're attempting to audit and why.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2014 at 5:13 am
Hi Grant,
Happy to see you in my post, my requirement is simple, just need to pull down the changes happens with the rows and to dump into someother history table.
Note:
DB size is very small and No: of tables also very less.
my point of doubt is, if the CDC features has been implemented with the production environment, then is it advisable or not. because I am completely new to CDC. kindly get me solution considering the note point.
Thanks,
Prabhu
January 3, 2014 at 5:16 am
If your needs are small, I'd go with CDC. It's easy to set up. Easy to maintain. No worries, especially on a small system as you say you have here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2014 at 6:36 am
Thanks Grant...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply