Best Practices for Deploying Microsoft SQL Server on Amazon EC2

Publication date: July 28, 2021 (Document history)

Abstract

This whitepaper focuses on best practices to attain the most value for the least cost when running Microsoft SQL Server on AWS. Although, for many general-purpose use cases, Amazon RDS for Microsoft SQL Server (MS SQL) provides an easy and quick solution, this paper focuses on scenarios where you need to push the limits to satisfy your special requirements.

In particular, this paper explains how you can minimize your costs, maximize availability of your SQL Server databases, optimize your infrastructure for maximum performance, and tighten it for security compliance, while enabling operational excellence for ongoing maintenance. The flexibility of AWS services, combined with the power of Microsoft SQL Server, can provide expanded capabilities for those who seek innovative approaches to optimize their applications and transform their businesses.

The main focus of this paper is on the capabilities available in Microsoft SQL Server 2019, which is the most current version at the time of this publication. Existing databases that run on previous versions (2008, 2012, 2014, 2016, and 2017) can be migrated to SQL Server 2019 and run in compatibility mode.

Mainstream and extended support for SQL Server 2000, 2005, and 2008 has been discontinued by Microsoft. Any database running on those versions of SQL Server must be upgraded to a supported version first. Although it is possible to run those versions of SQL Server on AWS, that discussion is outside the scope of this whitepaper.

Introduction

AWS offers the best cloud for SQL Server, and it is the proven, reliable, and secure cloud platform for running Windows-based applications today and in the future. SQL Server on Windows or Linux on Amazon EC2 enables you to increase or decrease capacity within minutes, not hours or days. You can commission one, hundreds, or even thousands of server instances simultaneously.

Deploying self-managed, fully-functioning, and production-ready Microsoft SQL Server instances on Amazon EC2 is now possible within a few minutes for anyone, even those without deep skills on SQL Server and cloud features or configuration nuances, thanks to AWS Launch Wizard for SQL Server. Using AWS Launch Wizard, you can quickly deploy SQL Server on EC2 Windows or Linux instances, with all the best practices already implemented and included in your deployment.

Independent benchmarks have proven that SQL Server runs 2X faster with 64% lower costs on AWS when compared with the next biggest cloud provider. AWS continues to be the most preferred option for deploying and running Microsoft SQL Server. This is due to the unique combination of breadth and depth of services and capabilities offered by AWS, providing the optimum platform for MS SQL Server workloads.

Requirements for running SQL Server often fall under following categories:

• High availability and disaster recovery

• Performance

• Security

• Cost

• Monitoring and maintenance

These requirements map directly to the five pillars of the AWS Well-Architected Framework, namely:

• Reliability

• Performance efficiency

• Security

• Cost optimization

• Operational excellence

This paper discusses each of these requirements in further detail, along with best practices using AWS services to address them.

Are you Well-Architected?

The AWS Well-Architected Framework helps you understand the pros and cons of the decisions you make when building systems in the cloud. The six pillars of the Framework allow you to learn architectural best practices for designing and operating reliable, secure, efficient, cost-effective, and sustainable systems. Using the AWS Well-Architected Tool, available at no charge in the AWS Management Console, you can review your workloads against these best practices by answering a set of questions for each pillar.

For more expert guidance and best practices for your cloud architecture—reference architecture deployments, diagrams, and whitepapers—refer to the AWS Architecture Center.