April 6, 2015 at 6:58 pm
Hello everyone, I hope you all had a good Easter breaks.
I'd like to get a little advice on using CDC to keep another database in synch. My goal is to use CDC (or Change Tracking, I do not need the history, just the current rows) enabled on a database, and just apply the updates etc. to another identical database. I understand that an initial load to an empty database needs to a happen. The database that I need to update will have data in it already as it is a restored backup of the source on another SQL instance.
Would it be possible to pull all the changes made that day (on a daily basis), and apply them straight to the other database? Would I need to have the inserts/deletes/updates produced, then use them in an Execute SQL task to update the other database?
I have read up on some of the system defined functions, there is one called cdc.fn_cdc_get_all_changes_<capture_instance>
if this is ran, does it get all the changes that occurred on all table in the database, or would I have to write this for every table that was configured for CDC in the database? I'd also like to understand in what format it is returned, is returned as a .sql script consisting of all the inserts/updates, and deletes, like you'd expect from tablediff output?
Or would I need to use an empty staging database and use the import/export wizard to apply changes to the second database.
I am using 2008 R2, so I do not have the extra SSIS tasks you get with 2012. I interested in knowing more but the articles I am reading are not going into the nitty gritty of using the output to update another DB, just turning it on and configuring it, but nothing for after that.
I'd appreciate any help or info that can be given. Thank you for reading.
Regards,
D.
April 7, 2015 at 3:11 pm
Start here.
If you do not use ssis 2012, you need to compose your own sql to do the incremental merge.
April 7, 2015 at 6:25 pm
Hello,
Thanks for getting back, I had in fact already come across this article, as it would be a temporary measure, I may go with log shipping or T-replication.
Regards,
D.
April 7, 2015 at 6:26 pm
Hello,
Thanks for getting back, I had in fact already come across this article, as it would be a temporary measure, I may go with log shipping or T-replication.
Regards,
D.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply