Migration and modernization strategies for your SQL Server workloads on AWS

In this post, we explore various options to consider when optimizing your workloads to run on AWS. There is no one-size-fits-all approach to your migration and modernization journey. Use this post as a guide to help you strategically decide and plan migrations for your SQL Server workloads.

Migrating your workloads to AWS offers several advantages, including cost savings, staff productivity improvements, operational resilience, and business agility.

Successful migration of your SQL Server environments to the AWS Cloud is based upon generating a detailed inventory of your SQL Servers and their dependencies, identifying your authentication scheme, capturing your high availability and disaster recovery requirements, assessing your performance targets, and evaluating your licensing options (bring your own license or license included). This inventory will assist you in determining the target database platform, as well as defining your migration options.

Overview of SQL Server migration and modernization strategies

There are three main migration and modernization paths that you should consider for your SQL Server workloads:

Rehosting (lift and shift) – Involves migrating your on-premises SQL Server databases to SQL Server on an Amazon Elastic Compute Cloud (Amazon EC2) instance in the AWS Cloud. You may choose this approach if faster migration to AWS is your priority.
Replatforming (lift and reshape) – Involves migrating your on-premises SQL Server databases to Amazon Relational Database Service (Amazon RDS) for SQL Server in the AWS Cloud. Replatforming is best suited when you want to continue using SQL Server, but want to offload the undifferentiated heavy lifting tasks, such as installation, configuration, patching, upgrades, and setting up high availability.
Refactoring (re-architect) – Typically involves application changes and modernizing by using open-source databases or databases built for the cloud. In this scenario, you modernize your on-premises SQL Server databases to use either Amazon RDS for MySQL, Amazon RDS for PostgreSQL, or Amazon Aurora. By moving to an open-source database, you can avoid expensive licenses (resulting in lower costs), vendor lock-in periods, and audits.

Use the following decision tree to find the recommended migration or modernization paths for your SQL Server workloads based on your requirements. You should also consider making this decision on a case-by-case basis rather than an all-in approach. This may improve cost control and help you optimize quicker.

Figure 1: Decision Tree for migrating and modernizing SQL Server workloads

Figure 1: Decision Tree for migrating and modernizing SQL Server workloads

We deep dive into each strategy in the next few sections.

Rehosting

Rehosting is homogenous. Choose this approach when you want to migrate your SQL Server database as-is without changing the database software or configuration. In large-scale legacy migrations, organizations are looking to move quickly to meet their business objectives. Most of these applications are rehosted.

As depicted in Figure 1, you have multiple options to rehost SQL Server on Amazon EC2. Choose the option that best suits your requirements and helps you optimize costs. It will depend on whether you have existing licenses to use and whether you have active Microsoft Software Assurance on those existing licenses.

With Amazon EC2, you can bring your existing SQL Server licenses (BYOL) or purchase license included (LI) instances from AWS. The BYOL experience enables customers that want to use their existing SQL Server licenses to save on costs. AWS License Manager assists in controlling the allocation of your available licenses when instantiating virtual machines with SQL Server in Amazon EC2. AWS License Manager helps ensure compliance with licensing rules specified by the customer.

You can rehost SQL Server to Amazon EC2 shared tenancy instances using BYOL only if you have Microsoft Software Assurance (SA). If you don’t have SA on your SQL licenses, you can rehost to Amazon EC2 Dedicated Hosts, as long as the licenses were purchased prior to October 1, 2019, or added as a true-up under an active Enterprise Enrollment that was effective prior to October 1, 2019.

The following are a few approaches for rehosting your SQL Server databases on AWS:

Server rehost using, for example, AWS Application Migration Service (AWS MGN) or AWS Server Migration Service (AWS SMS)
SQL Server backup and restore
SQL Server replication
Extending your availability group to the cloud
AWS Data Migration Service (AWS DMS)

The AWS Launch Wizard service could also be used as it guides you through the sizing, configuration, and deployment of Microsoft SQL Server on Amazon EC2. It supports both SQL Server single instance and high availability (HA) deployments on Amazon EC2.

If you have in-house Linux administration expertise, rehosting to Amazon EC2 Linux is a good choice to save on Windows Server licensing costs. Consider using Windows to Linux replatforming assistant for Microsoft SQL Server Databases tool to automate this process.

Replatforming

Replatforming is homogenous. Choose this approach when you want to reduce the time you spend managing database instances by using a fully managed database offering.

As you can see in the decision tree in Figure 1, you can either replatform your SQL Server to Amazon RDS for SQL Server or to Amazon RDS Custom for SQL Server. The choice depends on whether you need access to the underlying operating system or require database customizations. A fully-managed database in Amazon RDS for SQL Server limits you from accessing the underlying operating system, system volume, or installation of custom drivers. If these features are necessary for your use case, consider replatforming to Amazon RDS Custom for SQL Server.

To replatform your SQL Server databases to run on Amazon RDS for SQL Server, consider using the approaches provided in Amazon RDS for SQL Server resources.

Refactoring

Refactoring is heterogeneous. Choose this approach when you’re ready to restructure, rewrite, and rearchitect your database and application to take advantage of open source and built for the cloud database offerings. If you’re open to refactoring your database and respective applications, you can modernize your SQL Server to either Amazon RDS for MySQL, Amazon RDS for PostgreSQL, Amazon Aurora MySQL-Compatible Edition, or Amazon Aurora PostgreSQL-Compatible Edition.

As shown in Figure 1, you have several choices in refactoring your database and applications. The choice you make depends on many factors, including your readiness to refactor your applications, modernization timelines, and performance requirements.

Amazon RDS for MySQL and Amazon RDS for PostgreSQL are fully-managed database offerings for their respective open-source databases. Amazon Aurora is a relational database management system (RDBMS) built for the cloud with full MySQL and PostgreSQL compatibility. Amazon Aurora features a fault-tolerant storage system and gives you the performance and availability of commercial-grade databases at one-tenth the cost.

You can also use Amazon Aurora Serverless to run your database on AWS without managing database capacity. Amazon Aurora Serverless v2 scales instantly to hundreds of thousands of transactions in a fraction of a second. You pay only for the capacity your application consumes, and you can save up to 90% of your database cost compared to the cost of provisioning capacity for peak load.

To refactor your SQL Server databases to one of these offerings, consider using AWS Schema Conversion Tool (AWS SCT) with AWS DMS. For more information, refer to the AWS SCT documentation.

If your goal is to accelerate your application and database migrations to AWS, consider using Babelfish for Aurora PostgreSQL. With Babelfish, applications that were originally written for SQL Server can work with Aurora with minimal code changes. As a result, the effort required to modify and move to Babelfish for Aurora PostgreSQL applications developed for SQL Server 2019 or older is reduced, leading to faster, lower-risk, and more cost-effective refactoring.

SQL Server database migration summary

You can choose from various methods to migrate your SQL Server databases to AWS based on your assessment and requirements. The following table summarizes some of the most common methods.

Migration Method	Target	Features and limitations
Native backup and restore	Rehost to Amazon EC2 Replatform to Amazon RDS	Can be applied to one or many databases at one time Requires downtime for final synchronization before cutover Supports all database sizes In addition to AWS Virtual Private Network (AWS VPN) or AWS Direct Connect, you can use the AWS Snow Family with Amazon Simple Storage Service (Amazon S3) to bring your database backups to AWS
Log shipping Database mirroring Always On availability groups Distributed availability groups	Rehost to Amazon EC2	Applied on a per-database basis Log shipping is asynchronous, whereas migration methods based on availability groups or database mirroring can be synchronous or asynchronous Can be used as a hybrid SQL Server deployment between on premises and AWS
Transactional replication	Rehost to Amazon EC2 Replatform to Amazon RDS	Supports migration of a set of objects (tables, view, stored procedures) Supports asynchronous replication with near real-time data Subscriber database is readable Requires close monitoring of SQL Server replication jobs that perform the replication Every table should have a primary key (this is a limitation of transactional replication)
AWS Application Migration Service (AWS MGN) AWS SMS	Rehost to Amazon EC2	Highly automated lift-and-shift solution AWS MGN is agent-based, block-level replication AWS SMS supports migrations of virtual machines from VMware, Hyper-V, or Microsoft Azure, but it doesn’t currently support physical infrastructure
AWS DMS AWS SCT	Rehost to Amazon EC2 Replatform to Amazon RDS Refactor to MySQL or PostgreSQL for deployment on Amazon RDS or Aurora	Supports full load and CDC Supports all database sizes Near-zero-downtime migration AWS SCT can analyze application code to attempt to identify and convert SQL statements in the code AWS SCT generates a migration assessment report, which includes an executive summary, license evaluation, cloud support evaluation (indicating any features in the source database not available on the target), and set of recommendations, including conversion of server objects, backup suggestions, and linked server changes
Bulk copy program Detach and attach Import and export	Rehost to Amazon EC2	Supports small databases May require downtime for final synchronization before cutover
Babelfish	Refactor to Babelfish for Aurora PostgreSQL	Apps written for SQL Server can work with Aurora No or minimal code changes Built-in capability with no additional cost The Babelfish Compass tool can analyze SQL and DDL code and highlight SQL Server elements requiring changes or incompatible with Babelfish for Aurora PostgreSQL

Conclusion

Planning migrations can be challenging. You can use the strategies and guidance provided in this post to better assist you with planning and implementing your migration and modernization journey. Let us know how this post helped resolve your migration challenges.

For a more detailed review of migration methods, check out the AWS re:Invent videos Migrate Microsoft SQL Server to AWS and Dive deep into database migration services AWS DMS and AWS SCT.

Microsoft Workloads on AWS