SQL Server Performance

How to syncronize publisher and subscriber in this case? any risk?

Discussion in 'SQL Server 2005 Replication' started by xiebo2010cx, Aug 23, 2007.

  1. xiebo2010cx Member

    Repl Gurus, since this is our PROD environment, I need to be very careful.
    System topology: SQL 2K EE with SP4 publisher + separate SQL 2K EE with SP4 distributor + 3 PULL SQL 2K SP4 EE Subscribers. ALL are sitting on Win 2K3 OS.
    Problem: We need to do some maintenance on distributor, this may cause 4 hours downtime for distributor, then need to reboot the distritutor.
    During this situation, what should we do when distributor is up to synchronize the publisher and subscribers? any risk about the data loss?
  2. simondm New Member

    The distributor will continue from where it left off when it was shut down.
    One thing to keep in mind is that replication (excluding snapshot replication) uses a log reader which runs on the publisher. This sends the commands to the distributor.
    Whilst the distributor is down this log reader will fail. This is turn will cause the transaction log to fill up (because the log cannot checkpoint with commands waiting to replicate). Depending on how busy your server is this may or not be a problem. But keep an eye on the log file size, and make sure you have space.
    When the distributor comes back online the log reader will post the backlog of commands to the distributor and continue as if nothing happened.
  3. xiebo2010cx Member

    Thanks for your reply...
    Do I need to manually stop the LogReader Agent, and distribution Agent before shuting down the distributor? And restart the LogReader and Distribution Agents after the Distributor up?..... During this process, how about the subscribers... they will out of synchronization...Do I need to trun off some Repl Alert?
    Except the LOG SPACE concern, any other potential issues? thanks.
  4. satya Moderator

    Better to stop them than interuptting the transactions during that time, scheduled maintenance means you need to take care every simple thing such as checking error log too [:)].

Share This Page