14. March 2018 22:20
by Aaron Medacco
0 Comments

Migrating SQL Server Databases to Amazon RDS

14. March 2018 22:20 by Aaron Medacco | 0 Comments

If you're interested in moving an on-premises SQL Server database or a customer-managed SQL Server database powered by EC2 to RDS and need a simple method for doing so then this post is for you. Not everyone is familiar working with the AWS "lego-kit" and sometimes you just need to get things done. The following is meant to get your SQL Server data migrated without requiring a large time investment reading the AWS documentation.

Microsoft SQL Server

For this, I'm going to use the sample database backup found here. This is a small .bak file which, if you use SQL Server a lot, is something you're accustomed to working with.

As usual, please make sure you have installed and configured the AWS CLI on your workstation before invoking these steps. I won't be specifying the AWS region where these resources get provisioned so you'll need to configure the CLI to place everything where you'd like or provide the appropriate options through this process. Additionally, the IAM user I'll be using to invoke these commands has unrestricted administrator access to the account. 

Creating an RDS instance:

  1. If you've already provisioned an RDS instance and just need to move the database to it, you can skip this. In my case, I ran the following to provision a new database instance:
    aws rds create-db-instance --db-instance-identifier sample-instance --allocated-storage 20 --db-instance-class db.t2.medium --engine sqlserver-web --master-username aaron --master-user-password password --db-subnet-group-name default-vpc-07e6y461 --no-multi-az --publicly-accessible --engine-version 14.00.1000.169.v1

    Note: You'll need to provide your own subnet group and your own username and password that's more secure than my demo values.

  2. This commands creates a small RDS instance running SQL Server 2017 Web Edition in a non-Multi-AZ configuration using minimal resources and standard storage.

Creating and uploading your .bak file to S3:

  1. If you already have an S3 bucket and your .bak file stored within it, you can skip this. Otherwise we need to create an S3 bucket:
    aws s3 mb s3://aarons-sqlserver-rds-demo

    Note: Of course, you'll need to choose your own bucket name that is unique and available. Remember to substitute your bucket name for mine on subsequent commands where appropriate.

  2. And to upload the object, I can navigate to the directory where my .bak file lives and invoke the following:
    aws s3 mv AdventureWorks2017.bak s3://aarons-sqlserver-rds-demo/AdventureWorks2017.bak

    Note: Swap the name of your .bak file and bucket name with my example if different.

  3. Understand that this might not work for you depending on the size of the .bak file you are attempting to upload. You may need to use S3 multi-part upload or another method to move a file of a more significant size.

Creating an IAM role granting access to our .bak file for restore:

  1. Create a file named iam-trust-policy.json with the following contents and save it in your working directory:
    {
        "Version": "2012-10-17",
        "Statement":
        [{
            "Effect": "Allow",
            "Principal": {"Service":  "rds.amazonaws.com"},
            "Action": "sts:AssumeRole"
        }]
    }
  2. Create another file named iam-permission-policy.json with the following contents and save it in your working directory:
    {
        "Version": "2012-10-17",
        "Statement":
        [
            {
            "Effect": "Allow",
            "Action":
                [
                    "s3:ListBucket",
                    "s3:GetBucketLocation"
                ],
            "Resource": "arn:aws:s3:::aarons-sqlserver-rds-demo"
            },
            {
            "Effect": "Allow",
            "Action":
                [
                    "s3:GetObjectMetaData",
                    "s3:GetObject",
                    "s3:PutObject",
                    "s3:ListMultipartUploadParts",
                    "s3:AbortMultipartUpload"
                ],
            "Resource": "arn:aws:s3:::aarons-sqlserver-rds-demo/*"
            }
        ]
    }

    Note: This policy assumes you do not require encryption support.
    Note: Remember to swap your bucket name in for mine.

  3. Create the IAM role for RDS by running the following:
    aws iam create-role --role-name rds-backup-and-restore-role --assume-role-policy-document file://iam-trust-policy.json
  4. Write down the ARN value for the role you just created. You'll need it when we add an option to our option group.
  5. Create an IAM policy which defines the necessary permissions to grant RDS by running the following:
    aws iam create-policy --policy-name rds-backup-and-restore-policy --policy-document file://iam-permission-policy.json
  6. Write down the ARN value for the policy you just created. It should be returned to you from the command output.
  7. Now we just need to attach the IAM policy to our IAM role:
    aws iam attach-role-policy --policy-arn <policy-arn> --role-name rds-backup-and-restore-role

Adding the appropriate option group to enable native backup and restore:

In order to restore our .bak file into RDS we need to add an option group to the instance that enables the native backup and restore functionality. 

  1. To create an option group that does this, you can invoke the following command to create an option group. You will need to modify the command for your specific version and edition of SQL Server:
    aws rds create-option-group --option-group-name sqlserver-web-backupandrestore --engine-name sqlserver-web --major-engine-version 14.00 --option-group-description "Allow SQL Server backup and restore functionality."
  2. Now we need to add an option for the native backup and restore. This is where you need to enter the role ARN you saved from earlier:
    aws rds add-option-to-option-group --option-group-name sqlserver-web-backupandrestore --apply-immediately --options OptionName=SQLSERVER_BACKUP_RESTORE,OptionSettings=[Name="IAM_ROLE_ARN",Value="<role-arn>"]
  3. Finally, we need to modify our RDS instance to use the new option group we've setup:
    aws rds modify-db-instance --db-instance-identifier sample-instance --apply-immediately --option-group-name sqlserver-web-backupandrestore
  4. You may need to walk away for a few minutes and come back while RDS modifies the instance option group. If you want to know when the new option group is active then you can run this command:
    aws rds describe-db-instances --db-instance-identifier sample-instance
    and make sure you see a status of "in-sync" for the option group added.

Restoring the .bak file to your RDS instance:

At this point, we can actually begin restoring our backup. You'll need to connect to your database instance, presumably with SQL Server Management Studio or another tool. Assuming that you've defined the appropriate networking settings (enabling public accessibility on the database instance for example) and security group rules (SQL Server requires enabled traffic on port 1433), you can connect using the endpoint for your database instance. If you don't know your database endpoint, you can find it by running the describe-db-instances command again:

aws rds describe-db-instances --db-instance-identifier sample-instance

and checking the address value for "Endpoint".

  1. Once you have connected to the database instance, run the following stored procedure (targeting the "rdsadmin" database is fine). Remember to swap your own values where appropriate:
    exec msdb.dbo.rds_restore_database @restore_db_name='AdventureWorks2017', @s3_arn_to_restore_from='arn:aws:s3:::aarons-sqlserver-rds-demo/AdventureWorks2017.bak';
  2. After running the stored procedure, your restore request will be queued. To see the progress of your request while it executes, you can provide your database name and run this:
    exec msdb.dbo.rds_task_status @db_name='AdventureWorks2017';
  3. Once the request has a status of "SUCCESS", your database should now be available for use.

If you'd like to read the documentation yourself, you can find the relevant material here and here. I thought it would be helpful to condense everything into a step-by-step post for those developers or DBAs still getting up to speed with AWS.

I also encourage readers to review the limitations when using native backup and restore for SQL Server on RDS which you can find here. For instance, you can only restore databases that are 4 TB or less in size at the time of writing. If this solution doesn't work for you due to size limitations or if you require the database to remain online during migration, you may want check out AWS Database Migration Service as recommended by AWS.

Cheers!

Copyright © 2016-2017 Aaron Medacco