question

KaziArifulHaq-9926 avatar image
0 Votes"
KaziArifulHaq-9926 asked christophBueche-0792 answered

Service Pack and Cumulative update in SQL Server Failover Cluster

Hi,

We have 2 node SQL Server Failover cluster of 2012, 2014, 2016 & 2017 versions. We need to apply service pack and cumulative update. Is it possible to apply service pack/cumulative update one node at a time and keep SQL server service on to remaining nodes to minimize the downtime?

Regards
Arif

sql-server-generalwindows-server-clustering
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LeonLaude avatar image
2 Votes"
LeonLaude answered FrankJohnson-4888 commented

Hi @KaziArifulHaq-9926,

Yes you can, you should perform the updates in a similar fashion as mentioned below:

1. Backup all databases.
2. Failover all SQL cluster roles to Node A.
3. Install the patches on Node B (passive).
4. Restart Node B.
5. Failover all SQL cluster roles to Node B.
6. Install the patches on Node A (passive).
7. Restart Node A.

You may also follow the official documentation over here:

Upgrade a failover cluster instance
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance?view=sql-server-ver15


(If the reply was helpful please don't forget to upvote or accept as answer, thank you)


Best regards,
Leon

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Between Steps 2 and 3 of @LeonLaude list...
You need to Suspend data movement to the databases between Primary and Secondary replica before you interrupt SQL services with a patch/SP/CU install. It is critical to adjust failover mode to manual, if its set to automatic before you suspend.
Suspending data movement is done by going into the Availability Group under Available Databases and right click "Suspend data movement". This assures continuity on continuance after you execute step 4 of the Restart. Once you have patched ALL secondary's, you resume data movement and verify with dashboard that sync is caught up. If needed then adjust nodes to synchronous, wait for green, and you fail over primary to a newly patched secondary. Repeat data suspend data movement steps and patch the secondary just as you had.

I hope this ads value to the instructions.

Frank

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft commented

Hi @KaziArifulHaq-9926,

Also refer to the following posts for the basic steps.
https://sqlha.com/2011/05/10/patching-clustered-sql-server-instances-with-sql-server-2008-and-sql-server-2008-r2/
https://dba.stackexchange.com/questions/133595/installing-a-sql-server-patch-on-a-node-within-a-2-node-fci


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KaziArifulHaq-9926,

Is there any update on this case?
If the answer is helpful, please click "Accept Answer" and upvote it.

0 Votes 0 ·
christophBueche-0792 avatar image
0 Votes"
christophBueche-0792 answered

One good question will be if the way for SQL Failover cluster and the way for SQL Always On is the same (for example MS SQL 2016 SP3).
Because in opposite to SQL Always On will the SQL Failover Cluster have the SQL part (Binaries) as shared resources. The SQL Always On always have the SQL Binaries local and only the HA DB will run inside the cluster or better a pointer to the local resources (DB).

In other words: SQL failover cluster (the classic one), is it possible to implement a SP (to the inactive on) if the resources are owned by the other node. The Always On have primary and secondary roles, there the way is clear, because primary and secondary will act also as a local MS SQL installation and you patch the local part and the HA DB together (First the secondary than the primary).


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.