Managing and Connecting to AlwaysOn Availability Groups

Manual Failover

You can configure your availability group for automatic failover or for manual failover. In case of automatic failover if the primary replica/instance does down the AlwaysOn availability group switches the role of primary replica/instance to another synchronous secondary replica.

You can also elect for manual failover. The failover will occur without data loss if both primary and secondary replicas are in synchronous commit mode and are in a healthy state, data loss will occur if the secondary replica is in asynchronous commit availability mode. To do a manual failover using the wizard, click on the Start Failover Wizard link on the top right corner of the availability group dashboard as shown below:

Description: cid:image009.png@01CCD05A.6B3E97C0

This will launch the failover wizard, the first screen of the wizard is the welcome screen so click Next to proceed:

Description: cid:image010.png@01CCD05A.6B3E97C0

On the Select New Primary Replica page of the Failover Availability Group wizard, you need to specify the secondary replica which will be the new primary replica after the failover, the screen will also display the failover readiness and availability mode (synchronous or asynchronous). Click next continue:

On the Connect to Replica page of the wizard, you need to connect to the secondary replica which you want to make the primary replica after failover, click on the Connect button to get connected to the secondary replica:

The Summary page of the Failover Availability Group wizard will display a summary on your selection and actions that wizard is going to perform. Click on Next to failover. You can also generate the failover T-SQL script by clicking on Script button at the bottom:

Clicking on Finish will start switching the role or performing the failover, you can monitor the progress and overall of status of operation as shown in screen below:

If I open the availability group dashboard again or refresh it, I can see role has now, my earlier primary replica (ARSHAD-PC) is now the secondary replica whereas my earlier secondary replica (ARSHAD-LP) is now the primary replica:

Please note, this example and demonstration is based on SQL Server 2012 RC0 and there might be slight changes in the RTM release; refer to BOL (Books online) or msdn for updated information.

Summary

In this article I discussed the AlwaysOn availability group feature of SQL Server 2012. AlwaysOn is a new High Availability (HA) and Disaster Recovery (DR) solution in SQL Server 2012 which improves high availability and protects data of your mission critical applications. AlwaysOn availability group is recommended over database mirroring since it combines the best of failover clustering and database mirroring and overcomes the several limitations imposed in failover clustering or database mirroring setup.

Resources

Overview of AlwaysOn Availability Groups (SQL Server)

AlwaysOn Failover Cluster Instances (FCI)

Overview of PowerShell Cmdlets for AlwaysOn Availability Groups (SQL Server)

Overview of Transact-SQL Statements for AlwaysOn Availability Groups

Monitor Availability Groups (Transact-SQL)

Client Connectivity and Application Failover (AlwaysOn Availability Groups)

Pages: 1 2




Related Articles :

One Response to “Managing and Connecting to AlwaysOn Availability Groups”

  1. Hi Arshad,

    Suppose the client in the day of MSCS/SQL2008 (new term as FCI) used resource_virtual_host_name/instance_name to connect the database, if we migrate to AAG (as HA, not DR) with VNN as the same of virtual_host_name and keep the same instance name, could the client behave the same (normal connection as well as failover) by the same connection string (not change to AAG listener name)?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |