Chapter 5. AlwaysOn Availability Groups

AlwaysOn Availability Groups was a new feature in SQL Server 2012 and was a major factor in certain shops while making the decision to migrate to SQL Server 2012. In SQL Server 2014, there have been some major enhancements and improvements in AlwaysOn Availability Groups. This includes allowing a replica to reside on Microsoft Azure Virtual Machine to help maintain replicas across different data centers, which have built-in disaster recovery as well, thus making use of the cloud, and also expanding the number of replicas that can be included in your Availability Group.

In this chapter, you will learn about:

• Availability Group enhancements in SQL Server 2014, including the fact you can have an eight node Availability Group

• Using Microsoft Azure Virtual Machines as replicas

• Troubleshooting Availability Groups

• Creating a hybrid Availability Group

Introducing AlwaysOn Availability Groups

AlwaysOn Availability Groups, introduced with the release of SQL Server 2012, are best described as enhanced database mirroring. It allows a set of user databases to failover automatically between different instances installed on separate servers; these are commonly known as replicas. Thus, it provides both high availability and disaster recovery, and applications that use these databases are automatically redirected; this minimizes the application downtime. From a disaster recovery perspective, multiple copies of the databases are maintained. So, if you have your AlwaysOn Availability Group replicas dispersed across different sites, then you can make use of the multiple database replicas for disaster recovery; if you lose one site, your database and its data are also stored safely on the other site or sites.

Enhancements in AlwaysOn Availability Groups

If you didn't know before, you must know now that you should have an understanding of what an AlwaysOn Availability Group is and what it can be used for. In this section, you will learn about what has been changed and enhanced in AlwaysOn Availability Groups in SQL Server 2014:

• Microsoft Azure Replica is one of the best features in SQL Server 2014. This feature gives the ability to have a replica hosted in Microsoft Azure. So, even if you don't have a second site that can act as a DR site, then you can make use of the cloud. Even if you have a DR site set up, you can have even more resilience by having a copy of your database at a third location—online—and satisfy your user requests.

• There has been a change in the number of replicas that you can have in your AlwaysOn Availability Group. In SQL Server 2012, you were limited to four replicas. With SQL Server 2014, this number has increased to eight replicas.

• If a secondary replica becomes disconnected...

Using Microsoft Azure Virtual Machines as replicas

One of the new features of 2014 is the ability to combine your on-premise AlwaysOn Availability Group instances and also maintain a replica on Microsoft Azure Virtual Machine. This feature allows you to combine the flexibility and control of managing your own infrastructure along with maintaining a secondary stored site in the Microsoft Azure data centers.

The commit mode

Earlier in this chapter, we described AlwaysOn Availability Groups as database mirroring on steroids. Like database mirroring, AlwaysOn Availability Groups have two modes that you can choose from when configuring each replica:

The asynchronous commit mode: We can choose this commit mode when performance is more important than protecting against data loss. If every replica is running in the asynchronous commit mode, then the primary replica will not wait for any acknowledgement from the secondary replica that implies the transaction has been written to the log of secondary...

Building AlwaysOn Availability Groups

To create an AlwaysOn Availability Group, you need the following:

• Windows Server Failover Cluster (WSFC)

• Enable AlwaysOn Availability Groups on each instance

• An IP address for the listener

Windows Server Failover Cluster

In order to make use of SQL Server Availability Groups, you need Windows Server Failover Cluster. The SQL Server instances will be standalone instances, and it is important to understand that AlwaysOn Availability Groups are not SQL Server clustering. In order to make use of the automatic failover and client redirection, the servers that will act as replicas in your configuration will need to be part of Windows Server Failover Cluster. This is not as traumatic as it sounds. There is no need for shared storage, we are not clustering the SQL Server instance, and clustering since Windows Server 2008 R2 is pretty painless to configure. If you are a DBA in a relatively large organization, there is a high possibility that you won't have the permissions...

Creating Availability Group

With a Windows cluster built and each node configured for an AlwaysOn Availability Group, you will learn next to create an Availability Group in SQL Server 2014. You will then build the Availability Group and create a secondary replica using Microsoft Azure Virtual Machines.

On the instance you initially want to be the primary replica, connect in Management Studio's Object Explorer, expand the AlwaysOn High Availability folder, right-click on the Availability Groups folder, and choose Always On Availability Group Wizard. The first screen explains what the wizard will implement; click on Next. On the second screen, you will specify a name for your Availability Group; in this case, it is called AlwaysOnDBs. This can be seen in the following screenshot:

On the next screen, you will choose which databases to include in your Availability Group. In this example, two databases called DB1 and DB2 will form the databases in the Availability Group. There are some prerequisites...

Troubleshooting Availability Groups

A new system function has been added that will help you identify if the database and instance you are currently working on is the primary replica. The function is called sys.fn_hadr_is_primary_replica and the syntax for the is function is as follows:

sys.fn_hadr_is_primary_replica ( 'dbname' )

Here, 'dbname' is the name of the database you want to check. The procedure returns a 1, if it evaluates to true, that is, it is the primary replica. You can combine this with some logical code to determine which section of the code to run depending on whether the replica is currently the primary replica.

The following script will back up your database if the current instance that it runs is the primary instance:

declare @dbname varchar(30)
declare @backuplocation varchar(80)

set @dbname = 'DB1'
Set @backuplocation = 'c:\BACKUP\'
set @backuplocation = @backuplocation + @dbname
If sys.fn_hadr_is_primary_replica ( @dbname ) <> 1 
begin 
SELECT 'Nothing to backup...

Creating a cloud-based AlwaysOn Availability Group

We now have an on-premise Availability Group. With SQL Server 2014, it is possible to create an AlwaysOn Availability Group in Azure. You have two options: you can either create an availability that resides entirely in the cloud, or you can create a hybrid environment.

Creating an AlwaysOn Availability Group in Microsoft Azure

In this section, we will look at creating an Availability Group that resides entirely in Microsoft Azure. In order to configure this on Microsoft Azure, you need the following:

• A virtual network setup in Azure

• A storage account setup

• A Microsoft Azure domain controller

• WSFC

• One or more Microsoft Azure Virtual Machines with SQL Server 2014 installed and configured for AlwaysOn Availability Group

Build the Availability Group just as you have read in the previous tutorial. As you can see, in this case, it is the Infrastructure as a Service (IaaS) you have configured everything else. The following tutorial provided by Microsoft...

Summary

In this section, you have learned about some of the enhancements and changes in AlwaysOn Availability Groups with the release of SQL Server 2104. These changes allow you to build up highly available solutions that consist of both on-premise and cloud-based replicas, which when combined can provided a high degree of flexibility and reliability while configuring your high availability solution.

In the next chapter, we will explore some additional performance features that have been added in SQL Server 2014.