Upgrading SQL Server having multiple instances

Question

Our SQL is of 2014 RTM.

Planned to upgrade it to 2014 SP3 in the first place later to 2022. And our SQL Server do have extra 2 instances as being used for replication DBs.

I saw rolling upgrade as solution to HA environment. But nowhere could I see steps for the same.

Anyone can guide us?

Or Can we opt for direct in place upgrade? Will that work too?

(Not much tags are available to express all the needs :( (Replications, HA ))

Answer 1

Hi，Radhai Krish

For HA environments, it is recommended to choose a rolling upgrade method.

You can refer to Rolling upgrade.

The following are the upgrade steps to pay attention to：

Database Mirroring:

Depending on the mirroring mode, the following recommended steps can be adopted.

• First, set the database to High-Safety mode and remove the witness.
• Upgrade the mirror server.
• Switch the database to the upgraded mirror server.
• On the new principal server, check the database and resume mirroring.
• Upgrade the new mirror server (the old principal server), and after a successful upgrade, switch back once more so that the database continues to run on the old principal server.

Log Shipping:

• The monitor database service can be upgraded at any time.
• The secondary database service should be upgraded first. Otherwise, if the primary database is upgraded first, the backup of the primary database service cannot be restored to the secondary database service, and log shipping will fail.
• After upgrading the secondary database, upgrade the primary database.

Replication:

For replication upgrades, pay attention to the following points. The distribution database version should be higher than or equal to the publication database version.

For transactional replication：

the publication and subscription databases can differ by two versions.

For example, the publication database version can be SQL Server 2005, and the subscription database version can be SQL Server 2012, or vice versa. Before upgrading, ensure that all committed transactions in the published database have been processed by the Log Reader Agent.

• Confirm that the Log Reader Agent is running.
• Stop user operations on the published tables.
• Allow enough time for the Log Reader Agent to replicate transactions to the distribution server.
• Run sp_replcmds to ensure all transactions have been processed; the result should be empty.
• Run sp_replflush to close connections.
• Upgrade the database.
• After the upgrade, confirm that both the Log Reader Agent and the SQL Server Agent are running.

Merge Replication:

• The subscription database version must be lower than the publication database version.
• After the upgrade, run the Snapshot Agent for each merge publication and the Merge Agent for each subscription to update replication metadata.

Clustering:

• Ensure all disk resources are online.
• First, upgrade the database server on the passive node.
• Perform a failover to switch the database service to the passive node.
• Then, upgrade the new passive node.
• Switch the database service back to the original active node.

Best Regards,

Mikey Qiao

---

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.