Microsoft Workloads on AWS

Migrate SQL Server databases to AWS with minimal downtime using AWS Migration Hub Orchestrator

In this blog post, we will show you how to configure AWS Migration Hub Orchestrator using the recently-launched support for transactional log backups to reduce downtime during Microsoft SQL Server database migrations to Amazon Web Services (AWS). Customers worldwide trust AWS to run their mission-critical SQL Server databases. For SQL Server databases that are still on premises, many customers are seeking a more streamlined approach to AWS migration. Migration Hub Orchestrator for SQL Server is designed to simplify and streamline the migration of SQL Server databases to AWS.

Migration Hub Orchestrator helps orchestrate and accelerate application migrations with predefined workflow templates. Use Migration Hub Orchestrator to rehost SQL Server databases on Amazon Elastic Compute Cloud (Amazon EC2) or replatform SQL Server on Amazon Relational Database Service (Amazon RDS) with automated native backup and restore. Migration Hub Orchestrator is able to rehost the whole instance with SQL Server databases running on premises to Amazon EC2 at the server level. When bringing SQL Server licenses to Amazon EC2, Migration Hub Orchestrator will import customer-owned SQL Server images.

During the migration workflow creation, the transaction logs, along with full and differential backups, can be applied to reduce downtime. Migration Hub Orchestrator can upload SQL Server backup files to Amazon Simple Storage Service (Amazon S3) using AWS Direct Connect or Site-to-Site VPN to AWS through Migration Hub Orchestrator’s workflows. Because the Migration Hub Orchestrator process is templated, it can make difficult scenarios, including multiple SQL Server instances, easier to manage and provide parallelism to migrations.

Solution overview

Figure 1 shows the solution architecture of how to use Migration Hub Orchestrator to migrate a source SQL Server from on premises to Amazon RDS for SQL Server and SQL Server on Amazon EC2.

Solution Overview

Figure 1: Solution Overview

Prerequisites

Walkthrough

We will now walk you through the steps of creating a migration workflow. This workflow helps you orchestrate the migration of SQL Server databases from on premises to SQL Server on Amazon EC2.

  1. To get started, go to the AWS Migration Hub console, click Workflows in the Orchestrate section (Figure 2), and click Create workflow.Create Workflow

Figure 2: Create Workflow

  1. When you click Create workflow, there is an option to Choose a workflow template. For this example, we will select the Rehost SQL Server Databases on Amazon EC2 as the template, as shown in Figure 3. Select this template and click on Next.

Figure 3: Rehost SQL Server Databases on Amazon EC2

  1. On the Configure your workflow page, specify a name for the new workflow template and, optionally, provide a description. The Application dropdown is populated with applications that were discovered with the AWS Application Discovery Agent. Refer to Figure 4.Rehost SQL Server Databases on Amazon EC2

Figure 4: Configure Workflow

  1. Scroll down to specify the Source environment configuration, as shown in Figure 5. Even though an Application can contain a group of servers, this workflow works with one server at a time. Select the server from the Application in the Server Info dropdown menu. There are three options to select. Use the default “Use Full backup, Differential backup for pre-cutover and T-Log backup for Cutover” to take advantage of the transaction log backup support added to Migration Hub Orchestrator.For Source SQL Server Database names, provide the database names to be migrated as part of this workflow. You can choose multiple databases by separating each database with a comma. At least one database must be provided for the workflow to function. In this example, we will specify these 3 databases: dbtest_01, dbtest_02, and dbtest_03.For Source SQL Server Instance name, provide the instance name of the source SQL Server machine. For this example, we’re using sql-on-premises. Provide the path for the backup location of the source SQL Server database under Backup location. In our example, we’re using G:\backup.There are four selectable options:1. TDE (optional) – If Transparent Data Encryption (TDE), is enabled select this option. This example is not using TDE.
    2. SQL Server System Databases – logins (optional) – This will copy logins from the source database to the target database.
    3. SQL Server System Databases – Agent jobs (optional) – This will copy agent jobs configured on the source database to the target database.
    4. Uploading backup files to S3 (required) – Agreement to upload SQL Server backup files to Amazon S3 using HTTPS if you do not have AWS Direct Connect configured. If you are comfortable with this, please select this checkbox. If you are not comfortable with this, we recommend using AWS Direct Connect with a Public VIF setup. The migration workflow will not create unless you check this checkbox or have the Public VIF setup. Click Next.
    Source Environment Configuration

Figure 5: Source Environment Configuration

  1. On the Review and submit screen, review the details previously provided, and click on Create to create the workflow.
  2. The workflow created is shown in the Workflows section under Orchestrate, as shown in Figure 6. Select the newly created workflow, and click on Run workflow in the upper right of the page.Workflows

Figure 6: Workflows

  1. When you select Run workflow, a new window will pop up prompting Run migration? (Figure 7). Additional resources will be created when the workflow is started. Click on the Run button to continue.Run Migration

Figure 7: Run Migration.

  1. After clicking on Run workflow, a message that states “User attention required” will be displayed. Either click on View Details or click on the workflow itself to see the steps listed. Clicking on either will go to the same location.
  2. From the Orchestrator Workflows page, a list of expandable steps, as shown in Figure 8.Workflow Steps

Figure 8: Workflow Steps

  1. Expand the steps to see the sub-steps for each number. Next to each sub-step, there are two options for Type: Manual or Automated. The Manual type will require some user actions while the Automated type steps will automatically run. For Manual steps, right click on Actions and Change status to Completed if the step has been completed.
    Workflow Steps Manual and Automated

Figure 9: Workflow Steps Manual and Automated

Note: Workflow customization allows you to add custom steps to perform based on any preferred migration tasks.

  1. These steps are confirming the prerequisites for running the workflow. For example, Step 1.a (Figure 9) will prompt for the name of the profile created on the source server. For our example, the source profile is named: mho (Figure 10). This does not create the profile; it is asking for the name of the profile that has already been created.
    Source Profile Name

Figure 10: Source Profile Name

  1. After clicking on Confirm to provide the source profile name, step 1.a now has a status of Completed with 1.b (Figure 9) now in a Status of Ready or User attention required. Step 1.b is confirming that a target instance has been created and will prompt for the target instance details, as shown in Figure 12. Select step 1.b Figure 9), select Actions, and Change status to Completed to enter the target instance details. For the target parameters in step 1.b (Figure 11), there are five input fields:EC2 Instance ID – The ID of the target Amazon EC2 instance
    Target SQL Server Name – The Windows Server name of the target instance
    Target Backup path – The backup path for SQL Server running on the target instance
    Target Log path – The log path for SQL Server running on the target instance
    Target Data path – The data path for SQL Server running on the target instance
    Target environment details

Figure 11: Target environment details.

  1. The next manual step is to configure the Amazon EC2 instance permissions. Once completed, this step can be marked as complete.
  2. The last action for step 1 is to provide a SQL Server user with sysadmin permissions on the target SQL Server instance. As a prerequisite, these credentials must be stored in AWS Secrets Manager. When this step is moved to Completed, the Secret Name must be provided. The Secret ID must begin with the prefix migrationhub-orchestrator- and must be followed by an alphanumeric value as shown in Figure 12).
    SQL Server Credentials Secret

Figure 12: SQL Server Credentials Secret

  1. In Steps 2-4, all sub-steps are automated and completed automatically as part of the workflow. In Figure 13, steps 2-4 focus on creating and restoring backups of databases, agent jobs, and logins:
    Steps 2-4 Workflow

Figure 13: Steps 2-4 Workflow

  1. In step 5, as shown in Figure 14, additional manual steps are required to change the status to Completed.
    Cutover to AWS

Figure 14: Cutover to AWS

  1. As shown in Figure 14, steps 5.e and 5.f perform the automated T-log backup and restore during the cutover to AWS, thus decreasing downtime.

Cleanup

Any workflows created in Migration Hub Orchestrator do not incur additional costs. But if you want to delete these workflows, navigate to Workflows in the Migration Hub Orchestrator console, select your workflow, and then choose Delete from the Actions dropdown menu.

Conclusion

In this blog post, we showed how to simplify and automate the migration of SQL Server workloads to AWS using AWS Migration Hub Orchestrator. Take advantage of AWS Migration Hub Orchestrator to help orchestrate and accelerate your database migrations with predefined workflow templates. We have provided steps that demonstrate additional functionality that allows for the
creation and restoration of transaction log backups. This allows database migrations with zero data loss and minimal downtime.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.

TAGS: , , ,
Vikas Babu Gali

Vikas Babu Gali

Vikas Babu Gali is a Senior Specialist Solutions Architect, focusing on Microsoft Workloads at Amazon Web Services. Vikas provides architectural guidance and technical assistance to customers across different industry verticals accelerating their cloud adoption.

Alan Cranfield

Alan Cranfield

Alan Cranfield is a Senior Database Engineer in the EC2 Windows team where he focuses on enabling and optimizing SQL Server workloads for AWS.

Blake Lyles

Blake Lyles

Blake Lyles is a Microsoft Workloads Specialist Solutions Architect with a special focus on SQL Server. Blake has been at Amazon for over 6 years, spending most of that time working with database workloads, including, SQL Server on EC2, supporting RDS, Database Migration Service, and Amazon DocumentDB. Blake has helped customers migrate and modernize their database workloads on AWS.