Transactional Replication

Question

Transactional Replication

sqlrocks79

Old Hand

Points: 354
More actions
September 22, 2010 at 12:04 pm

#226741

Hi
Actually we have 2 servers Server A and Server B. And we are replicating some tables from server A to Server B using Transactional Replication and without updatable subscribers.
We are having an application that access Server B and reads the data from the replicated tables. But whenever a record is getting deleted on the publisher, by the time the delete gets replicated to the Subscriber on Server B, our application is reading those deleted records.
Actually those records shouldn’t appear in the reads made by the app. So our mgt decided to apply the delete both at the Publisher as well as the Subscriber at the same time.
So that by the time the delete gets replicated to the subscriber , we are already doing that delete on the subscriber side so that we don’t get any non-repeatable reads or fake records.
But our concern is
1.Will there be any issues with this approach?
2.If we get severe blocking at subscriber side due to this operation, how to overcome that?
Any help in this regard is much appreciated.
Thanks in advance.
SQLRocks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

I'm pretty sure if you just set it to ignore data consistency errors, it will skip over deleting records that it can't find. There is no way to prevent blocking on a delete statement though..whether or not you actually end up with unmanageable blocking is an unknown until you test it.

sqlrocks79 Old Hand Points: 354 More actions · Answer 2

Thanks a lot for the reply. BTW can we use snapshot isolation to avoid the blocking and increase the concurrecy of these operations.

or do you thick there are any other solutions for this ?

Thanks in advance

SQLrocks.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

How frequently is your subscriber pulling updates ? I think if you set it to "continuous" you should get fast deletes at server B

(I've been away from replication for a while though)

sqlrocks79 Old Hand Points: 354 More actions · Answer 4

Thanks a lot for the reply. I think Less than a sec.

They are are actually feeling that even 100msec latency is not sufficient.

Thanks in advance

SQLRocks

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

You can't update a record in two places at once at the same without at least a small delay (few seconds at most).

Is the app polling continuously? If it literally needs up-to-the-millisecond data, then you need to have it polling the source database.

sqlrocks79 Old Hand Points: 354 More actions · Answer 6

@ Derrick

Thanks a lot for your help. There will be small delay in the updates and the app is polling continously. Unfortunately we are not allowed to poll the source.

All your help is much appreciated.

Thanks

SQLRocks

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

The point is, no matter what, there is a delay.

If it's continuously polling, it would pick it up within the next second anyway right? There is absolutely no physical way to have 0ms latency. You could _try_ to improve performance by lowering the batch commit threshold, but that might have the opposite effect depending on the number of transactions.

sqlrocks79 Old Hand Points: 354 More actions · Answer 8

@derrick,

You are right! Thanks a lot for all your help and valuable advice. Now, I have enough information to face the beast.

Thanks once again

SQLRocks.