SQL Server 2012 AlwaysOn

With the release of SQL Server 2012, launch date of March 7 2012, release date April 1 2012. One of the more well-known features being included in the release is SQL Server 2012 AlwaysOn. For those of you who have had a look at SQL Server Code Named ‘Denali’ or as it is now known ‘SQL Server 2012’ you will be aware of some good blogs out there covering this topic.

When I started to look at this new feature I found not all of the information out there was clear or easy to understand, hence leading me to this blog post. SQL Server 2012 AlwaysOn has come about from the ever increasing need for higher ‘High Availability’ (HA). Currently, depending on your environment, you could be using one or more of the following HA components:

• Single Site Windows Server Failover Clustering
• Multi-Site Windows Server Failover Clustering
• San level Block Replication
• Transaction Log Shipping
• Database Mirroring
• Transactional Replication
• Peer-to-Peer Replication

To achieve the level of high availability you require, some of these can be quite expensive to implement, and may not be meeting your current requirements. This is where SQL Server 2012 AlwaysOn has been born and will be able to make this more achievable.

Windows Server Failover Clustering (WSFC)

This has been around for quite some time, providing server availability for your SQL Server’s to reside on to help give you the uptime your business requires. The technology for WSFC provides some really good functionality, and as such, it is part of the back bone behind AlwaysOn. The WSFC provides the ability to span multiple subnets, however, up until now SQL Server which is cluster aware does not support a clustered instance of SQL Server across multiple subnets. To achieve the setup across multiple data centres, this is quite expensive due to the WSFC requiring shared storage in both data centres as well as the block level SAN replication. You need to work closely with your storage vendors to get your setup correct.

Database Mirroring

Database mirroring gives you the ability to mirror databases from one instance of SQL Server to another, whether the second instance is located on the same server, a different server in the same data centre or to a server in another data centre. One of the problems with database mirroring is the inability to automatically failover a group of databases that are inter-related. What does this mean? If you have multiple databases residing in an instance of SQL Server and one of those databases is failed over to the secondary location (via your database mirroring setup), this database may be dependent on one or more of the other databases in the instance as well. In this scenario your application may not operate correctly. How do you manage to handle this situation? Another downside is that the mirrored database is not accessible. You can get around this using database snapshots to give you a ‘read only’ copy.

SQL Server 2012 AlwaysOn

With the Release of SQL Server 2012 this requirement of more granular control/ability for High Availability is now at your fingertips. AlwaysOn has the following key point benefits:

• Enterprise Edition Only
• Utilises the WSFC API’s to perform failovers. Shared storage is not required – Cheaper solutions
• Utilises Database Mirroring for the data transfer over TCP/IP
• Utilises a combination of Synchronous and Asynchronous mirroring
• Availability Groups provide a grouping of like databases
• Up to four readable secondary replicas
• Allows backups to be undertaken on a secondary replica
• Perform DBCC statements against secondary replica
• Built-in Compression & Encryption

AlwaysOn pre-requisites

Previously when you were wanting to build a WSFC you required the following pre-requisites:

• X physical/virtual nodes
• IP addresses for each node
• IP address for the WSFC
• IP address for the SQL Server’s to be built as Clustered Instances
• Shared storage
• Possibly multiple data centre’s
• Possibly spanned subnets (Not supported by previous versions of SQL Server)
• Service accounts

With SQL Server 2012 AlwaysOn even though the WSFC API’s are used along with Database Mirroring, the pre-requisites for building your solution is slightly different. Don’t think of clustering in the traditional way, the following pre-requisites allow you to build your SQL Server 2012 AlwaysOn environment:

• X physical/virtual nodes
• IP addresses for each node
• IP Address for the WSFC
• Possibly multiple data centre’s
• Storage in each data centre
• Possibly Spanned subnets (Not supported by previous versions of SQL Server)
• Service accounts

AlwaysOn Nodes

The nodes that you will use in your SQL Server 2012 AlwaysOn solution, have to be part of a WSFC. The first step we need to undertake in preparing our AlwaysOn nodes is to add the Failover Cluster Feature to each node. For More information on how to build a SQL Server 2012 AlwaysOn solution read – SQL Server 2012 Always On : Prerequisites.

AlwaysOn Storage

In what we know and understand as a traditional WSFC, whether it is a single site or multi-site, for the storage to be connected it needs to be presented as shared storage. This requirement leads to the storage being more expensive and a little bit more complicated. With SQL Server 2012 AlwaysOn your solution does not have to utilise shared storage. Your solution can utilise SAN, DAS, NAS, Local Disk, based on your expenditure and requirements. I suggest working with your storage providers to come up with the solution you need.

Synchronous & Asynchronous Mirroring

AlwaysOn utilises the database mirroring technology that has been around in SQL Server for quite some time. Synchronous Mirroring like its name indicates, requires the transactions to be written at both sites for the transaction to be completed. This can lead to increased latency in your system but also gives you zero data loss. Up to two secondary replica’s synchronously replicated per availability group is supported. Asynchronous Mirroring does not have to complete the transaction at the secondary site for the transaction to complete, thus can lead to potential data loss. The advantage of AlwaysOn over previous versions of SQL Server mirroring is the ability to have multiple usable secondaries of the database. Another advantage is the ability to have a combination of Synchronous & Asynchronous Mirroring in your setup. Depending on your HA/DR requirements you possibly could have a synchronous setup to a server in your local data centre and an asynchronous setup to a server in a secondary data centre.

Availability Groups

SQL Server 2012 AlwaysOn allows for the more granular control of your environment with the introduction of AlwaysOn Availability Groups (AG’s). AG’s allow you to configure groups of databases that you would like to failover all together when there is an issue at the host server. When configuring your AG’s you:

• Configure your AG on the Primary Replica
• Your AG contains the group of DB’s you wish to group together to failover to your secondary replica’s
• You will need to configure one or more secondary replicas; up to four secondary replicas, with any combination Synchronous (Maximum of two) and Asynchronous mirroring
• Your primary replica is available for read and write connectivity, while your secondary replicas can be configured for read-only, read intent or no access

For more information on Availability Groups have a read of – SQL Server 2012 Always On : Availability Groups.

Maintenance Tasks/ Reporting

With the introduction of AlwaysOn in SQL Server 2012, you are able to utilise the secondary replica’s that you have created when you setup your AG’s to undertake some regular database maintenance tasks to remove some of the performance overheads from your primary production server. Some of the tasks that you could look at undertaking on a secondary replica are:

• Database Backups
• Full Backup With Copy_Only
• Transaction Log Backups
• DBCC CheckDB
• Reporting
• Database Snapshots

Licensing

SQL Server 2012 has been released with a new license model. With SQL Server 2012 AlwaysOn’s ability to have multiple secondaries you need to take into account the licensing when you are going to be implementing multiple secondaries. The license model requires you to license your Active (Primary) SQL Server in your AlwaysOn Cluster. You are allowed one Passive (Secondary) server that you do not need to license. If you have more than one secondary server, regardless of it being active or passive you need to license that server. For more information on licensing have a look at the SQL Server 2012 Licensing Reference Guide and talk to your licensing reseller.

What is Active and what is Passive? Your Primary is active because you are accessing it and using the database. If you setup a secondary server to perform any of the tasks listed above in Maintenance Tasks/Reporting then this secondary is also active and needs to be licensed. Remember if you access it, it is active.

For example: If we were to have one Primary Server (Active), three Secondary Servers (one Active, two Passive) we would be required to license three of the four servers.

Security & Performance

With the movement of your data between data centres, to give you the added highly available benefits you are probably thinking about some of the dangers with the movement of this data, and the time it can take as well as the bandwidth being used to achieve your solution. Built-in to the Enterprise Edition you can take advantage of the Transparent Database Encryption (TDE) as well as Backup Compression.

Now that we have covered off the basics of what an AlwaysOn solution could possibly look like we are ready to start and plan for implementing this solution to meet your ever increasing High-Availability requirements and DR needs.

Your final solution could look something like this: