Upgrading SQL Server having multiple instances

Radhai Krish 191 Reputation points
2024-06-12T06:56:26.71+00:00

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 ))

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,183 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,854 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
62 questions
0 comments
Accepted answer
  1. MikeyQiaoMSFT-0444 1,505 Reputation points
    2024-06-12T09:08:26.55+00:00

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest