If you are considering hosting your SQL Server based applications in the cloud using Amazon Web Services (AWS), you have a couple of options to choose from. In this article, we will explore the two main options available and discuss the key differences between them.

Option 1: EC2 Instance

The first option is to have an EC2 instance, which is essentially a virtual machine (VM) where you can install the desired versions of the operating system and SQL Server. You can either install them manually or use pre-installed images provided by AWS. This option, known as Infrastructure as a Service (IaaS), gives you full control over the server and is similar to having SQL Server on-premises.

Option 2: Amazon RDS

The second option is to use Amazon RDS (Relational Database Service), which is a managed service provided by AWS. With RDS, Amazon takes care of certain aspects of your database server, allowing you to focus more on your application. This option, known as Platform as a Service (PaaS), offers some advantages but also comes with some limitations compared to the EC2 instance option.

Advantages of Amazon RDS

One of the advantages of using Amazon RDS is the built-in high availability (HA) feature. Instead of using technologies like Availability Groups or Log Shipping, RDS achieves HA by using multiple Availability Zones (AZ). This means that writes are performed synchronously to a replica in a separate AZ. In the event of a primary AZ failure, failover happens automatically.

RDS also provides automated backups of your entire instance, including log backups every 5 minutes. This ensures a maximum data loss (RPO) of 5 minutes in case of a disaster or accidental data deletion. You can restore your instance to a specific point in time, but keep in mind that individual database recovery is not possible.

Another advantage of RDS is that AWS handles patching of minor versions for you. You can choose to have this done automatically or trigger it manually through the AWS console. RDS also offers at-rest encryption similar to Transparent Data Encryption (TDE) in SQL Server, which is available for all instances hosted on RDS regardless of the SQL Server edition.

Limitations of Amazon RDS

While Amazon RDS offers convenience and managed services, it also comes with some limitations. For example, you have limited control over the underlying server hosting your instance. You cannot configure the operating system or access the disks directly. However, you can still connect to and manage your instance using SQL Server Management Studio (SSMS) with a slightly restricted access level.

Another limitation is that all SQL Server instances on RDS have a fixed server collation of SQL_Latin1_General_CP1_CI_AS, which cannot be changed. While you can specify a different collation for your databases, this may cause collation issues when comparing string-based columns with temporary tables, as temporary tables are created with the collation of the instance.

Additionally, certain SQL Server configuration options, such as MAXDOP (Maximum Degree of Parallelism) and Optimize for Ad-hoc Workloads, cannot be modified directly through SQL. Instead, these options must be configured through parameter groups specific to RDS.

Conclusion

When choosing between an EC2 instance and Amazon RDS for hosting your SQL Server applications on AWS, it is important to consider the trade-offs between control and convenience. The EC2 instance option provides more control over the server and allows for greater customization, while Amazon RDS offers managed services and built-in high availability.

Ultimately, the choice depends on your specific requirements and preferences. It is recommended to thoroughly evaluate the features and limitations of each option before making a decision.

Thank you for reading! If you have any questions or would like to share your experiences with SQL Server on AWS, please leave a comment below.