Amazon AWS RDS vs EC2 with SQL Server

Question

We have a web application on Windows Server and SQL Server and you want to move it to Amazon AWS.

We will have a Windows EC2 server working with IIS, but the question arises whether it is better to have a SQL Server RDS database or is it better to have EC2 bundled with SQL Server.

For performance and replication do you recommend?

I appreciate your help.

Answer 1

We recently reconfigured the SQL database with some deadlock issues in AWS and looked carefully at AWS - it didn’t match us because of the huge performance risk regarding deadlocks, so we went with EC2 instances. Users were very pleased with the performance.

In terms of performance, the Amazon features provided by IOPS are excellent and are available for both RDS and EC2 server instances. RDS also has automatic backup capabilities. This is a truly customizable and forgotten type of system.

AWS RDS does not support SQL Server replication. If you intend to copy something from your site to an AWS instance, then your options should use a base Windows Server instance with your own SQL Server license or use the more expensive SQL Server-based AM2 AMI. RDS has its own replication system that will support replication to another instance of RDS (even in a different availability zone) in order to increase stability or automate the transition to another resource. With RDS, you can also flip your own site to replicate cloud snapshots using bcp on prem, but this may require changing your application or scheme.

A few of the advantages we found to use an EC2 instance over RDS:

• With EC2 instance, you get sysadmin for the database instance and administrator on the underlying Windows Server. RDS does not allow this, and in fact, with RDS, you lose access to the shell and the basic Windows Server administration tools. This means that you have disabled some basic performance management tools: system monitor, tracerpt.exe, Windows system resource manager, etc.

• With an EC2 instance, you can use SQL Server Agent to run jobs that include command-line or powershell scripts. RDS does not allow this.

Answer 2

Assuming your IIS is on one instance of EC2 and SQL on another EC2 vs RDS ...

RDS and EC2 performance seem fluid in comparison to price, but there is one significant impact on Microsoft SQL Server and RDS users that can help you decide.

You cannot use Backup / Restore to move DB files to and from RDS.

If that matters, EC2 is the only way to go.

Of course, there are a number of options other than Backup / Restore, including decent data migration designed to work with Azure, which also works with RDS, but often does not work for some complex environments.

Answer 3

Check out the AWS Architecture Center . The technical passport of the web application matches your scenario. Deploying each application layer to a suitable infrastructure service will help you create a reliable and efficient application.

Answer 4

Its obvious best practice is to keep the DB and web layer on separate machines, so EC2 instance for your IIS and RDS for the database will work fine.

You can also use 2 instances of EC2, one for IIS and one for the database, which will also work.

RDS is probably the best option if you do not take into account the price - without knowing the traffic on your site, how important is the database (i.e. can you afford to sleep for 8 hours? The last 15 minutes on db changes, etc. .d.), there is no right answer ... your budget and your requirements will be decisive factors ...

All that said, I have several low-traffic sites that work with IIS and SQL Server that express on the same EC2 instance and they work just fine - and this is a very affordable and easy way to get to AWS, with the option to improve this configuration as your budget allows and traffic dictates.

Answer 5

The answer is after experiencing both - if the whole new development database (without production level data) - no need to think and just go with RDS.

But if you are working with an existing application and database. The easiest way is to install SqlServer on EC2. this will save you a ton of data type errors that are not supported by RDS. Moving existing data to RDS can be a big pain. I studied my lesson diligently. And end up using an ec2 sql server instance.

Answer 6

You must definitely go with RDS. Moving existing data to RDS was a big pain for us before we found the CloudObject SQL RDS AlwaysOn tool in the AWS market:

https://aws.amazon.com/marketplace/pp/B00OU0PE5M/ref=_ptnr_blg_blg4

This tool was designed for continuous RDS geo-replication from one region to another for HA / DR and reporting. But it can also be used to move data to and from RDS. Best of all, it supports DB instance replication for SQL Server Web Edition. You can even replicate from SQL Standard to the web version from SQL 2008 to 2014, etc.

Another reason RDS is recommended for EC2 with SQL Server is the Multi-AZ HA feature. However, note that it only works for standard versions of Standard and Enterprise, and the replica is not available (this is for mySQL and oracle, but not for SQL Server). It cannot be used for reporting. You just know that he is, but cannot access him. And Multi-AZ only works in the AWS region. If you're okay with cost and limits, this is definitely recommended. Alternatively, you can use the Cloudbasic tool for continuous replication of RDS SQL Web Edition, i.e. Your primary Virginia RDS AWS RDS in Oregon for reporting and DR. And it costs a lot less than the RDS Multi-AZ with the SQL standard.