Amazon RDS SQL Server Hosting - Pros and Cons

Category : Articles Published : September 5, 2015 User Rating : 4.5 Stars      Views : 3 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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 :

BuildVersion
11.00.5058.0SQL Server 2012 Service Pack 2 (SP2)
11.00.2100.60SQL Server 2012 RTM
10.50.6000.34SQL Server 2008 R2 Service Pack 3
10.50.2789.0SQL 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.
Link back to this article : https://www.sqlmatters.com/Articles/Amazon RDS SQL Server Hosting - Pros and Cons.aspx

Keywords

SQL 2012, 2008R2, AWS, RDS, cloud


Comments
Post by Robson Ramos on Tue 18 Dec 2018 17:34. Report Inappropriate Post


Excellent article, it helped me a lot to solve the doubts I had
Post by AWS training in hyderabad on Fri 09 Oct 2020 10:09. Report Inappropriate Post
Website : https://aditidigitalsolutions.com/aws-training-hyd
This is a wonderful article, Given so much information in it, These type of articles keeps the user's interest in the website, and keep on sharing more ... good luck.
Post by AWS training in hyderabad on Fri 08 Jan 2021 08:24. Report Inappropriate Post
Website : https://aditidigitalsolutions.com/aws-training-hyd
Excellent article useful to all the aspirants.

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered