Post reply

So what if the link fails?

  • August 13, 2007 at 2:51 am

    #197251

    Hi,

    I have 2 SQL Server 2005 databases which are held in different data centres.  One of the databases is the Publisher (in data centre #1), the other the Subscriber (in data centre #2).

    Data is replicated from the Publisher to the Subscriber using Transactional replication.

    If (for some reason) the network connection between the Publisher and the Subscriber was to fail, are the transactions that still need to be replicated stored in the Publishing database transaction log, and the distribution database stores the last replicated transaction ID, so when the network link is restored the log reader will automatically start replicating the stored/queued transactions?

    Also, if the network link was down for 24hrs before it came back up again, would I need to manually start the log reader again? (can I increase the retry frequency and number of attempts to get round this?)

     

    Thanks in advance.

    www.sqlAssociates.co.uk

  • August 14, 2007 at 6:37 am

    #726743

    If the link fails for a short time (you configure how long), replication will essentially wait and send the transactions when the link is back up.  You can configure replication to notify you if the link is not working.  It will show up as having gotten an error in the replication monitor.

    As far as what happens if it is down for 24 hours, look up "Subscription Expiration and Deactivation" in SQL books online.  You have a number of options that can be configured to tell replication how long to allow a disconnected subscription to wait before it is marked as deactivated or it is dropped.  This will determine if you have to just re-connect, reinitialize, or re-create the subscription.

    Note that the longer you have the distributor wait before deactivating a subscription, the more transactions it will hold so it can distribute them.  If you have a lot of transactions, this can become a problem pretty quickly.

  • August 15, 2007 at 8:16 am

    #727041

    The big question is: Who is the distributor?

    Usually, the distributor is the same as the publisher, this is the default. In this case the LogReader won't stop if the network is down, because it's not interested in the network. It reads the log of the publisher database and writes the transactions into the distribution database.

    What you need to restart is the distribution agent. The retry number and interval is set at the job step properties. You can avoid the manual restart by setting up a secondary schedule which ticks every 5 minutes. If the distribution agent job runs, it will be ignored, if the distribution agent job failed, it will start it in 5 minutes.

    -- Erik http://blog.rollback.hu

Viewing 3 posts - 1 through 2 (of 2 total)

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