• You seem to be on the right track and merge replication would certainly cover what you are trying to do. You might also look at peer-to-peer transactional replication but you will need to run your requirements through the recommended topology to see if there is a fit there.

    A couple of thoughts, in no order of importance to consider;

    1. Due to distance you are likely going to see some latency on the replicated transactions. You will have to determine if that is acceptable. It sounds like it will be based on what you are discussing doing.

    2. Being that this is DR / Active, you will need to determine how much data loss is acceptable AND how long you have to get a site back online to service all customers.

    3. You will need to consider what objects need to stay in sync. If there is general web site traffic information that may be used for analytics and reporting but is not used for actual business matters then you may not want to replicate that and take up potential bandwidth for other replicated transactions.

    These are just some cursory thoughts and I'm sure there are other things to consider. I will try to think on this a bit more and post some more as time permits.

    Oh yeah, log shipping will not work in the situation that you describe. If you were speaking of a strictly passive DR site then that would work but you are looking for both your main and your DR site to be servicing requests so, that won't fly.

    Hope this helps.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot