Introduction
Hosting a SQL Server database on an Amazon RDS server can provide a very cost effective hosting solution, with server prices starting at about $300 per year.
Add to that the fact that Amazon take over responsibility for keeping service packs up to date, doing database backups, DR (with automatic multi data
centre failover), virus checking, hardware maintenance etc and it’s no wonder that many firms are seriously looking at moving their database
infrastructure to the AWS cloud.
However there are also significant downsides and concessions to be made (isn’t there always?) when using RDS, so not all SQL Server
based systems will work with RDS. If your database is just the backend of a simple website then the chances are it will work, but if you
want to use some of the more advanced SQL Server features such as sending emails, filestream, CDC or maintenance plans then RDS is
probably not for you. When I’m evaluating whether a system will work with RDS I use a checklist to determine if there are likely to be any issues.
Amazon have provided a list of features supported by RDS here :
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html
No doubt most of these limitations are based on the need to secure their SQL Server environment for all customers (who behind the scenes are probably
sharing the same server), and because the server is “managed”. Amazon can’t change the operation of SQL Server to suit their own purposes (which
incidentally potentially puts them at a disadvantage to Azure).
Over the last couple of years I’ve moved several hundred databases to RDS, and this article is drawn from my experiences of those migrations. I’ll go through
all the major aspects that have caused us issues.
Database Backups
Amazon have implemented full and transaction log backups for all databases. However they do not allow any access to the local file system, so you cannot copy a backup file to your
local test server for instance. Equally if you’ve developed your database on your laptop you can’t just back it up, copy the backup file to the RDS server
and restore it (SQL Server will only restore from a local file location). If you want to copy a database to and from a non RDS server then
you’ll need to script it up (including the data) and use that. There are some 3rd party tools that can help with this, but they’re not as simple as
doing a backup and restore.
The same applies if you want to copy a single database from one RDS server to another. The nearest you can get to this is to clone the whole server
to a new one, but this only works if the source and destination servers are RDS, and will copy all databases (of course you can delete any that you don’t want).
In my view this is one of the biggest shortcomings of RDS and hopefully something AWS will change at some point.
Database Size and Scaling
One of the big advantages of Amazon cloud hosting is supposed to be that it’s easy to scale up or down the instance size, to cope with peaks and troughs in
demand. However for SQL Server RDS that’s not possible, so once you chosen an instance size you’re stuck with it. This is a great shame as otherwise you could
start with a small instance for development and then scale it up as required. You can’t even clone the server into a new larger instance size – you have to
create a clone of the same size.
If you want to scale up then the only real option is to create a new larger empty instance and then copy the relevant databases across, and even that isn’t
very easy because of the restrictions around backups I mentioned earlier.
Data Migration
As I mentioned native backups and restores are not supported, so if you have an existing database that you want to move then you’ll need to use some
other method of moving it. You can use the SQL Server Import and Export Wizard, or a tool such as the SQL Database Migration Wizard (available from CodePlex).
This will move the schema (tables, stored procedures, views, rules etc) and data, but you’ll still need to move login, user and permission details separately.
Dates and Times
The server time is always set to UTC time (this is not configurable), so in the UK it will be an hour out in the summer. If your application uses the
server time (for instance with
SELECT GetDate()) to get the local time then the result may not be as expected.
Versions
At the time of writing only the following SQL Server versions and service packs are available :
| Build | Version |
| 11.00.5058.0 | SQL Server 2012 Service Pack 2 (SP2) |
| 11.00.2100.60 | SQL Server 2012 RTM |
| 10.50.6000.34 | SQL Server 2008 R2 Service Pack 3 |
| 10.50.2789.0 | SQL Server 2008 R2 Service Pack 1 with CU3 |
Although Amazon claim that they will implement minor version upgrades, in practice the number of versions available is quite limited (as shown above),
so I wouldn’t anticipate frequent service pack and CU updates. If you need a specific service pack level for your application you may well be out of luck.
However on a positive note, AWS supports a neat feature where you can upgrade databases on a 2008 R2 instance to 2012 using the AWS management console.
Patching of the operating system is done by Microsoft, you just specify a maintenance window during which the patching is done.
Sysadmin and Other High Level Permissions
There is no sysadmin access allowed to an RDS server, and some of the other elevated privileges also can’t be granted to a login (such as securityadmin,
diskadmin, bulkadmin, dbcreator and diskadmin).
If you’re designing a system from scratch then you can probably work around this, but I’ve found that the installation program of some software packages
requires these elevated privileges to work correctly, especially older software dating back to when security was less of an issue. That said I’ve often
found workarounds such as creating a database before the installer is run.
The highest level access account is the ‘master user’, of which there can only be one. This has ProcessAdmin, SetupAdmin server access and
database owner access to all databases. Usually this login is used by a DBA to create logins and permissions etc., however occasionally I’ve found
that the only way to get an application to work is to use the master user account – usually because the application expects to create other logins
and allocate permissions etc. In this situation I usually keep the databases on a dedicated instance, for security purposes.
Windows Authentication
Windows authentication is not available, so for instance you can’t hook up the server to your Active Directory. Instead you can only use SQL Server
authentication, so each user will need a login name and password.
SSIS and SSRS
It’s not possible to run SSIS and SSRS on an RDS server, however we’ve set up a central SSIS and SSRS server on an Amazon EC2 instance and
successfully run them from there using an RDS server as the data source.
High Availability
Although the Enterprise version of SQL Server is available in RDS most of the high availability features such as AlwaysOn, clustering and
replication are not available. However Amazon have implemented their own HA solution using mirroring to a separate data centre in some of
their regions. This provides resilience in the event that one of Amazon’s data centres or the instance is unavailable.
SQL Server Agent
SQL Server agent is available and can be used to schedule SQL commands, but can’t be used to run SSIS packages,
maintenance plans, PowerShell commands etc.
Configuration Values
Setting some configuration values, such as enabling CLR functions, are disabled in RDS. However for many of the values
these can be enabled using a “DB Parameter Group”.
Bringing a Database Online and Deleting a Database
With RDS this requires a special SQL command (though taking a database offline can still be done with
ALTER DATABASE DatabaseName SET OFFLINE).
EXEC rdsadmin.dbo.rds_set_database_online DatabaseName
Note that if you are deleting (i.e. dropping) a database that it will need to be online, otherwise you'll get an error.
Rename Databases
You cannot rename a database from SQL Server Management Studio, as much of the AWS infrastructure is based around the database name. However
databases can be renamed from the web based AWS console.
Recovery Model - Full vs Simple Recovery
All databases in RDS are created in full recovery. If you try to switch to simple recovery then an AWS process will switch it back to full recovery again. In
most situations this shouldn’t cause an issue (after all AWS handle the t-log backups).
SQL Profiler
Client side traces can be run against an RDS server in the same way as you would for a non RDS server. For server side traces these can also be
run, but if you want the results stored in a file (rather than a table) then it’s a little more tricky because they can only be saved to a
specific folder on the RDS server (D:\RDSDBData).
Further Information
The issues listed above are the major ones that I’ve experienced. The list is by no means exhaustive, and it’s worth checking with Amazon if a feature
that you need is available. Amazon have produced a very useful web page with some more information around limitations and common DBA tasks in RDS :
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.html
Number of Databases and Size Limits
Each RDS instance can host a maximum of 30 databases, and the largest instance size is 4TB.
Conclusion
If you need a simple and easy way to host your SQL Server database then RDS is the way to go, but if you need some of the more sophisticated
features of SQL Server or if you have a legacy application that expects greater control over SQL Server then it’s probably not for you.