Amazon Web Services (AWS) is a platform that offers a ton of services ranging from block storage, operating systems, to mail queues, DNS, and caching. When you say “cloud”, a lot of people think of AWS. AWS hosts a variety of businesses, large and small. Unfortunately, you still have to administer and configure your servers. That’s not a bad thing, but it’s something that many teams have to keep in mind. Configuring SQL Server in AWS environment isn’t the easiest thing on earth: the technology behind the AWS platform changes on a regular basis and it sometimes changes in subtle ways that can change how SQL Server performs.
Relational Database Service
Amazon Relational Database Service (RDS) is Amazon’s attempt at creating a hosted database platform on top of the other AWS services. The point is to take a lot of the headache out of managing relational databases. Instead of setting up and configuring servers for MySQL or Oracle, customers can have a database server up and running in a few clicks. Backups happen automatically, restores are easy to accomplish, and instances can be easily added or removed.
The problem is that there hasn’t been a way to do this with SQL Server. Companies using the Microsoft stack but invested in AWS have had to set up and configure their own SQL Servers. While this is normal for many businesses, for start ups this is an extra expense – it means that the servers are either configured by developers or operations staff, or that the company has to bring a DBA on board sooner than they had planned.
Enter SQL Server RDS
Amazon have launched SQL Server RDS: a hosted SQL Server service. Users can easily create instances of SQL Server through a wizard, web service, or command line script. It’s easier than ever to scale your infrastructure in the AWS environment.
Previously it was possible to create a new instance of SQL Server by spinning up a new SQL Server equipped AMI, but it still took time for both Windows and SQL Server to finish the sysprep process. On top of the instance set up time, a DBA would still have to configure the instance with additional security and configuration settings. In short, you could spin up instances of SQL Server in Amazon, but someone on your own team was still responsible for patching and maintenance.
Like every other AWS service, there’s a free usage tier. Developers, or really lean applications, can start out using a free version of the software and migrate up to larger instances as needed. The majority of the instances types are supported, apart from a notable absence of cluster compute instances.
SQL Server RDS gives you most of the features of SQL Server – it compares pretty closely with SQL Server and many features are fully supported. Amazon are pretty explicit about which features aren’t supported, so if you’re expecting a SQL Server Agent, maintenance plans, or the DTA, then you’re out of luck.
Update: Things change all the time in AWS, and SQL Server RDS is no exception. As Ola Hallengren points out below, SQL Server RDS now supports the SQL Server Agent as well as SQL Server 2012, Provisioned IOPS drives, and being run inside a VPC. Make sure you check the supported feature list or the AWS blog.
There’s a lean set of features that can be relied on to work even if your SQL server instance needs to be restarted or if the OS disk becomes corrupted. Features that rely on MSDB or any other system databases simply can’t be used because there’s no guarantee that you’ll be attached to the disk when your new OS volume is spun back up. By making sure that the OS volume never changes, it’s very easy to upgrade an instance – you just attach a new OS volume with the requisite changes at the next reboot. Because of limited access to the system database, many SSMS standard reports don’t work.
SQL Server RDS imposes a hard limit of 30 databases per instance of SQL Server. While that seems purely arbitrary, remember that there’s an unknown pool of storage underneath the instance that is invisible to you as a consumer of the product. On that note, you also can’t scale the storage underneath a SQL Server RDS instance – once you set up your storage at a certain level, that’s the amount of storage that you are stuck with.
Many DBCC commands don’t work at all –
DBCC DBINFO and
DBCC LOGINFO were explicitly denied. I did, however, discover that I could run
DBCC FREEPROCCACHE as often as I wanted.
Keep in mind that these limitations may change over time – new features are added on a regular basis.
Configuring SQL Server
Some system tools, like
sp_configure are not available as a way to set server level settings – you can run
sp_configure and see the configuration values, but you can’t change them directly in SQL Server. Instead, you create a SQL Server parameter group.
DBAs may find it frustrating to use command line tools to set up SQL Server parameters, however once a parameter group has been created, it’s incredibly easy to apply the parameter group to any number of SQL Servers at the same time. The upside is that both trace flags and
sp_configure settings can be modified at the same time. The downside is that the commands to set up the parameter group are not at all intuitive. Changing a the max server memory looks something like this
rds-modify-db-parameter-group sqlservergroup --parameters "name='max server memory (mb)', value=65536, method=immediate". Intuitive? No. Powerful? Yes. Once you’ve set up the parameter group, you just tell RDS to create new SQL Servers inside that parameter group and they will automatically be started with those trace flags and settings in place.
How are backups configured and maintained? By you… in a way.
During instance creation, you specify a backup retention period between 0 and 30 days. You can also specify your backup window and maintenance window – just in case you want to specify quiet hours when maintenance should occur. Point in time restore is available via a GUI, command line tools, and that’s it. Keep in mind that these aren’t database level restores,these are instance level restores – during the backup, writes will be quiesced, the storage will be snapshotted, and then writes will resume again. Point in time recovery is also available and transaction logs are backed up every 5 minutes. There’s more information and details in the AWS RDS documentation.
And A Gotcha
When you create a user, they’re automatically given access to the rdsadmin database. This is a database that the RDS team have created to provide access to some of the low level functionality, like reading trace files and other actions that you normally must be a sys admin to perform. The big gotcha here is that any user who can access the SQL server can, by default, access the rdsadmin database and execute the stored procedures in the database. Admittedly, the stored procedures that could potentially cause any harm aren’t accessible, but the unprivileged user can still see them.
sp_helprotect shows that
guest has privileges to execute these stored procedures. Although
guest can run the stored procedures, the procedures that can potentially cause changes will fail with errors like
Login needs CREATE ANY DATABASE permission. So while a malicious user could determine that you’re running your system on RDS, there’s only a limited amount they could do from that point.
When Would You Use This?
Let’s be fair, it isn’t apparent to everyone when they might want to use a hosted SQL Server. There are a lot of reasons why you would want to stand up a brand new SQL Server quickly.You may want to create multiple backend servers as you scale out your application – maybe you create one instance per customer to make billing easier. Developers can spin up a full copy of the application stack to test how changes will work – they no longer need to maintain a copy of the database locally, instead a clone of the production system can be restored and made available in a matter of minutes.
How Does It Perform?
SQL Server RDS performs admirably. I ran a variety of different benchmarks against my test instances and I maxed out the CPU of the RDS instance at 42% while running around 710 transactions per section and sustaining a decent rate of throughput. The biggest bottleneck was the CPU and network connection on the clients running the benchmarking tools. Obviously, benchmarks aren’t a real measure of anything apart from the benchmark’s performance, so test your application appropriately.
Alerting and Monitoring
Amazon haven’t forgotten about setting up alerts and monitoring. Every piece of AWS comes with instrumentation in the form of Amazon CloudWatch. CloudWatch is a set of metrics that are collected from underlying OS or application server. Even though you have no access to the underlying OS, SQL Server RDS exposes many metrics that can help you determine whether you need to move to a larger instance, monitor CPU, tune for memory, or purchase more storage. It’s even possible to stack graphs to see how two instances compare to each other.
Beyond that, it’s easy to make alarms on your instances that will alert you when your instance goes beyond specific performance metrics.
Once you’ve configured your alerts, you can even see how current instance performance compares to the alerts you’ve set up. All of your alerts for all AWS services are located in the same place, so you’ll be able to see just how your entire virtual infrastructure is performing.
How’s RDS Different From SQL Azure?
Microsoft’s database-in-the-cloud solution has a subset of SQL Server’s capabilities. It supports less SQL Server features, less datatypes, and smaller databases. Don’t get me wrong – it’s still a solid product – but it’s half the man that SQL Server is.
Amazon SQL Server RDS is full-blown SQL Server. You don’t have to change anything whatsoever about your apps – if it runs on-premise, it likely runs in Amazon RDS. Your tools like SSMS won’t know they’re talking to anything but a regular SQL Server instance.
However, neither SQL Azure nor RDS will act as a log shipping target or a database mirror: you won’t be restoring databases directly. Both Microsoft and Amazon try to automate routine maintenance for you, but as a result, routine maintenance tasks aren’t available to you. This limitation on database restores means Amazon RDS won’t replace every SQL Server running in Amazon EC2 today.
What’s an RDS SQL Server Cost?
The Amazon RDS pricing page has tabs for MySQL, Oracle, and SQL Server, and unlike SQL Azure, it gets complicated fast. Pricing depends on:
- The SQL Server Edition – Express, Web, Standard, or bring-your-own
- The hardware size – from micro (630mb RAM) to quadruple-extra-large (68GB memory)
- The storage size – from 20GB to 1TB
- The datacenter you choose – Oregon is cheaper than Tokyo, for example
- How much bandwidth you use
And more. To get started, a micro instance with 20GB of storage running SQL Server Express Edition is just $.035 per hour, or about $306.60 per year. A Standard Edition, quad core, 15GB memory instance is $1.22 per hour, or $10,687.20 per year, but it drops to $6,219.60 if you bring your own licensing or $6,482.40 if you use Web Edition. Reserved instances become even cheaper by the hour in exchange for a one time upfront payment. The break even point typically comes at the three month mark – after three months you were better off buying a reserved instance. That quad core 15GB instance drops down to $3206.16 per year, plus a one time payment of $5730 for a 3-year reserved instance.
One More Thing
Just one more thing: the
sp_Blitz that we know and love? When you use
sp_BlitzUpdate to grab the latest version, you’re fetching it from a SQL Server RDS instance right now.