locked
Applying Service Pack for a server that hosts distribution database RRS feed

  • Question

  • Hi

    I need to apply Service Pack 3 to a SQL instance that houses a distribution database which is a part of active replication. The publishers and subscribers reside on different SQL instances than the one that hosts the distribution database.

    Server A has the publication, Server B has the distribution database, Server C has subscription database. If I were to install SP3 for SQL 2005 on Server B, I will have to reboot the Server B, how will it affect the replication? Will it pick up the transactions after it comes up? Should I stop replication before hand?

    Thanks

    Satya

    Friday, March 26, 2010 8:56 PM

Answers

All replies

  •  Start the Service Pack installation from Server A , then B and then C.

    Restat the Server A once service pack installation is done , then do the same on other server.I hope you will not see any error.

    Have a look on the Bugs fixed after SP3 release

    http://support.microsoft.com/default.aspx/kb/960598?p=1

     


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

    Friday, March 26, 2010 9:32 PM
  • Thanks for your input, I dont need to apply SP3 to Server A and C, I just need to apply SP3 to server B which hosts the distribution database, how will it affect the replication in this case?
    Saturday, March 27, 2010 2:08 PM
  • No issue , it will not effect the distributor. Before doing the installation stop the publisher and subscriber.
    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Sunday, March 28, 2010 7:36 AM
  • Thanks, this is a transactional replication, so I should be good if I stop the log reader agents for active publications (following what is mentioned in http://msdn.microsoft.com/en-us/library/ms151783.aspx) and start them after the patching is done, correct?
    Monday, March 29, 2010 2:01 PM
  • the correct sequence is distirbutor, publisher, then subscribers. You do not need to stop any agents.

     

    from the release notes from SQL Server 2005 http://www.google.com/url?sa=t&source=web&ct=res&cd=3&ved=0CBkQFjAC&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F5%2F7%2F1%2F5718A94A-3931-457B-8567-AA0995E34870%2FReleaseNotesSQL2005SP3.htm&ei=dLSwS4X9DMX7lweN-biQAQ&usg=AFQjCNF0WtNLoI8JwDS5Zr921IxzAFy2OA&sig2=RcSW9wEYEkA4xZBqXxgxxw

     

    Considerations for Upgrading Servers in a Replication Topology


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Proposed as answer by Tom Li - MSFT Monday, April 5, 2010 1:33 AM
    • Marked as answer by Tom Li - MSFT Monday, April 5, 2010 1:34 AM
    Monday, March 29, 2010 2:09 PM
    Answerer
  • Hi Hilary

     

    Thanks for your input, if I understand Mohammed's suggestion, he asks me to stop the publisher and subscriber before I patch the distribution server. How can that be achieved if I dont stop the log reader agents?

    Thanks

    Satya

    Monday, March 29, 2010 3:52 PM
  • there is no need for you to stop the agents. When the upgrade is applied the server will go into single user mode and none of the agents will be able to connect.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Tuesday, March 30, 2010 3:03 PM
    Answerer
  • I have contacted Microsoft for knowing the best practice to do this and they suggested that I stop SQL server agent before patching the sql instance, then patch it and finally restart the server. Replication jobs being configured to start up immediately after the sql agent comes online, all the jobs started automatically, without any problem, thanks for all the feedback

    Satya

    Tuesday, May 11, 2010 9:27 PM