question

salves avatar image
0 Votes"
salves asked YiEWang-MSFT action

What is the recommended procedure for updating clustered servers with SQL Awals On?

Hi,

is there any recommendation to update the servers, both Windows and SQL in a scenario with Always On?

I know that the windows cluster has a feature called (Cluster-Aware Updating), but I never know. But I believe that it is for updating windows only of the cluster nodes and not SQL.

Thank you.

sql-server-general
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.

Shashank-Singh avatar image
2 Votes"
Shashank-Singh answered Shashank-Singh edited

You have good Microsoft Article on That. And it is upgrading-always-on-availability-group-replica-instances. This also applies to WIndows Server update. Quoting from the Article

When upgrading a SQL Server instance that hosts an Always On Availability Group (AG) to a new SQL Server 2019 (15.x) version, to a new SQL Server service pack or cumulative update, or when installing to a new Windows service pack or cumulative update, you can reduce downtime for the primary replica to only a single manual failover by performing a rolling upgrade.

Basically the step is

  1. Apply service pack or cumulative update on the secondary replica.

  2. If an instance hosts both primary and secondary replica. Failover such that the instance only hosts
    secondary replica

  3. Post SP/CU upgrade failover to newly patched replica.

  4. Now apply patch on other secondary replicas and repeat the steps 1-3 till all the replicas are patched.

Now if you read the article it has few recommendations mentioned as what to do before applying SP/CU please follow that.

  1. Like taking complete SQL Sever backup.

  2. Making sure replicas are green in Availability Group dashboard when you plan to apply CP/CU

  3. Apply SP/CU only when load is very very less or preferably during application downtime.

  4. Before applying Sp/CU manually failover and check that your AG is working fine. I understand you
    cannot do this with Production Database always but yes it is best to make sure that system is good
    in terms of failover before upgrading.

  5. Please test your SP/CU on UAT before applying it on production.

Please read the document completely to get more insight.


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

@salves

Guidance on how to do replica instance upgrades including the OS patching, refer to the MS article provided by Shashank-Singh.

It is not supported to use the Cluster-Aware Updating (CAU) to update the Windows Server cluster with SQL Server AlwaysOn availability groups.


If the answer is helpful, please click "Accept Answer" and upvote it.

· 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 @salves,

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

0 Votes 0 ·
FAhmed-4761 avatar image
1 Vote"
FAhmed-4761 answered

The OP asked about update and all the comments are regarding upgrade which are completely two different things.

I have the same questions, if I have 2 nodes in SAG is CAU is the recommended way to take care of failover and patching? Does it make sure the databases are fully available on the other node?

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.

FAhmed-4761 avatar image
1 Vote"
FAhmed-4761 answered

The OP asked about update and all the comments are regarding upgrade which are completely two different things.

I have the same questions, if I have 2 nodes in SAG is CAU is the recommended way to take care of failover and patching? Does it make sure the databases are fully available on the other node?

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.