AWS Advent Day – Deploying SQL Server in AWS

05. December 2014 2014, Uncategorized 0

Our third AWS Advent post comes to us by way of Jeremiah Peschka. You can find him fiddling with databas for BrentOzer

For one reason or another, you’re deploying SQL Server into AWS. There are a few different ways to think about deploying SQL Server and a few concerns that you have to address for each one. This doesn’t have to be difficult and, in some ways, it’s a lot easier than buying a physical server.

We’re going to take a look at three ways to deploy SQL Server in AWS. In two situations we’ll look at renting the licensing from Amazon and in two situations we’ll look at running our own instances. There’s some overlap here, but rest assured that’s a good thing.

SQL Server as a Service

One of the easiest ways to run SQL Server in AWS is to not run it at all. Or, at least, to make AWS run it for you. Amazon have a hosted database as a service product – Amazon RDS.

Benefits of SQL Server RDS

Good operational database administrators are hard to come by. SQL Server RDS doesn’t provide a good database administrator, but it does turn a large portion of database administration into a service.

Amazon provides:

  • An operating system and SQL Server
  • Automated configuration tools
  • Regular backups
  • The ability to clone a database
  • High availability (if you check the box)

In addition, you can provision new SQL Servers in response to customer demand as needed. The ability to rapidly spin up multiple SQL Server installations can’t be understated – new SQL Servers on demand is critical for multi-tenant companies. Abstracting away the creation, patching, and other operational tasks is a boon for small companies without experienced DBAs.

The Downside of SQL Server RDS

It’s easy to think that getting someone else to handle SQL Server is the way to go. After all, Amazon is responsible for just about everything apart from your code, right?

They’re not. While AWS is responsible for a lot of plumbing, you’re still responsible for writing software, designing data structures, monitoring SQL Server performance, and performing capacity planning.

Even worse, you’re responsible for the maintenance of all of this functionality and making sure that your index structures are free of fragmentation and corruption. It is still necessary for someone to set up jobs to monitor and address:

  • Index fragmentation
  • Database corruption

Even though AWS is doing some of the work, there’s still a lot left to do.

AWS Licensing

It’s possible to rent your licensing from AWS. This happens with SQL Server RDS, but it’s still possible to rent licensing if you’re using SQL Server Standard Edition. For many companies, this is an easy way to get into SQL Server licensing. AWS can offer a competitive price.

For teams who don’t need Enterprise Edition features, renting the licenses from AWS is an easy on-ramp. SQL Server Standard Edition supports a number of high availability features that are good enough for most applications. Many AWS instance sizes are small enough that the limitations of SQL Server Standard Edition – 16 cores and 64GB of memory (128GB for SQL server 2014) – isn’t a limitation at all.

Enterprise Edition and AWS

Sometimes you need more than 128GB of memory. Or more than 32 cores. In these case, you can buy your own licensing for SQL Server Enterprise Edition. Although it’s expensive, this is the only way to take advantage of the larger AWS instance types with their high core counts and reasonably large volumes of memory.

Many aspects of SQL Server Enterprise Edition are the same as they’d be for a physical SQL Server. The most important thing to realize is that this giant SQL Server is subject to some very finite scaling limitations – AWS doesn’t always have the fastest CPUs and the maximum instance sizes are limited. Scaling up indefinitely isn’t always an option. DBAs need to carefully watch the CPU utilization of different SQL Server features.

Embrace the limitations of AWS hardware and use that to guide your tuning efforts.

Lessons Learned

Follow a Set Up Guide

No, really. Do it. My coworkers and I maintain a SQL Server Setup Checklist. Don’t deploy SQL Server without one.

Script Everything

I can’t stress it enough – script everything.

Instance spin up time in AWS is fast enough that it makes sense to have a scripted installation process. Whether you’re scripting the configuration of an RDS SQL Server or you’re installing SQL Server on your own VMs, the ability to rapidly configure new instances is powerful.

Script your SQL Server setup and keep it in version control.

Use Solid State

  1. Don’t go cheap out and use rotational storage. 
    Just stop it. Even if you’re hosting a data warehouse, it’s not worth it. The throughput available from AWS SSDs is more than worth it.
  2. Use the local SSDs. 
    The ephemeral SSDs won’t last between reboots, but they are still local SSDs. SQL Server can take advantage of low latency drives for temporary workloads. Careful configuration makes it possible to house the tempdb database on the local AWS SSDs. Since tempdb is recreated on every boot, who cares if it goes away on instance restart?

Plan Carefully

Measure your storage needs in advance. In How Much Memory Does SQL Server Need?, I did some digging to help DBAs figure out the amount of I/O that SQL Server is performing. Measuring disk use (both in IOPS and throughput) will help your capacity planning efforts. It’s okay to get this wrong, most of us do.

SQL Server tracks enough data that you can make an educated guess about IOPS, throughput, and future trends. Just make sure you look at the metrics you’re given, figure out the best route forward, and have plans in place to deal with a mistake in your capacity planning.

Hardware is Limited

It’s easy enough to buy a big 4 or 8 socket with terabytes of RAM. But that’s not possible in AWS. Embrace limitations.

Instead of scaling up SQL Server as high as the budget will allow, embrace the constraints of AWS hardware. Spin off different services into other AWS services or bring your services into the mix. Don’t think of it as abandoning SQL Server, think of it as scaling your architecture. By moving away from a monolithic database server, you’re able to scale individual portions of the application separately.

SQL Server Full Text Search is one example of a feature that can be scaled out. Finding the resources to create a full text search server using ElasticSearch or SOLR can be difficult in a physical data center. With AWS, you can spin up a new VM, with software installed, in a few minutes and be ready to index and query data.

Licensing is Tricky

Starting in SQL Server 2012, licensing became core based, rather than socket based. And, because of how Microsoft licenses SQL Server in virtual environments, those core based licenses may not be what you thought they are. Check with Amazon or your licensing reseller to make sure you’re licensed correctly.

Wrapping Up

There’s no reason to fear deploying SQL Server in AWS, or any cloud provider. For many applications, SQL Server RDS fits the bill. The more customized your deployment, the more likely you are to need SQL Server in an EC2 instance. As long as you keep these guidelines in mind, you’re likely to be successful.