Running SQL Server Databases in the Amazon Cloud - RDS Limitations (Part 2)

By: Sadequl Hussain   |   Updated: 2014-07-03   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | > Amazon AWS RDS

Problem

In the first part of our series about running SQL Server databases in the Amazon Cloud, we discussed the various options when using Amazons Relational Database Service (RDS).  In this next part we are going to talk about things you need to know about how to manage SQL Server using RDS and some of the limitations of hosting SQL Server RDS instances.

Solution

Amazon Web Service Relational Data Service (RDS) is a cloud-based, managed, hosted solution for relational databases. At the time of this writing, Microsoft's SQL Server is supported by RDS. In a previous article of this series we talked about the basic concepts behind RDS and how to setup a simple SQL Server instance in RDS.

As developers or DBAs we also need to understand some challenges that come with SQL Server RDS. Some of these challenges can affect our decision to move on-premises databases to the cloud. Having said that, the pace of innovation and the turnaround time for new features is truly fast in the cloud world, so what we see as limitations today may not exist tomorrow. Amazon releases new features for AWS quite regularly and I am sure some of the restrictions will no longer apply as the service matures over time.

So without further ado, here are some of the things I think you need to be aware of.

Version

At the time of writing (June 2014), AWS RDS only supports two versions of Microsoft SQL Server: 2008 R2 and 2012. Both versions support Enterprise, Standard, Web and Express Editions. So if your application requires an older version of SQL (2008 or even 2005), RDS may not be the best choice. Older versions of SQL Server don't have mainstream Microsoft support and it's unlikely Amazon would release them in the RDS platform.

On the newer side, SQL 2014 has just been released and most companies are yet to implement it for large scale production fleets. SQL Server 2014 is not yet available in RDS (as of June 2014), but like anything else in the cloud world, it will only be a matter of time.

The best solution for both these cases would be to install the unsupported version in an EC2 instance. EC2 (Elastic Compute Cloud) is what AWS offers as standalone virtual machines in the cloud.

You Only Get the SQL Service

The SQL Server suite comes with a number of additional database server components: Integration Service, Analysis Services and Reporting Services. These have been part of the SQL Server suite for a long time. In recent versions we have seen Master Data Services and Data Quality Services included as well. In a standalone installation, you can choose to add any of these server components in addition to the basic SQL service.

No such option exists with RDS. RDS will only install and give you a functional SQL Server instance. So if your application architecture involves running SQL Server instances with SSAS or SSRS, those components will have to be hosted elsewhere: this can be either an on-premises server within your network or an EC2 instance in the Amazon cloud. As an architectural best practice, it would make sense to host them in EC2.

But why wouldn't RDS offer this facility? It would make things so much simpler. Well, the answer is in the name: RDS is Relational Database Service; it's not meant to be hosting anything else.

Remote Desktop Access

DBAs and system administrators are used to remotely logging in to database servers. Windows RDP or VNC is used for such access. This does not apply for RDS instances and it makes sense.  RDS is a managed environment, which means Amazon retains the control of the underlying host and the operating system and you can only access databases through a client tool. If you try remote access with the RDS instance's endpoint, you won't succeed.

This has some implications for the traditional DBA. Here are some examples:

• You can't copy, paste or create files in the underlying disk system. If your on-site DB server has non-SQL related files on disk, they can't be ported across.
• You can't run batch files, Windows Command Shell files or PowerShell scripts in the host.
• You can't monitor disk space, CPU usage or memory usage from the host. AWS provides a different way for monitoring.
• You can't copy backup files into the local disk from another location and restore databases from there.
• You can't decide which drive your database files go to, AWS has a default location for that.

In the same way, PowerShell access to the server is blocked.

Some of the configuration properties can't be changed from the server properties window or through code:

File System Access

 I tried to create a database in a SQL Server 2012 RDS instance from Management Studio. The New Database wizard screen shows me that data and log files go to D:\RDSDBDATA\DATA folder.

When I try to change the location, an error message comes up:

Okay, so my user account doesn't have "access" to the required stored procedures. Surely, I can modify the model database properties to fix this. The moment I click on the model database, another message pops-up.

Here is another example. I try to manually backup a database that I created. I can access the dialog box for backup, but when it comes to the actual work, I am told I don't have the rights.

Both these example should make it clear: you don't have host level access in RDS.

So how do you backup your databases? The answer is, there is no option for backing up individual databases in RDS. You can configure automatic backup for the whole RDS instance when you roll it out (or later). When enabled, RDS will automatically make a full backup of every database in your instance once every day. You can either choose the backup window or let AWS choose a default window for you. If automated backup is enabled, RDS will also make sure every database is in full recovery mode and transaction log backups are made. When you need to restore, RDS will only be able to restore the whole instance. It won't overwrite the existing instance but create a brand new instance with the restored databases in it.

You can also take manual "snapshots" of your RDS instance. This is effectively a full database backup at a point in time. Snapshots are available even after the RDS instance has been terminated. You can create a new RDS instance from a snapshot later.

We will talk about backups and snapshots in the next part of this series.

Instance Size

The minimum size of a SQL Server RDS instance for Standard or Enterprise Edition is 200 GB. The largest instance size can be up to 1024 GB. That's 1 TB and you can't go beyond that. Amazon says this is due to the extensibility limitation of striped storage attached to Windows Servers. This doesn't help if your on-premises SQL Server instance is more than 1 TB in size or if your new RDS database grows beyond I TB. You can extend storage space for other database types in RDS; not SQL Server.

So what's the workaround? Well, there are two things you can do. First, you can pre-allocate the space you think your server instance will ever need. You are buying some insurance upfront here. You could be paying for disk space your database server may never use, but that's better than the server running out of space down the track and having an outage.  

The second option is to monitor the instance. This is just like you would do for an on-premises SQL Server. AWS can provide you the details, but you need to take pro-active steps. You can keep an eye on the space used by the RDS SQL Server instance and take corrective measures when necessary. You can do this in an automated fashion using Amazon's performance monitoring service called the CloudWatch. In a later tip we will see how to configure CloudWatch for RDS monitoring.

Once your database has reached its size limit, it will be in the "storage-full" critical status. You will need to act proactively before this happens. If you can't shrink the databases or cull data to free up space, the only option would be to roll out a new RDS instance. Here is how you would go about doing this:

• Create another SQL Server RDS instance.
• Create new databases and their object structures manually in the new instance.
• Export the data out of the original instance.
• Import the data into the new instance's databases.
• Create all user accounts, jobs etc.
• Change the application connection strings to point to the new RDS endpoint.

Needless to say, this is a messy approach. Logical steps no doubt, but time consuming and it requires further testing.

Number of Databases

Another limitation to be aware of is the number of SQL Server databases an RDS instance can host. It's only 30 per instance. So perhaps you have a standalone SQL Server in your network that's hosting only 250 GB worth of databases. You don't think it will ever grow to reach the 1 TB limit. However, there are 40 small databases in that instance. Can you move the whole instance to RDS? Simply put, no. But having said that, there's nothing stopping you from rolling out two RDS instances and hosting 20 databases in each.

Renaming Databases

You can't rename RDS SQL Server databases. Not yet at least. The screenshot below shows this:

So what do you do if you need to rename your database? You guessed it: the long winded way of exporting all the data and schema objects out, creating a new database with the correct name and importing the objects and data back in.

Instance Collation

Each RDS SQL Server instance is installed with SQL_Latin1_General_CP1_CI_AS collation order. That's one of the most common collation orders used throughout the SQL Server landscape, but it also means extra work if the on-premises SQL application is using a different collation like Latin1_General_CI_AS or something else. Without collation compatibility your queries would fail and that means you have to set the correct collation either at the database level or at the table level when you create them.  The first approach is easier; with the second approach, you need to modify the table creation scripts to explicitly specify the desired collation.

"sa" Access and Server Roles

This was a shocker for me. You won't have the "sa" privileges in your RDS instance. Shocked? Angry? You, the DBA denied this access? Well, the sa account doesn't even exist in an RDS SQL instance so to speak. In its place, we have the "rdsa" account, an account with sysadmin privileges. But this account is disabled. The image below shows the existing logins in my freshly created RDS instance in US West 2 region. I am logged in as the masteruser and rdsa is disabled.

Well, it should be fairly easy, right? Enable the account and change the password and you will have that elusive sysadmin privilege. As you can see from the screenshot below, it's not so easy.

So what's the next best level of access for you? Remember the "master user" you specified when creating the instance? Effectively that account is granted db_owner role membership for all databases within the instance. Plus it has some extra custom rights assigned too. If you log in with this account you will have all the privileges for everything RDS would allow you to do.

If you think you can add the master user account to the sysadmin server role, tough luck. You are not allowed to do so because you wouldn't have the rights. In fact you are not allowed to add logins to a few of the server roles. Here is the picture for the master user in my RDS instance. These are the server roles the "master" account comes with by default.

If you try to create a custom login and assign it to one of the "unavailable" roles, you get a message like the following:

Why is it like this? The answer again comes back to the platform being managed by AWS. Some of these server roles and the sa account gives you privilege to run system stored procedures and access the underlying host. They also give you access to some of the powerful procedures in the master database. To keep the environment safe from yourself and safe for you these privileges have been locked down.

SQL Server Features

Enterprise SQL Server deployments are usually highly distributed in nature and makes use of various features of the product. A typical production SQL Server may have jobs calling custom stored procedures that would be accessing other SQL Servers via linked servers. Flat file data can be imported from network shares and exported back to another location; certain groups of users could be receiving auto-generated e-mails at the end of a workflow and there could be full text indexes rebuilding every night. DBAs on the other hand could be rolling out maintenance plans for automating routine tasks. High availability solutions may involve anything from older style replication to modern day Always On technology.

When it comes to RDS, many of these features are not available. To make the platform truly "managed", Amazon had to lock down so many features of the product that it may seem like a major barrier to migrating your SQL Servers to the cloud. Here are some of the things missing:

• There are no high availability options available. So no replication, no log shipping, no AlwaysOn and no manual configuration of database mirroring. Mirroring is enabled for all databases if you are opting for a Multi-AZ rollout. The secondary replica hosts the mirrored databases.
• There is no option for linked servers. So you can't roll out two RDS instances and expect to call one from the other using linked servers. Neither is MSDTC.
• You can't create maintenance plans for your RDS instances.
• Database Mail feature is not available.
• Things like Filestream, Change Data Capture or Service Broker are not available. 
• Some advanced DBA features are also not available. This includes policy based management, server audits and performance data collectors.

Here are some of the error messages I get when I try to access some of the features as the master user.

Replication errors when I try to create a publication.

Error message that pops-up when I try to configure Database Mail:

And the error message thrown when I try to create a linked server in my SQL 2012 Standard Edition:

If you look at the options under the "Management" folder in SSMS, it should become obvious that Maintenance Plans are not available:

Okay, so RDS won't maintain indexes and rebuild them for you, neither will it check databases for consistency or update statistics. But at the same time it has taken away your ability to automate these tasks through maintenance plans. This means you will either need to run the checks and index rebuilds on a periodic basis manually, or you need to create schedule jobs with custom-written codes.

Accessing SQL Server Logs is a pain when tried from the Management Studio:

Trying to read it using sp_readerrorlog shows the following message:

Msg 15003, Level 16, State 1, Procedure sp_readerrorlog, Line 11

Only members of the securityadmin role can execute this stored
procedure.

However, you are able to read the error logs from AWS Management Console as long as you are accessing the console with either the root account or a user with sufficient privileges for RDS. The screenshot below shows I am accessing a SQL Server error log from the AWS Management Console logged in as an administrative user:

As you can see, I can either watch the live error logs, view the file from within the console or choose to download it.

Here is how it looks like in a "live preview"

SQL Server Agent

A number of SQL Server Agent options are unavailable in the RDS platform. For example, you can't create proxies, operators, alerts etc.

You can't run ActiveX, Windows Command Shell or PowerShell commands and scripts from your jobs. The following screenshot shows the error message:

AWS also recommends scheduling your jobs to run at different times than the backup / maintenance window.

Data Migration

Backup and restore has always been the easiest way of migrating SQL Server databases. Unfortunately this option is not available for RDS so DBAs have to fall back on the manual process of creating database schemas and importing data. Depending on how complex the application is, this can be a simple matter of generating scripts from the source database and running it against the destination, or it can be a major time consuming affair. If you generate a single file for all your database objects, just make sure the objects are created in the correct order. Same goes for importing data; you may need to disable triggers, rules and foreign keys in tables before copying the data in. Once the data has been imported the constraints can be switched back on. Obviously the process will be a tedious affair if you have a number of databases to migrate, but that's the way it is.

You can also use the Database Import Export Wizard. In the following screenshot, I am migrating the AdventureWorks2008R2 database from a SQL Server running in EC2 to a SQL Server 2012 instance running in RDS.

Conclusion

In this tip we have seen how RDS can pose certain limitations that can affect our ability to migrate SQL Server databases to the cloud. What we have covered here today may get you thinking if RDS is right for your databases. You may be right, but then again, you may have database instances that are easily portable to RDS. Think about all the small, ad-hoc instances your developers may have installed in their desktop VMs; all the small instance lying around your database landscape. You can think about consolidating all those instances and databases into one place and RDS could be a perfect solution.

Or perhaps you may need a simple back-end for a web application or CMS. No complex logic, no distributed queries, just a pure data store for your site. RDS could work particularly well for such scenarios.

Sometimes the team needs a SQL Server on a priority basis: perhaps the latest application testing phase is behind schedule, all dev and test environments have been booked well in advance for other running projects. RDS can be a time saver in those situations as long as the application's needs don't exceed RDS limits.

Amazon has incorporated a number of enhancements to RDS since inception and as I mentioned at the beginning of this article, what seems like a limitation today may not be there tomorrow, so the best thing would be to start playing around with it.

In the next installment of this series we will see how we can backup an RDS instance and how to restore from it.

Next Steps

• Create a SQL Server RDS instance, connect to it using SQL Server Management Studio and check what features and T-SQL functions are available.
• Check out these case studies from AWS RDS web site.
• Think about the database servers in your enterprise that can be candidates for a small scale pilot or proof-of-concept project.

About the author

Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips