Blog | Sep 19, 2013

A revolution in High Availability and Data Recovery Planning!

Downtime, planned or unplanned, is an important aspect in service delivery. Maximum Uptime and data availability is what organizations aspire for all the time. An efficient HA / DR solution which is also cost efficient is the need of the hour.

One such functionality that helps meet the above objective is SQL Server 2012 AlwaysOn.

Let’s understand what it is all about:
Introduced in MS SQL Server 2012, AlwaysOn is gradually becoming a preferred feature in building a robust disaster recovery solution. It is definitely revolutionizing the way we see and think about our data availability and recovery options. AlwaysOn can be classified into two major components:
• AlwaysOn Availability Groups, for database level failover
• AlwaysOn Failover Cluster Instances, for instance level failover


AlwaysOn Availability Groups

Prior to this we have been using Log Shipping, Mirroring, Replication and Failover Clustering as the HA and DR options.  These were used either for a single database or for all databases, in case of failover clustering.
With AlwaysOn, the most interesting thing that one can do is to create a group of databases and fail them over as a group. So, if your application depends on multiple databases then you can simply group all the databases together and create a HA / DR solution. This grouping of databases is known as Availability Groups in AlwaysOn. With AlwaysOn, the emphasis has been to combine all the HA/DR solutions so that we have a holistic solution. Below is what we can achieve with AlwaysOn Availability Groups:
• Active Secondaries
• Multi-Secondaries
• Multi-Database failover


For each Availability Groups, we can now have multiple replicas. This would mean that we can have one or more local replica as well as one or more remote replica as a DR solution. There are certain backup operations that can now run on the secondary. Log backups done on all secondary’s form a single log chain, thus making a recovery possible by using backups from any secondary. So, it is recommended that the log backups are going to a single location. The Database Recovery can actually help in making the recoveries faster and simpler using the backups.

You can also have Read-Only replicas that can fulfil the entire reporting requirement, taking significant load off the primary server. This means less maintenance, and helps get rid of the overhead of snapshots.
Another interesting thing about Availability Groups is that it allows you to failover different groups to different secondaries.  You need not have a secondary which is at par to the configuration of your primary to handle the entire load, if needed. You can use less powered servers as secondaries to distribute the load. And more importantly, less powered servers means less cost to the company, something which your management will surely like.

AlwaysOn Failover Cluster Instances (FCI)

Before AlwaysOn, SQL Server failover could only happen on the servers that were on the same subnet. In case you are looking for a DR / HA solution across geographical locations you need to make sure that it falls in the same subnet, something difficult to achieve. The reason behind this is that SQL Servers contributing to a failover cluster use to bind the IP addresses of the servers thus not enabling a failover across subnet.

With AlwaysOn, it is a thing of the past, as failover clustering has been enhanced to perform faster failovers and provide support for a multi-site clustering. You can now perform a failover (cluster or mirror) across subnets while implementing a true Multi-Site DR / HA solution for your organization that enables you to now setup a DR site between, for instance – Somerville and Chicago, easily. Importantly, you can reside your tempdb on your local drives.

In addition, with AlwaysOn FCI you can actually control your failover. This is termed as Flexible Failover Policy. This policy provides administrators the control over the conditions as to when an automatic failover is triggered. In a traditional cluster failover setup there is a continuous call that is being made to SQL Server to make sure it is available. If your server is too busy and does not respond in a timely manner the cluster setup can assume that the service is unavailable and force a failover, thus causing a false failover.

With AlwaysOn FCI, there is a stored procedure sp_server_diagnostics which is continuously sending health data of the SQL Server to the cluster setup. This includes Service State, Responsiveness of SQL Server Service and SQL Server component diagnostics (system, resource, query process, IO, events). The failure conditions are then classified on a 0 – 5 scale where 0 being the lowest priority and 5 being the highest. Selecting a value of ZERO in failover policy would actually mean no failover will be done even if the services are down, 1 may mean that automatic failover would happen only if SQL server services are down. Similarly, as you go up you can actually control that on what condition the automatic failover will happen. This option is very interesting, unique and thoughtful.

Post research on this new feature of SQL Server 2012, it can be said that AlwaysOn is a giant leap towards achieving high availability and fast data recovery solutions in comparison to the earlier versions of the SQL Server. This is definitely one of the top five new features introduced in this version.

SQL Server 2014 and some more new enhancements are on its way. The maximum number of Active Secondary’s is expected to go up from 4 – 8. Stay tuned.

For more information click below:

http://technet.microsoft.com/en-us/library/hh510230.aspx
http://technet.microsoft.com/en-us/library/ms189134.aspx