transaction replication and upgrade.

Heisenberg 261 Reputation points
2022-06-09T20:11:23.34+00:00

hi Folks,
We have transactional replication with 2 node subscriber and 1 publisher. We are planning on upgrading the subscriber nodes and on a later date we will upgrade publisher. Ive checked the version compatibility between publisher and subscribers and we are good on that part. My question is do we have to stop any distribution agent or any job before doing the upgrade , we will be doing in place upgrade on subscribers.

SQL Server | Other
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 122K Reputation points MVP Volunteer Moderator
    2022-06-09T20:37:15.45+00:00

    Are these machines virtualised? In that case, you have a good possibility to take snapshots and test and then revert back to the snapshot.

    I don't work much with replication, but my gut feeling is that I would prefer to stop the incoming the incoming stream during the upgrade, just in case something could roll in at the wrong occasion.

    1 person found this answer helpful.
    0 comments No comments

  2. Heisenberg 261 Reputation points
    2022-06-09T21:05:12.647+00:00

    Thanks @Erland Sommarskog these machines are EC2 instances on AWS. I agree we should stop incoming stream, probably i will have to stop the distribution agent.

    0 comments No comments

  3. CathyJi-MSFT 22,396 Reputation points Microsoft External Staff
    2022-06-10T02:00:23.81+00:00

    Hi @Heisenberg ,

    Quote from MS official document;

    SQL Server supports upgrading replicated databases from previous versions of SQL Server; it is not required to stop activity at other nodes while a node is being upgraded.

    Run the Log Reader Agent for Transactional Replication Before Upgrade

    Before you upgrade SQL Server, you must make sure that all committed transactions from published tables have been processed by the Log Reader Agent.To make sure that all transactions have been processed, perform the following steps for each database that contains transactional publications:

    1.Make sure that the Log Reader Agent is running for the database. By default, the agent runs continuously.
    2.Stop user activity on published tables.
    3.Allow time for the Log Reader Agent to copy transactions to the distribution database, and then stop the agent.
    4.Execute sp_replcmds to verify that all transactions have been processed. The result set from this procedure should be empty.
    5.Execute sp_replflush to close the connection from sp_replcmds.
    6.Perform the server upgrade to the latest version of SQL Server.
    7.Restart SQL Server Agent and the Log Reader Agent if they do not start automatically after the upgrade.

    In-place upgrade

    1.Upgrade the Distributor.
    2.Upgrade the Publisher and the Subscriber. These can be upgraded in any order.

    Please read the MS official document to get more detail notes about upgrade SQL in replication environment.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.