Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Creating a Microsoft SQL Server DB Instance and Connecting to a DB Instance

The basic building block of Amazon RDS is the DB instance. Your Amazon RDS DB instance is similar to your on-premises Microsoft SQL Server. After you create your SQL Server DB instance, you can add one or more custom databases to it.

In this topic, you create a sample SQL Server DB instance. You then connect to the DB instance and run a simple query. Finally you delete the sample DB instance.

Creating a Sample SQL Server DB Instance

In this procedure you use the AWS Management Console to create a sample DB instance. Because you are only creating a sample DB instance, each setting is not fully explained. For a full explanation of each setting, see Creating a DB Instance Running the Microsoft SQL Server Database Engine.

Before your begin, you must have an AWS account before you can create a DB instance. If you don't have an AWS account, open https://aws.amazon.com/, and then choose Create an AWS Account.

To create a DB instance running the Microsoft SQL Server DB engine

  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 AWS Region where you want to create the DB instance.

  3. Locate the Create database card below the Resources card. If you don't see it, scroll down or choose Databases from the navigation pane at left.

    Choose Create database.

  4. On the Select engine page, choose Microsoft SQL Server.

  5. At the bottom of the page, check the box for Only enable options eligible for RDS Free Usage Tier. Doing this simplifies the setup process. For more information, see Amazon RDS Free Tier.

  6. Choose for the SQL Server Express Edition. This is the only SQL Server option that is eligible for RDS free Usage Tier.

  7. On the Instance specifications page on the Specify DB Details page, provide the information for your DB instance as shown in the following table.

    For This Parameter Skip all of these. Provided here for reference only.

    License Model

    Skip. There is only one option available: license-included.

    DB Engine Version

    Skip. This defaults to the most recent version of SQL Server available in the list.

    DB Instance Class

    Skip. This defaults to the Free Tier eligible option, the smallest DB instance class available. This instance class is appropriate for testing.

    Time Zone

    Skip. If you don't choose a different time zone, your DB instance uses the default time zone.

    Storage Type

    Skip. This defaults to General Purpose (SSD).

    Allocated Storage

    Skip. This defaults to 20 to allocate 20 GiB of storage for your database.

    Storage autoscalling

    Skip. Use the default settings, which enable storage autoscaling and set the maximum storage threshold to 1000 GB.

  8. On the Settings card at the bottom of the Specify DB Details page, provide the information for your DB instance as shown in the following table.

    For This Parameter Enter the following

    DB Instance Identifier

    Enter sample-instance.

    Username

    Enter a name to use as the primary user name. The "master" user name is a login with all database privileges. It uses SQL Server Authentication.

    Password and Confirm Password

    Enter a password. It must contain between 8–128 printable ASCII characters (excluding /,", and @).

    Choose Next to continue.

  9. The Configure Advanced Settings page appears.

    On the Network & Security card on the Configure Advanced Settings page, provide the information for your DB instance as shown in the following table.

    For This Parameter Do This

    Vitual Private Cloud (VPC)

    Choose Create new VPC.

    Subnet Group

    Choose Create new DB Subnet Group.

    Publicly Accessible

    Choose No.

    Availability Zone

    Choose No Preference.

    VPC Security Group

    Choose Create new Security Group.

  10. On the Microsoft SQL Server Windows Authentication card on the Configure Advanced Settings page, provide the information for your DB instance as shown in the following table.

    For This Parameter Do This

    Directory

    Skip. This defaults to None.

  11. On the Database Options card on the Configure Advanced Settings page, provide the information for your DB instance as shown in the following table.

    For This Parameter Do This

    Database Port

    Skip. This defaults to 1433. 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

    Skip. Keep the default value.

    Option Group

    Skip. Keep the default value.

  12. On the Encryption card on the Configure Advanced Settings provide the information for your DB instance as shown in the following table.

    For This Parameter Do This

    Encryption

    Choose Disable encryption.

  13. On the Backup card on the Configure Advanced Settings page, provide the information for your DB instance as shown in the following table.

    For This Parameter Do This

    Backup Retention Period

    Skip. Defaults to 7 days.

    Backup Window

    Skip. Defaults to No Preference.

    Copy Tags To Snapshots

    Skip. Defaults to Copy tags to snapshots. However, you can clear the checkbox if you prefer to do so.

  14. On the Monitoring card on the Configure Advanced Settings page, provide the information for your DB instance as shown in the following table.

    For This Parameter Do This

    Enhanced Monitoring

    Choose Disable enhanced monitoring.

  15. On the Performance Insights card on the Configure Advanced Settings page, provide the information for your DB instance as shown in the following table.

    For This Parameter Do This

    Peformance Insights

    Choose Disable Performance Insights

  16. On the Maintenance card on the Configure Advanced Settings page, provide the information for your DB instance as shown in the following table.

    For This Parameter Do This

    Auto Minor Version Upgrade

    Choose Disable auto minor version upgrade.

    Maintenance Window

    Choose No Preference.

  17. On the Deletion protection card on the Configure Advanced Settings page, provide the information for your DB instance as shown in the following table.

    For This Parameter Do This

    Deletion protection

    Clear the box for Enable deletion protection, to make it easier to delete this DB instance when you are done with it.

  18. Choose Create database to confirm your choices and create the DB instance.

  19. After you create the database, 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.

Connecting to Your Sample SQL Server DB Instance

In this procedure you connect to your sample DB instance by using Microsoft SQL Server Management Studio (SSMS).

Before you begin, your database should have a status of Available. If it has a status of Creating or Backup-up, wait until it's Available. The status updates without requiring you to refresh the page. This process can take up to 20 minutes.

Also, make sure you have SSMS installed. If you can also connect to SQL Server on RDS by using a different tools, such as an add-in for your development environment or some other database tool. However, this tutorial only covers using SSMS. To download a stand-alone version of this SSMS, see Download SQL Server Management Studio (SSMS) in the Microsoft documentation.

To connect to a DB Instance using SSMS

  1. Find the DNS name and port number for your DB Instance.

    1. Open the RDS console and then choose Databases to display a list of your DB instances.

    2. Hover your mouse cursor over the name sample-instance, which is blue. When you do this, the mouse cursor changes into a selection icon (for example, a pointing hand). Also, the DB instance name, becomes underlined.

      Click on the DB instance name to choose it. The screen changes to display the information for the DB instance you choose.

    3. On the Connectivity tab, which opens by default, copy the endpoint. The Endpoint looks something like this: sample-instance.abc2defghije.us-west-2.rds.amazonaws.com. Also, take note of the port number. The default port for SQL Server is 1433. If yours is different, write it down.

  2. Start SQL Server Management Studio.

    The Connect to Server dialog box appears.

  3. Provide the information for your sample DB instance.

    1. For Server type, choose Database Engine.

    2. For Server name, enter the DNS name, followed by a comma and the port number (the default port is 1433). For example, your server name should look like the following.

      sample-instance.abc2defghije.us-west-2.rds.amazonaws.com,1433
    3. For Authentication, choose SQL Server Authentication.

    4. For Login, enter the user name that you chose to use for your sample DB instance. This is also known as the master user name.

    5. For Password, enter the password that you chose earlier for your sample DB instance. This is also known as the master user password.

  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.

Exploring Your Sample SQL Server DB Instance

In this procedure you continue the previous procedure and explore your sample DB instance by using Microsoft SQL Server Management Studio (SSMS).

To explore a DB Instance using SSMS

  1. Your SQL Server DB instance comes with SQL Server's standard built-in system databases (master, model, msdb, and tempdb). To explore the system databases, do the following:

    1. In SSMS, on the View menu, choose Object Explorer.

    2. Expand your DB instance, expand Databases, and then expand System Databases as shown following.

      
								Object Explorer displaying the system databases
  2. Your SQL Server DB instance also comes with a database named rdsadmin. Amazon RDS uses this database to store the objects that it uses to manage your database. The rdsadmin database also includes stored procedures that you can run to perform advanced tasks.

  3. You can now start creating your own databases and running queries against your DB instance and databases as usual. To run a test query against your sample DB instance, do the following:

    1. In SSMS, on the File menu point to New and then choose Query with Current Connection.

    2. Enter the following SQL query.

      select @@VERSION
    3. Run the query. SSMS returns the SQL Server version of your Amazon RDS DB instance.

      
			                    SQL Query Window

Deleting Your Sample DB Instance

Once you are done exploring the sample DB instance that you created, you should delete the DB instance so that you are no longer charged for it.

To delete a 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 navigation pane, choose Databases.

  3. Choose the button next to sample-instance, or whatever you named your sample DB instance.

  4. Choose Delete from the Actions above (it's to the left of the orange Create database button.

    If you see a message that says This database has deletion protection option enabled, follow these steps:

    • Choose Modify.

    • On the Deletion protection card (near the bottom of the page), clear the box next to Enable deletion protection. Then choose Continue.

    • On the Scheduling of modifications card, choose Apply immediately. Then choose Modify DB Instance.

    • Try again to delete the instance by choosing Delete from the Actions menu.

  5. Clear the box for Create final snapshot. Because this isn't a production database, you don't need to save a copy of it.

  6. Verify that you selected the correct database to delete. The name "sample-instance" displays in the title of the screen: Delete sample-instance instance?

    If you don't recognize the name of your sample instance in the title, choose Cancel and start over.

  7. To confirm that you want to permanently delete the database that is displayed in the title of this screen, do the following:

    • Check the box to confirm: I acknowledge that upon instance deletion, automated backups, including system snapshots and point-in-time recovery, will no longer be available.

    • Type "delete me" into the box To confirm deletion, type delete me into the field.

    • Choose Delete. This action can't be undone.

    The database shows a status of Deleting until deletion is complete.