Microsoft SQL Server 2012: AlwaysOn Availability Groups Feature

One of the most compelling features in the upcoming SQL Server 2012 release (formerly code named Denali) is the new AlwaysOn Availability Groups. Microsoft's use of the term AlwaysOn is a bit confusing. AlwaysOn actually refers to two separate but related technologies: AlwaysOn Failover Clustering and AlwaysOn Availability Groups. AlwaysOn Failover Clustering is essentially the same thing as SQL Server failover clustering in earlier releases. It entails running a SQL Server instance on a Windows failover cluster. However, the AlwaysOn Availability Groups feature is completely new to SQL Server 2012, and it brings several new availability enhancements to SQL Server. If you're using database mirroring, AlwaysOn Availability Groups could be a good reason to upgrade to SQL Server 2012.

Related: Upgrading Clusters to SQL Server 2012 When AlwaysOn Availability Groups Will Be Used

After I describe the availability enhancements you'll find in AlwaysOn Availability Groups, I'll tell you about the system requirements and the basic system setup. I'll also show you how easy it is to configure and manage AlwaysOn Availability Groups.

The Availability Enhancements

The AlwaysOn Availability Groups feature is the next evolution of database mirroring. Database mirroring is an effective technology for enhancing single database availability. However, database mirroring has several significant limitations:

AlwaysOn Availability Groups address all these limitations. They provide support for one primary replica and up to four secondary replicas, where each replica is located on a separate SQL Server instance running on different Windows failover cluster nodes. More important, AlwaysOn Availability Groups can contain multiple databases, all of which can be automatically failed over as a unit. This means that AlwaysOn Availability Groups can protect multiple related databases and fail them over simultaneously. For example, if your application uses ASP.NET forms integration for authentication, you can fail over the default aspnetdb database in addition to your application's primary database. That way, the user authentication information can go along with your production data.

Related: SQL Server 2012 High Availability

Another important advantage with AlwaysOn is that you don't have to choose between asynchronous mode and synchronous mode, like you did with database mirroring. AlwaysOn can have both synchronous and asynchronous replicas at the same time. Synchronous connections are typically used in high-availability scenarios, where there's automatic failover. Asynchronous connections are typically used in disaster recovery scenarios, where there's geographical distance between the different servers. AlwaysOn Availability Groups enable you to have both types of protection simultaneously and can support a maximum of three synchronous replicas. In addition, the replica databases are able to provide read-only access. This enables the replicas to be used for both reporting and backup purposes, potentially offloading some of the workload and I/O from the primary server.

The Requirements

On the hardware side, AlwaysOn Availability Groups require at least two server systems and can work with as many as four systems. They can be either physical server systems or virtual machines (VMs). In addition, AlwaysOn Availability Groups require Windows failover clustering, which means you have to use the Enterprise Edition (or higher) of Windows Server 2008 R2 or Windows Server 2008. (The Standard Editions of Server 2008 R2 and Server 2008 don't support failover clustering.) At the time of this writing, it hasn't been announced which editions of SQL Server 2012 will support AlwaysOn Availability Groups, but most of the high-availability features have traditionally been provided in the Enterprise Edition or higher.

The AlwaysOn Availability Groups' requirement for clustering means additional complexity compared with database mirroring. Fortunately, Windows failover clustering became much easier to set up beginning with Server 2008. It's important to note that although AlwaysOn Availability Groups require a Windows failover cluster, they don't require installing SQL Server as a clustered application. (That's the purview of AlwaysOn Failover Clustering.)

There are several requirements regarding which databases can be a part of AlwaysOn Availability Groups:

Microsoft also recommends that a database file's path be the same on the primary and secondary servers.

System Setup

To use availability groups, you need at least two SQL Server instances running on different Windows failover cluster nodes. The general steps to set up the SQL Server instances on Windows failover cluster nodes are as follows:

1. Use Server Manager to install the Failover Clustering feature on all nodes.

2. Use the Failover Cluster Management tool to create a new Windows failover cluster.

3. Install a new standalone instance of SQL Server on each cluster node.

The detailed steps to set up a Windows failover cluster are beyond the scope here. For information about those steps, see "Hyper-V Live Migration: A Step-by-Step Guide."  The first part of that article covers setting up a Windows failover cluster. You can also find more information about this topic in the references listed in the Learning Path box.

Availability Group Configuration

Let's look at how to configure AlwaysOn Availability Groups between two standalone instances of SQL Server running on different cluster nodes. To begin, you first need to start the AlwaysOn High Availability service on all the nodes that will be included in the availability group. The AlwaysOn High Availability service isn't running by default. To start the service, open SQL Server Configuration Manager and select SQL Server Services in the navigation pane on the left. Right-click the SQL Server (MSSQLSERVER) service and select Properties from the context menu. In the AlwaysOn High Availability tab, select the Enable AlwaysOn Availability Groups check box, as shown in Figure 1. Click Apply. Stop, then restart the SQL Server service.

After the AlwaysOn High Availability service has been restarted, you can create a new availability group by opening SQL Server Management Studio (SSMS), expanding the server's Management node, and right-clicking the Availability Groups node. From the context menu, select the New Availability Group Wizard. The wizard's walks you through configuring an availability group using the following pages:

Introduction page. The first page just summarizes the steps required to create a new availability group.

Specify Availability Group Name page. In this page, you supply the name for the availability group. For this example, I named it PentonEditorialDB.

Select Databases page. You select the databases that will be included in the availability group in the Select Databases page. In Figure 2, you can see that I selected two databases: aspnetdb and PentonEditorialDB. The total number of databases that you can include is unlimited, but Microsoft recommends that you have no more than 100. In practice, most organizations will only need a few. All the databases in the availability group will be protected and failed over as a unit.

Specify Replicas page. On the Replicas tab of the Specify Replicas page, you can add up to four secondary replicas. When the page is first displayed, it lists the local SQL Server instance in the primary role. This instance is the primary replica, and it allows read-write actions to be performed on all the databases in the availability group.

You add replicas by clicking the Add Replica button. This displays a SQL Server connection dialog box in which you enter the SQL Server instance name and provide the authentication information for the system that will act as a secondary replica. All of the replicas you add will assume the secondary role. You can configure these databases to allow either no access or read-only access.

As Figure 3 shows, you need to configure the Replica Mode and Connection Mode in Secondary Role settings for each replica. SQL Server 2012 availability groups support three replica modes, which are summarized in Table 1. The default replica mode is Automatic failover. This means that the replica will have the ability to automatically fail over and the connection between the primary and the secondary systems will use the synchronous mode.

The Connection Mode in Secondary Role setting enables you to specify the access that will be permitted on the secondary replicas. The available connection modes for the secondary replicas are listed in Table 2.

On the Endpoints tab of the Specify Replicas page, you can customize the endpoint URLs, ports, and endpoint names. The default port is 5022, and the default name is Hadr_endpoint. You can also choose whether to encrypt the data sent from the primary replica to the secondary replicas. By default, the data sent between the endpoints is encrypted.

Specify Availability Group Listener page. In this page, you can create a DHCP listener for the availability group, as shown in Figure 4. The listener is designed to allow fast application failover after an availability group fails over by redirecting the application to one of the secondary replicas. The listener is configured as a DNS entry, and it initially points to the primary replica. In the event of a failover, the entry is updated to point to the secondary replica. Client connections can use this entry in their connection strings to connect to the databases in the availability group.

Select Initial Data Synchronization page. Figure 5 shows the Select Initial Data Synchronization page, which is essentially the last page you need to configure. You can choose to use the wizard to perform an initial synchronization between the primary and secondary replicas or manually perform the initial synchronization later by backing up and restoring the databases and log files. If you want the wizard to perform the initial synchronization, you need to specify a network share that can be accessed by the primary replica and all the secondary replicas. The wizard then performs the database and log backups for all the databases in the availability group and saves those backups on the specified network share. Afterward, it restores the database and log backups on the secondary replicas.

Validation page. When you reach the Validation page, the wizard checks for all the prerequisites needed to set up the new availability group. This includes checking for an available network share, making sure there's adequate free disk space on the replicas, making sure the database already exists on the replicas, and checking to see whether the database location is compatible on the primary and secondary replicas.

Summary page. The Summary page lists all the availability group configuration choices you made. If they're correct, you just need to click Next to create the availability group.

Results page. After the availability group is created, the Results page shows the outcome. The successfully completed steps will be listed with a green checkmark. Any warnings will be displayed with a yellow triangle. Errors will be displayed with a red circle.

Availability Group Management

After the availability group has been created, you can manage it using T-SQL, Windows PowerShell, or SSMS. To manage availability groups in SSMS, connect Object Explorer to the SQL Server Database Engine in either the primary replica or one of the secondary replicas. Next, expand the Management node, then the Availability Groups node. In Figure 6, you can see the new PentonEditorialDB availability group in SSMS.

The Availability Replicas node lists all the primary and secondary replicas. The Availability Databases node lists the databases that are included in the availability group. The Availability Group Listeners node displays the listeners that have been configured for the availability group. Right-clicking a node or one of the availability group components displays different context menus that enable you to interact with the availability group.

A Great New Tool

SQL Server 2012's AlwaysOn Availability Groups feature provides a great new tool to enhance SQL Server availability. For organizations relying on database mirroring, it provides a compelling reason to upgrade to SQL Server 2012. This tool is essentially the next evolution of database mirroring. It addresses all of the significant limitations that were found in database mirroring and provides several important advantages, such as the ability to protect multiple databases and the ability to mix and match synchronous and asynchronous replicas.

LEARNING PATH

For detailed information about setting up Windows failover clustering and installing SQL Server on a cluster:

“How to: Create a New SQL Server Failover Cluster (Setup)”

“Failover Cluster Step-by-Step Guide: Configuring a Two-Node File Server Failover Cluster”

To learn more about creating AlwaysOn Availability Groups:

“Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)”

For information about using T-SQL commands to manage availability groups:

“Overview of Transact-SQL Statements for AlwaysOn Availability Groups”

For information about using PowerShell to manage availability groups:

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