March 2, 2003 at 6:23 am
This may sound like a silly question but how does one know that "immediate updating" is immediate?
We have been using transactional replication and it was working "fine". We set it up with immediate fallback to queued. The servers are on the same LAN (actually on their own LAN for inter-server communications).
We were very focused on functionality and getting all our procedures to work, and it seemed to be working just fine. But it finally dawned on us that it wasn't really immediate, it would be a few seconds to a few minutes before changes would occur. We then did some huge inserts and once it hit 30 hours (since addressed by a change in how we approach that).
Now I'm finally getting back to the "immediate" question. It works, there are no errors, but it is not immediate.
I set up a test on my own server last night, replicating to itself, and ran profiler. I purposely locked the to-be-replicated rows in the subscriber, and did an update on the publisher. It updated on the publisher and said "query batch completed". Watching in profiler it just sat and wait on the subscriber. I didn't know how long it would wait, but I think the significant thing is that the originate batch finished -- if it was in a 2PC transaction it would not have.
There are no errors when we do this, we've dropped and re-setup replication numerous times for various reasons. If you look at publisher and subscriber properties while it is running they show correctly.
We definitely have ALWAYS been able to query the publisher and subscriber and see the updateon the publisher first, then seconds (to minutes to hours) later see it on the subscriber, again not what I would expect if 2PC was used.
I even tried switching my test to immediate-only (no queued) and saw the same thing.
Maybe I don't understand what to expect? My expectation was that I would see something close to the same transaction structure as if I had done an update to two servers inside the same transaction.
What am I missing?
March 17, 2003 at 8:45 am
I have now been told that the "immediate" part of immediate updating only applies to updates on the subscriber, and not on the publisher.
That probably explains what I saw.
March 17, 2003 at 6:43 pm
Not 100% sure that is true. In standard transactional you'll have some latency as you wait for the log reader to read the transaction, post it to distribution db (as a logged transaction), then the distribution agent picks it up and posts to the subscriber(s).
I'll have to look again to be sure, but with updating subscribers it is a distributed transaction, all updates are handled via triggers. That's why you can't update text columns back to the publisher - not visible in the after trigger. If you do immediate w/queued, or just queued, then there is the chance that the update may be stored and applied later.
Maybe that helps?
Andy
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply