Suitable Topology For SQL Server Replication

Hi,

I am given with the responsibility to set up database replication in my organisation.

Before starting the replication set up, I would like to know which is the best suited replication for my application.

My application is much process oriented, and the users are working on records parallely(but with different records).

Say for ex:

There is a module for Insurance claim processing, it is handled in the following manner:

There are many employees with CSR roles, and all are working on some set of records to be processed. But No two employees should get the same record for processing. For that, we are doing locking of the cases by having one column called 'LOCKED_STATUS' and setting value as LOCKED. So that other users wont get the same case. Once the case is processed, the record is marked as unlocked.

My analysis for replication is as follows:

1. Snapshot replication: This wont be a suitable type of replication as the updations are to be immediately updated on all the subscribers, because when the user executes select query to get the next unlocked case, No 2 user should be able to get the same case.

I think, the best replication to chose from is, Transactional replication with Immediate Updation/Queued updation. But the problem is, when one of the subscriber goes offline, the updations are queued up. But in my case, say for ex. One case is updated as locked, but the subscriber is offline and the updations are queued up. At the same time, the other subscriber can fire an select query to get the next unlocked case. Since the update is on queue, there are possibilies for this subscriber to get

the same case.

Can anyone let me know, are the select statements also queued up(i.e., along with update and insert statements.)? If not please let me know how to approach for this problem.

Thanks in advance.

Prashant N M