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.
The Limitations
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.
Backups
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.
Running 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.
Learn more about our SQL Server in Amazon cloud resources page.
49 Comments. Leave new
This is exciting news! Today in your “When would you use this?” question, “this” refers to the cloud; how soon until “this” refers to buying dedicated hardware?
Great post, comprehensive and gets right to the material of interest to DBAs, thanks Jeremiah!
Thanks for the kinds words. Flattery will get you everywhere.
You’re absolutely right – “this” refers to SQL Server RDS. You can get a dedicated VM in Amazon right now by purchasing a reserved instance. You get dedicated hardware by choosing the biggest instance size possible in a single class of instances – an m1.xlarge or m2.4xlarge allegedly takes up an entire VM host.
Thanks for an excellent post. Really interesting.
I noticed it doesnt support multi-AZ mirroring. in fact it seems MSSQL RDS doesnt support mirroring at all. Whats your thoughts about it? can you really run a production system running without mirroring? interested in your thoughts.
SQL Server supports mirroring, and has since SQL Server 2005. Functionality equivalent to MySQL’s mirroring is available is SQL Server 2012. Something similar could be accomplished with earlier versions using transactional replication.
I created a instance of sql web server.
Now i used the master user to connect to it, which i was able to.
But that master user is not a sysadmin. I require to perform certain operations with the sysadmin permission.. Is there a way?
No, you can’t become a sysadmin on an RDS instance of SWL Server. The administrator account that you created should have enough permissions for almost anything. What are you trying to do that you can’t do another way?
Well, basically im trying a simple thing like deploying my database project, from visual studio to the sql server RDS. With the master user as well. And this fails mainly because there is step which requires a sysadmin permission. I believe it setting the database compatibility level.
I suggest mapping out which operations you need to perform and then take a look in Books Online to determine which permissions you actually need. Setting a database compatibility level only requires the ALTER permission on that database (http://msdn.microsoft.com/en-us/library/bb510680.aspx). There are very few operations that you legitimately need sysadmin to perform, but most people are used to using it because it’s how they’ve set things up locally.
SQL Server Agent is now available on SQL Server RDS.
http://docs.amazonwebservices.com/AmazonRDS/latest/UserGuide/Concepts.DBEngine.SQLServer.html
Thanks, sir. I’ve updated the post and with a link to your comment.
I noticed that RDS (SQL Server 2012 enterprise) Doesn’t support “Allways ON” feature, Any idea when this will be available in RDS ?
I’m not aware of the AWS RDS team’s roadmap, but I suspect it will be a long time. There are a number of additional complexities that the combination of failover clustering, AWS, and AlwayOn Availability Groups bring into the mix. You can build that environment yourself and, frankly, that’s the route I woud choose over having someone else manage a database critical enough to warrant an Availability Group set up.
Thanks for the quick reply, only concern is the SQL server enterprise Licensing.
Can’t find any AWS AMI with SQL 2012 enterprise edition. Do we have to take our own licensing for this ? We want to use the AWS hourly rate price structure if possible.
Enterprise Edition isn’t available via Amazon’s SPLA. You need to bring your own licensing if you want EE…
10x for the gr8 blog.
i have 1 question :
1. Do we have linked server from and to…
A list of features supported by SQL Server RDS is conveniently available on the AWS documentation site. This changes all the time, so if something isn’t there, check back. It looks like Linked Servers are supported.
also its writen under sql server 2008 . i tried to create it in 2012. and did not succeeded… 🙁
Unfortunately, linked server is now off the list of supported features. Amazon was making some changes that led to losing info on remote accounts. After that they decided to declare it unsupported.
However, you can still create them and use them. 🙂
Thanks for the info Dejan. That highlights how much you need to pay attention to updates and changing feature sets with hosted services.
When one of my clients migrated some development SQL Servers to Amazon RDS I was tasked with migrating the databases to the new RDS instances.
I had read *many* complaints about the inability to restore databases from backup into an RDS instance.
I solved the problem by using the SQLPackage.exe utility that ships with SQL Server Data Tools,
I wrote a PowerShell script that extracted the legacy database, connected to the RDS instance, and “published” the database with schema and data intact.
If I had a blog, I’d actually write this up in greater detail, because it’s a very slick workaround to not having backup/restore capabilities with Amazon RDS.
For further reading, check the SQLPackage.exe documentation, and you can use any type of shell or batch runner to execute the utility.
Thanks for sharing, Chris. The conventional wisdom has been to use Azure Migration Wizard. This should avoid a lot of the hand scripting involved in building your solution.
@Chris Carson – would you mind sharing your powershell solution to successfully migrate SQL databases into RDS. I’ve tried the Azure migration tool, and Amazon’s whitepapers of exporting and importing + bcp commands, but still having hassles with incomplete data uploads. Db is +- 40gb in size. Any suggestions welcome. Thank you.
Does anyone have some good suggestion about analyzing performance issue in RDS ? Since we can not hook any monitoring tool there and cloud watch is definitely not enough to provide good insights .
As long as your monitoring tools don’t require an agent on the SQL Server itself, you can monitor a SQL Server RDS instance.
Otherwise, I’d suggest using the DVMs or scripts that access the DMVs, to determine what’s going on. sp_BlitzCache and sp_AskBrent can be used to analyze poorly performing queries. sp_AskBrent can also help you analyze waits during a sample period. And sp_BlitzIndex can help you understand your indexing situation.
Hi Can AWS RDS be destenation of replica?
Replication is on the list of unsupported features as of today (April 9, 2013). This could change, but I doubt that it will happen quickly; replication snapshots require a shared volume be exposed on a UNC path.
10x.
I hoppen i could have replicated from on premise to the RDS…
I am trying out a SqlServer instance on Amazon RDS and I have created an instance and I can connect from Mgmt Studio 2008, however the problem is that the “master user” I created is not a sysadmin and I can’t even create a database. I see another user named “rdsa” which is sysadmin, but I have no idea what the password is. Googling for an answer hasn’t helped – any idea how to get my user assigned the sysadmin role or how to log in as rdsa? I searched the RDS Management Console and didn’t find any help there either.
There’s no way to log in as sysadmin or the rdsa – these are Amazon only roles.
You should be able issue a create database statement as long as you don’t specify drive locations, just
CREATE DATABASE my_databaseshould work fine.Will RDS support the use of super helpful utilities such sp_Blitz and sp_BlitzIndex? Seems that these may want access to functionality that RDS does not make available. I haven’t tried this yet… we’re thinking of working with RDS instead of using EC2 based servers, but want to evaluate how much ‘flexibility’ we have available to us. Certainly the lack of AlwaysOn mirroring/replicas is something I’m not real happy about…
Greg – nah, a lot of that type of sysadmin functionality isn’t allowed. They do the management for you – this is the point of the cloud.
sp_Blitz is definitely not supported in RDS, but sp_BlitzIndex should work.
As Brent points out – a core benefit of moving to a hosted database solution like RDS is that someone else is managing HA. You just managed the data.
Yeah that’s what I figured. From a dev standpoint, the sp_BlitzIndex is more important to me since it help me to identify issues with my schema and queries/stored procs.
As for HA, I would love to have the ability to have read replica(s) for our heavier read request requirements and have this not impact the insert/update activity that is also going on just in case the RDS instance goes sideways on us. Doesn’t look like that’s available for SQL Server RDS (unless I’m missing something about RDS).
I know AWS RDS does support read replicas for MySQL so I do on occasion consider making the switch, but all my DB guys have only MSSQL experience so there’s certainly some learnin’ that has to go on to make this happen.
Hi Brent,
This article is very informative and useful but I wanted to know one thing in particular, how’s the support for transactional replication into RDS environment and out of it into an IT environment, if both are required to be in play?
I would much appreciate if you could point me to anything detailing this specific feature usage with RDS.
Thanks.
Sanjay
Hi Sanjay,
I’m glad you liked my article.
One of the best things about AWS driven solutions is that Amazon writes great documentation. The supported features of SQL Server RDS have been heavily documented by the AWS SQL Server RDS team and are updated on a regular basis. Right now, SQL Server RDS only supports being a replication subscriber.
Sanjay,
Brent didn’t author the article.
Jeremiah Peschka <— This guy did.
Unfortunately, it seems that Amazon has forgotten about SQL Server on RDS. They promised to add support for Windows authentication to it (and many other things) in RDS 18 months ago, but that never happened.
Dejan – generally speaking, I wouldn’t recommend using Windows authentication in the cloud. Keep it simple. 😀
Hi Jeremiah,
Can I take a backup from RDS database and restore then in a normal SQL Server Instance?
I need to migrade some bases from RDS to On-Premises SQL Server.
No sir. This isn’t an option.
Hi Mike. I edited your comment to remove the ad link – we try to keep vendors and sales folks from doing promos on our site. Thanks though!
No worries Brent. Can’t blame a guy for trying though. Keep up the good work. Love this blog.
Has anyone played around with RDS Migration Tool (AMI) to successfully migrate from 1 RDS (MSSQL) to another RDS (MSSQL) with a bigger block of disk storage?
Hi, is there any difference between size of the database when we restore/transfer database from EC2 to RDS?server?
Hope its ok to post to this Thread…I noticed it started in 2012.
We add Agent jobs to our AWS RDS Multi-Site MSSQL instance. We have to failover to get add them to the Mirror…so they are there if there is a failover. BUT, we also have a vendored support company that has been able to add the jobs without a failover. They have some way of determining the partner IP or address to connect directly. I have not been able to find this. Looked in sys.database_mirroring and other mirroring dmvs but only find pseudonyms for the partner that I haven’t been able to address directly in SSMS (used tcp: with port, etc… for different server strings I guessed at and nothing). I found AWS RDS docs say to failover to add the jobs…B UT that vendor seems to know how to find the “address” to the mirror. How do they do that?
Your best bet is to ask them.
First, sorry to reply in your post Brent, I was doing some research and this post comes out. Johh, take a look in to my post https://www.simple-talk.com/sql/database-administration/automating-synchronization-rds-sql-server-agent-jobs-multi-az-environment/ . The DNS is one of course. When Failover it have propagation with the other node. So by the endpoint will be the same. In this article I have a powershell module wehere I get the hostname oif the machine. Basicalloy query
$sql = “select SERVERPROPERTY(‘MachineName’) as [ClusterVirtualServerName]” and keep checking with the last name. If it is different the prpagation is finished. The endpoint is the same , but thge hostname is not. You cannot connect direclty to the hostname, but you can check if its the same as the other one ..or means a failover ocurred
https://aws.amazon.com/about-aws/whats-new/2017/11/amazon-rds-for-sql-server-supports-scaling-storage-size/