AWS RDS vs EC2

Decision Making Tree to choose RDS / EC2



• RDS is a managed database. It won't provide direct access to the server. DBAs can access the server only through the client software , like SSMS ,SQLCMD,etc.
• EC2 is like a virtual server. Its like any other windows server , but on cloud.

 

Before Choosing RDS , consider the below limitations as of this writing.
 

1. Server Time is fixed to UTC.
   GETDATE () function always returns in UTC.
   If time zone is a major concern in your environment then choose the EC2.
   
   Mitigation : use the datetimeoffset datatype, at the cost of changing source code and existing data
   
   Cost : This is the major concern for most of the applications.
   Takes lots of efforts to modify the application source code, default constraints, programs, BI related code (SSIS, SSAS, SSRS) source code with modifying the entire database date values to UTC takes lots of development efforts and testing.
2. RDS is a managed database. you can't access the server operating system.
3. No Read Replicas - Read Replicas (Atleast now) not available in SQL Server RDS , its available with other RDS products (Aurora,MySQL,Postgres)
4. you cant sell RDS in secondary market unlike EC2
5. Limitations with RDS
• No Database Email -
• No Linked servers - but you can create RDS as a linked server in other servers
• No Openrowset / BulkInsert - you can use BCP
• Not More than 4 TB and you must select the max database size
  when you configure it as it won’t allow to increase the size later.- You can shard and hibernate. Make sure to implement before moving to RDS
• No Windows Authentication - you can create IAM users and authenticate
• No Sysadmin server role - DBOWNER role exists. by default the user who creates the RDS assigned to DBOWNER database role. if the user drops from this role by mistake then change the password of the IAM user (you can just update the password with the same password before). this will add the user back to dbowner role
• You cannot select the Availability Zone (AZ) for the standby instance, so when you deploy application hosts please take this into account.
  The database could failover to another AZ and the application hosts might not be in the same AZ as the database. For this reason it is a best practice to balance your application hosts across all AZs in the region.
• SQL Server Agent - Support , but wait.  if its configured with multi AZ then since MSDB cant be mirrored , each time when you create or modify a job , you have to do a fail over to replicate the job changes to the mirrored server. Other way to avoid this problem is to create separate instance specifically to schedule the job but at the cost of more money to spin one more instance
• Max 30 Databases - most of the production environments wont need more than 30 , but if its a requirement then you need to consider this
• No Replication - But there are 3rd party replication tools available on RDS
• No log shipping
• No MSDTC
• No Policy Based Management
• No SQL Server Audit
• No semantic search (atleast now)

Conclusion

Unlike other RDBMS products on RDS , SQL Server on RDS is not fully matured to meet most of the application needs. If you are building a new application then consider other RDBMS products on cloud before moving to SQL Server RDS or simply use the EC2

:In Summary

choose EC2 if

• If you don't want to make any changes to the existing application.
• If you can manage your database instances

Choose RDS if:

• Its a new application. but also compare with other RDBMS products on cloud like  Arora,Postgres,MySQL , etc.. SQL Server RDS on AWS has lot of limitations compared to other RDBMS products on cloud.
• leaving managing the database administration to AWS
• You need high availability (mirroring) at the cost of latency of transactions
• Your app/service is designed for multi-zone

 

From <https://www.linkedin.com/pulse/rds-vs-ec2-chandra-sekhar-pathivada-pmp-acp-itil?trk=prof-post>