Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server - How to synchronize data from a table between different databases using Trigger

Post Views 3,046 views
Reading time 4 minutes

Hey guys!
In this post today I'm going to bring a very simple solution, but that solved a problem of a person who asked me for help to synchronize data from a table between different bases, in the same instance, in the simplest possible way, where you insert, update or deletes data from the source table and all changes are replicated to a mirror table in another base.

As the focus of the solution is to be fast, simple, work in any version and edition of SQL Server, not requiring a Job to keep updating, having the data updated almost in real time and there are only a few tables instead of the entire base, I thought the best solution for this scenario is to use Triggers.

Are there other solutions to solve this problem of replicating data between different databases? For sure!

  • Change Data Capture: It's fast, it's simple to use, but it doesn't work on any edition of SQL Server and it stores all changes in a historical basis (no need in this case).
  • Temporal Tables: It's fast, it's simple, but it only works from SQL Server 2016, it will require a job to synchronize the data
  • Always On availability group: It will not be easy to implement and not fast. It won't replicate just a few tables but the whole base and it doesn't work in all versions of SQL Server
  • replication: It won't be easy to implement and it won't be fast, but it meets the requirement to work on all versions and editions of SQL Server, it doesn't require a job, the data is updated almost in real time and it can only replicate a few tables

Creating source and mirror tables

Trigger that will mirror the original table

With the trigger below, I will replicate all INSERT, UPDATE and DELETE operations done on the original table to the mirror table.

Remember that if you change the structure of the source table, like a new column, removing/renaming existing columns for example, you must always apply the same changes to the mirror table. Otherwise, this will generate errors in the trigger execution, unless you change the trigger code to specify exactly the column names in the INSERT and include the changes in the UPDATE as well.

Testing the solution

Let's now test if the trigger is really working as expected.

I'm going to insert 10 random rows into the dbo.Sales table in the Base1 database:

Result:

Now I'm going to UPDATE the original table:

Result:

And finally, finish the tests with a DELETE on the original table:

Result:

ATTENTION: This trigger will be executed every INSERT/UPDATE/DELETE performed on the original table. This means that if you execute a bulk DML operation, such as inserting a block of rows or updating the entire table, for example, each affected row will trigger an execution of the trigger, which can leave the process itself very slower. Be careful when using DML triggers because of this.

And that's it, folks!
I hope you enjoyed this super simple tip, but it can help a lot of people on a daily basis with a simple solution that works 🙂