Amazon AWS offers two main options for deploying SQL Server in the cloud: Elastic Compute Cloud and Relational Database Service. I recently did a few experiments with both types of instances and learnt a lot about what is and is not possible with the two options. It is worth noting that I have previously worked with MySQL on RDS and found it very satisfactory and very much like any other MySQL instance. For SQL Server, it does appear that there is some significant difference between deployments on EC2 and those on the RDS offering.
Elastic Compute Cloud
Elastic Compute Cloud (EC2) is the AWS version of a virtual machine in the cloud. In this experiment, this virtual machine can be configured with versions of SQL Server pre-installed. Amazon Machine Images (AMIs) available for use on AWS are classified based on SQL Server edition and version as shown in the partial screen capture in Fig 1.
Fig. 1 - Amazon Machine Images for SQL Server EC2 Instances
As with all Infrastructures as a Service (IaaS) cloud offerings, Amazon EC2 instances running SQL Server include a number of items discussed below.
Highly Scalability
This is achieved by simple creating a custom AMI from an existing EC2 instance and then creating another EC2 instance using this already configured image. It is worth noting that when working with EC2 instances that have been joined to a domain, correct behavior is guaranteed when you are replacing an EC2 with a higher spec (vertical scaling) rather than trying to duplicate instances for purposes such as load balancing. This is because the actual computer name is still the same when you duplicate the EC2 instance and Active Directory does not like this.
Easy Backups
Creating an image of an EC2 instance is basically taking a backup of the entire server. Cloud computing makes this extremely easy. Thus, when configuring a web server, for example, you may want to create an image of the server at certain checkpoints. You can fall back to these images when you need to correct a problem. In recent times, I had to resort a previous image when the instance I was dealing with had problems communicating with the Active Directory Domain. You can see where to do this in Fig. 2.
Fig. 2 Creating an Image
Easy Security
When using Amazon EC2 instances, configuring firewall rules is as easy as creating a Security Group in which are defined the ports you need open and the source IP addresses that you want to allow access to your instances. It is typically not a good idea to have your source IP Address defined as 0.0.0.0/0 (the entire world). You may see this in publicly available tutorials on using AWS, but this is not suitable for deploying instances you intend to use for live services. It is better to be sure of your public IP Address and restrict access using Security Groups. In case you do not have your own office (startups using shared workspaces), you can check your IP Address on the URL https://www.whatismyip.com/ . Taking advantage of the security options and advise available on AWS is a good idea.
Using SQL Server on an Amazon EC2 instance is very much like having a SQL Server instance deployed on-premises. Access to the OS is allowed, you can add more storage and configure database file locations as desired, you can use any features available foe the edition of SQL Server you have chosen, etc. You can also perform native backups to Amazon S3 (Simple Storage Service) buckets using software called TNTDrive. The idea is simple to mount an S3 bucket as a drive on the operating system of an EC2 hosing SQL Server. Using this approach, you can migrate data from RDS to EC2 and vice versa. See this URL for information on how to mount an S3 bucket on a Windows OS: https://tntdrive.com/.
Connecting to a SQL Server instance on EC2 is very much the same as on any other server. However, I did notice that establishing connections using private IP Address from another EC2 instance appeared a little slow. This could be due to processing firewall rules or the low-end instance type I used for my experiments, but I have not fully investigated this issue.
Relational Database Service
In the case of RDS, the client is offered a Database as a Service (DaaS). No access is allowed to the Operating System hosting the SQL Server instance. All instance options exposed by AWS are configured on the RDS dashboard. Options such as license model, port number, domain membership, authentication mode, backup settings etc. can be modified on the RDS Dashboard.
To modify settings, choose "Modify from the Instance actions menu (shown in Fig. 3).
Fig. 3 Modifying an SQL RDS Instance
Once the page opens, you will see which items can be changed. A few of these are shown in Fig. 4.
Fig. 4 Modifying an SQL RDS Instance
This may not be a serious restriction in simple deployments but SQL Server RDS does have a few restrictions.
Operating System Access
Operating System Access is not permitted when using RDS. This has a few implications, one of which is that in case you need to migrate databases, you will not be able to use native backups written to the server. You will need to configure Amazon S3 to achieve this. Amazon’s S3 has options for backing up databases to a bucket and restoring from a bucket to an RDS instance. The following URLs give details on how to accomplish this:
- https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html
No Native Backups
AWS states that RDS is provided as a managed service thus shell access is not available. Administrative privileges are restricted to all user databases on the RDS instance. In effect, the master user, which is configured when the instance is deployed, is given db_owner privileges on ALL databases. However, privileges required for backing up databases natively are not granted. To accomplish the earlier mentioned backup to Amazon S3, it is necessary to configure an IAM role and use same to configure an Option called SQLSERVER_BACKUP_RESTORE to the option group you have configured for your instance.
No Database Mail
In my experience I was quite disappointed to find that Database Mail is not supported on Amazon’s RDS for SQL Server. Other key features such as AlwaysOn, Log Shipping, Replication and Backup to Azure (understandably) are not supported. A full list can be found here:
Connecting to a SQL Server instance on RDS requires the use of a long endpoint fully qualified name, such as the following:
sqlsvrinst.cvws0ubvnq7f.eu-west-1.rds.amazonaws.com,1433
Some developers might find this lengthy endpoint annoying, but it works the same as a regular hostname or IP address for an instance running on a regular server. It is composed of your instance name, the identifier for the server hosting the instance (hostname), the region where this instance is sitting and the rest of the string indicating the instance is actually an Amazon RDS instance. The IP port may need to be added at the end.
Epilogue
When deciding whether to use SQL Server on EC2 or SQL Server RDS one has to think about cost, scale and the required features. I would recommend using EC2 SQL instances for more serious projects requiring a wide range of SQL Server features and RDS for simple deployments or cases where cost is a big issue.
Deploying any of the options is quite easy with a little training/reading. Amazon Web services provides ample documentation on all their services and good support. In addition, manipulating resources in the cloud is safer than on-prem because it is much easier to deal with errors or to set up mechanisms for recovery.
References
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Limits.html
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.FeatureNonSupport
- https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/
- https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html
- https://tntdrive.com/