14. March 2018 22:20
by Aaron Medacco

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",
            "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",
            "Effect": "Allow",
            "Resource": "arn:aws:s3:::aarons-sqlserver-rds-demo"
            "Effect": "Allow",
            "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.


21. February 2018 21:57
by Aaron Medacco

Blog Sponsorship

21. February 2018 21:57 by Aaron Medacco | 0 Comments

When I started this blog, I knew I would always keep the content ad-free. That sentiment hasn't and will never change. However, starting today, I'd like to offer a method for companies or organizations within the AWS ecosystem to gain exposure to my readers, whilst also supporting the content and time I invest in producing it. Amazon Web Services is a mammoth, ever-changing catalog of cloud offerings that can be overwhelming to learn and keep up with. The articles I write here are a collection of my own understanding and monkeying around within the Amazon cloud and are intended to assist others who may find the content useful. To that end, using the platform as a means to advertise helpful, relevant products within the AWS ecosystem aligns well with that objective.

Handshake Icon

My audience is primarily technology professionals working with Amazon Web Services in some capacity. Therefore, if you believe you offer a product or service that can service my readers and are interested in sponsoring the material here while gaining some brand exposure to boot, please reach out to me at acmedacco@gmail.com for more details. 


20. January 2018 12:00
by Aaron Medacco

New Pluralsight Course: Visualizing Data with Amazon QuickSight

20. January 2018 12:00 by Aaron Medacco | 0 Comments

I've recently completed another course for Pluralsight, this time for Amazon QuickSight. QuickSight is a business intelligence offering within the AWS suite that allows you to import your data and analyze it using dynamic visualization. It's a rather young service, competing with other big-name products like Power BI and Tableau. It'll be interesting to see how this service evolves in the coming years given the pace at which Amazon Web Services moves. And while there are some limitations to the product which I think will be addressed soon, it's a fast and easy data analysis tool to use, especially if you're an AWS customer who already stores their data within the Amazon cloud. 

In Visualizing Data with Amazon QuickSight, I assume the viewer has no experience with Amazon QuickSight or with data analysis at all. The course begins by covering the basics such as account creation, setup and user access management. From there, it covers how to connect or import your data wherever it is to QuickSight. This might mean a flat file you want to import ad-hoc style, objects in S3, a Redshift cluster (which served as the primary data source for the course), or a database stored on-premises or with another provider. Then, I walkthrough how data preparation is done in QuickSight, which is essentially the process for taking data in it's unmodified, raw form and formatting it into a data set that will provide the most value in data analysis. Naturally, data analysis finishes out the course where we enter a deep-dive into the QuickSight data analysis interface and explore the different visualizations and features available to us.

Visualizing Data in Amazon QuickSight

Pluralsight courses are a lot of work, but I'm very satisfied with how this one turned out. There's a lot of demos, but being a very visual tool, that's not a surprise. Plus, "death by slides" is a real thing of which I've suffered through as a student myself. If you're curious about Amazon QuickSight and want to see how you might use it for your own data, go check it out


10. January 2018 00:06
by Aaron Medacco

No Custom SQL Query Option in Amazon QuickSight

10. January 2018 00:06 by Aaron Medacco | 0 Comments

Whether you're practiced or new in using Amazon QuickSight to analyze your data, you're probably already aware of the fact that you define the data set for a SQL database source by either using the table selector found in the data preparation interface or you can write a custom SQL query. However, you've also probably noticed that the option to use a custom SQL query is sometimes absent from the interface when in data preparation:

Amazon QuickSight Missing SQL Option

This can happen sometimes, particularly when you have already defined and saved a data set using the table selector option. This doesn't mean you can't use the custom SQL query option. You just need to create a brand new data set from the data source so that you get into data preparation prior to the data set's creation. Doing this, you should now see the option to use custom SQL in the tables section. 


9. December 2017 11:56
by Aaron Medacco

AWS re:Invent 2017 - Day 5 Experience

9. December 2017 11:56 by Aaron Medacco | 0 Comments

The following is my Day 5 re:Invent 2017 experience. Missed Day 4? Check it out here.

AWS re:Invent winds down pretty quickly after Thursday night. There's a few last minute sessions, and things like the Certification Lounge are open for a little bit in the morning, but for the most part, things are over. After being out pretty late, I missed the sessions I had in the morning. I was wait-listed for these but I'm sure I would have gotten in since people who had them reserved would probably be hung over or have slept in. Oh well. More YouTube viewing. 

I chose to spend the day getting blog posts together and viewing the Keynotes I missed as well as some of the uploaded sessions. Therefore, this post will be sparse of happenings and somewhat of a post-conference overview. Since I was asked to stop recording more than once when walking around the hotels, I didn't get as much footage as I would have hoped. Here's a mashup / montage of what I did gather. Please excuse the bumpiness. I walk with purpose.

Note: Thank you Amazon Elastic Transcoder. Files were huge AVIs from the camera.


I actually decided to leave Friday night around 9:30pm, even though I had the room for the night. Just wanted to get home. For some reason, seemed like a faster ride home. More energy I guess.

Here's some random post-conference stats:

  • Times asked to stop video recording by staff: 2
  • Money lost from gambling: $1295
  • Steps taken walking around Las Vegas: Integer Overflow Exception
  • Days woken up by cleaning lady: 2
  • Shuttles taken: 2
  • Breakout sessions attended: 7
  • Days wishing I had lodged with the Venetian: 5
  • Casinos gambled at: 3
  • Number of times re-starting the re:Invent app to go check something: 40+
  • Number of times re-opening the re:Invent app and immediately closing it when asked to login again: 25+

And some lessons learned for next year:

  • Lodge with the Venetian. Most of the events and sessions are there. This means you won't be encouraged to skip sessions because of the walk or laziness. Plus, you'll be around more re:Invent attendees than otherwise.
  • Take the shuttles. They are usually located near the breakout sessions. Ask one of the re:Invent guides to lead you where to go. Your feet will thank you later.
  • Sanely schedule your week when the event catalog becomes available. Do not assume you will just go back-to-back sessions all week, especially if those sessions are in different hotels. Plan to have ample shuttle time, walk time, bathroom time, eating time, networking, etc.
  • If you are already learned or even certified in AWS, stick to the 400-level and 300-level courses. Most of the sessions will have a lot of information you already needed to learn for your certifications, so get the most out of your time by taking the tougher classes. 200-level courses can be good when a new service is announced and it covers it.
  • Talk to people. We're all nerds who are at re:Invent because we work with or enjoy Amazon Web Services. I found most people were friendly and legit. You could talk about AWS and they would understand you.
  • Show up early to registration. It's going to be a crowded, but the hoodie sizes go fast, and if you want to avoid trying to go back and get a different size later, just get there early. This goes for the Expo, too. Show up early and get your swag early in the week if that's important to you.
  • Keep coming back to check on the Event Catalog even after initial release. Multiple sessions as well as the Keynotes show up way later than the release of the reservation, so keep coming back if you want a spot. 
  • Spend money. It's Vegas. And remember that you get what you pay for.
  • Treat yourself to the Ask The Experts section of the Expo. This was huge value. You get to talk to AWS themselves one-on-one and there wasn't really any lines. Each booth I went up to had like 2 people in front of me max if there was a wait at all.
  • If you're going to do video-recording inside the buildings, use your phone. Seriously, the venues could never enforce this and you can just act like you're taking a photo, which they allow. Still annoyed by this, if it's not apparent.
  • Try to hit everything. And for things that overlap, I would opt to miss breakout sessions you scheduled. They get uploaded to YouTube very quickly. You won't miss that content, it's not going anywhere. Do the other thing.

Okay, well that's all I have. Hopefully, some found this helpful. If you're on the fence about going, just go. I paid for my ticket personally and it's worth it. And I'll be going every year from now on. 


Copyright © 2016-2017 Aaron Medacco