The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

Hello,

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:

  • When data is changed on server 1, it is replicated to server 2
  • The caches on server 2 (at application level) are not informed of the changes and therefore do not invalidate their caches.

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.

André

asked 08 Dec '10, 11:34

ASchild's gravatar image

ASchild
777222740
accept rate: 14%

@André: Feel free to leave a comment in case the underlying problem has been solved (or becoming irrelevant) in the meantime:)

(19 Jan '11, 14:53) Volker Barth

I'm just implementing it, I should be able to give feedback in 1-2 weeks

(03 Feb '11, 06:54) ASchild

Your statement:

I did think about doing this via triggers, but I'm not sure if that will work, as triggers actions apparently are not replicated.

could need some clarification, methinks: This is just the (sensible) default setup and can be changed by the DBREMOTE -t option:

-t Replicates all triggers. - If you use this option, you must ensure that the trigger actions are not carried out twice at remote databases, once by the trigger being fired at the remote site, and once by the explicit application of the replicated actions from the consolidated database.

To ensure that trigger actions are not carried out twice, you can wrap an IF CURRENT REMOTE USER IS NULL ... END IF statement around the body of the triggers. Using the CURRENT REMOTE USER special value.

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:

create trigger ...
begin
  -- common trigger actions...

if current remote user is not null then
    -- DBRemote has sent updates, force to reload the cached data...
  end if;
end;

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.

permanent link

answered 08 Dec '10, 12:43

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 23 Aug '11, 03:04

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×73
×64
×18

question asked: 08 Dec '10, 11:34

question was seen: 748 times

last updated: 23 Aug '11, 03:04