Step 6: Building SQL

Authors: Dean Suzuki, Vladimir Provorov, Ashita Dsilva (Last Updated: 8/23/20)

Abstract

In this lab, you will create a new Microsoft SQL Server instance leveraging Amazon RDS for SQL Server. Amazon RDS for SQL Server is AWS’s managed Microsoft SQL Server service. By managed, AWS manages the hardware and software (e.g. patching the Windows operating system and Microsoft SQL server software, backing up the databases). You can still use SQL Management Studio to create databases, indexes, etc. and manage them.

Amazon RDS for SQL Server is only one of the many relational database engines that Amazon supports. The other database engines that Amazon Relational Database Service (Amazon RDS) supports currently are: Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle, and Microsoft SQL Server. See here for more information

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks.

Amazon RDS supports DB instances running several versions and editions of Microsoft SQL Server. The full list of supported versions, editions, and RDS engine versions can be found at: Version and Feature Support on Amazon RDS.

Create RDS for SQL Server Instance

In this task, you will create a new RDS SQL Server instance joined to your Managed Active Directory.

  1. Navigate to RDS Console and click “Create database
  2. Select “Standard Create” to see all features of RDS for SQL Server that can be configured during the deployment.
  3. Under Engine options:

    • Engine type: “Microsoft SQL Server

    • Edition: “SQL Server Standard Edition

    • Version: keep version as is (latest available).

  4. For Templates, select “Dev/Test

  5. For Settings:

    • DB instance identifier: myrdssql

    • Master username: admin

    • Master password: Passw0rd1

  6. Under DB instance size:

    • DB instance class: Standard classes (includes m classes)

    • DB instance class: db.m5.large

  7. Under Storage:

    • Storage type: General Purpose (SSD)

    • Allocated storage: 20 GiB

    • Storage autoscaling: Check Enable storage autoscaling

    • Maximum storage threshold: 32

  8. Under Availability & durability:

    • Multi-AZ deployment: No

    • Note: Amazon RDS for SQL Server supports multiple Availability Zones (AZ) deployments. However for the purposes of this lab, you are choosing the single AZ deployment model to reduce the amount of time that it takes to create the environment.

  9. Under Connectivity:

    • Virtual Private Cloud (VPC): WinVPCVPCStack-* (created in Lab1)

    • Under Additional Connectivity configuration

      • Publicly accessible: No

      • VPC Security Group: Leave the default option.

      • Availability zone: us-east-1a

      • Database port: 1433

  10. Under Microsoft SQL Server Windows Authentication:

    • Check Enable Microsoft SQL Server Windows authentication

    • Directory: corp.example.com

  11. Keep all other options as is by default and click “Create Database”.

AWS will now begin the process of provisioning a Microsoft SQL server environment based upon the parameters that you specified. The database creation process usually takes around 20 minutes.

The database will be operational when the status changes to Available.

Click the hyperlink for the database and then review the tabs (Connectivity & security Monitoring, Logs & events, Configuration, Maintenance and backups, Tags).

  1. On the Connectivity & security tab, the Endpoint is what should be used in SQL Management Studio to connect to the SQL server. Please save this connection endpoint to a text file. You will use it in the next section.
  2. On the Monitoring tab, note the metrics that are monitored.
  3. Click the Modify button to see the additional configuration options that available (e.g maintenance and backups).

Congratulations!

You have now added a Microsoft SQL server to your environment. This SQL server is operated by Amazon RDS which provides Microsoft SQL as a managed service. To access the SQL database, you use the same SQL Management Studio tool that you use to manage SQL server databases on-premises.

Connecting to the RDS SQL server

In this step, you will connect to the RDS SQL server that you created in the prior step.

To do this step, you will leverage the AD Management server that you created earlier. If you haven’t created the management server from Step 3, please follow the steps in the Step 3 > Administering Managed AD to create the management server. Also follow the steps to establish a RDP connection to it as documented in that lab.

  1. Login to the Management EC2 instance that you created earlier.

  2. On the Management Ec2 instance, download the SQL Server Management Studio tools. Please see here. To do this step, you will need to add this site to the Trusted Sites in Internet Explorer. Also, you may want to turn off the IE Enhanced Security Configuration.

    • To add the site to the Internet Explorer Trusted Sites list, hit the gear icon in IE in the upper right corner. Select Internet Options. Select Security tab. Select Trusted Sites. Press the Sites button. Make sure that the website URL is listed in the field. If not, paste it into the field. Then press Add. Then Close. Back on the Internet Options page, press OK.

    • To turn off the IE Enhanced Security Configuration. Launch Server Manager by typing it in the Start menu. On the left navigation, select Local Server. On the right side of the page, select the On hyperlink next to the IE Enhanced Security Configuration. On the pop-up menu, select Off for Administrators, please OK.

    • For more details on Creating a Microsoft SQL Server DB Instance and Connecting to a DB Instance, please see here.

  3. Install the Microsoft SQL Server Management Studio on the management server.

  4. Launch Microsoft SQL Server Managemetn Studio.

  5. On the connection dialog box, specify the following:

    • For the Server name, paste in the connection endpoint that you saved earlier.

    • For Authentication, change to SQL Server Authentication.

    • For Login, enter admin (or what you specified earlier)

    • For password, enter the password that you specified when you created the RDS SQL (e.g. Passw0rd1).

    • Click Remember password

    • Press Connect.

Congratulations!

You should be connected to the RDS SQL server instance. Explore the Databases available. Notice that you use the same SQL Server Management Studio tool to managed RDS SQL as you would for managing SQL on-premises.