Replatform SQL on Amazon RDS

Ensure that PowerShell is enabled on the server that contains your SQL server instance.

Install AWS.Tools on the server that contains your SQL server instance, with the following command.

Install the DBA.Tools module on your Windows machine, with the following command.

Go to https://console.aws.amazon.com/migrationhub/orchestrator/

Select Create migration workflow.

On Choose a workflow template page, select Replatform SQL server on Amazon RDS template.

Configure and submit your workflow to begin migration.

TDE - Check this checkbox if you have TDE enabled on your Databases. If you select this option, your certificates will be migrated to the target server.

Migration Mode - This template offers 3 distinct migrations depending on your use-case. 

Allow Migration Without Direct Connect - This template uploads backup files from your source instance to S3 using the AWS CLI. The database files are transmitted over an HTTPS to AWS S3. However, if you are not comfortable with the backup files travelling over the public Internet, we recommend using AWS Direct Connect with a Public VIF setup. If you are comfortable with this, please select this checkbox. The migration workflow will not create unless you check this checkbox or have the setup mentioned above.

Source SQL Server database names - The names of the SQL Databases that you would like to migrate.

AWS ADS server ID for your application - See “ServerId” section above.

Source SQL Server instance name - The name of your SQL server instance.

Backup location - As a part of the migration, this template needs to take backups of your SQL Server. The path specified here is where the backup files will be stored. Please ensure this is an absolute path and has enough space for a Full and Differential backup of your databases.

When configuring the Migration Hub Orchestrator plugin, ensure that the username that is provided to connect to your Windows machine has the SYSAdmin permission on the source SQL server instance.

Create an IAM policy with the following permissions.

Create an IAM user with the above policy attached.

Configure a named profile for AWS Command Line Interface that uses the preceding IAM user. For more information, see Using AWS credentials. The credentials stored in the profile are used to upload your backups to a S3 bucket located in your account. Note the name of this profile and enter it into the step when prompted.

Deploy an Amazon RDS SQL server with the same version as the source SQL server.

Configure the target Amazon RDS SQL server with the same parameter groups as the source SQL server.

Configure the option group for backup/restore and transparent data encryption in Amazon RDS, and attach the following policies to the created IAM role.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "kms:Decrypt",
                "s3:ListAllMyBuckets",
                "kms:DescribeKey"
            ],
            "Resource": "*"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketAcl",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Sid": "VisualEditor2",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:ListMultipartUploadParts"
            ],
            "Resource": [
                "*"
            ]
        }
    ]
}

The trust policy for this role should be:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
              "Service": "rds.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Deploy an Amazon EC2 instance and create an instance role.

Ensure that your Amazon RDS instance can be reached from the created Amazon EC2 instance.

This instance is used to connect to your RDS instance and run restore procedures.

Provide credentials in AWS Secrets Manager for the username and password for the admin user for your RDS Server.

For full backup only migrations set the databases to readonly before begging the migration workflow.

For full and differential backup migrations, set the databases to read only when instructed to do so on Step 4.1 in the workflow.

For full, differential and tail-log backups, set the databases to read only when instructed to do so on Step 4.4 in the workflow.

This template does not migrate System Databases or SQL Server properties.

This template can only migrate SQL logins. Any Windows level logins are not guaranteed to be migrated.

This template expects that while the workflow is running, you will not initiate a full-backup of the database yourself. If a full-backup is taken, it breaks the chain of backups used to restore your databases on the target server.

This template can only migrate databases that have the “DBO” set as a sql user or AD user. If the database is owner is a Windows level user which is not available in the RDS environment, the database will be inaccessible when restored on RDS.

If this occurs on the source, ensure the user that was given to the plugin to connect to the machine has SYSADMIN permissions on your source SQL server.

If this happens on the target, ensure that the EC2 Instance ID provided during workflow creation has connectivity to your RDS Endpoint, and ensure the SQL credentials stored in your Secrets Manager Secret are correct.