The Database Trigger Issue

In order to be able to mirror each change to a database table as soon as it happens, SymmetricDS installs database triggers in each table of interest. In each table there will normally be 3 triggers, one for insert, one for update and one for delete.

However, there is an issue with using database triggers in the main database.

There is some sort of interaction with stored procedures which can cause problems, so it has been necessary to find an alternative solution which does not rely on database triggers being installed in the tables of the main database.

The Solution

With MS SQL Server it is possible to use the in-built replication feature to make a replica of a database which can be automatically kept in sync with the source database.

Fortunately, this in-built database replication can operate without the need for database triggers to be installed.

The solution is to make a new local replica database the name of which is ShelfSync. It is only necessary to include the subset of tables from the main database which are required to be replicated to the cloud server. (Currently there are 14 of these.)

Having put this new replica database in place, symmetricds was reconfigured to use the replica database as the source for replication to the cloud server.

One other small benefit of this solution is that that various tables that symmetricds uses internally for its own operation no longer need to be added to the main database, but instead are now in the local replica database.