1

At the moment, I need to synchronize data between two SQL Server databases on separate servers but on the same network. As the schema between the tables that are to be synced differ, I've been developing a solution that utilizes a custom queue to transfer data. This involves triggers for each table that inserts the new/updated fields into a transfer queue table, to which a periodically run stored procedure via SQL Server Agent checks for entries, acts accordingly, then deletes those entries.

My question is whether or not it'd be more efficient to remove the transfer queue table and instead evoke the stored procedure via the trigger and transfer the data that way. Would the trigger simply call the stored procedure and complete or would it wait for the stored procedure to complete before exiting itself?

  • 1
    A neat solution for asynchronous transfer might be using the Service Broker. It already has queuing implemented. – sticky bit Jul 24 '18 at 23:00
  • 1
    Is queuing transactions for udpates & such with triggers a requirement to get duplicated DBs across SQL Servers instances on the "same network"? I think there are simpler methods that are much easier to maintain with less logic for a standby warm server, DR server—even same subnet—replicated instance, custom trans log backup/restore jobs, log shipping, etc. etc. etc. Don't want to add a comment answer, but for your particular needs for just two DBs, not sure why you can't replicate much easier and understand how to make the switch between the two for whichever is production.... easy appeasey!! – Pimp Juice IT Jul 25 '18 at 5:41
  • Don't reinvent the wheel, avoid using triggers to replicate all data between databases, it will make all operations slower and bring performance and inconsistencies. Use a replication method like Pimp mentioned, which are tested and working standard solutions for this problem. – EzLo Jul 25 '18 at 8:15
  • I wouldn't say that queuing transactions are a requirement, just something I've been testing to keep the triggers as "light" as possible and have the actual syncing done as a background job. The issue is that the data in the databases that need to be synced are not necessarily in the same tables and don't even share the same field names. For example, an insert in two tables of B should incur an insert of some of those fields in one table of A. I'm not familiar enough with SQL Server features to know if they solve this problem. If you're willing @PimpJuiceIT, I appreciate any help! – Julian Toya Angeles Jul 25 '18 at 22:12
  • Yeah, as per those requirements and inserting specific fields from some tables to others in another DB table it's not something I'm familiar enough with to be certain if other replication methods would suffice but I don't think those methods are robust enough for that scenario—they just replicate the tables and data from one DB to another and not the way you describe unfortunately—at least afaik. I have some innovative ideas but I cannot make suggestions without testing personally as I'm not even sure if they'd work. – Pimp Juice IT Jul 26 '18 at 2:15
1

To answer your question directly, the trigger will not finish until the SP finishes, meaning your DML statements that cause the trigger to fire will also not finish (until the fired trigger finishes). This may cause long duration of open transactions with locks everywhere.

So including SP inside trigger is a big NO. This can cause huge performance issue when your SP slows down for whatever reason. It has direct negative impact to your workload throughput.

Not sure about your sql server version, if your version is SQL Server 2008+, you may consider Change Data Capture as your solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.