Skip to content

Migrate Your First SQL Server Database to Amazon RDS for SQL Server using Backup/Restore

With the approaching end of support for Microsoft SQL Server 2008, it is important to understand your options for upgrading the database layer of your existing applications. The fully managed Amazon Relational Database Service (Amazon RDS) for SQL Server makes it easy to upgrade, set up, operate, and scale your SQL Server business applications for high availability. Join us for this lab session where you and a small group work with an AWS subject matter expert to learn how to migrate a SQL Server database to Amazon RDS.

Prerequisites

Demo Template - Source SQL Server 2008

Inventory:

  • SQL 2008 on Server 2008 R2 (with SSMS)
  • Standalone
  • Max Setup Time is around 10 mins

S3 link: https://s3.amazonaws.com/sacrj-cftemplates-prod/templates/Demo_W2008R2SourceSQL.json

Demo Template - Target RDS - no Managed AD

Inventory: - EC2 w/ SSMS (as Jump Server) - SQL RDS - MultiAZ not available in certain Regions - Max Setup time is around 25 mins (RDS Multi-AZ - 20+ Mins)

S3 link: https://s3.amazonaws.com/sacrj-cftemplates-prod/templates/Demo_W162k16SSMS_RDSSQL-se_AZ.json

Demo Template - Target RDS - with Managed AD

Inventory: - EC2 w/SSMS - Domain Auto-joined (as Jump Server) - SQL RDS - Domain Auto-joined, MultiAZ not available in certain Regions - EC2 (Auto Domain Join) - Requires IAM Role and Instance Profile that has the AmazonEC2RoleforSSM Policy - Provided by Template - RDS (Audo Domain Join) - Requires IAM Role that has the AmazonRDSDirectoryServiceAccess Policy - Provided by Template - Auto-adds [domain]\admin user with elevated rights on SQL RDS - Max Setup time is around 1 hour (MAD - 30 Mins, RDS Multi-AZ - 20+ Mins)

S3 link: https://s3.amazonaws.com/sacrj-cftemplates-prod/templates/Demo_W162k16SSMS_RDSSQL-se_AZ_MAD.json

User Instructions

Deploy the demo environments

Here we deploy the source SQL Server 2008 and target RDS SQL Server (Central – Canada region)

  1. Sign In to the AWS Management Console and open the Amazon EC2 console at https://console.aws.amazon.com/cloudformation/
  2. Click Create Stack
  3. Specify the S3 template URL https://s3.amazonaws.com/sacrj-cftemplates-prod/templates/Demo_W2008R2SourceSQL.json to build SQL Server 2008 environment in Oregon region and click Next Picture 1
  4. Enter the stack name “BuildSQLServer2008”
  5. Specify the administrator password
  6. Click Next
  7. Click Create Stack

Repeat the above step for the https://s3.amazonaws.com/sacrj-cftemplates-prod/templates/Demo_W162k16SSMS_RDSSQL-se_AZ.json template as well to launch the RDS SQL Server in Canada region.

Please remember the passwords at you will use it to connect later on.

Get Source and target info

Source SQL Server on EC2

  1. Sign In to the AWS Management Console and open the Amazon EC2 console at https://console.aws.amazon.com/ec2/
  2. Choose the Region on the upper-right hand side, where the Source EC2 Instance resides Picture 2
  3. On the left navigation pane, go to Instances and select the EC2 Instance (for this demo, Name Tag should have EC2SQLServer suffix) Picture 3
  4. Grab either the Public DNS or the Public IP as your primary connection info Picture 4
  5. User name would be administrator and password would be you have set while creating the stack

Target SQL Server on RDS

  1. Sign In to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
  2. Choose the Region on the upper-right hand side, where the Target RDS Instance resides Picture 5
  3. On the left navigation pane, go to Instances and click on the RDS Instance (for this demo, Name Tag should have rdssqlinstance suffix) Picture 6
  4. Under the Details Section, grab the EndPoint information, which will serve as the primary connection info for your RDS instance Picture 7
  5. Password would be which you set while running the cloud formation stack

Target EC2 Jump Server

  1. Sign In to the AWS Management Console and open the Amazon EC2 console at https://console.aws.amazon.com/ec2/
  2. Choose the Region on the upper-right hand side, where the Target EC2 Instance resides Picture 8
  3. On the left navigation pane, go to Instances and select the EC2 Instance (for this demo, Name Tag should have EC2JumpServer suffix) Picture 9
  4. Grab either the Public DNS or the Public IP as your primary connection info
    Picture 10
  5. Password would be you set while running the cloud formation template

Creating an 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 11
  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.
  4. For Region, (for the purposes of this demo) Choose the region where the Target RDS SQL Server resides.
  5. Choose Create Picture 12

Source SQL Server 2008 on EC2

Connecting to Source DB server via RDP

  1. Go to Remote Desktop Connection
  2. Specify the Public IP or Public DNS of the Source EC2 Instance as the Computer Name
  3. Username: localhost\administrator
  4. Password: Given by Facilitator Picture 13
  5. Click on Connect

Connecting to SQL 2008 via SSMS

  1. Go and click on the SSMS icon on the Desktop, or go to Start and click on the Microsoft SQL Server management studio. Picture 14
  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 15
  3. In the Connect to Server window, do the following:
  4. For Server type, select Database Engine (usually the default option).
  5. For Server name, enter the name of your SQL Server instance. (For this demo, since the DB engine resides in the same server, you can specify "localhost", ".", or the hostname) Picture 16
  6. For Authentication, select Windows Authentication.
  7. After you've completed all the fields, select Connect
  8. Example of successful connection Picture 17

Backing up a Database via SSMS

  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 (SalesDB or Credit)
  3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears. Picture 18
  4. In the Database drop-down list, verify the database name. Optionally, you can select a different database from the list. Picture 19
  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.

Transferring Backup Files to s3

  1. While logged in the Source EC2 Server, sign back in to the AWS Management Console and open the Amazon S3 console at https://console.aws.amazon.com/s3/
  2. In the Bucket Name list, choose the name of the bucket that you want to upload your object to: Picture 20
  3. Picture 21
    • Choose Upload
  4. In the Upload dialog box, choose Add files to choose the file to upload.
    Picture 22
  5. Choose a file to upload (in this case, our backup file/s .bak), and then choose Open Picture 23
  6. Choose Upload Picture 24

Target SQL Server on RDS

Prerequisites

There are three components you'll need to set up for native backup and restore: - An Amazon S3 bucket that contains the backup files to be restored (in the same region as the RDS Instance). - 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.

Creating an Option Group for RDS SQL Server

  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 Picture 25
  3. Choose Create Group Picture 26
  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 matches the target RDS SQL Instance
    • For Major Engine Version, choose the major version that matches the target RDS SQL Instance Picture 27
  5. To continue, choose Create

Configuring the Option Group for SQL Native Backup/Restore

  1. Select the Option Group that you want to modify, and then choose Add Option Picture 28
  2. Add the SQLSERVER_BACKUP_RESTORE option to the Option Group Picture 29
  3. For IAM Role, for the purposes of the demo, Choose New Role Picture 30
  4. For the S3 Bucket, select the bucket that was recently created, that stores the backup files.
    Picture 31
  5. For Encryption, you can choose either Picture 32
  6. Select Yes on Apply Immediately, and continue on to Add Option Picture 33

Adding the Option Group to the RDS SQL Instance

  1. In the RDS navigation pane, choose Instances, and then select the Instance that you want to modify Picture 34
  2. Choose Modify. The Modify DB Instance page appears Picture 35
  3. In Option Group section, select the newly created option group
  4. Click Continue to proceed to the next page Picture 36
  5. For the purposes of the demo, choose Apply Immediately so the changes are immediately applied to the Instance
  6. Click on Modify DB Instance to Continue. Picture 37
  7. If you go back to the Instances Section, you would be able to see that the status of the Instance is Modifying, which should change back to Available once complete Picture 38

Using an EC2 Jump Server to Access RDS

  1. Go to Remote Desktop Connection
  2. Specify the Public IP or Public DNS of the Target EC2 Jump Server as the Computer Name
  3. Username: localhost\administrator or Domain\Admin
  4. Password: Given by Facilitator Picture 39
  5. Click on Connect

Working with SQL Server on RDS via SSMS

  1. Go and click on the SSMS icon on the Desktop, or go to Start and click on the Microsoft SQL Server management studio. Picture 40
  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 41
  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 Endpoint of your SQL Server RDS Instance. Picture 42
    • For Authentication, you have 2 methods:
    • Windows Authentication - You can use the domain\admin account and password Picture 43
    • SQL Server Authentication - You can use the local admin credentials Picture 44
  4. After you've completed all the fields, select Connect
    • Example of successful connection Picture 45

Restoring a Database on RDS via SSMS

Reference - Importing and Exporting SQL Server Databases https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

Restoring a Database To restore a Database from S3, we will be utilizing the msdb.dbo.rds_restore_database stored procedure located under the msdb system database. For the purposes of the demo, we will be Restoring without Encryption

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

  1. Open up a New Query Window, and using the example above, execute the aforementioned stored procedure under the msdb database: Picture 46
  2. Once executed, the task will be queued, you can then check the status by using the msdb.dbo.rds_task_status stored procedure:

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

    Picture 47

    • To cancel any given task, you can use the msdb.dbo.rds_cancel_task stored procedure

    Syntax: exec msdb.dbo.rds_cancel_task @task_id=1234;

    • Restore times vary (from a few minutes, to around 30 minutes)
  3. Once the task status shows SUCCESS, the Restore is complete. Picture 48

    • To Verify, you can see the new restored database on the Object Explorer pane on the left Picture 49