SQL Bits: Working with AlwaysOn Availability Groups in SQL Server 2012

by Dmitry Kirsanov 24. February 2013 14:23

In SQL Server 2012, we have 4 main options for High Availability: Database Mirroring, Failover Clustering, Availability Groups and Log Shipping. All but Availability Groups (AG) are available in SQL Server 2008 as well. Today we’ll review Availability Groups and how to create and use them.

Before we begin, make sure you understand what is Windows Server Failover Cluster (WSFC). I covered it almost a year ago for Windows Server 2008 R2 and SQL Server 2008 R2, and you’ll need WSFC in order for AG to work.

A few points about Availability Groups to make sure they are what you are looking for:

  • Availability Groups are not share-nothing scalability option. They are using shared storage, such as storage area network (SAN), although it’s not a requirement. We’ll speak about share-nothing scalability later.
  • For AlwaysOn Availability Groups, you need SQL Server 2012 Enterprise edition or higher. Business Intelligence edition or Standard won’t do.
  • AG provide failover with up to 5 readable nodes.
  • AG provide you with something like load balancing option, but AG is not NLB. Also, all nodes can work independently, i.e. you can still connect to them to retrieve information. Only the primary node can change information in the database.
  • AG does not require working in the same IP subnet, but it requires Active Directory domain. This means, that you can have geographically remote nodes participating in one AG, but they must be members of the same AD domain.
    In case of multi-subnet cluster, you’ll have to implement the file replication solution on your file servers to synchronize the data.
  • AG is database-level, as opposed to node-level failover cluster. AG serves group of databases, unlike the simple database mirroring, with 4 replicas instead of one.

Overall, the AlwaysOn Availability Groups are conceptually similar to database mirroring, but provide more advanced functionality and security.

Windows Server Failover Cluster

WSFC provides database-level high availability to AlwaysOn Groups. You need to create the WSFC and add all instances of SQL Server 2012 as members of the cluster. However, it doesn’t mean that you must install these SQL Server instances as clustered. They should be installed as standalone instances and so has it’s own dedicated storage. Every node operates it’s own copy of the database.

At the video in the end of the article we’ll have the cluster already installed and will only check that it’s here, up and running.

AlwaysOn Availability Groups

AlwaysOn Availability Groups may contain 2 to 5 nodes. One node is called Primary, and it hosts the read-write copy of your database. Other 1 to 4 are Secondary nodes, and they may or may not provide data to consumers (either users or applications).

Secondary replica holds full copy of each database in availability group. When needed, you can set up read-only access to the databases on specific node, or no access at all. Read-only or no access setting will only work, when node is Secondary. If, during the failover or manually, Secondary replica will be promoted to Primary, it will serve all requests.


Failover event is when your primary replica fails. In that case, Secondary node which can be automatically promoted to Primary, will be promoted automatically. Such nodes are known as Failover Targets. You may also have secondary nodes which may not be automatically promoted to primary. The new primary replica then recovers its databases and makes them available to users of the availability group.

There are 3 types of failover in AG:

  1. Automatic failover (without data loss). Automatic failover is only available when both the primary replica and the secondary replica are running in synchronous-commit mode, and the failover mode is set to automatic. In automatic failover mode, failure of the primary replica causes failover to the secondary replica without the need for administrator intervention. No data loss will occur on failover.
  2. Planned manual failover (without data loss). Planned manual failover is only available when both the primary replica and the secondary replica are running in synchronous-commit mode. In planned manual failover mode, a database administrator must issue a failover command to initiate failover. No data loss will occur on failover.
  3. Forced manual failover (with possible data loss). Forced manual failover is the only failover type that you can use for replicas that are in asynchronous-commit mode. You must initiate forced manual failover manually. Any transactions that were committed on the primary replica, but which the secondary replica has not yet written to its log, will be lost. You can also use forced manual failover for replicas that are in synchronous-commit mode when the secondary replica is not showing as synchronized with the primary replica.

Load balancing with Active Secondary Replicas

All your nodes can be set as Active Secondary replicas. In that case, you can route connections with read-only intent to these secondary nodes, thus reduce the workload from your primary replica. That’s the point of read-only intent – when you are connecting to the AlwaysOn Group and not specifying the intent, you’ll connect to the primary replica. If you will specify the intent when connecting to the AG, you may be redirected to active secondary replica instead.

You can also take backups from Active Secondary Replicas, which can further reduce the workload placed on the primary replica.


One of the settings you’ll have to set when creating your AlwaysOn Availability Group is the backup creation policy. You’ll have to identify the nodes responsible for backing up the database. Since most of the load will be on your primary replica, I would recommend to not make backup the responsibility of the primary replica. Backups may consume significant server resources, especially when they are compressed.

During the creation of AG, you’ll see a few options you have for backups.

Active secondary replicas support two types of backup operations:

  • Log backups.
  • Copy-only backups of the database, filegroups, or files.

You can specify how backups are performed in the availability group by configuring the automated backup preference property of the availability group. The options for automated backup preference are:

  • Only on the primary replica. This setting helps ensure that all backups occur on the primary replica.
    You can use this setting when you need to run a backup job, such as a differential backup, that is not supported on active secondary replicas.
  • On secondary replicas. This is the default setting. This setting runs all backups on an active secondary replica; if there is no active secondary replica available, backup jobs will run on the primary replica.
  • Only on secondary replicas. This setting runs backup jobs on active secondary replicas only. If there are no active secondary replicas available, backup jobs will not run.
  • No Preference. That is – run the backup on any node. You can use the backup priority values to set the priority for specific replicas, on the scale from 0 to 100. 100 is the highest priority, and 0 means that replica won’t be used for backup jobs.

Implementing AlwaysOn Availability Groups

The easiest way to create an AlwaysOn Availability Group is to use the New Availability Group Wizard in SQL Server Management Studio. You can also create an availability group by using the graphical tools in SQL Server Management Studio, running Transact-SQL statements, or running PowerShell cmdlets.
Regardless of the tool you use to create an availability group, you must perform the following

  1. Install the Failover Clustering Windows Server feature on each server that you want to include in the availability group.
  2. Create a Windows Server failover cluster that includes all of the servers. You must specify a name and IP address for the cluster.
  3. Install a standalone-instance of SQL Server 2012 on each server in the cluster. You must install the database engine feature, and optionally the management tools feature.
  4. Use SQL Server Configuration Manager to enable AlwaysOn Availability Groups for each server in the cluster.
  5. Create a file share for the backup files used to synchronize the availability group replicas.
  6. Create the databases you want to protect on the server that will become the primary replica, and perform a full backup of each database.
  7. Create an AlwaysOn Availability Group, specifying:
  • A name for the availability group.
  • The databases to be included in the availability group.
  • The replicas (server instances) to be included in the availability group, including the type of replica (primary or secondary), the type of failover supported (manual or automatic), the type of synchronization to be used (synchronous or asynchronous), and the read-only support (none, read intent only, or full) for each replica.
  • The endpoints to be used by the replicas.
  • The backup preferences for the availability group.
  • Optionally, listener configuration for the availability group, including a DNS name, port, and IP
    address for the listener.
  • The file share to be used for synchronization (which should be the file share you created in step 5).

The Life after Five

AlwaysOn Availability Group allows you to join up to 5 nodes into one powerful cluster. But what if you’ll have to scale out wider? There are two options. One of them is to use SQL Server 2012 Parallel Data Warehouse edition – that is, to buy the appliance by either HP or Dell. It’s not cheap. Another option is to create a sophisticated share-nothing scaling solution, which we’ll cover soon, as that’s really fascinating topic. Well, if you are still reading this, anyway.

Hope it was informative enough, and if so – please, leave a comment or click “like” wherever you see it – that helps to set priorities for future topics.

And here’s the video of how to set up the AlwaysOn AG in your premises in 10 minutes or so. Enjoy!


Setting up the AlwaysOn Availability Group in SQL Server 2012
blog comments powered by Disqus