If you’ve ever wanted to use the cloud for a DR site, there’s good news: as of this week you can use AlwaysOn Availability Groups and set up an asynchronous replica of your data into Amazon Web Services (AWS). If you’re already in the cloud, or if you were holding off, this means that you can run multiple SQL Servers in several Availability Zones and Regions to scale out reads or survive outages.
What Can We Do With Availability Groups in AWS?
It’s possible to create an Availability Group and take advantage of almost all of the AlwaysOn Availability Group functionality. Readable replicas can be easily configured and with a few additional steps it’s possible to get the Availability Group Listener working. The listener is the part of the Availability Group that will send read requests to the readable secondaries. You really want this working – without the listener it’s up to you or your networking team to create and maintain load balancers and perform some kind of complex DNS dance to send writes to the correct SQL Server. That doesn’t sound like anyone’s idea of a good time.
The other piece of functionality that’s now available is manual failover. Failover didn’t work previously – there were limitations to how AWS handled networking and virtual IPs. While many of those issues have been resolved, a few still remain. Manual failovers work… with some caveats (more on that in a minute).
What Doesn’t Work With Availability Groups in AWS?
Automatic failover doesn’t work: Amazon’s networking stack doesn’t support it. But that’s okay, and here’s why – automatic failover in SQL Server 2012 AlwaysOn Availability Groups requires synchronous commit. This can introduce additional latency and we’re interested in keeping things fast. Instead use asynchronous replication and your SQL Servers will be within a few minutes, if not milliseconds, of each other.
What Happens When It’s Time To Failover?
When the time comes to failover, you’re going to have to do it manually. When the primary server, or even Availability Zone, goes down you’ll need to run three commands. If you’re failing over across multiple Availability Zones or regions, this is really simple. Connect to the new primary node in SSMS and run:
ALTER AVAILABILITY GROUP MyTestAG FORCE_FAILOVER_ALLOW_DATA_LOSS;
It’s just that simple. When that Availability Zone or Region comes back up, connect to the original primary node in SSMS and run:
ALTER AVAILABILITY GROUP MyTestAG FAILOVER;
When you’re failing over inside the same Availability Zone you need to move an IP address around the cluster manually, but the EC2 command line tools make this easy as well. Any computer that can connect to EC2 can run a combination of
ec2-assign-private-ip-addresses to move the listener to the new location in the Availability Group.
This sounds easy, and failing over is. However, this isn’t exactly easy to set up. SQL Server AlwaysOn Availability Groups have a couple of things to watch out for.
What Should You Watch Out For
AlwaysOn Availability Groups are an Enterprise Edition feature of SQL Server. To deploy SQL Server AlwaysOn Availability Groups in AWS you’re going to have to bring your own licensing. More than that, it means that you won’t be able to use one of Amazon’s SQL Server images that they provide – you’re going to have to install SQL Server on your own VMs unless you want to pay for SQL Server licensing twice. If you want to make it easy to automate deploying SQL Server in AWS you’re going to have to take a lot of specialized steps that are well outside of most DBAs’ job duties – sysprepped SQL Server installations and command line Linux enter into the picture. You’ll have put in a lot of effort to automate your SQL Server deployments but it will be worth it.
This isn’t exactly easy to set up. SQL Server AlwaysOn Availability Groups have a number of installation steps and deploying everything in a purely virtual environment adds additional complexity. If you thought that configuring AlwaysOn Availability Groups was tricky on hardware in your data center you’ll find that configuring AlwaysOn Availability Groups in AWS adds a few layers of complexity.
Why You Should Care About Availability Groups in AWS
Even though not all of the SQL Server AlwaysOn Availability Groups functionality can be used, this is a huge step forward for businesses who are using the cloud or have been considering a move to AWS. Companies can use their existing SQL Server 2012 licensing to establish their Availability Group. Availability Groups make it easy to have multiple DR sites. With recent cloud outages (both in Azure and AWS), companies are aware that even massively redundant cloud hosting is vulnerable. AlwaysOn Availability Groups make it possible to implement a highly available deployment strategy that can be used to survive severe outages.