Microsoft SQL Server on Amazon Web Services Cloud

Today one of my customers asked me to help him with SQL Server installation using his AWS cloud account. So, it's a great opportunity to write a post about SQL Server on AWS. Let’s dive in and explore it!

First, we need to understand what AWS is and the options available for running SQL Server.

What AWS is?

AWS full name Amazon Web Services, is a cloud platform like Microsoft Azure and Google Cloud. This platform lets us use computing resources and services without needing physical hardware.

Options to run Microsoft SQL Server on the AWS Cloud

There are three options for running Microsoft SQL Server in the AWS Cloud. Each option has a different architecture and key characteristics. Features like control, management, limitations, and ideal use cases may affect our choice.

By exploring these options, we can make a smart choice!

I will include links to all references at the end of this post.

1. Amazon Elastic Compute Cloud (Amazon EC2)

   In simple words, this is a virtual machine in the cloud that runs a standard SQL Server. We will have access to the underlying operating system (OS), installation, and configuration.

   
   

   This allows us full control over the database. For example, we can manage backups, tune parameters, and configure security. We can set up high availability or replication between a few different instances.

   
   

   This supports all features in SQL Server!

   

2. Amazon RDS for Microsoft SQL Server

   This is a managed service for SQL Server. We don't have access to the underlying operating system! It handles routine tasks like backups, patching, and scaling. This applies not only to operating systems but also to database administration tasks!

   This allows us to simplify infrastructure and reduce managing the underlying system needs.

   
   

   Unfortunately, this option has several limitations. For example, sysadmin role, PolyBase, Resource Governor and Server-level triggers are unavailable. Safe CLR exists for SQL Server 2014 and 2016 versions only.  Minimum and maximum storage sizes, and maximum number of databases are limited too. Additionally, we can't use the Developer Edition for Dev/Test environments.

   
   

3. Amazon RDS Custom for Microsoft SQL Server

   This service is kind of a mix between EC2 and regular RDS. This option is designed for legacy and custom applications. Specifically for configurations or features not available in the standard RDS offering.

   
   

   In any case, don't forget it is still RDS with some limitations.

For the summary

EC2 offers the highest level of control but requires more hands-on management. RDS, on the other hand, provides a fully managed service that handles routine tasks, although it comes with certain limitations.

RDS Custom strikes a balance between the two. It can be more customizable than standard RDS but still automates many management tasks.

When choosing between EC2, RDS, and RDS Custom, we must consider our specific needs.

Now we can make an informed decision. A choice that best suits our application’s requirements and operational preferences. This is necessary to ensure an efficient and reliable SQL Server installation. Hope it help :)

Additional reading:

• SQL Server on AWS

• Options to run SQL Server on the AWS Cloud

• Amazon EC2 for SQL Server

• Amazon RDS for Microsoft SQL Server

• Working with RDS Custom for SQL Server

• Choosing between Amazon EC2 and Amazon RDS