Using AWS Migration Hub Orchestrator to simplify and accelerate Microsoft SQL Server migrations to AWS

This year, Amazon Web Services (AWS) introduced AWS Migration Hub Orchestrator, a feature which provides customized migration templates with a predefined set of migration tasks, migration tools, and automation features. Moreover, it allows customers to track their migration progress in one place.

From a SQL Server migration perspective, customers often run into challenges, like not having a step-by-step migration workflow that has been tested and having to do too many manual tasks during the migration. Moreover, it often includes multiple stakeholders, such as customers, migration partners, and AWS Professional Services. These stakeholders often use different processes and tools in different phases of the migration, which can affect the migration timeline. Furthermore, SQL Server migrations involve many manual tasks, such as importing customers’ own images for SQL Server bring-your-own-license (BYOL), installing storage gateway virtual machines (VM) on premises, and validating source and target environments. The manual nature of migrations can add additional effort and impact overall migration timelines. Customers are looking for a solution that will remove migration barriers and accelerate SQL Server migrations.

With the initial release of AWS Migration Hub Orchestrator, there were two out-of-the-box workflow templates: Rehost application on Amazon EC2, and  Migrate SAP NetWeaver applications to AWS.

AWS is now announcing the availability of two new migration templates for Microsoft SQL Server workloads, designed to help our customers reduce SQL Server migration time and effort. You can get started with predefined workflow templates, which are based on the proven Microsoft SQL Server migration best practices. Additionally, you can customize the migration workflows by adding, reordering, and removing steps according to your specific requirements. At this time, the templates only support user database migrations between the same major version.

Let’s delve into two new workflows to understand how it works.

Getting started with new Microsoft SQL Server Migration Workflow

To get started with the new Microsoft SQL Server Migration workflow, go to the  Migration Hub console and choose Get Started, as shown in Figure 1.

Figure 1. AWS Migration Hub Orchestrator Console

Before you can create a new workflow, you will need to: add data sources from on-premises servers and applications using AWS discovery tools; define the applications; and finally, download and configure the plugin server in the same environment as the source servers you want to migrate. This plugin requires a one-time agentless setup in your source environment. Refer to the Configure the Migration Hub Orchestrator plugin documentation.

Figure 2. Prerequisites before creating a new workflow

The plugin server can also be run as a virtual machine in a VMware environment by downloading the AWS-provided Open Virtualization Archive (OVA) file. The plugin server is responsible for running migration tasks on the source SQL Server while executing the workflow, such as validating connectivity, verifying source database configuration, backing up the database, etc. Once completed, the plugin server setup can be seen in the Plugin menu under Orchestrate in the Migration Hub Console, as shown in Figure 3.

Figure 3. Plugin Server in AWS Migration Hub Console

As shown in Figure 4, you can start configuring a workflow to migrate your source database by clicking the Create workflow button in the Workflows menu.

Figure 4. Create workflow in AWS Migration Hub

In the Create migration workflow page in AWS Migration Hub, there are two templates available for SQL Server migration: one is for replatforming to Amazon Relational Database Service (Amazon RDS) and another is for rehosting on Amazon Elastic Compute Cloud (Amazon EC2), as shown in Figure 5.

Figure 5. Two new workflows for SQL Server migration in AWS Migration Hub

Let’s start with the replatform template.

Replatform SQL Server databases on Amazon RDS for SQL Server

Prerequisites

1.       AWS Identity and Access Management (IAM) role

Before you can migrate the source database to Amazon RDS for SQL Server, you will need to set up a new Amazon EC2 instance with Windows operating system in the same VPC as the destination Amazon RDS for SQL Server. You can use a general purpose instance type, such as T3. You will use this EC2 instance to run migration jobs, such as validating the destination SQL Server version, restoring a database, etc. In order to perform those activities, you will need to create (a) a Customer managed Policy, (b) an IAM role that includes required permission policies, and (c) allow inbound traffics from this EC2 instance IP address to Amazon RDS SQL server in the security group.

Let’s create a Customer managed Policy to grant access to the Amazon Simple Storage Service (Amazon S3) bucket, which will be used to upload the backup of the source SQL Server database and save artifacts generated while running the migration workflow.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::migrationhub-orchestrator-*",
                "arn:aws:s3:::aws-migrationhub-orchestrator-*/*"
            ]
        }
    ]
}

Now, you are going to create an IAM Role that includes the customer-managed policy that you created earlier and two additional policies – AmazonSSMManagedInstanceCore and AWSMigrationHubOrchestratorInstanceRolePolicy – as shown in Figure 6.

Figure 6. New IAM role

As illustrated in Figure 7, once the IAM role is ready, you will need to attach this IAM role to the Amazon EC2 instance that you will be using to run migration scripts against Amazon RDS for SQL Server.

Figure 7. Attach IAM role to new Amazon EC2 Instance

2.   Installation of Powershell Modules

Install the dbatools and sqlserver modules by running the following PowerShell commands:

Install-Module dbatools
Install-Module sqlserver

3.   AWS Secrets Manager

You will use AWS Secrets Manager to save credentials for connecting to Amazon RDS for SQL Server. You will use this secret name when you create workflows later. The Secret ID must begin with the prefix “migrationhub-orchestrator-“ and can only be followed by an alphanumeric value as described in Figure 8.

Figure 8. New secret in Secrets Manager

4.       Amazon RDS for SQL Server option groups

You need to create an Option Group in Amazon RDS with the SQLSERVER_BACKUP_RESTORE and TDE (Transparent Data Encryption) options, as shown in Figure 9. The former is required for database backup and restore, while the latter is for restoring TDE databases. Once Options Group is created, attach it to the destination Amazon RDS for SQL Server under Database Options.

Figure 9. Option Groups in Amazon RDS

Migration Workflows creation

Now you are ready to create a workflow using the Replatform SQL Server databases on the Amazon RDS template.

Figure 10. Replatform SQL Server databases on Amazon RDS workflow template

In Configure your workflow page, as shown in Figure 11, enter a name for your workflow, select your application to migrate, and, optionally, add a description.

Figure 11. Workflow details

In Source environment configuration, provide the name of the databases you want to migrate. Use a comma as a separator for multiple databases. Select the TDE (Transparent Data Encryption) option for the TDE databases.

Figure 12. Source environment configuration

In Target environment configuration, provide the Amazon RDS endpoint for the destination Amazon RDS for SQL Server, the Amazon EC2 instance ID that will be used to run scripts, and the Secret name to connect to Amazon RDS for SQL Server. You can also select the Restore Logins, Restore agent jobs and Differential Migration option.

• Restore Logins: Selecting this option will migrate the source SQL Server logins to Amazon RDS for SQL Server. When you create a new DB instance, the default master user gets certain privileges that a managed database platform provides for that DB instance. You may have to check the source SQL Server for any elevated permissions before the migration.
• Restore agent jobs: Selecting this option will migrate the jobs from source SQL Server to Amazon RDS for SQL Server. The credential created in the Secrets Manager will be used to migrate the agent jobs. Thus, this user will be the owner of the job, which can be managed by that user. Refer to this blog post to learn how to grant the SQLAgentOperatorRole permission to other logins to manage the job.
• Differential Migration: Selecting this option will let you define when to cutover by restoring the differential backup. Note that if you want to perform the manual full backup in between workflow execution, take the COPY_ONLY full backup.

Note: Native backup and restore limitations of Amazon RDS for SQL Server is applied.

You can optionally add a tag and click Next to proceed.

Figure 13. Target environment configuration

In Step 3, Review and submit, verify all the inputs, and proceed to create. It takes several minutes to create your workflow. You can confirm the list of migration workflows you have created in Orchestrate  Workflows in the AWS Migration Hub console, as shown in Figure 14.

Figure 14. Created Migration workflows list

Select the newly created migration workflows and click Run under the Action menu to begin the migration. It takes several minutes to finish the migration. AWS Migration Hub Orchestrator also allows you to pause, resume, or delete your workflows.

Figure 15. Run the newly created workflow

During the migration, you can verify the status of each migration step, from validating the source environment to completing the cutover to Amazon RDS for SQL Server, as shown in Figure 16.

Figure 16. Migration steps in the workflow

When you select one of the steps, you will see the details of what the step is and which script was run to perform the job.

Figure 17. Migration step in details

At the final migration step, the workflow will pause and wait for you to stop updating the source database, and mark the source database to ReadOnly. After that, you can select step 4.a and click Completed under Actions and Change status menu to proceed with the final data migration, as shown in Figure 18.

Figure 18. Resume the migration workflow

The workflow will perform differential backup and restore as a next step. Once the database restore is complete, the workflows will pause again to give you time to update the connection string in the application by pointing to the new Amazon RDS for SQL Server instance. Once ready, select Completed to cutover and finish the entire migration.

Figure 19. Completing the migration workflow

Rehost SQL Server Databases on Amazon EC2

Let’s create a new workflow to migrate a database to SQL Server running on Amazon EC2 by selecting the Rehost SQL Server Databases on Amazon EC2 template.

Figure 20. Rehost SQL Server Databases on Amazon EC2 template

In the Configure your workflow page, the only setting that is different, as compared to the Amazon RDS template, is the Target environment configuration. Enter the required information and create the workflow. You can also select the Restore Logins, Restore agent jobs, and Differential Migration option in this template as well.

• Restore Logins: Selecting this option will migrate the source SQL Server logins to Amazon EC2 SQL Server.
• Restore agent jobs: Selecting this option will migrate the jobs from source SQL Server to Amazon EC2 SQL Server.
• Differential Migration: Selecting this option will let you define when to cutover by restoring the differential backup. Note if you want to perform the manual full backup in between workflows, take the COPY_ONLY full backup.

Figure 21. Target environment configuration

The remaining steps will be the same as you go through the template creation for Amazon RDS for SQL Server.

Figure 22. Migration steps for migration of SQL database to Amazon EC2 instance

Tracking Migration workflow in AWS Systems Manager

AWS Migration Hub Orchestrator stores the outputs and logs of steps in Amazon S3 buckets in your account. These logs can be used to examine the output of the migration steps. At the same time, SQL Server migration workflow templates are integrated with AWS Systems Manager. You can find details of each step in Run Command under Systems Manager, as shown in Figure 23.

Figure 23. Migration steps details in Command history under Systems Manager

Now Available

AWS Migration Hub Orchestrator with SQL Server migration workflows are now generally available. There is no additional cost for using these templates, and you only pay for the AWS resources that you provision for the migration. To learn more, see the product page and the documentation.

If you are looking for a migration partner to support your database migration, visit the AWS Migration Hub Partners page.

Partners’ quotes

Our enterprise customers have hundreds of SQL Server databases with business-critical apps. The AWS Migration Hub Orchestrator automates the migration processes, reduces migration risks, and shortens migration durations. With approximately 75% of our customers SQL Server workloads still running in on-premises, using the AWS Migration Hub Orchestrator in the migrations to AWS is a smart choice. JP Chen, Senior Director, SQL Practice at Datavail

AWS Migration Hub Orchestrator provides an easier path to migrate from on-premises environments to AWS with built-in templates for SQL Server. We can monitor and troubleshoot the migration jobs using the detailed logging that is stored in S3 for each workflow. It also gives us the option to add more steps with custom scripts to meet our migration needs. The automatic steps in the workflows cut down on the downtime and will make SQL migrations faster in the future. Chinta Mallikarjunarao, Cloud Architect at Agilisium

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your modernization journey today.