Amazon AWS RDS vs EC2 with SQL Server [closed]

Question

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

We will have an EC2 Windows Server running IIS but we have a question about if it better to have an RDS SQL Server database or if we should better have an EC2 bundled with SQL Server.

For performance and replication what do you recommend?

I appreciate your help.

Answer 1

We recently migrated a SQL database with some deadlock issues to AWS and looked carefully at AWS -- it was not a match for us due to a huge performance risk vis a vis deadlocks, so we went with EC2 instances. The users have been very happy with the performance.

From a performance perspective, Amazon's provisioned IOPS features are excellent, and these are available on both RDS and on the EC2 server instances. RDS also has killer automated backup capabilities. It's truly a set up and forget type of system.

AWS RDS does not support SQL Server replication. If your intent is to replicate something from your site to the AWS instance, then your options are to use a Windows Server base instance with your own SQL Server ISO/license, or to use a pricier EC2 AMI based on SQL Server. RDS has its own replication system that will support replication to another RDS instance (even on a different availability zone) to increase resilience or automate failover. With RDS you could also roll your own site to cloud snapshot replication by using bcp on prem, but that might require you to alter your application or schema.

A couple of advantages we found to using an EC2 instance over RDS:

• With an EC2 instance, you get sysadmin on 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 to the underlying Windows Server administration tools. This means that you cut out some basic tools for performance management: 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 EC2 instance and SQL on another EC2 vs RDS...

RDS performance vs EC2 performance vs price seems fluid but there is one major impact for Microsoft SQL Server users and RDS that may help you decide.

You can not use Backup / Restore to move DBs in and out of RDS.

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

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

Answer 3

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

You could also use 2 EC2 instances, one for IIS and one for the DB - that will also work.

RDS is probably the best option, if you don't take into account price - without knowing the traffic on your site, how important the DB is (i.e. could you afford to be down for 8 hours? could you afford to lose the last 15 minutes worth of db changes etc), there is no right answer...your budget and your requirements will be the deciding factors...

All that said, I have some low traffic sites that run with IIS and SQL Server express on a single EC2 instance, and they run just fine - and is very affordable and easy way to get on AWS, with the option of improving that config as your budget allows and traffic dictates.