August 25, 2006 at 4:54 pm
Greetings,
Here's the scenario I am beset with!
We have two databases, CDO and Global (on different servers), CDO is our main database for our department, Global, as you might imagine, is a global database for all departments.
We are currently attempting to move most of our data entry and table structures out of Global and into CDO database as there is dual-manual entry going on, as well as to allow more flexibility to customize the interface to our needs.
Unfortunately, we cannot just move all of our tables and data completely out of Global, as they still need certain data points from us, so we are looking at having to replicate from CDO back into Global. Since the table structure of CDO will be different, we will need to map the fields in CDO to Global.
I'm attempting to determine the best mechanics to facilitate the replication and would definitely appreciate any comments on them as this is my first time implementing something of this scale.
- Table Triggers: UI is connected to CDO database, new data hits a table, which has INSERT / UPDATE triggers that will INSERT / UPDATE information in Global essentially real-time. One worry about this method is that it seems as if it can get pretty resource intensive if the triggers are firing off a lot.
- Data Comparison / Sweeping Job: Setting up a SQL job to run every couple of minutes and query compare specific CDO Tables with specific Global tables, and move over all records with IDs not in Global. A couple of minute delay seems reasonable, but from my limited past experience, SQL jobs seem very finicky (this could also be due to the underlying SQL did not have enough error handling in it).
Are there any other methods we might be able to use to move over data?
A web services architecture has been mentioned, where we develop a web-service and allow the Global DBAs create an interface to call our webservice to update their tables when necessary, but at this point that seems might be a bit overkill.
This thread seems to suggest never to use triggers, will transactional replication be able to accomplish the task though?
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=300767#bm301458
Thanks kindly,
- Jeff
August 26, 2006 at 1:27 am
Transactional replication may serve you better. Triggers are OK unless you have heavy update/insert load.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply