AWS Cloud Operations & Migrations Blog

Best practices for migrating Microsoft SQL Server databases to Amazon EC2 using CloudEndure Migration

June 22, 2021: This blog post describes CloudEndure Migration. AWS Application Migration Service, the next generation of CloudEndure Migration, is now the recommended service for lift-and-shift migrations to AWS.

If you have Microsoft SQL Server workloads running in an on-premises environment, you might be looking for ways to migrate to AWS with minimal or no changes to your existing environment. You want to lift and shift one or more large-scale machines from an on-premises environment to AWS without changing the SQL Server version, operating system, or code in the databases and with near-zero or minimal downtime. You can use CloudEndure Migration to quickly lift and shift physical, virtual, or cloud servers without compatibility issues, performance impact, or long cutover windows.

There are ways to migrate a SQL Server database to an Amazon Elastic Compute Cloud (Amazon EC2) instance using SQL Server features like log shipping, mirroring, and Always On availability groups. These options are appropriate if you’re migrating a single database or set of databases to a new SQL Server instance running on Amazon EC2. These options are database-native and dependent on specific SQL Server versions and editions. In addition to the database migration, you might also have to perform steps to migrate SQL Server level objects like logins, jobs, database mail, and linked servers.

As an alternative, CloudEndure Migration supports all SQL Server editions and versions. Because CloudEndure Migration does block-level replication, you don’t need to perform extra steps to migrate SQL Server level objects.

We created step wise guidance for migrating a Microsoft SQL Server database from on-premises to an Amazon EC2 instance using CloudEndure Migration.

In this blog post, we highlight some best practices when you use CloudEndure Migration to migrate Microsoft SQL Server database workloads to AWS. We include information to consider before, during, and after migration.

Best Practices and Considerations

As outlined in the prescriptive guidance, the following diagram illustrates the solution architecture for migrating an on-premises SQL Server to an EC2 instance.

 

The diagram illustrates the solution architecture for migrating an on-premises application servers to an EC2 instance. CloudEndure Migration consists of following main steps: 1. Installing the Agent: The CloudEndure Agent replicates entire machines to a Staging Area in your target. 2. Establish communication: Each Source machine with an installed Agent must continuously communicate with CloudEndure Replication Servers in the Staging Area over TCP Port 1500. TCP Port 1500 is needed for the transfer of replicated data from the Source machines to the Staging Area. 3. Configuration and testing: You configure your target machine settings and launch non-disruptive tests. 4. Performing cutover: CloudEndure automatically converts machines to run natively in AWS. The Staging Area comprises both lightweight Amazon EC2 instances that act as replication servers and staging Amazon EBS volumes. When the target machines launch for testing or cutover, CloudEndure automatically converts the target machines so that they boot and run natively on AWS.

Figure 1: Solution architecture

Migrating an on-premises standalone server running SQL Server to an EC2 instance

Here are the high level steps:

  1. Sign in to the CloudEndure Migration console and create a project. For project type, choose Migration and then configure your AWS credentials.
  2. Configure your replication settings.
  3. Complete the network requirements. For information, see Network Requirements.
  4. Download and install the CloudEndure Migration agent on source machines with all required volumes.
  5. Configure a blueprint to define the target machine type, launch type, subnet, security groups, Amazon Elastic Block Store (Amazon EBS) volume type, and other base settings that matches your requirements. The target machine blueprint is a set of instructions for launching a target machine for the selected source machine.
  6. After the initial sync is complete, perform a test cutover.
  7. Shut down the SQL Server services on the source machine and then perform the cutover.

In most cases, on-premises business-critical applications running with SQL Server are deployed in high-availability modes using Database Mirroring, SQL Server failover clusters, or Always-On availability groups.

Database Mirroring

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. The data resides on local storage attached to the respective nodes participating in database mirroring. If you use database mirroring in your on-premises environment, make sure you migrate all the nodes participating in replication together using CloudEndure Migration.

SQL Server Failover Cluster Instance (FCI)

SQL Server Failover Cluster Instance (FCI) relies on shared storage being accessible from all nodes participating in FCI. To migrate FCI instances using CloudEndure Migration, you must break up the cluster on the target machine after migration and use alternative approaches for HA that don’t require shared storage.

You cannot use CloudEndure Migration to migrate SQL Server failover cluster instances to a similar architecture on AWS. Instead, you must build the SQL Server failover cluster on AWS using Amazon FSx for Windows File Server and use SQL Server native features to migrate databases from on-premises to AWS.

If you use SQL Server Always On FCI on-premises (or a simple file server cluster), you can deploy a similar architecture on AWS using Amazon FSx for Windows File Server. For information, see the Simplify your Microsoft SQL Server high availability deployments using Amazon FSx for Windows File Server blog post.

SQL Server Always On Availability Groups (AAG)

If you use Always On availability groups in your on-premises environment, you can deploy a similar architecture on AWS and use SQL Server native tools to replicate the databases with minimal downtime. For information, see the Best Practices for Deploying SQL Server on AWS whitepaper and the Using Always On availability groups and Windows Server Failover Clustering (WSFC) quick start.

tempdb

The tempdb system database in SQL Server is a work area for the database to temporarily store data until a process is complete and the data can be written to the log files. The tempdb will be dropped and recreated each time the SQL Server service is restarted, starting with a new clean copy of the database.

tempdb is usually placed on separate drives. Having the tempdb files on separate drives would provide additional performance benefit. To optimize the replication data transfers or to avoid lags in case of heavy tempdb usage, you can exclude tempdb drives while installing the CloudEndure Migration agent because it is recreated when the server boots on AWS hardware.

To ignore tempdb drives while installing the CloudEndure Migration agent, follow these steps:

  1. Launch the server in cutover mode.
  2. Create an EBS volume and attach it the new server.
  3. Create a folder structure similar to an on-premises server for tempdb. Assign the same drive letter that was used by tempdb disk.
  4. Start SQL Server services so that tempdb is recreated in the new EBS volume.
  5. Verify SQL Server is running and verify the tempdb files appear on the new EBS volume.

Backups

To optimize the replication data transfers, the best practice is not to replicate the disks that the database is dumping the backups on. Backups tend to generate huge amounts of writes and slow down replication.

Microsoft Active Directory

You can perform test cutover of your source SQL Server to do a sanity check. Block the traffic to domain controller. When you perform the test cutover, use Remote Desktop using a local user. Otherwise, the test server that boots on AWS hardware might join to the domain automatically while the on-premises server is still being used by the application.

If you need Active Directory authentication to perform a sanity check during test cutover, follow these steps.

  1. Launch the target machine with a security group that blocks access to Active Directory.
  2. Wait for 2/2 status checks on the EC2 console.
  3. Use Remote Desktop as a local admin user, rename the host, and then reboot.
  4. Attach the security group that allows access to Active Directory.
  5. Join the server to the domain and then reboot.
  6. Use Remote Desktop using the new server name and perform the sanity check.

Block outbound traffic

Analyze your scheduled agent jobs. For example, there might be SQL Server Integration Services (SSIS) packages that are scheduled on your on-premises server that would connect to the database, get the data, do transformations, and then save the data to a CSV file on a Server Message Block (SMB) file share. These files are consumed by a different application or process.

During the test cutover, the job might run on the on-premises server and test server. If outbound access is enabled, the test server might also save or override the existing file in the SMB share that is generated by on-premises server.

Maintenance plans

Take care of maintenance plan schedules during the cutover window. For example, a SQL Server reindexing maintenance job will cause most of the database blocks to change and generate lots of transaction logs. This might result in replication lag in the CloudEndure Migration console and delay the cutover. Avoid scheduling reindex jobs before the planned cutover date.

Patches

Server restarts causes re-scan of the volumes and impact the cutover. Avoid any patches or upgrades before the cutover that would require a restart of the server.

Conclusion

In this post, we offered some best practices and considerations for migrating your on-premises SQL Server database to an Amazon EC2 instance using CloudEndure Migration.

To learn more about CloudEndure Migration, check out the getting started with CloudEndure guide and CloudEndure Migration technical training.

About the Authors

Ganapathi Varma Chekuri

Ganapathi Varma Chekuri is a Database Specialist Solutions Architect at AWS. Ganapathi works with AWS customers providing technical assistance and designing customer solutions on database projects, helping them move their existing databases to AWS cloud.

 

Ranga Cherukuri

Ranga Cherukuri is a Cloud Consultant with Professional services team at AWS. Ranga focuses on helping customers to build highly available, cost-effective database solutions and migrate their large scale SQL Server databases to AWS. He is passionate about Databases and Analytics.