Can servers in a replication topology be rebooted automatically?

  • Jimmy M

    Default port

    Points: 1481

    Dear all,

    SQL Server 2000, SP4 (all servers)

    Transactional replication, 1 push and 1 pull (db_x and db_y on same subscriber)

    No clustering involved

    Tired of having to apply windows security patches and do the reboots in a controlled way for my publisher, distributor and subscriber, I am finally asking the question - do i need to do this?!

    My fear is that an automatic reboot done by our WSUS server will break replication, e.g. if there are transactions being replicated while one or all of the servers goes down.

    If the publisher or distributor are rebooted while there are transactions being read from the publisher's log or being written to the distribution database, then the transactions are presumably not marked as complete in the publisher. Therefore upon startup the transactions would get read from the log again and propagated to the dist db again. Would you agree?

    If the distributor or subscriber are rebooted while there are transactions being applied to the subscriber, then the transactions are presumably not marked as complete in the distributor. Therefore upon startup the transactions would just get applied again, would they not?

    Do you think my assumptions are valid and that i might just let WSUS do the reboots? I could always do a checksum validate the next day to be sure. (At the time of the WSUS reboot, database usage would be light, but i need to cover all angles!).

    What do the rest of you do about this sort of thing?

    Many thanks,

    James

  • Mike_D

    Hall of Fame

    Points: 3295

    You can do all the rebooting you want without fear of losing any data. We have scheduled reboots for most of our publishers and distributors (publishers and distributors run on their own servers in our setup). The only thing you may need to look out for are any log reader agents or distribution agents which require restarting (if the agents are run in continuous mode). It is very unlikely that any will fail in the time your server takes to reboot. Of course if, like me, your distributors are dedicated servers then all agents servicing your push subscription(s) (if they are set to run continuously) will restart automatically when SQL Server Agent starts.

  • Jimmy M

    Default port

    Points: 1481

    Great, thanks for this, it gives me the confidence to go through with this new approach. The agents do indeed run in continuous mode and we do have the distributor on its own dedicated box (although i am looking at moving the distribution agent onto the publisher because i believe the work done by our distributor server can easily be taken on by the publisher box, and my colleagues are keen for the space in the server room (!)).

    On a cheekily different note, if you use clustering anywhere at all, would you take this approach with a cluster too - to allow an auto-reboot? Again, i currently apply patches manually because i am concerned about how the cluster will come back up if the reboot of both nodes happens automatically, at more or less the same time.

    Thanks again.

    James

  • Mike_D

    Hall of Fame

    Points: 3295

    James,

    Sorry. I've never used clustering so if you don't mind I'll chicken out and not even take a stab at an answer!

    Mike

  • AndyG-538510

    Old Hand

    Points: 337

    With Multi-instance clusters (Active-Active or Active-Active-Active) we have always applied the patches manually so that we can control the ultimate destination of the instances (Instance 1 on Node A, etc.)....

    For Active-Passive clusters we have used multiple group policies in WSUS so that the A nodes get patched and reboot at a certain time and then the B nodes get patched and reboot at a later time...we have not had a problem with this arrangement.

    AndyG

Viewing 5 posts - 1 through 5 (of 5 total)

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