Our Blog

where we write about the things we love

04

May

High Availability and Microsoft SQL Server 2012

In this blog I will be providing a high-level overview of the high availability options in Microsoft SQL Server 2012, and how these compare with the solutions provided in earlier versions of SQL Server.

Before explaining the differences, I think it’s important to differentiate between high availability (HA) and disaster recovery (DR), terms which can often cause some confusion.

Essentially, a HA solution minimises or eliminates data loss and impact to the users. Depending on the whatever client service level agreements may be in place, the annual downtime can range from five minutes (99.999% availability) to three days (99% availability). To be able to achieve this goal, an automated failover is required which delivers little to no impact to the user.

Conversely, a DR solution requires manual intervention to achieve failover, and because of this delay there will typically be some level of data loss.

It’s worth noting that any self-respecting DBA would recommend using a combination of both HA and DR. Such a “belt and braces” approach ensures that in a highly unlikely situation of the HA solution collapsing, we still have a DR instance of SQL Server to fall back on.

Below is an overview of the current high availability features available in Microsoft SQL Server 2012, including two tried and tested options (Clustering and Mirroring) that have been available in past releases in SQL Server, and a completely new feature (AlwaysOn).

Clustering

First introduced in SQL Server 6.5, clustering has been around for many years and has progressively improved.

The main advantage of clustering is that it can recover from either a software or hardware failure. The failover is seamless to the application and, in the case of a failover cluster, only one SQL Server license is required.

The downside of clustering is that shared storage is required; this can be costly and only single database copies exist – which can be considered a point of failure.

Mirroring

Mirroring was introduced in SQL Server 2005 and has proven to be a great high availability solution. Mirroring has three different operating modes: High Availability, High Performance and High Safety.

  • High Availability: Uses a witness server to achieve a quick automatic failover between the principal and mirrored SQL Server. All transactions are committed to both the mirror server and the principal server (synchronous-commit) ensuring there is no data loss. There are costs to achieving these benefits: performance overheads are incurred for the application, there are high-speed network requirements and there can be geographical restrictions.
  • High Performance: Does not have a witness server and only has manual failover. All transactions are committed on the principal server before transferring to the mirror (asynchronous-commit), while performance, overall, is not affected. It is possible to lose transactions in the event of a failover. This approach is ideal for DR.
  • High Safety: Not recommended for normal operations, this option should only be used if replacing the witness server as it prevents any new connection to the mirrored databases.

The downside of mirroring is that three SQL Servers are required for the principal, mirror and witness. While the witness server can be run using SQL Server Express, the other two servers will require SQL Server licenses. The mirror SQL Server is also unusable when the databases are in a restoring state.

SQL Server AlwaysOn

AlwaysOn is the new integrated HA solution for SQL Server 2012. AlwaysOn consists of two features: AlwaysOn Availability Groups and AlwaysOn Failover Cluster.

AlwaysOn Availability Groups is database mirroring on steroids, providing flexibility in terms of database failover as well as zero data loss. Availability Groups use Windows failover clustering and support both shared and non-shared storage. Databases are placed into availability groups; multiple availability groups can be created allowing a DBA to separate the databases by client or by application. Each of the availability groups can be added to a replica; SQL Server 2012 allows one primary replica and up to four secondary replicas. The advantage is not just database redundancy, but the secondary replicas can be used for executing reporting and other read-only operations (database backups included). The limit to the number of replicas that can have synchronous-commit and therefore automatic failover is two. The number of replicas that can have asynchronous-commit and manual failover is also limited to two.

AlwaysOn Failover Cluster Instances (FCIs) is, as the name suggests, failover clustering with extras. The new features available in FCI are as follows:

  • Multi-Subnet Clustering: This is new to SQL Server and allows clusters to be geographically dispersed (otherwise called ‘stretch clusters’) without the need for shared storage. Data is replicated between storage devices on multiple subnets, delivering a “belt and braces” solution that ticking both boxes for HA and DR.
  • TempDB on Local Disk: TempDB generates some of the highest disk I/O, and the ability to move TempDB on to a local disk will certainly improve performance.
  • Flexible Failover Policy: Eliminates the issue of false failover by allowing you to choose what failure conditions trigger failovers.

It’s also worth noting that the FCI can also be used with Availability Groups, as long as the Availability Groups have shared storage and manual failover.

Operating System Support for SQL Server 2012 Deployment

Windows Server Core was first released with Windows Server 2008, however earlier versions of SQL Server were not compatible with Windows Core; this has changed with the release of SQL Server 2012.

The advantage of Server Core is that it’s a minimal deployment of Windows, which dramatically reduces patching and potential planned downtime (up to 60%). The attack surface is also reduced which makes the server more secure. Only a subset of SQL Server’s features are available on Windows Core. These include:

  • Database Engine Services
  • SQL Server Replication
  • Full Test Search
  • Analysis Services
  • Client Tools Connectivity
  • Integration Services

The additional SQL Server features can only be used by remote connection to the SQL Server Core instance.

Summary

Microsoft has significant enhanced high availability in SQL Server 2012.

Historically, a general rule with high availability solutions is that the more uptime you require, the greater the cost. This rule still applies in SQL Server 2012, as the AlwaysOn feature is only available in the Enterprise Edition of SQL Server 2012.

With SQL Server 2012 having only recently been released, it’s still too early to see if they are any cost savings in adopting the SQL Server AlwaysOn feature for clients with small to medium sized SQL Servers. Even though each replica can be used as a read-only database, a production SQL Server license is still needed. However, AlwaysOn does give the DBA the ability to achieve a 99.999% uptime benchmark straight out of the box and that in itself is a cost saving.

The failover clustering option offered by the Standard Edition remains the same as SQL Server 2008 R2 with the advantage of running on Windows Server Core Edition; to me is an ideal cost effective HA solution.

Features Available with each SQL 2012 Server Edition

Finally, I would also recommend using the Server Core Edition for those clients that have replicated SQL databases in the DMZ.

Overall the SQL 2012 HA features provides DBAs with significantly more flexibility and stability in implementing a better-fitted solution that can meet clients’ needs while also requiring less support and maintenance in the long-term.

Posted by: Senior SQL Data Base Administration Consultant, Senior SQL Data Base Administration Consultant | 04 May 2012

Tags: Microsoft, SQL Server 2012


Top Rated Posts

Blog archive

Stay up to date with all insights from the Intergen blog