Failing over on a node with different Cumulative updates in SQL Server 2016

Question

Hey Team, I am planning for a failover from a physical data center to AWS and currently using distributed availability groups for it.

One of my concern is that the nodes in the physical data center are SQL Server 2016 ENT SP2 CU5 and in AWS it is SQL Server 2016 ENT SP2 CU15. What is the possible impact if we move from CU5 to CU15 will there be an increased downtime for it?? and what are the other things that I should consider before the failover?

Any thoughts would be highly appreciated.

Thanks in advance

Answer 1

Good day,

planning for a failover from a physical data center to AWS and currently using distributed availability groups for it.

Why?
You provide us information about your plan but not the reasons you planned it. Therefore, we cannot advice about the plan too much since we have no idea if this is the best plan for your needs.

If this is done in the purpose of upgrade the "physical data center" (meaning the on-premises SQL Server), then the procedure can be as @Jeffrey Williams wrote (BUT REMEMBER TO BACKUP ALL DATABASES BEFORE YOU START ANYTHING).

What is the possible impact if we move from CU5 to CU15

Upgrade from CU5 to CU15 usually is fully supported and in most cases there should be no negative impact.

With that being said, you should always check in testing environment before working on production!

Between CU5 and CU15 there were many changed tens of fixes but in some rare cases you might have negative impact on performance in specific query for example.

Moreover, in some cases new upgrade bring new bug. For example SQL Server 2019 CU9 added new bug which was not in CU8 and it is still here in CU10 (should be fixed in next version CU11). So don't think that new version only fix :-)

in general, I HIGHLY RECOMMEND to go over all the information of all the news between CU5 to CU15 and check exactly what was changed. You can find the most important information here:
https://support.microsoft.com/en-us/topic/kb4577775-cumulative-update-15-for-sql-server-2016-sp2-881a15d9-be2d-7c30-c38d-658a6abe4f62
Go to CU5 first and then CU6 and so on...

will there be an increased downtime for it??

For the failover from CU5 to CU15 instances there is no downtime - this part of the idea of using failover. failover will impacts the uses, but no downtime.

But for the installation or update of the instance of SQL Server (the on-premises in your case), it is should at least restart computer according to the results of rules checks (while upgrade there is an automatic check which will inform you if you need restart). In this case there is /SkipRules option which you can use if you use command shell. I DO NOT RECOMEND TO USE IT. I recommend to restart after upgrade.

and what are the other things that I should consider before the failover?

(1) BACKUP EVERYTHING!!!
(2) BACKUP EVERYTHING!!!
(3) BACKUP EVERYTHING!!!
(4) Make sure that you can restore using your backup BEFORE you count on that backup!
(5) Inform your users and if possible stop the activity before - you do not want to failover exactly when all your users use the database.
(6) verify Availability Mode: Synchronous commit (right-click on the availability group and open its properties).
...

Note: better to do it with someone that have experience in such tasks. Above points are only what came to my mind right now in theory in the scope of the forum. In anyway! always test it first in a testing environment

Answer 2

Hi @Ashif Shaikh ,

> What is the possible impact if we move from CU5 to CU15 will there be an increased downtime for it??

No. There won’t be an increased downtime for it. Don’t worry about it. But as others mentioned, suggest you upgrade primary AG group to CU15 to make all replicas has same version.

Because there are two separate availability groups, the process of installing a cumulative update on a replica that's participating in a distributed availability group is slightly different from that of a traditional availability group:

1.Start by updating the replicas of the second availability group in the distributed availability group.
2.Patch the replicas of the primary availability group in the distributed availability group.
3.As with a standard availability group, fail over the primary availability group to one of its own replicas (not to the primary of the second availability group) and patch it. If there is no replica other than the primary, a manual failover to the second availability group will be necessary.

Please refer to MS document SQL Server version and edition requirements for distributed availability groups.

> what are the other things that I should consider before the failover

We need stop all data traffic to the original availability group, and change the distributed availability group to synchronous data movement. This action ensures no data loss.

To manually fail over a distributed availability group:

1.To ensure that no data is lost, stop all transactions on the global primary databases (that is, databases of the primary availability group), then set the distributed availability group to synchronous commit.
2.Wait until the distributed availability group is synchronized and has the same last_hardened_lsn per database.
3.On the global primary replica, set the distributed availability group role to SECONDARY.
4.Test failover readiness.
5.Fail over the primary availability group.

Refer to MS document Fail over to a secondary availability group to get detail steps information.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.