October 24, 2008 at 3:52 am
Good article... Well explained ...
October 24, 2008 at 8:08 am
Hi,
Good article, clean and simple.
Another option to audit data changes, which is quite simple and done from outside the server, is this tool: (free)
http://www.nobhillsoft.com/DataProfiler.aspx
doesnt require any SQL configuraiton, works on all versions of SQL server, offers clear GUI to see the changes, and generates scripts to roll back between versions.
January 14, 2009 at 2:25 pm
Is there any way to set up the change tracking tables - the ones that are created with a suffix "-CT" in a separate database?
We have a high transaction environment and the _CT tables can grow to enormously large tables and if they are in the same database, the backup sizes become huge.
Any solution that we can think of?
We can have these tables in a separate file groups, but we take transaction log backups for log shipping and we also need to take full database backups.
January 15, 2009 at 6:27 am
No, the tables have to be created in the same database. You can adjust how long the data stays in there (I can't remeber off the top of my head how this is done but I remember seeing it somewhere.) another option is to have a process that offloads that data into another database so your ct tables are really just a temporary storage place. If you offload the data prior to the full backups, you would keep the size of the full backup low.
January 17, 2009 at 7:59 am
Is it possible to find who changed the data in CDC ?
Login id or host name something like that ?
January 20, 2009 at 11:21 am
CDCUserName should be in each cdc table. This is the sql/windows login used at the time of the change.
April 9, 2009 at 2:01 pm
There's also a video on the SQL Server "How Do I?" Videos page called Provide an Up-to-Date Data Warehouse with Change Data Capture and hosted by Scott Golightly. After showing how to configure Change Data Capture on a database, Scott shows a sample SSIS package that extracts the changed data from the database. These tutorials are a favorite of mine - short and to the point.
April 10, 2009 at 7:02 am
Thanks for the link to the videos.
May 6, 2009 at 9:42 am
Thanks for the article. We plan to ETL the CDC data on a nightly basis. Nevertheless, I'm put off by the fact that we can't add columns to tables without disabling/re-enabling CDC. Disabling of course drops the cdc_ table, so goodbye data.
It would be nice if CDC could pick up the new column. DDL changes like this can now be propagated to replication subscribers; it would be nice if CDC could as well.
And what is up with not being able to reindex a CDC-enabled table?:angry:
May 6, 2009 at 4:01 pm
And what is up with not being able to reindex a CDC-enabled table?
I'm a bit puzzled by this. I have CDC enabled tables and I can both rebuild and reorganise the indexes on them. I guess it depends how you perform reindexing. CDC does not let you drop the existing PK as it is used for CDC. If your reindexing scripts go through and actually drop and recreate indexes then this will not work with CDC enabled tables (same applies for transactional replication for that matter), but that is no longer a recommended method to rebuild indexes. However if you use "alter index rebuild/reorganize" functionality this works without an issue with CDC enabled. Maybe I'm missing something though.
Cheers
Roddy
May 7, 2009 at 7:18 am
However if you use "alter index rebuild/reorganize" functionality this works without an issue with CDC enabled.
You are correct sir!! I was using old syntax. Thanks!
May 29, 2012 at 3:15 pm
We have two tables with triggers already on them. Each table has three triggers, two of which are "Instead of" triggers. Can we still do CDC on these tables?
Joe
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply