lab steps for the AWS 
Relational Database Service 
on Qwiklabs
Introduction to Amazon Relational Database Service (RDS) - SQL Server | Qwiklabs + amazon-run

00:50:00

Lab failed on Fri, 14 Apr 2017 19:10:05 -0400: CloudFormation_stack returned nil, [Rollback or Create failed] Any credits or access code you used to start this lab have been refunded.

Introduction to Amazon Relational Database Service (RDS) - SQL Server

Self-Paced Lab

Version 1.2

spl145-intro-rds

2017 Amazon Web Services, Inc. and its affiliates. All rights reserved. This work may not be reproduced or redistributed, in whole or in part, without prior written permission from Amazon Web Services, Inc. Commercial copying, lending, or selling is prohibited.

Errors or corrections? Email us at aws-course-feedback@amazon.com.

Other questions? Contact us at https://aws.amazon.com/contact-us/aws-training/

Lab Overview

Overview

This guide introduces you to Amazon Relational Database Service using the AWS Management Console.

Topics covered

By the end of this lab, you will be able to:

  • Create an Amazon Relational Database Service (RDS) SQL Server instance.
  • Connect to the RDS instance using Microsoft SQL Server Management Studio.

Lab setup

The lab provides a preconfigured environment consisting of an EC2 instance running Windows Server and an Amazon RDS SQL Server instance. Microsoft SQL Server Management Studio is already present on the EC2 instance. This setup will take around 20 minutes to complete but you can begin to perform the steps in this lab guide while the setup is in progress.

Amazon Relational Database Service (RDS)

What Is Amazon RDS?

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easy to setup, operate, and scale relational databases in the cloud. It allows you to create and use MySQL, PostgreSQL, Oracle, or SQL Server databases. This means the code, applications, and tools you already use today with your existing databases, can be used with Amazon RDS.

Amazon RDS for SQL Server

With Amazon RDS for SQL Server, you can deploy multiple editions of SQL Server (2008 R2, 2012 and 2014) including Express, Web, Standard and Enterprise (2008 R2 and 2012 only for Enterprise), in minutes with cost-efficient and re-sizable compute capacity. Amazon RDS frees you up to focus on application development because it simplifies many database administration tasks including taking backups, software patching, monitoring, hardware scaling, and designing for high availability.

The AWS Console

Accessing the AWS Management Console

  1. On the Lab Details tab, notice the lab properties below the lab title:
  • setup - The estimated time to set up the lab environment.
  • access - The time the lab will run before automatically shutting down.
  • completion - The estimated time the lab should take to complete.
  1. Click START LAB to launch your lab. If you are prompted for a token, use the one distributed to you (or credits you've purchased).

    A status bar shows the progress of the lab environment creation process (the AWS Management Console is accessible during lab resource creation, but your AWS resources may not be fully available until the process is complete).

  2. In the LAB RESOURCES section, click OPEN CONSOLE, which will automatically log you in to the AWS Console.

Creating a Relational Database Service (RDS) Instance

  1. In the AWS Management Console, on the Services menu, click RDS.

  2. Click Launch a DB Instance.

  3. Click Microsoft SQL Server, then beside Microsoft SQL Server SE (Standard Edition), click Select.

  4. Under Dev/Test, select the SQL Server SE radio button, and then click Next Step.

  5. On the Specify DB Details screen, leave all the default values except set the following, and then click Next Step.

    a. License Model: license-included

    b. DB Instance Class: db.m4.large

    c. DB Instance Identifier: rdstest

    d. Master Username: student

    e. Master Password: Pass.123

    f. Confirm Password: Pass.123

  6. On the Configure Advanced Settings screen, leave all the default values except set the following, and then click Launch DB Instance.

    a. Publicly Accessible: No

    b. VPC Security Group(s): Select the security group having rds-qls in the name. Note: You may need to wait a moment for the 'rds-qls' security group to be created. AWS CloudFormation is still deploying the lab environment as you work.

    c. Backup Retention Period: 0 days to disable automatic backups.

Edit the Security Group

This task shows you how to edit the security group applied to your RDS instance so you can enable access to the database from other machines.

  1. Click View Your DB Instances.

  2. On the Services menu, click VPC.

  3. In the navigation pane, click Security Groups.

  4. Select the checkbox next to the security group that contains rds-qls in the name.

  5. At the bottom of the screen, click the Inbound Rules tab.

  6. Click Edit.

  7. The rule for MS SQL (port 1433) is currently configured to allow inbound traffic only from instances within a particular security group. Edit the Source column to instead be 0.0.0.0/0. Then click Save.

    Note: The RDS instance will take about 20 minutes to create. Hence, as part of the lab setup, a similar RDS instance was automatically provisioned to expedite the lab. The rest of the steps will focus on using the pre-created instance.

Connecting to the EC2 workstation instance

While you were creating the database, an instance of Windows Server was being prepared for you to use as a workstation.

  1. On the Services menu, click EC2.

  2. In the navigation pane, click Instances.

    By default, the checkbox next to the single instance is selected.

  3. In the bottom panel, copy the Public DNS string to your clipboard, which you will use to connect to the instance. It will look something like this: ec2-xx-xxx-xx-xxx.us-west-2.compute.amazonaws.com.

  4. If you are running Windows, which includes the Remote Desktop Connection RDP client, you can skip this step. If you are running Mac or Linux, you may need to download a Remote Desktop Protocol client application, such as one of the following. A few steps may differ if you are using one of these RDP clients.

  1. In Windows, open the Start menu and type mstsc. This will open Remote Desktop Connection.

  2. Paste the Public DNS string into the Computer box.

  3. Click Show options to display the User name box.

  4. In the User name box, type Administrator

  5. Click Connect. Note: If you get an error here, there are two likely causes. First, the instance may not be ready yet. It takes about 4-5 minutes after you click 'Start Lab' before you can login, so please try again in a moment. Second, it's possible that you are behind a firewall that restricts outbound TCP traffic on port 3389. If that is the case, you either need to request your network administrator to open the port, or try the lab again later from a different location.

  6. Return to the qwikLABS console. In the LAB RESOURCES section to the left of the lab-guide, under RDP DETAILS, click the icon beside the textbox to copy the Password to the clipboard. You browser may prompt you to allow clipboard access.

  7. Paste the password into the password textbox in the RDP client and click OK.

  8. Click Yes to ignore the security warning indicating that the EC2 instance doesn't have a client certificate for your computer.

  9. Since the instance is just launching, it may take a couple of minutes for the remote desktop to appear. Click the Windows icon (lower left corner). On the start page, type ssms to search for SQL Server Management Studio.

  10. Wait for the search results to catch-up, then click Microsoft SQL Server Management Studio.

Create the AdventureWorks database on Amazon EC2

You can proceed with this activity while SSMS is launching in the background. You will install a sample database on the Amazon EC2 instance so that later you can learn how to migrate it to the Amazon RDS instance. In this lab, you'll use the EC2 instance to mimic an on-premises SQL Server deployment.

  1. In the Remote Desktop session, click the Windows Start button, then click the down arrow icon to see the Apps screen.

  2. On the Apps screen, click Windows PowerShell ISE.

  3. Copy and paste the following four lines into the PowerShell Script Pane (press CTRL+R if you don't see the script pane) and execute it by pressing F5:

    cd \users\public
    mkdir DBDump
    cd DBDump
    Invoke-WebRequest http://msftdbprodsamples.codeplex.com/downloads/get/478219 -OutFile ADW2008LT.zip
    

    This set of commands downloads a sample database called AdventureWorks.

  4. In the RDP session, open Windows Explorer, navigate to , and right-click the zip file. Click Extract All, and then click Extract.

  5. In Powershell ISE, delete the previous commands, then copy and paste the following command (all on one line). Press F5 to run this command.

    sqlcmd -Q "Use [master]; CREATE DATABASE [AdventureWorks] ON (FILENAME = 'C:\Users\Public\DBDump\ADW2008LT\AdventureWorksLT2008_Data.mdf'),(FILENAME = 'C:\Users\Public\DBDump\ADW2008LT\AdventureWorksLT2008_Log.ldf') for ATTACH"
    

    This command imports the database you downloaded into SQL Server running on the Amazon EC2 instance.

  6. Wait for the command prompt to reappear, then minimize PowerShell and File Explorer.

Connect to SQL Server on Amazon EC2

  1. When SQL Server Management Studio appears, click Connect. This connects you to the local instance of SQL Server on the workstation using Windows Authentication.

  2. In Object Explorer, expand the Databases object. Observe that the AdventureWorks database has been created by the PowerShell commands you ran earlier.

Access the RDS instance via Microsoft SQL Server Management Studio

Now you need to gather the connection details to create a connection to your remote RDS instance.

  1. Return to the AWS Management Console. On the Services menu, click RDS.

  2. In the navigation pane, click Instances.

  3. You should see two instances in the list. Check the box beside the instance named qlcreated-rds, which was pre-created as part of the lab setup.

    Note: You may need to wait here until the Status changes to "backing-up" or "available".

  4. Copy the Endpoint immediately below the selected RDS instance. It will look similar to rdslab.xxxxxxxxxx.us-west-2.rds.amazonaws.com:1433

  5. Return to the RDP session, click the Start menu, and type notepad. Click Notepad in the search results to open it.

  6. Paste the RDS instance URL from the clipboard onto the first line in Notepad.

  7. Edit the URL by deleting ":1433" (the colon and the port number) from the end. Press CTRL+A to select the shortened URL, then press CTRL+C to copy it to your clipboard. Leave Notepad running -- you will use it later.

  8. In SQL Server Management Studio, in Object Explorer, click Connect and then click Database Engine.

  9. In the Connect to Server dialog box, in the Server name box, paste the endpoint you copied -- without ":1433" (the colon and the port number) on the end.

  10. In the Authentication drop-down list, click SQL Server Authentication.

    Note: Amazon RDS for SQL Server supports Windows Authentication, and AWS provides several options for using Active Directory in the cloud. For example, you can deploy a Windows domain controller on Amazon EC2; or you can use Directory Service Simple AD to proxy requests to your on-premise domain controller, or it's easy to create an Active Directory domain in AWS Directory Services. But those options are out of scope for this lab, so we'll simply use SQL Server Authentication.

  11. In the Login textbox, enter student. In the Password textbox, enter Pass.123, and then click Connect.

  12. You have two connections showing in SSMS. In the Object Explorer pane, beneath the RDS instance (named qlcreated), right-click Databases, and then click New Database.

    Note: Make sure you are creating the new database in the remote RDS SQL Server instance and not the local SQL Server instance (named "WIN-xxx").

  13. In the New Database dialog box, in the Database name box, type Sample. Then click OK.

    This has shown you how to use SQL Server Management Studio from a remote workstation to login to your RDS instance and create your own database.

Export AdventureWorks from SQL Server on Amazon EC2 to Amazon RDS

  1. In Object Explorer, beneath the local DB instance (i.e. the one named "WIN-xxx"), right-click the AdventureWorks database, then click Tasks, and then click Export Data.

  2. On the Welcome to SQL Server Import and Export Wizard page, click Next.

  3. In the Data source drop-down list, scroll down and click SQL Server Native Client 11.0. It may take a minute to initialize.

  4. In the Server name drop-down list, if the local server name is not selected, type localhost.

    Note: The source database is on the local server, so the Server name starts with "WIN-".

  5. In the Authentication section, verify that Use Windows Authentication is selected.

  6. In the Database drop-down list, verify that AdventureWorks is selected, and then click Next.

  7. On the Choose a Destination page, in the Destination drop-down list, scroll down and click SQL Server Native Client 11.0.

  8. In the Server name box, copy and paste the Amazon RDS server endpoint that you previously pasted into Notepad from the Amazon RDS console. Remember, ":1433" should be deleted from the end.

  9. In the Authentication section, select Use SQL Server Authentication.

    a. In the User name box, type student.

    b. In the Password box, type Pass.123.

  10. In the Database drop-down list, select Sample, and then click Next.

  11. On the Specify Table Copy or Query page, verify that Copy data from one or more tables or views is selected, and then click Next.

  12. On the Select Source Tables and Views page, check the box on the very top row of the grid. This selects all the check boxes below it. Then click Next.

  13. On the Save and Run Package page, verify that the Run immediately check box is selected, and then click Finish twice.

    The copy operation starts and, within a couple of minutes, it moves all the tables and data to the database in your Amazon RDS instance. A message is displayed when the operation is complete.

  14. Click Close.

  15. To verify the data transfer, in Object Explorer, expand the Databases object under the "qlcreated-rds" instance, right-click Sample, and then click New Query.

  16. In the query window, type or copy/paste the following query to see if the data has been copied to the destination database.

    select * from SalesLT.Address
    
  17. Click Execute.

    You should see the list of records displayed in the Results tab below.

    Congratulations! You loaded a database and executed queries against the RDS SQL Server Instance.

End Your Lab

Follow these steps to close the console, end your lab, and evaluate the experience.

  1. Return to the AWS Management Console.

  2. On the navigation bar, click <yourqwiklabsaccount>@<AccountNumber>, and then click Sign out.

  3. Close any remote desktop sessions.

  4. On the qwikLABS page, click END LAB.

  5. Click OK.

  6. (Optional) Select the applicable number of stars, type a comment, and then click SUBMIT.

    Note: The number of stars indicates the following:

  • 1 star = Very dissatisfied
  • 2 stars = Dissatisfied
  • 3 stars = Neutral
  • 4 stars = Satisfied
  • 5 stars = Very satisfied

You may close the dialog if you don't want to provide feedback.

Conclusion

Congratulations! You now have successfully:

  • Created a Relational Database Service (RDS) SQL Server Instance.
  • Connected to the RDS instance using Microsoft SQL Server Management Studio.

Additional Resources

For feedback, suggestions, or corrections, please email us at aws-course-feedback@amazon.com.

×