July 28, 2022 | By Greg Dougherty
When it comes to moving SQL Server databases to the cloud — the AWS cloud in particular, makes the decision to do so is the easy part. After all, the benefits of both cloud migration and the AWS cloud are well documented.
Where the difficulties lie are in the various decisions that must be made along the way. Among them: Will you continue using SQL server or switch to a different database engine? If you stick with SQL server, will you go with a managed or unmanaged cloud-based option?
If you’re moving away from SQL Server, should you opt for an open-source or cloud-native database? If you’re moving multiple SQL Server databases to AWS, do you have to make the same choice for all of them?
How will you migrate your databases from your on-premises environment to the AWS cloud? Will rehosting suffice or do you need to re-platform or refactor? And will you use an offline or online migration method?
There’s a lot to consider and to do. And as is usually the case, there’s no one-size-fits-all solution.
Do It Yourself or Partner Up
The correct course of action for migrating SQL Server databases will depend on numerous factors, ranging from business requirements to timeframe and budget parameters.
For many organizations, working with a third-party company that specializes in database migration eases much of the burden. In many cases, the steps entailed and decisions that need to be made are interconnected. A partner with extensive experience in database migrations is likely to know what works and what doesn’t, so it can help ensure that the decisions made will generate optimal outcomes.
If that partner has AWS-specific expertise, all the better. It will be better positioned to leverage AWS’s many resources to streamline the migration process, reduce costs and ensure success.
Whether you go the partner route or forge ahead alone, the information that follows provides a high-level overview of some of the various considerations for moving SQL Server databases to the AWS cloud.
- Know Your Workloads
Among the first things to do is to review your SQL Server database workloads. This will provide an understanding of their complexity, compatibility, and other factors that may influence your migration choices. That includes things such as:
- The SQL Server software version and edition
- The size and overall capacity growth of the databases
- The Input/Output Operations Per Second (IOPS) and throughput of the databases
- All database and SQL Server dependencies
- Current architecture, auditing, or compliance needs
- The need for high availability (HA) and automated failover capabilities
- Network connectivity to ensure sufficient bandwidth for fast transfers of data between on–premises and AWS environments
- Acceptable amount of downtime available for migration
- Recovery time objective (RTO), recovery point objective (RPO), and service-level agreement (SLA) requirements for existing database workloads
- Determine the Migration Type
Next up, determine the migration type you’ll use to move your on–premises SQL Server databases to AWS: homogeneous and heterogeneous.
With a homogeneous migration, you’ll continue using SQL Server, but will be doing so in the AWS cloud using either a self-managed or fully managed option (see details in #3). You retain the familiarity of working in SQL Server while moving up-stack without experiencing any downtime.
A heterogeneous migration, on the other hand, entails switching to a different database engine and may require some downtime. You can opt for an open-source database engine such as MySQL or PostgreSQL or a cloud-native database such as Amazon Aurora, Amazon DynamoDB or Amazon Redshift.
Open-source databases are more cost-effective than the proprietary kind like SQL Server due to low operating costs and no licensing fees. You also avoid vendor lock-in periods. However, transitioning to an open-source database can be difficult and time-consuming. Fortunately, services like AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT), can speed up the process with minimal downtime.
Cloud-native databases are cost-effective alternatives too and are the way to go if workload modernization is a priority. As the name indicates, cloud-native allows for fully taking advantage of cloud benefits such as scalability, elasticity, automatic updates, easy implementation, and access that allows databases to expand and become more distributed.
AWS offers numerous services to facilitate this option as well, in addition to a variety of development resources that enable building cloud-native applications in the new AWS environment.
- Choose Self-managed or Managed
If you’re going to stick with SQL Server databases on AWS, you can manage them yourself with SQL Server on Amazon Elastic Compute Cloud (Amazon EC2). Or you can let AWS handle the management by using SQL Server on Amazon RDS.
- SQL Server on Amazon Elastic Compute Cloud (Amazon EC2) is a self-managed option that works much like on-premises SQL Server. You maintain control over every aspect of the SQL environment. Since you set up the databases, you’re also responsible for availability, configuration, and backups with the EC2. It’s a good option if you want:
- Features and options not currently supported by Amazon RDS
- A specific SQL Server version that isn’t supported by Amazon RDS or your database size and performance needs exceed the current Amazon RDS for SQL Server offerings
- Higher IOPS and storage capacity than the current limits.
- To bring your own license instead of using the Amazon RDS for SQL Server license-included model
- SQL Server on Amazon RDS is a fully-managed database service with AWS handling things like patching, backup, disaster recovery, and event notification. It also includes features to provide better control over compute, memory, and storage capacity utilization. You can take advantage of hourly pricing with no upfront fees or long-term commitments. Among the reasons to go with Amazon RDS for SQL Server, it:
- Frees your IT staff from handling day-to-day database administration tasks
- Offers a highly available database solution that doesn’t require you to manually set up and maintain database mirroring, failover clusters, or Always On availability groups
- Enables you to scale the instance type up or down based on workload patterns without being concerned about licensing complexities
- Allows you to pay for the SQL Server license as part of the instance cost on an hourly basis instead of making a large, upfront investment
- Rehost, Replatform or Refactor
Whether you continue working with SQL Server databases or transition to open source or cloud-native databases, you have to decide how to move your on-premises databases to the cloud. The primary choices are: rehosting, replatforming or refactoring.
- Rehosting, also known as lifting and shifting, is the easiest method. It enables you to migrate a SQL Server database as is. It’s quick, avoids a costly refresh cycle, and entails minimal changes to your SQL Server application architecture or data. If you want to run SQL Server on EC2, this is typically the migration strategy you’ll use.
One potential challenge is that your new environment will need to be optimized so that you don’t waste money on storage and computing power you don’t need.
- Replatforming involves making some modifications to the databases to take advantage of cloud infrastructure. Among its advantages: you can move some databases to the cloud, experiment with the cloud environment, learn lessons and then move on to other databases, without committing to a large migration effort. If you want to run SQL Server databases on Amazon RDS for SQL Server, replatforming is the preferred migration strategy.
One of the biggest disadvantages, however, is that replatforming can quickly balloon in scope. To avoid scope creep, it’s important to focus on changing only the specified features.
- Refactoring entails re-architecting the software, or a large chunk of the code base, to take advantage of cloud-based features and the flexibility and elasticity that comes with them. It’s a primary enabler of application modernization and leverages things like microservices, containers and continuous integration / continuous delivery (CI/CD) pipelines. While it’s time-consuming and resource-intensive, it can provide the highest return on investment.
Refactoring is required if you’re migrating to an open-source database or to cloud-native database such as AWS’s Aurora PostgreSQL.
- Create Hybrid and Phased Approaches
As previously noted, there is no single solution that works for all organizations. If you’re migrating multiple SQL Server databases to the AWS cloud, you might find some will work better running directly on Amazon EC2 while others work better with Amazon RDS. It may be optimal to modernize a SQL Server database running on Windows to run on a Linux operating system to save on cost and licenses.
In addition, it could be beneficial to employ a phased migration approach. Doing so helps reduce costs, resources, and risks during the migration phase and enables you to focus on optimization and modernization in the next phase.
Let’s say you want to transition an on-premises SQL Server database to Aurora MySQL. Initially, you could rehost it on Amazon EC2 or replatform it on Amazon RDS for SQL Server. Then, in a subsequent phase you could refactor it to Aurora MySQL.
- Employ Online or Offline Migration
Yet another decision that will need to be made is whether to use offline or online migration to move your SQL Server databases from an on-premises environment to AWS. Your choice will be based in large part on your migration timeline and how much downtime you can allow.
- Offline migration is used when your application can tolerate a planned downtime. While the source database is offline, it’s migrated over to the target database on AWS. Once the migration is done, validation and verification checks are performed to ensure data consistency with the source database. You can then cut over to AWS by connecting your application to the target database on AWS.
- Online migration is used when your application requires little or no downtime. The source database is migrated to AWS in multiple steps. The data in the source database is first copied to the target database while the source database is still running. Changes from the source database are propagated to the target database in the next steps. When both databases are in sync, they are ready for the cutover. The application switches its connections to the target database on AWS. No connections are left to the source database.
Your Next Steps
The considerations cited are just a few of the many you’ll likely encounter during a database migration project. While it may seem overwhelming, it doesn’t have to be.
The migration experts at Opti9 can help.
They employ a proven, flexible framework of services designed to not only help organizations migrate to AWS. It enables them to make the most of all that AWS has to offer. That’s because Opti9 is an exclusively AWS Certified Advanced Consulting Partner and direct reseller focused on compliance-driven migration and spend optimization of legacy workloads and cloud-first application development.
Call 402.955.0489. Or email contact@opti9tech.com