Configure SQL Server Standard Edition for High Availability on AWS
It’s not always clear how to build a high availability (HA) infrastructure for the Microsoft SQL Server Standard Edition on Amazon Web Services.
After all, there are two different paths leading to a SQL Server deployment on AWS. One involves the Amazon Relational Database Service (Amazon RDS), and the other involves Amazon Elastic Compute Cloud (Amazon EC2).
Either one can be configured across multiple availability zones — essentially, separate data centers — to ensure that there’s no single point of data center vulnerability.
Amazon RDS is a fully managed service; you only need to tell AWS that you want a multicenter instance of SQL Server. AWS will set it up for you, and you’re off and running.
With Amazon EC2, though, you will need to do most of the work setting up and maintaining your infrastructure. AWS will deploy and support the underlying virtual machine (VM) that you’ve chosen, and it will ensure that the operating system running on the VM is properly configured and updated, but on Amazon EC2, you’re in control from there on out.
Amazon RDS makes it easy to get up and running, but Amazon EC2 gives you greater flexibility and control. The environment you choose for a HA deployment of SQL Server Standard Edition depends on which release of SQL Server you want to use, the number of databases you’re going to use and the level of high availability you want to ensure.
Which Release of SQL Server to Use?
Let’s start with the release of SQL Server you intend to use. After all, if you’re already using a specific release of SQL Server and are contemplating a move to AWS with the same release, you’ll want to make sure that you can use that release in the environment you choose.
Amazon RDS supports all releases of SQL Server Standard Edition that are currently mainstream or in extended support. Practically speaking, that means the 2014, 2016, 2017 and 2019 versions of SQL Server. As long as you’re interested in using one of these releases, Amazon RDS can configure a HA solution with the version of SQL Server Standard Edition that you specify.
However, if the application you want to run relies on another release of SQL Server — say, the 2012 or 2008 R2 releases — then you’ll have to go with the Amazon EC2 option. Amazon EC2 provides only the virtual hardware for your HA configuration; on that VM, you can run any licensed release of SQL Server that you want.
How Many SQL Server Databases Will You Run?
This quickly becomes an important question when deciding whether to run SQL Server Standard Edition on Amazon RDS or Amazon EC2. Amazon RDS ensures HA through the use of SQL Server’s Basic Availability Group (AG) feature.
This ensures that any data written to the SQL Server database in storage attached to the active VM will also be written to a copy of that SQL Server database in storage attached to the secondary VM sitting in another AZ.
If for some reason the active infrastructure goes offline, SQL Server’s Basic AG feature will failover to the secondary infrastructure, and your operations can continue with minimal interruption. Because the data from the previously active instance of SQL Server is already present in storage on the secondary infrastructure, you can essentially pick up operations where they left off before the primary infrastructure went offline.
The downside of relying on the Basic AG functionality to ensure HA on Amazon RDS is that SQL Server Standard Edition permits the replication of only one SQL Server database between the active and secondary infrastructures. You might be able to create multiple AGs, each replicating a single SQL Server database, to overcome this limitation, but that quickly becomes cumbersome.
Each AG will have its own listener, and there’s no guarantee that all the databases will failover together. That could cause problems if your databases are dependent on each other. You could replicate multiple SQL Server databases in a single AG if you were to configure your Amazon RDS infrastructure using the “Always On” AG functionality built into the SQL Server Enterprise Edition, but that would require you to move from SQL Server Standard Edition to the more expensive Enterprise Edition of SQL Server.
If you’re committed to using SQL Server Standard Edition, Amazon EC2 provides you with other options for ensuring HA.
Instead of using the AG functionality in SQL Server Standard Edition in an Amazon EC2 environment, you could configure your VMs in what is known as a failover cluster instance and rely on SIOS’ SANless Clustering software to orchestrate data replication. SANless Clustering software replicates data from primary to secondary infrastructure, just like the AG functionality in SQL Server does, but the SANless Clustering approach imposes no limit on the number of databases you can replicate from one infrastructure to another.
All the SQL Server databases on your primary infrastructure could be replicated to secondary infrastructure — and you would gain this benefit at a far lower cost than you would if you upgraded to SQL Server Enterprise Edition on Amazon RDS.
What Level of Availability Does Your Environment Require?
The final difference to consider when weighing your Amazon RDS vs. Amazon EC2 options comes down to the degree of application availability you require.
Amazon RDS is a fully managed service, which certainly lifts many burdens from the shoulders of your IT team. However, the service-level agreement (SLA) offered for HA under Amazon RDS is 99.95% availability vs. an SLA of 99.99% availability offered under Amazon EC2.
An SLA of 99.95% means that your SQL Server configuration could be offline for up to four hours and 22 minutes per year in an Amazon RDS environment — and still be compliant with the SLA. During a downtime event, none of your VMs or SQL Server databases will be accessible. In comparison, the Amazon EC2 SLA of 99.99% caps downtime at less than 53 minutes per year.
Here, it’s really up to you to determine what level of availability you ultimately require. If more than 53 minutes per year of potential downtime is unacceptable, then Amazon EC2 is the right path. You might use the Basic AG functionality of the SQL Server Standard Edition if you have only one database to replicate, but you could use SANless Clustering software to replicate your databases if you have more than one database you need to replicate reliably from your primary to secondary infrastructure.
You also gain the option to use whatever release of SQL Server you want, which may be critical if you’re still relying on a release of SQL Server Standard Edition that’s more than a decade old and don’t want to port your application to a later version of SQL Server.
