we have a business application which uses a lot of caching for a better user experience. When we data is stored on the server we use JMS to invalidate the caches of all sessions/instances accessing that given database.
We did now add a replicated database at another physical location and use ftp replication to exchange data.
Now our problem:
The same is true when data is changed on server 2 and then replicated to server 1.
What we would need is a way to trigger a JMS message when a record is added/modified/deleted by sql remote replication only.
I did think about doing this via triggers, but I'm not sure if that will work, as triggers actions apparently are not replicated.
asked 08 Dec '10, 11:34
could need some clarification, methinks: This is just the (sensible) default setup and can be changed by the DBREMOTE -t option:
In my understanding, SQL Remote by default does not replicate the actions of triggers (except some cases of BEFORE triggers) as it assumes the triggers are defined at the remote side, too, and therefore the remote triggers will take the appropriate action. This obviously means that triggers are fired on DML that is executed via the Message Agent.
So, in your particular situation, you might use triggers with an if CURRENT REMOTE USER IS NOT NULL logic to handle DML done via the Message Agent:
That being said, the difficult part will be to choose an appropriate measure when exactly to reload the cached data. I guess it would not be senseful to force this for every single row-level operation, particularly as this could happen within transcations and as such, violate RI constraints.
Therefore, a better approach might be to use SQL Remote hooks like sp_hook_dbremote_message_apply_end or sp_hook_dbremote_end to force a cache-reload once the complete remote operation is done.