Migration & Architecture Options for Microsoft SQL Server on AWS
Since the public release of AWS’s Elastic Compute Cloud (EC2) service, Cloudnexa has assisted customers with migrating their SQL Server infrastructures into the AWS cloud. Over the last decade, we’ve participated in countless projects of varying degree and difficulty and watched as the AWS ecosystem continuously evolves to provide increased compatibility for different SQL Server deployment models and migration methods.
Migration Methodologies
AWS provides a lot of flexibility when it comes to deployment options and target architectures for SQL Server workloads, but they can all be categorized as belonging to one of three general methodologies: Rehosting, Replatforming, and Refactoring.
Rehosting (Lift and Shift) is the relatively simple method of copying application and data bits from outside AWS into AWS. Classic examples include copying SQL Server database backups into a pre-provisioned AWS EC2 instance or moving self-contained virtual machines into a pre-configured target AWS account. Rehosting is probably the most common migration method due to its speed and relative simplicity. Most rehosting can be automated, although you may prefer to do this manually as you learn how to lift-and-shift your current SQL Server deployments to AWS.
Replatforming (Lift, Shift & Shape or Lift & Shape) is the compromise between Rehosting and Refactoring. When a SQL Server database is Replatformed in the cloud, it is modified to be more cloud-compatible, but without incurring too much change and risk. Replatforming to AWS RDS for SQL Server gives you a fully managed solution, decreased management overhead, single-click high availability, automated backups, the possibility for auto-scaled storage, and a number of easy migration options. Alternately, replatforming Microsoft SQL Server from Windows to Linux will save you on Windows licensing costs, while providing the same enterprise-ready platform with great performance and nearly the same number of supported features.
Refactoring is the most complex method of migrating to the cloud, the outcome being a fully cloud-native database. The effort and technical skills required to do this vary greatly but it is generally accepted that to successfully refactor you will need more advanced database and cloud aptitude than with any other migration method. An example refactoring target would be the AWS RDS Aurora platform. The target architectures in the remainder of this post specifically pertain to available options when following the Rehosting and Replatforming methodologies.
Target SQL Server Architectures on Amazon EC2
- SQL Server Instance: A single implementation of SQL Server running on EC2 without any high availability capability.
- SQL Server with Log Shipping: Log shipping lets you automatically send transaction log backups from a primary database instance to one or more secondary databases (also known as warm standby) on separate DB instances running on separate EC2 instances. Log shipping is often used as a disaster recovery solution but also can be used as a high availability solution.
- SQL Server Transactional Replication: Transactional replication is a SQL Server technology that is used to replicate changes between two databases, including database objects like tables, stored procedures, views, and so on, as well as data. The replication process involves a publisher, a subscriber, and a distributor, all running on SQL instances on EC2.
- SQL Server with Database Mirroring: Database mirroring takes a database located on an EC2 instance and provides a complete or almost complete read-only copy (mirror) of it on a separate DB instance, typically in a different availability zone (AZ). Note that although database mirroring is still available in SQL Server 2019, it is a deprecated feature which means it is no longer under active development and may be removed from a future version.
- SQL Server Linked Servers: Linked servers allow you to join tables between database servers and distribute queries through stored procedures and views across servers, without needing to change your application source code or manage multiple connection strings in your web tier. Linked servers’ connectivity can be set up using combinations of on-premise SQL instances, SQL instances running on EC2, and AWS RDS SQL Server instances.
- SQL Server on Linux: Starting with SQL Server 2017, SQL Server is available to run on Linux operating systems. Moving your SQL Server workloads to Linux provides both cost savings and performance improvements. SQL Server is currently supported on Red Hat Enterprise Server, SUSE Linux Enterprise Server, Ubuntu, and running in a container with Docker.
- SQL Server Always On Availability Groups (AG)
- Always On Basic Availability Groups: Starting with SQL Server 2016 SP1, SQL Server Standard edition provides basic high availability for a single, non-readable secondary database and listener per availability group. It also supports a maximum of two nodes per availability group with each node residing in a separate availability zone (AZ). Always On Basic Availability Groups replaces the deprecated Database Mirroring feature and provides a similar level of feature support.
- Always On Availability Groups: SQL Server Always On availability groups is an advanced, enterprise-level feature to provide high availability and disaster recovery solutions. This feature is available if you are using SQL Server 2012 and later versions. It includes support for up to nine availability replicas, both asynchronous- and synchronous-commit modes, automatic and manual failover, readable secondary replicas, etc.
- Distributed Availability Groups: A distributed availability group spans two separate availability groups. You can think of it as an availability group of availability groups. The underlying availability groups are configured on two different WSFC clusters. The availability groups that participate in a distributed availability group can be deployed across AWS regions.
- SQL Server Always On Failover Cluster Instances (FCIs): An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering nodes to provide high availability for the entire installation of SQL Server. If the underlying node experiences hardware, operating system, application, or service failures, everything inside the SQL Server instance is moved to another WSFC node. FCIs require some form of shared storage—disks on a storage area network (SAN), file shares on Server Message Blocks (SMBs), or locally attached storage with Storage Spaces Direct (S2D), SIOS Datakeeper, and most recently Amazon FSx—to provide resiliency and high availability. We will review the configuration and use of Amazon FSx for FCIs in an upcoming post.
Target SQL Server Architectures on AWS RDS
Amazon RDS supports DB instances running several versions and editions of Microsoft SQL Server, starting with SQL Server 2012. SQL Server Analysis Services (SSAS), Integration Services (SSIS), and Reporting Services (SSRS) are now also available on Single-AZ or Multi-AZ instances starting with Amazon RDS for SQL Server 2016 on both the Standard and Enterprise editions.
- Single Availability Zone (AZ) QL Server Instance: A single SQL Server instance running on RDS without any high availability capability. All editions are supported (Express, Web, Standard, Enterprise).
- Multi-AZ SQL Server Instances: Amazon RDS supports Multi-AZ deployments for DB instances running Microsoft SQL Server by using SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs). Multi-AZ deployments provide increased availability, data durability, and fault tolerance for DB instances. Amazon RDS supports Multi-AZ with Always On AGs for the following SQL Server versions and editions:
- SQL Server 2019: Standard and Enterprise Editions
- SQL Server 2017: Enterprise Edition 14.00.3049.1 or later
- SQL Server 2016: Enterprise Edition 13.00.5216.0 or later
Amazon RDS supports Multi-AZ with DBM for the following SQL Server versions and editions, except for the versions noted previously:
- SQL Server 2017: Standard and Enterprise Editions
- SQL Server 2016: Standard and Enterprise Editions
- SQL Server 2014: Standard and Enterprise Editions
- SQL Server 2012: Standard and Enterprise Editions
- SQL Server Read Replicas: SQL Server read replicase are available on the SQL Server Enterprise Edition engine for versions 2016-2019, and must be part of a multi-AZ deployment with Always On AGs. Up to five read replicas can be created from one source DB instance.
Migration Methods
Various methods are available to support migrating your SQL Server databases to AWS. Some of these most common methods and their characteristics are summarized in the following table:
In our next post, we will review the configuration and use of Amazon FSx for SQL Server FCIs, one of the more recent capabilities made available by AWS. Amazon FSx for Windows File Server provides fully managed, highly reliable, and scalable file storage that is accessible by using the Server Message Block (SMB) protocol, making it a suitable option to be used as shared storage in a Windows Server Failover Clustering node.
The content in this post references information from the following links. For additional details, please refer to these links:
https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/ec2-sql-ha.html
https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/methods.html
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.ReadReplicas.html
https://docs.aws.amazon.com/prescriptive-guidance/latest/migration-sql-server/methods.html
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.html
