This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(3.2, 54%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
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.
Hi @Heisenberg ,
Any update for this thread? Did reply(s) could help you? If the response helped, do "Accept Answer". If you have further question, please feel free to let us know.
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.
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.
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".