How to: Configure SQL Server 2012 AlwaysOn
How to: Fail-over
In this part of the blog series we will go over the various methods of manually failing over an Availability Group. Naturally if you are using automatic fail-over then there are no steps required for this to happen in an emergency but you might find a reason to conduct a planned manual fail-over when there is no actual problem (for example, fail-over testing).
What Happens During a Fail-over
Database Component:
A fail-over occurs by sending what amounts to a “make primary” command to a secondary replica. This happens because, of course, the primary database might not be available to receive commands.
Once the command to make primary comes through, the database will become transactional (in a read/write mode) and begin accepting connections of all types. If you were using synchronous commit then there will be no data loss and this new primary replica will begin stock piling transactions in the transaction log until the previous primary comes back online to receive the transactions or data transfer is manually severed. When the primary replica comes online there are no user actions required to resume the data transfer but a fail-back would require user interaction if it was desired.
If you are using asynchronous commit (like we are in our architecture) then the secondary replica will come back online but the data transfer will be paused. At this time you can use the database as a read-only replica to identify what data loss occurred or you can resume transfer. By resuming the transfer you lose the ability to identify what data might have been lost during the fail-over.
Cluster resource / AG listener
At the same time that the database component is failing over the cluster resource is also working on its fail-over.
Which ever secondary replica is becoming the new primary now needs to run the cluster resource for the Availability Group. The most important part of this fail-over is the AG listener. When crossing within the same sub-net the move causes a momentary outage of the IP address associated to the listener (no longer than the outage incurred by the database fail-over). If the resource is moving to a node where the listener must change sub-nets it will take the former sub-net IP offline and bring online the new sub-net’s IP address.
Availability Group Fail-over Wizard
The availability group fail-over wizard can be run from either the primary or secondary replica but you will have to be able to connect to the secondary replica in order to be successful.
There are two ways to access the fail-over wizard:
Availability group dashboard
The Start Failover Wizard link is available from both the primary and secondary replica dashboards.
SSMS Object explorer
To access the fail-over wizard from the Object Explorer; right click on the availability group and select Failover…
Wizard Walk-through
Upon opening the availability group fail-over wizard for the first time you will meet the Introduction page. If you’d like, check the Do not show this page again then click Next.
On the Select New Primary Replica page you will see a list of all available replicas with some status information. I find the most important column of this page to be the Failover Readiness column which will let you know whether you will have data loss by choosing this replica for fail-over.
NOTE: The Failover Readiness column is indicating data loss based on configuration not based on an actual representation of what has been synchronized. If you created a database inserted one record, verified that the secondary has the record, and then ran this wizard you would see that data loss is listed for asynchronous connections. It does not matter that there will actually not be any data loss in this situation.
Once you have selected your replica to fail-over to; click Next.
The Summary page will provide a list of the actions and affected components. This is where you can script out the process or go ahead and click Finish.
Finally, you reach the Results page and you are complete.
T-SQL Fail-over
Microsoft did a really great job at keeping the fail-over scripts simple for moving an Availability Group.
These commands must be run on the secondary replica that you wish to become the primary replica.
When no data will be lost by the fail-over
[sql]ALTER AVAILABILITY GROUP [AG_SQLTest] FAILOVER;[/sql]
When data might be lost by the fail-over
[sql]ALTER AVAILABILITY GROUP [AG_SQLTest] FORCE_FAILOVER_ALLOW_DATA_LOSS;[/sql]
Wrap-up:
It is easy to see that both the Availability Group Fail-over Wizard and the T-SQL methods are both very straight forward and easy to use. That is one characteristic of AlwaysOn that is very appealing, especially to junior DBAs.
Follow the links below to move on through this tutorial.
- Scope and Architecture – Part 1 of 7
- Building the Environment – Part 2 of 7
- Configuring AlwaysOn – Part 3 of 7
- Building Availability Groups – Part 4 of 7
- Explanation of Implicitly Created Components – Part 5 of 7
- How to: Fail-Over – Part 6 of 7
- The Availability Group Listener – Part 7 of 7
Leave a Reply