Since the arrival of BizTalk Server 2016, Microsoft added support for SQL AlwaysOn. It still is new to many BizTalk operators, administrators and developers. Often having been dealing with older versions of BizTalk.
Not many people have been dealing with SQL AlwaysOn already, so this post is for non-BizTalk geeks as well…
There was always the need for high available SQL environments. Companies want their data and systems running in a way that downtime can be minimized and SQL Server has a lot of solutions to that problem, one of them being SQL AlwaysOn.
When having dealt with a SQL Failover Cluster before, you might be tempted to use the Failover Cluster Manager to fail-over an availability group, however this is NOT a good choice when dealing with SQL AlwaysOn…
One way to rule them all
To perform a planned, manual fail-over, the SQL Server documentation recommend only three methods:
- Using SQL Server Management Studio (SSMS)
- Using T-SQL (can be complicated)
- Using PowerShell (ideal for scripting, but this is not the scope here)
I will not be going into details on T-SQL commands or PowerShell, instead I will focus here on the manual labor: using SSMS to perform a planned, manual fail-over.
To perform a planned, manual fail-over, open SSMS, look for the Availability Group (AG) you want to failover, right-click and choose Failover:
You will start a wizard, where you need to make sure you have no data loss, as shown below:
Now connect to the secondary replica, you want to fail-over to and Click Next and Finish.
On the last screen you’ll be able to check if the fail-over was successful (or not).
Why not use the Failover Cluster Manager?
SQL Server Availability Groups and availability replicas use synchronization state to indicate whether or not they are in-sync. This is maintained only in the SQL Server Availability Group and the Failover Cluster Manager is not aware of this.
Hence, failing over your Availability Group using the Failover Cluster Manager can have devastating consequences and result in expensive downtime and data loss, due to unsynchronized transactions.
The only supported ways of manually failing over an availability group are listed above.