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?
Best Answer
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.