SQL Server 2017 Enterprise Edition – Always-On failover procedure

Question

Hi people,

I need some steps and guidance, to perform the Microsoft Cluster Failover (MSCS 2016) for my SQL Server 2017 Enterprise Edition – Always-On 2x nodes.

This is to perform the Windows Server updates that will be rebooting one SQL Server node over the other.

I got confused between:
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/use-the-fail-over-availability-group-wizard-sql-server-management-studio?view=sql-server-ver15
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver15

Thank you in advance.

Answer 1

Hi @EnterpriseArchitect

There is a article regarding this in contrast to what you have shared. You can try the steps and see how it goes.

https://techcommunity.microsoft.com/t5/itops-talk-blog/step-by-step-creating-a-sql-server-always-on-availability-group/ba-p/648772#:~:text=Run%20the%20SQL%20Server%20Configuration,open%20the%20Properties%20dialog%20box.&text=Select%20the%20Always%20On%20High,to%20restart%20the%20Server%20service.

Once you have done with this then you can try the steps as per this article

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-setup?view=sql-server-ver15

-----
--If the reply is helpful, please Upvote and Accept it as an answer--

Answer 2

These steps apply for all upgrade and patching activities for all clusters.

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance?view=sql-server-ver15#perform-a-rolling-upgrade-or-update

Answer 3

Hi EnterpriseArchitect,

Do you mean you want to failover a SQL Server Always on Availability Group so that you can update the Windows Server? If I misunderstood, please let me know.
Before failing over the AG, please verify that the synchronization state of the failover target is SYNCHRONIZED, and make sure the secondary replica and the primary replica are running in synchronous-commit availability mode. Please update the OS of secondary replica first, then you can failover the Alwayson, update the OS of new secondary replica (old primary replica).
You can go to primary replica, expand the AlwaysOn High Availability node -> Availability Groups -> right-click the availability group ->select Failover to perform a manual failover.

Then please follow the doc to use Fail Over Availability Group Wizard to failover.
Please also check Upgrading Always On Availability Group Replica Instances which might be helpful.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 4

Hi EnterpriseArchitect,
Thanks for your reply.

So how do I check the Synchronized status?

You can go to primary replica, expand the Always On High Availability ->Availability Groups ->
Right-click the availability group -> Properties.

Check the availability mode in the Availability Replica Properties dialog box-> Availability mode.

I'm trying to do this via the Windows Admin Console of other GUI methods, outside of the SQL Server Management Studio.

It is not recommended to failover the SQL Server Always on Availability Group outside of SSMS. Please go to SSMS to failover Always on.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 5

Hi EnterpriseArchitect,

Thanks for your reply.

Because, even with the Planned manual Failover method, the Failover wizard will determine if there will be data loss or not.

There will be no data loss when planned manual failover happened if both the primary replica and secondary replica are configured for synchronous-commit mode, and both the primary replica and secondary replica are currently synchronized.
Please refer to this doc which might be helpful.

Best Regards,
Amelia