SQL Server 2012 High Availability and Disaster Recovery

Database mirroring as a technique to achieve high availability has several drawbacks:

•     Read-only queries cannot by executed against the mirror.
•     The technique can be applied only on two instances of SQL Server.
•     The technique mirrors only the objects inside the database; objects such as logins cannot be protect using mirroring.

To overcome these drawbacks of database mirroring, SQL Server 2012 introduces a new technique called high availability and disaster recovery (HADR). HADR allows you to maximize availability for your databases. Before I explain how HADR works, I will discuss the concept of availability groups, replicas, and modes.

Availability groups, replicas, and modes

An availability group comprises a set of failover servers called availability replicas. Each availability replica has a local copy of each of the databases in the availability group. One of these replicas, called the primary replica, maintains the primary copy of each database. The primary replica makes these databases, called primary databases, available to users for read-write access. For each primary database, another availability replica, known as a secondary replica, maintains a failover copy of the database, known as a secondary database.

Availability replicas can be hosted only by instances of SQL Server 2012 that reside on Windows Server Failover Clustering (WSFC) nodes. The SQL Server instances can be either failover cluster instances or stand-alone instances. The server instance on which the primary replica is located is known as the primary location. An instance on which a secondary replica is located is known as a secondary location. The instances that host availability replicas for a given availability group must reside on separate WSFC nodes.

The availability mode is a property that is set independently for each availability replica. The availability mode of a secondary replica determines whether the primary replica waits to commit transactions on a database until the secondary replica has written the records in the corresponding transaction logs to disk.

Each replica within an availability group is assigned one of the following roles:

•     Primary role
•     Secondary role
•     Resolving role

The current primary replica has the primary role. (At a given time, only one replica can have this role.) Each secondary replica has the secondary role. The resolving role indicates that the current status of an availability replica is changing.

Within a session, the primary and secondary roles are potentially interchangeable, in a process known as role switching. Role switching involves a failover that transfers the primary role to the secondary replica. The process transitions the role of the secondary replica to primary and vice versa. The database of the new primary replica becomes the new primary database. When the former primary replica becomes available, its database becomes a secondary database.

Configuration of HADR: hihg-level overview

The configuration of HADR is very complex, because, as a prerequisite, you have to create a two-node cluster, the description of which is beyond the scope of this article. For this reason, I will give just a brief description of the necessary steps.

To configure HADR, you have to execute the following steps (in the given order):

1. Install the database instances on both nodes.
2. Enable the HADR feature on both instances. Choose SQL Server Configuration Manager | SQL Server Services, right-click the instance, and choose Properties. On the SQL HADR tab, check Enable SQL HADR Service.
3. Create an availability group in the primary instance. Expand the Management folder in Management Studio, right-click Availability Groups, and choose New Availability Group.
4. Start data synchronization. Click the Start Data Synchronization button on the Results page of the New Availability Group dialog box.
5. Test the availability groups. Create a table on the primary replica, using the ON PRIMARY clause in the CREATE TABLE statement, and insert some rows.
6. Test the failover. Choose Management | Availability Groups | AVG | Availability Replicas, right-click the secondary replica, and choose Force Failover. After that, the roles of the secondary and primary replicas should be interchanged.

NOTE

The detailed description of HADR can be found in Books Online and  www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/.