RDS is the relational database service of Amazon Web Services. It is a ready-to-use cloud service: No OS or RAID to set up; you just specify the type of RDBMS you want, the memory/storage sizes, and a few minutes later you have a database instance up and running. The instance has its own (very long) DNS name, and you can access this database server from any place in the world using the standard SQL Server tools, if you give the client IP-addresses access through the “Security Group” linked to the database instance. Currently RDS is offered for Microsoft SQL Server, Oracle, and MySQL.
My company (Yuki) is currently using RDS for some smaller projects, and I’m investigating if/how our main database infrastructure could be moved to RDS, so that we can achieve improved scalability, world-wide reach, and lower maintenance costs on our database servers (which are by far the biggest bottlenecks for the web applications of Yuki). In this process I have discovered some important gotchas when using RDS SQL Server, that are not that well advertised, but can be big stumbling blocks:
- The sysadmin server role is not available. That’s right, you specificy a master user/password when creating the RDS instance, but this user is not in the sysadmin role; this user does have specific rights to create users and databases and such. Amazon has, of course, done this to lock down the instance. However, this can be a big problem when installing third-party software (such as Microsoft SharePoint) that requires the user to have sysadmin rights on the SQL Server.
- The server time is fixed to UTC. The date/time returned by the SQL Server function GETDATE is always in UTC, with no option to change this. This can give a lot of problems if you have columns with GETDATE defaults, or queries that compare date/time values in the database to the current date/time. For us, this currently is a big problem, which would require quite extensive changes in our software.
- No SQL Server backup or restore. Because you have no access to the file system (and the backup/restore rights are currently locked down in RDS), you can not move your data to RDS by restoring a backup. You have to use BCP or other export/import mechanisms. It also means, that you can only use the backup/restore that Amazon offers for the complete instance, meaning that you can not backup or restore individual databases. This point could easily be the biggest hurdle for many companies to move to RDS SQL Server.
- No storage scaling for SQL Server. Both Oracle and MySQL RDS instance can be scaled to larger storage without downtime, but for SQL Server you are stuck with the storage size you specify when you create the instance. This is a huge issue, since you have to allocate (and pay for!) the storage right at the start, when you have no idea what your requirements will be in a year’s time. This greatly undermines the whole scalability story of AWS.
- No failover for SQL Server. Again, both Oracle and MySQL can be installed with “Multi-AZ Deployment”, meaning there is automatic replication and failover to a server in another datacenter in the same Amazon region. No such option for SQL Server, meaning your only option in failure situations is to manually restore a backup that Amazon made of your instance.
All in all, still quite a few shortcomings, and some of which can be hurdles for deployment that can not be overcome. Personally, I love the service, as setting up the hardware and software for a reliable database server is a really complex task, which not very many people have any serious knowledge of. Let’s hope that Amazon keeps up its quick pace of innovation to improve the above points for RDS SQL Server.
Sebastian Toet 
Sebastian, I wish I had seen this article before I signed up for AWS RDS. I discovered all your “gotchas” the hard way, and moved on to another service. Do you know if Amazon has done anything to address these issues in the year that has passed since you wrote this article? Or are they still a problem?
Unfortunately, none of the issues I wrote down here a year ago have been resolved. In fact, the situation for Amazon RDS SQL Server has gotten comparatively worse, because Amazon now offers a formal SLA for RDS, but only when used in a Multi-AZ failover setup, which is not available for SQL Server! Also, none of the recent price cuts of AWS are applied to RDS (or EC2) instances that include a SQL Server license.
Understood. Thanks for the info. So are you using another service, like Azure? Or just maintaining your own servers?
I am actually using RDS, and am quite happy with it: Only one minute of downtime (for security updates) in almost a year, and performance is great (for the m2.2xlarge instances with provisioned IOPS that we use). We have created our own solutions for the problems mentioned in my article: We have written our own tools to migrate data into RDS (instead of using backups), adjusted our applications to insert explicit date/time values (instead of relying on the default UTC dates of RDS), etc. Also, for now, we can live without a real-time failover solution, as the 5-minute log-backup is acceptable to us and our customers.
RDS now supports multi-AZ on MSSQL instance!
Yes it does, good news. Unfortunately (for me, at least) it’s only available when you use SQL Server Standard or Enterprise, because multi-AZ uses database mirroring, which is not available in SQL Server Web Edition. All my SQL Server instances on Amazon use the Web Edition, because I never needed any features not available in that version, and there’s a big cost increase from SQL Server Web to Standard (something like $0.50 per hour).
Hi Is there any way i can give a sys admin role to the master username.
No. This is one of the biggest drawbacks of Amazon RDS SQL Server, that you have no access at all to the sysadmin role. For good reason, of course, because RDS needs to protect the consistency of the server instance, but nonetheless it can be a big problem, especially when installation of software require access to this role.
Can you share detail on the tool you create for data migration into RDS?
We are also considering the SQL Server Web Edition on RDS. When SQL Server Web Edition RDS fails do we have to manually restore from the backup logs or is there a automatic switch over? 5 mins downtime is OK for us and we do not want to add the additional costs of Standard Edition unless important.
Amazon RDS with SQL Server Web Edition has no automatic failover, because the Web Edition does not support database mirrorring. So, yes, in case of failure you will have to manually restore the latest point-in-time backup that Amazon RDS makes. You will lose at most 5 minutes of data, but the downtime can be much longer, because it can take a quite some time to restore all database from the full and log backups that RDS takes. I have an RDS instance with around 100GB of data, and it takes around 50 minutes to fully restore an instance. For bigger databases this can take several hours, or even longer. On the other hand, you have to consider how often this will happen. My RDS SQL Server instances are now running for almost 2 years, and in that time they had 2 minutes of downtime (during the specified maintenance window) to install patches, and have run continuously for the rest of the time.
But if you have databases larger than a few hundred GB, then it would probably be best to use RDS’s automatic failover with SQL Server Standard, because the downtime to restore an instance would be too long to be acceptable.
Hello Sebastian – Thank you much for this article. Some very good information. I am reading everywhere that RDS Sql Server implementations don’t support Bulk Insert. Is this an issue for you? If so, are you aware of a workaround? I am wondering if the reason for this is due to the need for elevated bulk admin perms….
I would think this would be a showstopper for quite a few potential RDS users.