Skip to content

Migrating SQL Server Database to Amazon RDS

Step to Connect to AWS EC2 Windows Instance

  1. Go to Remote Desktop Connection
  2. Mentioned the computer name as EC2 instance name, user name and password

    Picture 1

  3. Click On connect

Connect to SQL Server instance installed on EC2 using SSMS

  1. Go to Start window and click on the Microsoft SQL Server management studio Picture 2
  2. The first time you run SSMS, the Connect to Server window opens. If it doesn't open, you can open it manually by selecting Object Explorer > Connect > Database Engine. Picture 3
  3. In the Connect to Server window, do the following:
    • For Server type, select Database Engine (usually the default option).
    • For Server name, enter the name of your SQL Server instance. (In case if you don’t know the instance name, write “localhost” or windows server name. Picture 4
    • For Authentication, select Windows Authentication.
    • After you've completed all the fields, select Connect
    • Example of successful connection Picture 5

Steps to create the database

You can either use the AdventureWorks or create your own database & table

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. Right-click Databases, and then click New Database. Picture 6
  3. In New Database, enter a database name. In the below example, I have specified the ‘demo’ as database name. Picture 7
  4. To create the database by accepting all default values, click OK

Steps to create table in the database & insert values

  1. In SSMS, in Object Explorer, connect to the instance of Database Engine that contains the database to be modified.
  2. In Object Explorer, expand the Databases node and then expand the database that will contain the new table.
  3. In Object Explorer, right-click the Tables node of your database and then click New Table. Picture 8
  4. Type column names, choose data types, and choose whether to allow nulls for each column as shown in the following illustration: Picture 9
  5. From the File menu, choose Save table name.
  6. In the Choose Name dialog box, type a name for the table and click OK. Picture 10
  7. To insert value in the table, right click on the table name in the object explorer and click on Edit Top 200 Rows Picture 11
  8. Insert the values in the columns and close the window Picture 12
  9. To check the inserted values you can click again Edit Top 200 Rows or Select Top 1000 Rows

Steps to back up the database

  1. After connecting to the SQL Server instance in Object Explorer, click the server name to expand the server tree.
  2. Expand Databases, and either select a user database (AdventureWorks or database created by you)
  3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears. Picture 13
  4. In the Database drop-down list, verify the database name. Optionally, you can select a different database from the list. Picture 14
  5. In the Backup type drop-down list, select Full.
  6. For Backup component, select the Database radio button.
  7. In the Destination section, use the Back up to drop-down list to select the backup destination. Please keep a note of the backup up location as in the next step you have to upload the backup file to S3.
  8. Click OK to take the Full backup of the database.

Steps to create S3 bucket

  1. Sign in to the AWS Management Console and open the Amazon S3 console at https://console.aws.amazon.com/s3/
  2. Choose Create bucket. Picture 15
  3. In the Bucket name field, type a unique DNS-compliant name for your new bucket. (The example screen shot uses the bucket name admin-created. You cannot use this name because S3 bucket names must be unique.) Create your own bucket name using the follow naming guidelines:

    • The name must be unique across all existing bucket names in Amazon S3.
    • After you create the bucket you cannot change the name, so choose wisely.
    • Choose a bucket name that reflects the objects in the bucket because the bucket name is visible in the URL that points to the objects that you're going to put in your bucket.
    • For Region, choose US West (Oregon) as the region where your RDS instance reside.
    • Choose Create. Picture 16 You've created a bucket in Amazon S3.

Steps to upload backup file to S3

To upload an object to a bucket 1. In the Bucket name list, choose the name of the bucket that you want to upload your object to.

![Picture 17](https://s3.amazonaws.com/www.awslab.io/dotnet/sqlservertords/images/Picture17.png)
  1. Choose Upload.

    Picture 18

    • Or you can choose Get started. Picture 19
  2. In the Upload dialog box, choose Add files to choose the file to upload. Picture 20

  3. Choose a file to upload, and then choose Open. Picture 21
  4. Choose Upload. Picture 20

Steps to configure RDS instance for backup restore

There are three components you'll need to set up for native backup and restore:

  • An Amazon S3 bucket to store your backup files.
  • An AWS Identity and Access Management (IAM) role to access the bucket.
  • The SQLSERVER_BACKUP_RESTORE option added to an option group on your DB instance.

Steps to create option group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
  2. In the navigation pane, choose Option groups.
  3. Choose Create group.
  4. In the Create option group window, do the following:
    • For Name, type a name for the option group that is unique within your AWS account. The name can contain only letters, digits, and hyphens.
    • For Description, type a brief description of the option group. The description is used for display purposes.
    • For Engine, choose the DB engine that you want.
    • For Major engine version, choose the major version of the DB engine that you want.
  5. To continue, choose Create. To cancel the operation instead, choose Cancel.

Steps to configure option group for backup/restore

To add the Native Backup and Restore option

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
  2. In the navigation pane, choose Option groups.
  3. Select the option group that you want to modify, and then choose Add
  4. Add the SQLSERVER_BACKUP_RESTORE option to the option group, and configure the option settings.
    • For IAM Role, select an existing IAM role. Alternatively, you can choose to have a new IAM role created for you by choosing Create a New Role.
    • For Select S3 Bucket, select an existing bucket. Alternatively, you can choose to have a new Amazon S3 bucket created for you by choosing Create a New S3 Bucket.
    • For Enable Encryption, choose No
  5. In the navigation pane, choose DB Instances, and then select the DB instance that you want to modify.
  6. Choose Instance Actions, and then choose Modify. The Modify DB Instance page appears.
  7. In option group select the newly created option group
  8. To apply the changes immediately, select Apply Immediately. Selecting this option can cause an outage in some cases. For more information, see The Impact of Apply Immediately.
  9. When all the changes are as you want them, choose Continue.
  10. On the confirmation page, review your changes. If they are correct, choose Modify DB Instance to save your changes.

Command for the backup/restore

To perform the backup/restore on the RDS instance you will call the RDS backup/restore procedures.

Steps to restore database

The following parameters are required:

  • @restore_db_name – The name of the database to restore.
  • @s3_arn_to_restore_from – The Amazon S3 bucket that contains the backup file, and the name of the file.

The following parameters are optional:

  • @kms_master_key_arn – If you encrypted the backup file, the key to use to decrypt the file.

Example Without Encryption

exec msdb.dbo.rds_restore_database 
@restore_db_name='database_name', 
@s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';

Example With Encryption

exec msdb.dbo.rds_restore_database 
@restore_db_name='database_name', 
@s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension',
@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id';

Steps to backup database

To back up your database, you call the rds_backup_database stored procedure.

The following parameters are required: - @source_db_name – The name of the database to back up - @s3_arn_to_backup_to – The bucket to use for the backup, plus the name of the file (Amazon S3 bucket + key ARN).

The following parameters are optional:

  • @kms_master_key_arn – The key to encrypt the backup (KMS customer master key ARN). For more information about encryption keys, see Getting Started in the AWS Key Management Service (AWS KMS) documentation.
  • @overwrite_S3_backup_file – Defaults to 0
    • Don't overwrite the existing file. Return an error instead if the file already exists.
    • Overwrite an existing file that has the specified name, even if it isn't a backup file.
  • @type – Defaults to FULL, not case sensitive
    • differential – Take a differential backup.
    • full – Take a full backup.

Example Differential Backup without Encryption

exec msdb.dbo.rds_backup_database 
@source_db_name='database_name', 
@s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
@overwrite_S3_backup_file=1,
@type='differential';

Example Full Backup with Encryption

exec msdb.dbo.rds_backup_database
@source_db_name='database_name',
@s3_arn_to_backup_to='arn:aws:s3:::bucket_name/file_name_and_extension',
@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id',
@overwrite_S3_backup_file=1,
@type='FULL';

Cancelling backup/restore task

To cancel a backup or restore task, you call the rds_cancel_task stored procedure.

The following parameters are optional:

  • @db_name – The name of the database to cancel the task for.
  • @task_id – The ID of the task to cancel. You can get the task ID by calling rds_task_status.

    exec msdb.dbo.rds_cancel_task @task_id=1234;

Tracking the Status of Tasks

To track the status of your backup and restore tasks, you call the rds_task_status stored procedure. If you don't provide any parameters, the stored procedure returns the status of all tasks. The status for tasks is updated approximately every 2 minutes. The following parameters are optional:

  • @db_name – The name of the database to show the task status for.
  • @task_id – The ID of the task to show the task status for.

    exec msdb.dbo.rds_task_status @db_name='database_name';