SQL Server Performance Forum – Threads Archive

Data Replication Scripts vs. Triggers

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. Thanks kindly, – Jeff

For such operations I would go with replication only, as triggers are bad at sometimes giving lot of issues. As you said tried the SQL Jobs in this case, can you check why those are having such performance issues. As they seems to be easy to manage for data updation on the main server if the usage is limited or time-bounded. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

]]>