Welcome to Part 4 of my article detailing combining a Failover Cluster Instance of SQL Server into an Always On Availability group, here we'll dig into the Always On group setup and failover scenarios, we'll also look at the listener and the read only routing configuration too. Near the end of the article we'll look at testing failure scenarios. From Parts 1-3 you should now have a valuable insight into the basic platform required to implement both a Failover Cluster Instance of SQL Server and an Always On Availability group. To review, you need the following core components

• A Windows Server Active Directory domain
• A DNS infrastructure
• A DHCP scope (if you plan to use DHCP)
• A Windows Server Failover Cluster
• A set of nodes that are members of the same WSFC

Now that we have this in mind, let's look at a brief intro of Always On and then we'll get deeper into the deployment and configuration practical. For the purposes of this article the following apply;

Why Always On Availability Groups?

Traditionally, one would have used SQL Server failover clusters to attain SQL Server High Availability, this would have used either Microsoft Cluster Services (Windows 2003) or Windows Server Failover Clusters (Windows 2008). With a clustered environment we are able to fully mitigate server hardware failures by utilising

• Multiple network cards for network redundancy
• New majority node set quorum models (available in Windows server 2003 SP1 onwards) to remove the disk dependency and increase support for multi site clusters.
• Multiple computer nodes to negate core node hardware failure (i.e. motherboard, etc)

The only real weak link in the chain is the shared storage. There are many ways to achieve redundancy here, but it usually comes at a significant cost and it is often difficult to setup and maintain. Of course, as previously mentioned, a fail over cluster only mitigates the server hardware, it does not provide a single or even multiple secondary databases.

Windows Server Failover Clusters work at the SQL Server instance level to provide high availability for all objects, all cluster nodes joined in the Failover Cluster instance essentially act as a tag team for the clustered resources, when one partner drops the baton another steps in. During a failover, a restart of the SQL Server service is performed.

The next option for High Availability would have been database mirroring. The problem is, mirroring only provides scope for a single nonreadable (with the exception of snapshots) secondary database. You can combine other SQL Server availability technologies with fail over clusters and mirroring, but think of the increased complexity.

Database mirroring provides failover at a database level, the 2 systems involved in the mirror session are typically 2 completely separate SQL Server systems, no shared storage. The problem here is, the mirror database is not readily readable.

This brings us to Log Shipping with Standby/ReadOnly, this is a great way of replicating a database for read access. The only issue with log shipping is the fact that it can lag behind and users are disconnected from the database during the log restore process. It can however provide a one to many relationship from the Primary to multiple Secondary's.

What are Always On Availability Groups

An Always On Availability Group provides the ability to replicate a database in its entirety to a pre defined set of partner SQL Server instances for read access. This is achieved by creating an Always On Availability Group, which consists of at least two replicas and at least one database. A single database may only belong to one Always On Availability group, although you may have multiple Availability Groups. When creating a new group and adding databases a series of prerequisites are checked. The image below shows an example of the Pre Requisite check for a new Always On group.

The full list of Pre Requisites may be found at the following Microsoft KB link: http://msdn.microsoft.com/en-us/library/ff878487

So, we are able to replicate a database for read connectivity to a set of pre defined partner SQL Server instances. However, Always On groups have a limitation on the number of replicas that may be;

• Joined to the Availability Group (maximum of 5, 1 Primary and 4 Secondary, this has increased in SQL Server 2014 to 8 Secondarys).
• Configured for synchronous replication (maximum of 3).
• Configured for automatic failover (maximum of 2 replicas, must be in synchronous mode).

The Availability Group restrictions can be seen in the following example section of the "New Availability Group" wizard. The green ellipse shows the "Add Replica" option greyed out once there are 5 replicas defined in the list. The blue ellipse shows the maximum number of synchronous replicas, notice the checkbox for AONODE5\INST1 is greyed out. The red ellipse shows the maximum number of automatic failover replicas, notice the checkbox AONODE4\INST1 is greyed out. AONODE5\INST1 is greyed out as it's not configured for synchronous replication.

Despite these limitations, Always On offers us a new level of HA with the following features

• No shared storage, each server\instance has localised storage and removes the storage Single Point Of Failure.
• Always On listener service to accept centralised requests to HA database groups.
• Multiple availability databases instead of the traditional Principal\Mirror scenario.
• Better failover functionality leveraging Microsoft Windows Server Failover Clustering.
• The ability to suspend data movements at the primary level or individual secondary level.
• Support for multiple IP subnets.
• Ability to offload backup and maintenance operations to secondary databases.

By offloading backup operations to read only replicas, you can reduce the I\O requirements on your Primary production systems. Multiple secondary's can also provide DR and reporting replicas.

One item immediately noticeable is the fact that Always On still uses the familiar SQL Server database mirroring endpoints for instance-to-instance communication. Just as in database mirroring, you may either use Windows authentication for the Endpoints or certificates. You now have the option to create a highly available listener service which you will use to accept incoming connections to the availability group. The listener consists of a unique virtual IP address and a unique virtual network name, this is by far one of the most significant changes in making the groups databases highly available.

By providing a centralised access point into the availability group, clients are removed from the issues surrounding connection string configurations that are generated during the failover of a database. Availability Group replicas may also be configured for read-only routing, this allows online readable secondary's to handle read only requests, removing concurrency issues from the primary replicas.

With Always On you still also have the traditional synchronous and asynchronous modes that were used in database mirroring too. Asynchronous replicas support manual failover only, while synchronous replicas support automatic or manual failover.

There are a range of options for troubleshooting any errors, you have the Always On dashboard and also the SQL Server and Windows logs. The wizard driven deployment offers the easiest deployment route, whereas manual deployment requires a fair amount of manual interaction. Despite this, a basic AO group configuration is still extremely easy to deploy and configure and offers a level of HA that was previously not available without resorting to complicated levels of feature integration.

Before you may implement an Always On Availability group you must enable the feature on each SQL Server instance that is to participate as a replica in the Always On group. This feature may only be enabled when the node has been successfully joined to a Windows Server Failover Cluster. The option is enabled by opening SQL Server configuration manager on the node (the active node for a Failover Cluster Instance) and editing the properties of the SQL Server service. An example is shown below.

A restart of each SQL Server instances service will be required to complete the configuration. With the Always On option enabled, let's deploy an Always On group now using our pre installed instances from Part 3. Remember, we now have 1 failover cluster instance and 2 standalone instances, 3 in total.

To invoke the Always On availability group wizard, locate your preferred SQL Server instance and expand the "Always On High Availability" option in SSMS. Right click "Availability Groups", then select "New Availability Group Wizard" as shown below.

The first dialog specifies the objects required to successfully complete the wizard. Ensure you have the required information to hand before continuing.

Note: This may involve speaking to your system administrators and obtaining account information, virtual network names and virtual IP addresses.

Firstly specify an Always On Availability group name. The name you use here will be the name of the clustered role that is created within the Windows Server Failover Cluster and should be unique within that cluster.

Click "Next" to continue.

If you have no databases available on the SQL Server instance where you have initiated the wizard, you will see something along the lines of the dialog below.

Note: To continue I simply ran a full backup of my AdventureWorks2012 database and clicked the "Refresh" button. 

The pre requisites have now been met, to continue click "Next".

On the "Specify Replicas" dialog you will primarily see the instance of SQL Server where the wizard was initiated. You can add in further replicas, and you must add at least one secondary up to a maximum of 4 (5 replicas in total including the Primary).

My current configuration provides no automatic failover, we will revisit this later in the article during the Failover test scenarios. After setting the replica options move to the "Endpoints" tab.

On the "Endpoints" tab, check and adjust the port numbers to be used. You may of course accept the defaults offered. Once complete, move to the "Backup Preferences" tab.

Note: you will see below that when I installed the standalone SQL Server instances I opted for the default account configuration, we'll see shortly how this affects the group configuration.

On the "Backup Preferences" tab, specify your preferred backup options. Here I have chosen to exclude the replica "STOKECSCLNODE4\INST1", preferring the secondary when either of the remaining instances are in the secondary role. Once completed, move to the "Listener" tab.

On the "Listener" tab, select your preferred listener configuration. To create a listener you must supply a valid and unique Virtual Networkname, TCP port and Virtual IP Address (if you're not using DHCP). To add a static IP address, select the network mode to "Static" and click the "Add..." button. On the popup dialog you merely need to select the appropriate network and enter the chosen IP address. Once the configuration has been completed click "Next" to continue.

Now we see the impact from the standalone instances using local accounts for the service logon. As the accounts are local and not domain based, Windows authentication is not possible, so we must use certificates for this configuration. For our configuration, change the 2 instances to use a domain user account. To see the discussion around domain user accounts see the notes below.

The use of domain accounts itself has ramifications on the Always On group configuration and this is based around the Always On group listener. For Kerberos authentication to work, the SPN that is generated against the listener networkname should be bound to a domain user account. This domain user account must be used for the service logon on each replica that will participate in the Always On group. The following Microsoft article defines the requirements shown below.

From: http://msdn.microsoft.com/en-us/library/hh213417.aspx#SPNs

A Server Principal Name (SPN) must be configured in Active Directory by a domain administrator for each availability group listener name in order to enable Kerberos for the client connection to the availability group listener. When registering the SPN, you must use the service account of the server instance that hosts the availability replica . For the SPN to work across all replicas, the same service account must be used for all instances in the WSFC cluster that hosts the availability group.

Use the setspn Windows command line tool to configure the SPN. For example to configure an SPN for an availability group named AG1listener.Adventure-Works.com hosted on a set of instances of SQL Server all configured to run under the domain account corp/svclogin2:

At this point I stopped the group configuration and enabled each of my replicas in SQL Server configuration manager to use the same domain user account for the service logon. Note running the configuration wizard removes the Endpoint warning message and allows me to continue. To continue through to the next step click "Next".

On the "Data Synchronisation" dialog, select the synchronisation type preference. As I have already initiated my secondary databases from a full backup and a transaction log backup, I selected the "Join Only" option. You may of course allow the wizard to synchronise the databases for you. Click "Next" to continue.

At the "Validation" dialog, review the results and click "Next" to continue.

Click "Finish" to create the Always On Availability group.

Review the results and click "Close".

Now that the Always On group has been successfully created, looking at our Windows Server Failover Cluster configuration, we can see below the new clustered role and also the resources assigned to it. We have an Always On cluster role for the Always On group and a clustered Always On resource, both using the Group name we supplied at the start of the deployment wizard. For the listener, we have a clustered Virtual Networkname and Virtual IP address resource.

One of the queries that continually arises on the forums surrounding Always On groups is the necessity for all nodes to be in the same cluster. Well, here's the proof, if you had an Always On group that spanned nodes on 2 separate clusters, how would you be able to failover the clustered resource(s)? The clustered resources are native to the Windows Server Failover Cluster where they have been created, they do not move across clusters.

Note: Do not attempt to manually modify the group preferred owner or resource possible owners lists. Any such attempt is a futile exercise, as these are dynamically configured by the cluster for the Always On group during a failover and depending on the replica configurations. We'll see an example of this later in the article.

TIP: To find the current Always On group Primarys for any Always On groups within the cluster, you may use the following PowerShell command

get-clusterresource -cluster "WindowsClusterName" | where-object{$_.ResourceType -ilike "SQL Server Availability Group"} | ft

Practice manual failovers between the replicas, in SSMS expand the Always On group and right click then select "Failover". You may also use the following T-SQL from the synchronous Secondary for which you wish to assume the Primary role.

ALTER AVAILABILITY GROUP [YourAG] FAILOVER

Observe what happens to the group during these failovers. When you're happy continue with the rest of the article.

Configure ReadOnly Routing

After creating your highly available Always On Availability Group with a Listener, you'll likely want to configure ReadOnly routing to better manage ReadOnly requests. Before we can redirect clients to a readonly intent replica we must first create a listener, then we must configure the read only routing lists for the Always On replicas. We've already created the listener during the group deployment, so now it's time to configure the read only routing lists required to allow connections to the Always On group read intent replicas. Below is the T-SQL code used to configure my instances;

This first code block sets up the secondary role preference for each replica that will be used for Read Only routing. Substitute the items in red, these being the SQL instance ServerName and also the routing URL for the database engine itself (not the mirror endpoint URL or the listener URL). From the Primary replica use the following;

ALTER AVAILABILITY GROUP [MYAOGroup] MODIFY REPLICA ON  N'SQL2K12CLUST1\AOINST1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [MYAOGroup] MODIFY REPLICA ON  N'SQL2K12CLUST1\AOINST1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://sql2k12clust1.stokecs2.co.uk:58001')); ALTER AVAILABILITY GROUP [MYAOGroup] MODIFY REPLICA ON N'STOKECSCLNODE3\INST1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [MYAOGroup] MODIFY REPLICA ON  N'STOKECSCLNODE3\INST1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://stokecsclnode3.stokecs2.co.uk:58001')); ALTER AVAILABILITY GROUP [MYAOGroup] MODIFY REPLICA ON N'STOKECSCLNODE4\INST1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [MYAOGroup] MODIFY REPLICA ON N'STOKECSCLNODE4\INST1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://stokecsclnode4.stokecs2.co.uk:58001'));

The next step is to specify the routing preferences for each replica while in the primary role,

ALTER AVAILABILITY GROUP [MYAOGroup] MODIFY REPLICA ON  N'STOKECSCLNODE3\INST1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('SQL2K12CLUST1\AOINST1', 'STOKECSCLNODE4\INST1', 'STOKECSCLNODE3\INST1'))); ALTER AVAILABILITY GROUP [MYAOGroup] MODIFY REPLICA ON   N'STOKECSCLNODE4\INST1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('SQL2K12CLUST1\AOINST1', 'STOKECSCLNODE3\INST1', 'STOKECSCLNODE4\INST1'))); ALTER AVAILABILITY GROUP [MYAOGroup] MODIFY REPLICA ON  N'SQL2K12CLUST1\AOINST1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('STOKECSCLNODE3\INST1', 'STOKECSCLNODE4\INST1', 'SQL2K12CLUST1\AOINST1')));

Configure each replicas Primary role to include the Secondary replicas you wish to offload to, typically you may specify all replicas but that may not always be the case. If specifying all replicas you should ideally specify each Primary as the last connection in its routing list. The item "READ_ONLY_ROUTING_LIST" is enumerated from left to right, most preferred to least preferred.

Now that the ReadOnly routing lists have been configured we can test a ReadOnly intent connection via the listener. With the primary set as shown below, a SQLCMD query specifying ReadOnly intent is directed to an alternative node as specified in the scripts above.

The ReadOnly routing setup seems to have been a source of some confusion previously. In reality, once you are sure of the replicas you require, the scripts are easy to configure. The most important thing to remember is that the ReadOnly URL you specify is the URL of the database engine itself. It is not the database mirror endpoint URL or the listener URL. Practice a little with the ReadOnly intent scenarios, see if you are able to get a ReadOnly intent connection via a SQL Server Management Studio new query window. When you're happy proceed with the article.

Suspend Data Movement

This is a fairly simple feature, the Always On group offers the ability to suspend data movement between the Always On databases at the Primary or Secondary levels. In some cases this suspension occurs automatically, let's see quickly now how this is so.

As we know, if you have an Always On group Primary failure and have no automatic failover replicas, the group secondary replicas will enter the "Resolving" state. This requires either a manual failover to a synchronous replica or a forced failover to an asynchronous replica. It is when performing a forced failover to an asynchronous replica, the data movement is suspended automatically, you are required to actually reconnect the replica databases to the new asynchronous Primary. A typical scenario for this is detailed in the testing Failover Scenarios section.

Note: When an asynchronous replica is forced as a Primary, transactions are sent to all new Secondarys asynchronously regardless of their setting.

Suspending data movement on a primary stops the synchronisation between the Primary and the secondary's. The Primary database is still online and available, transaction log growth can be an issue on a busy database so care should be taken here to ensure that the suspension does not cause the transaction log to grow wildly or even fill completely.

Suspending data movement on a secondary database sets the database status to "Not Synchronising" and the secondary database becomes unavailable. Again unsent log records can build at the primary, resuming data movement synchronises the database and makes the secondary database available again.

Another reason for suspended data movement occurs when the group fails over from a replica which is at version SQL Server 2012 to a replica that is at a higher version, SQL Server 2014 for example. The image below shows an attempted failback from a Primary replica (SQL Server 2014) to a secondary replica (SQL Server 2012). Just like all of the other SQL Server HA techniques, once you move forward in database version you cannot revert, it is a one-time move.

Testing Failover Scenarios and Observing the Results

We will look at automatic and forced failover below. We will also look at the loss of a quorum and its effect on your Availability Group.

Automatic Failover

For automatic failover to be active you must have 2 synchronous partners that are configured for automatic failover, otherwise failover will be a manual process, with synchronous commit the data is protected. In the first scenario I have the Primary role on replica STOKECSCLNODE3\INST1, I've reconfigured the group so that STOKECSCLNODE4\INST1 is also a synchronous, automatic replica. I'm going to drop the Public NIC for the machine STOKECSCLNODE3 which will simulate a hardware failure, with the node dropping off the client network. An automatic failover should occur to STOKECSCLNODE4\INST1.

I'll logon via terminal services to node 4 and via the Hypervisor console, disconnect the Public vNIC for node 3. Before the failure, here is my Always On cluster role active on Node 3.

After the failure we can see the Always On cluster role coming online on Node 4. That does pretty much what you'd expect and essentially the same as synchronous database mirroring with a witness.

Note: Remember, I cannot use the FCI for auto failover as they are not supported

Forced Failover

Should the need arise to failover to a synchronous or asynchronous partner upon failure of the Primary, you will be asked to confirm during the failover wizard that you wish to accept the potential data loss. This will then enrol the selected replica as the new group Primary. But what about failing back, how is this achieved?

For this test scenario I'm going to maliciously terminate the SQLSERVR.EXE process on the Primary replica STOKECSCLNODE3\INST1 which is a synchronous replica, this will simulate a software failure, leave the remaining Always On group replicas in the resolving state, the loss of the Primary is catastrophic in this scenario where no automatic failover partners exist.

I will then perform a forced failover to STOKECSCLNODE4\INST1 which has been reconfigured as an asynchronous replica. During the forced failover, the wizard requires confirmation that I wish to accept the potential data loss.

To start, I have terminated the process on node3, this fails the Always On cluster role as shown below

To force the failover, right click the Availability group on Node 4 and select "Failover", this will invoke the failover wizard. Shown below is the selection screen for the asynchronous replica. When launching the failover from a secondary replica or a replica that is in the resolving state, you are offered only that instance as a target. Click "Next" through this screen

During the forced failover, I need to accept the prospect of data loss on this asynchronous partner. Check the box and click "Next"

After completing the failover wizard, the Always On cluster role is back online on node 4.

I'll bring the SQL Server instance on STOKECSCLNODE3 back online and it will join the group with the data movement suspended. Notice below that after the failover to the asynchronous replica, the remaining partner databases have had data movement suspended, indicated by the little pause symbol against each secondary database.

At this point I set my replica on STOKECSCLNODE4 to synchronous mode and right clicked each database and selected "Resume data movement" to synchronise the databases. When failing back over at a later point there will be no further possibilities of data loss. Leaving the replica as an asynchronous partner will have an impact when failing back at a later time and also sends transactions asynchronously regardless, as mentioned previously.

You may also force failover to a synchronous replica, which again will require you to confirm a potential data loss, however in the synchronous mode the potential is greatly reduced.

Loss of Quorum 

This is an extreme case of cluster outage, this time I'll simulate a scenario where the cluster loses quorum, although half the nodes (votes) will be online the Always On group will not. As such the Always On databases within this group will be unavailable due to the cluster service going offline on the remaining nodes. When attempting to open the "databases" view in SSMS you will see an error similar to the one below.

For this test I forcibly powered off half my nodes of my 4 node cluster to simulate a sudden loss of votes, the dynamic weight has no chance to recalculate the vote change and with no witness defined the Always On group clustered resource goes offline. Any attempt to open the Always On databases on the remaining nodes resulted in the error above. This is why it is important to ensure you configure the quorum witness appropriately for your cluster configuration.

Cluster Role Resource Status During Failover

It's worth taking a look at the cluster resource status' at this point to understand exactly what is happening to the cluster role during these outages.

When failing over the Always On group to a manual synchronous partner, this modifies the clustered role preferred owners to just that node. Also the possible owners list on the Always On resource is set to just that node too. For example, we have the following scenario.

Currently the FCI SQL2K12CLUST1\AOINST1 is active on node1, this replica is also the Primary replica for the Always On group. The Primary is synchronous with manual failover, let's look at the owners lists for the cluster resources.

A failover is initiated to a synchronous partner STOKECSCLNODE3\INST1. After a failover to the partner the resource lists have now changed, the role preferred owner and the clustered resource possible owner are now set to STOKECSCLNODE3.

Where the group has 2 synchronous replicas that are configured for Automatic failover, the resource lists differ. I reconfigured my Always On group to have the replicas

• STOKECSCLNODE3\INST1
• STOKECSCLNODE4\INST1

configured as synchronous replicas with automatic failover. The role preferred owners and resource possible owners lists are now as follows:

Important: These properties are dynamically managed, there is no need to intervene.

So there we have it, this concludes the article, we've covered the Always On group creation\configuration and also looked at some failover scenarios. We also looked at the quorum configuration and key feature information. Lastly we looked at failover modes and how they work within the group. I hope you find this series useful, keep an eye out for my Stairway to Always On series coming soon to SSC.

As always, have fun and if you're stuck post in the discussion thread, I'll help all I can.