High availability with SQL Server 2012 and 2014

Always Ready

Hybrid Deployment Scenarios

Migration "into the cloud" long seemed to be a clear trend. With industrial espionage and the NSA scandal making the headlines, however, data security has increasingly gained relevance. On-premises hosting and co-location in a data center still remain the preferred route for many companies.

On-premises use is exactly the opposite of a public cloud: It offers ultimate control over data security while generating higher costs (hardware and software, personnel costs, Internet connection, etc.). The available performance needs to be scaled up; companies concerned cannot avoid investing. Additionally, companies have no viable way to make excess capacity available to others and help the investment pay back faster. In an on-premises scenario, AlwaysOn Failover Cluster Instances in the Standard Edition of SQL Server with SAN storage are a viable option. In the cloud, things look different.

High Availability in the Cloud

Running SQL Server in a virtualized cloud environment can achieve cost reductions of up to 80% compared with on-premise use. However, virtualization introduces the additional risk of failure of the infrastructure itself, which, in turn, makes high-availability solutions in the cloud all the more essential. Unfortunately, these are not as easy to implement  – especially with SQL Server.

A good example is Amazon Web Services (AWS, the leading IaaS provider). AWS offers two solutions for SQL Server: EC2/VPC (Elastic Compute Cloud/Virtual Private Cloud) and a turnkey service known as RDS (Relational Database Service). Both services also support other databases, including MySQL and Oracle.

Companies that want to migrate their MS SQL server environment with failover cluster instances into the AWS cloud and implement in EC2/VPC are – unfortunately – in for a nasty surprise: The use of AlwaysOn Failover Cluster Instances requires the configuration of two EC2 instances on the same subnet or the use of shared SAN storage. Neither of these scenarios can be currently implemented on AWS. The only way of implementing high-availability with SQL Server that is supported in EC2/VPC is the use of AlwaysOn High Availability Groups with the Enterprise Edition.

SQL Server EC2/VPC

Amazon's infrastructure in each region is divided into availability zones (AZs). The failure of one zone does not affect the availability of the infrastructure in another zone. The latency between the different zones within a region is very small. This architecture is thus suitable for building an AlwaysOn Availability Group with SQL Server Enterprise.

A successful installation of SQL Server with AlwaysOn High Availability Groups typically requires the following components (using AWS in a VPC with subnets in two different availability zones as an example):

  • At least two instances of SQL Server – that is, 4x2 licenses of SQL Server Enterprise (SQL Server licenses are available for two virtual cores; the smallest supported instance thus needs two SQL Server license packages for four cores)
  • A Windows Server Failover Cluster (WSFC) with two nodes (one node per availability zone), each hosting one of the two installations of SQL Server
  • Two instances of Windows Server with Active Directory (one per availability zone)
  • Two instances as an intermediary for administrative access using Remote Desktop Gateway (one each per availability zone)
  • Two NAT instances running Linux for administrative access to the infrastructure of the VPC in each availability zone.

This configuration supports automatic failover between two WSFC nodes in two different availability zones in a region.

Incidentally, Microsoft offers its own alternative to Amazon EC2: Windows Azure Virtual Machines [4]. As with Amazon, users can also set up their own SQL Server licenses on an instance of Windows Server or rent one of the factory preinstalled instances with a license in place at an hourly rate.

Buy ADMIN Magazine

Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus