Skip to content

Creating a Sample RDS SQL Server DB Instance

In this procedure you use the AWS Management Console to create a sample DB instance.

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
  2. In the upper-right corner of the Amazon RDS console, choose the AW Region in which you want to create the DB instance.
  3. In the navigation pane, choose Databases.
  4. Choose Create database. Picture 1
  5. Choose the SQL Server icon, and then and Select SQL Server edition. Picture 2
  6. Choose the use case. I am selecting DEV/TEST for demo. Picture 3
  7. On the Specify DB Details page, provide the information for your DB instance as shown in the following table.

    For This Parameter Do This
    License Model Choose license-included to use the general license agreement for Microsoft SQL Server
    DB Engine Version Choose the most recent version of SQL Server available in the list.
    DB Instance Class Choose db.t2.micro. This instance class is appropriate for testing.
    Time Zone Don't choose a time zone. If you don't choose a time zone, your DB instance uses the default time zone
    Storage Type Choose the storage type General Purpose (SSD).
    Allocated Storage Enter 20 to allocate 20 GiB of storage for your database. There is a warning that you should consider allocating more storage, but because this is a sample DB instance, 20 GiB is sufficient.
    DB Instance Identifier Enter sample-instance
    Master Username Enter a name to use as the master user name to log on to your DB Instance with all database privileges. The master user name is a SQL Server Authentication login
    Master Password and Confirm Password Enter a password for your master user password. It must contain between 8–128 printable ASCII characters (excluding /,", and @).
  8. Choose Next to continue.

  9. The Configure Advanced Settings page appears. Picture 4
  10. On the Configure Advanced Settings page, provide the information for your DB instance as shown in the following table.

    ,For This Parameter Do This
    VPC Choose Create new VPC
    Subnet Group Choose Create new DB Subnet Group
    Publicly Accessible Choose Yes
    Availability Zone Choose No Preference
    VPC Security Group Choose Create new Security Group
    Database Port Keep the default value of 1433 unless you have a specific port that you want to access the database through. SQL Server installations default to port 1433, but in some cases a firewall might block this port. If in doubt, ask your network administrator what port you should use.
    DB Parameter Group Keep the default value
    Option Group Keep the default value
    Copy Tags To Snapshots Keep this setting unselected
    Backup Retention Period Choose 7
    Backup Window Choose No Preference
    Enable Enhanced Monitoring Choose No
    Auto Minor Version Upgrade Choose Enable auto minor version upgrade to enable your DB instance to receive preferred minor DB engine version upgrades automatically when they become available
    Maintenance Window Choose No Preference
  11. Choose Create database.

  12. Choose View Your DB Instances. On the RDS console, the new DB instance appears in the list of DB instances. The DB instance has a status of creating until the DB instance is ready to use. When the state changes to available, you can connect to the DB instance. Depending on the DB instance class and the amount of storage, it can take up to 20 minutes before the new instance is available. Picture 5

Connect your sample DB instance

To connect to a DB Instance using SSMS

  1. Find the DNS name and port number for your DB Instance.
    • Open the RDS console and then choose Databases to display a list of your DB instances.
    • Choose the name of your SQL Server DB instance to display the summary information for the DB instance. Picture 6
    • On the Connectivity tab, copy the endpoint. The Endpoint field has two parts separated by a colon (:). The part before the colon is the DNS name for the instance, and the part following the colon is the port number. Copy both parts.
  2. Start SQL Server Management Studio. The Connect to Server dialog box appears. Picture 7
  3. Provide the information for your sample DB instance.

    • For Server type, choose Database Engine.
    • For Server name, enter the DNS name and port number of your sample DB instance, separated by a comma.

    Important Change the colon between the DNS name and port number to a comma. For example, your server name should look like the following. sample-instance.cg034hpkmmjt.us-east-1.rds.amazonaws.com,1433

    • For Authentication, choose SQL Server Authentication.
    • For Login, enter the master user name that you chose earlier for your sample DB instance.
    • For Password, enter the password that you chose earlier for your sample DB instance.
  4. Choose Connect. After a few moments, SSMS connects to your DB instance. If you can't connect to your DB instance, see Troubleshooting the Connection to Your SQL Server DB Instance.

Create the EC2 SQL Server Database

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/EC2/
  2. In the upper-right corner of the Amazon EC2 console, choose the AWS Region in which you want to create the EC2 windows instance.
  3. In the navigation pane, click on Launch Instance Picture 8
  4. Search for the “Microsoft Windows Server 2019 with SQL Server 2017 Standard” AMI and Select it Picture 9
  5. Select instance type as “r5d.xlarge” and click on Next Configure Instance Details
  6. Click on Advanced Details and paste the below script to User Data text box
  7. Click Next: Add Storage
  8. Add New Volumes for Data, log, Backup Picture 10
  9. Configure security group. Security group will act as firewall for the inbound and outbound connections to EC2 instance
  10. Create a Key Pair and Launch your instance. Save the Key Pair .PEM file and you will use it later on to connect to instance
  11. Select the instance you created from the console and click on connect
  12. In order to connect to your Windows virtual machine instance, you will need a user name and password:
    • The User name defaults to Administrator
    • To receive your password, click Get Password Picture 11
  13. In order to retrieve the password, you will need to locate the Key Pair you created in Step 10. Click Choose File and browse to the directory you stored FileName.pem. Your Key Pair will surface in the text box. Click Decrypt Password.
  14. Click on Download Remote Desktop file and use the User Name/Password to connect to SQL Server Instance. Picture 12
  15. Once you RDP to the EC2 Windows. You can open the SQL Server management studio and connect to SQL Server instance Picture 13