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

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

The basic building block of Amazon RDS is the DB instance. This environment is where you will run your Microsoft SQL Server databases.

Important

You must complete the tasks in the Setting Up for Amazon RDS section before you can create or connect to a DB instance.

Creating a SQL Server DB Instance

The easiest way to create a DB instance is to use the AWS Management Console. Once you have created the DB instance, you can use standard SQL Server utilities to connect to the DB instance such as the Microsoft SQL Server Management Studio utility.

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 top right corner of the Amazon RDS console, choose the region in which you want to create the DB instance.

  3. In the navigation pane, choose Instances.

  4. Choose Launch DB Instance to start the Launch DB Instance Wizard.

    The wizard opens on the Select Engine page.

    Engine selection
  5. In the Launch DB Instance Wizard window, choose the SQL Server icon, then choose Select for the SQL Server version you want to use.

  6. The Production? page asks if you are planning to use the DB instance you are creating for production. If you are, choose Yes. If you choose Yes, the failover option Multi-AZ and the Provisioned IOPS storage option are preselected in the following step.

  7. Choose Next to continue. The Specify DB Details page appears.

    DB instance details
  8. On the Specify DB Details page, specify your DB instance information. The following table shows settings for an example DB instance using SQL Server Standard Edition.

    For This ParameterDo This

    License Model

    Choose license-included to use the general license agreement for Microsoft SQL Server.

    For more information about license models, see Licensing Microsoft SQL Server on Amazon RDS.

    DB Engine Version

    Choose the default version of SQL Server.

    DB Instance Class

    Choose db.m1.small for a configuration that equates to 1.7 GB memory, 1 ECU (1 virtual core with 1 ECU), 64-bit platform, and moderate I/O capacity.

    For more information, see DB Instance Class and DB Instance Class Support for Microsoft SQL Server.

    Time Zone

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

    For more information, see Local Time Zone for Microsoft SQL Server DB Instances.

    Multi-AZ Deployment

    Choose Yes to have a standby mirror of your DB instance created in another Availability Zone for failover support. We recommend Multi-AZ for production workloads to maintain high availability. For development and testing, you can choose No.

    For more information, see Multi-AZ Deployments for Microsoft SQL Server with Database Mirroring.

    Storage Type

    Choose the storage type Magnetic.

    For more information, see Amazon RDS Storage Types.

    Allocated Storage

    Type 200 to allocate 200 GB of storage for your database. In some cases, allocating a higher amount of storage for your DB instance than the size of your database can improve I/O performance.

    For more information, see Storage for Amazon RDS.

    DB Instance Identifier

    Type a name for the DB instance of 15 alphanumeric characters or less that is unique for your account in the region you chose. You can add some intelligence to the name, such as including the region and DB Engine you chose, such as sqlsv-instance1.

    Master Username

    Type a name that you will 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 that is a member of the processadmin, public, and setupadmin fixed server roles.

    Master Password and Confirm Password

    Type a password that contains from 8 to 128 printable ASCII characters (excluding /,", and @) for your master user password, and then type it again in the Confirm Password box.

  9. Choose Next to continue. The Configure Advanced Settings page appears.

    Additional Configuration panel
  10. On the Configure Advanced Settings page, provide additional information that Amazon RDS needs to launch the SQL Server DB instance. The table following shows settings for an example DB instance.

    For This ParameterDo This

    VPC

    This setting depends on the platform you are on. If you are a new customer to AWS, choose the default VPC shown. If you are creating a DB instance on the previous E2-Classic platform that does not use a VPC, choose Not in VPC.

    For more information, see Virtual Private Clouds (VPCs) and Amazon RDS.

    Subnet Group

    This setting depends on the platform you are on. If you are a new customer to AWS, choose default, which will be the default DB subnet group that was created for your account. If you are creating a DB instance on the previous E2-Classic platform and you want your DB instance in a specific VPC, choose the DB subnet group you created for that VPC.

    Publicly Accessible

    Choose Yes to give the DB instance a public IP address, meaning that it will be accessible outside the VPC; otherwise, choose No, so the DB instance will only be accessible from inside the VPC.

    For more information, see Hiding a DB Instance in a VPC from the Internet.

    Availability Zone

    Use the default value of No Preference unless you want to specify an Availability Zone.

    For more information, see Regions and Availability Zones.

    VPC Security Group

    If you are a new customer to AWS, choose the default VPC. Otherwise, choose the VPC security group you previously created.

    For more information, see Working with DB Security Groups.

    Database Port

    Leave the default value of 1433 unless you have a specific port 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

    Use the default value unless you have created your own parameter group.

    For more information, see Working with DB Parameter Groups.

    Option Group

    Use the default value unless you have created your own option group.

    For more information, see Working with Option Groups.

    Copy Tags To Snapshots

    Select this option to have any DB instance tags copied to a DB snapshot when you create a snapshot.

    For more information, see Tagging Amazon RDS Resources.

    Enable Encryption

    Choose Yes to enable encryption at rest for this DB instance.

    For more information, see Encrypting Amazon RDS Resources.

    Backup Retention Period

    Set the number of days you want automatic backups of your database to be retained. For testing purposes, you can set this value to 1.

    For more information, see Working With Backups.

    Backup Window

    Unless you have a specific time that you want to have your database backup, use the default of No Preference.

    For more information, see Working With Backups.

    Enable Enhanced Monitoring

    Choose Yes to enable gathering metrics in real time for the operating system that your DB instance runs on.

    For more information, see Enhanced Monitoring.

    Auto Minor Version Upgrade

    Choose Yes to enable your DB instance to receive minor DB engine version upgrades automatically when they become available.

    Maintenance Window

    Choose the 30 minute window in which pending modifications to your DB instance are applied. If the time period doesn't matter, choose No Preference.

    For more information, see Amazon RDS Maintenance Window.

  11. Choose Launch DB Instance.

  12. On the final page of the wizard, choose Close.

  13. On the RDS console, the new DB instance appears in the list of DB instances. The DB instance will have a status of creating until the DB instance is created and ready for use. When the state changes to available, you can connect to the DB instance. Depending on the DB instance class and store allocated, it could take several minutes for the new instance to be available.

    My DB instances list

Connecting to a SQL Server DB Instance Using SQL Server Management Studio

This example uses the Microsoft SQL Server Management Studio utility. This utility is part of the Microsoft SQL Server software distribution. To download a stand-alone version of this utility, go to the Microsoft Download Center - Microsoft SQL Server Management Studio Express.

To connect to a DB Instance using Microsoft SQL Server Management Studio

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

    1. Open the RDS console, then choose Instances in the left column to display a list of your DB instances.

    2. Choose the row for your SQL Server DB instance to display the summary information for the instance.

    3. The Endpoint field has two parts separated by a colon (:). The part before the colon is the DNS name for the instance, the part following the colon is the port.

      Locate DB Instance endpoint and port
  2. Run Microsoft SQL Server Management Studio.

  3. The Connect to Server dialog box appears.

    Connect to Server dialog
  4. In the Server type: drop-down list box, choose Database Engine.

  5. In the Server name: field, enter or paste the DNS name of the DB Instance running the Microsoft SQL Server database engine, followed by a comma and then the port number of the DB Instance. For example, the Server name could be: sqlsv-instance1.cg034hpkmmjt.us-east-1.rds.amazonaws.com,1433.

  6. From the Authentication drop-down list box, choose SQL Server Authentication.

  7. Enter the master user name for the DB Instance in the Login: box.

  8. Enter the password for the master user in the Password: box.

  9. Choose the Connect button.

    After a few moments, Microsoft SQL Server Management Studio should be connected to your DB Instance.

  10. Choose the New Query button at the top left of the SQL Server Management Studio window.

    A new SQL Query window opens.

    SQL Query Window
  11. Type the following SQL query:

    select @@VERSION
  12. Choose the ! Execute button on the SQL Enterprise Manager toolbar to run the query.

    You should see a version string returned from your Microsoft SQL Server DB Instance displayed in the output window.

    SQL Query results

Troubleshooting a Connection to a DB Instance Running SQL Server

There are several common causes for problems when trying to connect to a DB instance using SQL Server Management Studio:

  • The access rules enforced by your local firewall and the IP addresses you authorized to access your DB instance in the instance's security group are not in sync. If you used Microsoft SQL Server Management Studio and you followed the settings specified in the steps above and you are unable to connect, the problem is most likely the egress or ingress rules on your firewall. For more information about security groups, see Amazon RDS Security Groups.

  • If you cannot send out or receive communications over the port you specified when you created the DB instance, you will not be able to connect to the DB instance. Check with your network administrator to determine if the port you specified for your DB instance is allowed to be used for inbound and outbound communication.

  • For newly created DB instances, you must wait for the DB instance status to be "Available" before you can connect to the instance. Depending on the size of your DB instance, it can take up to 20 minutes before the instance is available.

Here are a few things to check if you know that you can send and receive communications through your firewall for the port you specified when you created the DB instance.

  • Could not open a connection to SQL Server - Microsoft SQL Server, Error: 53 - You must include the port number when you specify the Server Name when using Microsoft SQL Server Management Studio. For example, the server name for a DB instance (including the port number) could be: sqlsvr-pdz.c6c8mdfntzgv0.region.rds.amazonaws.com,1433.

  • No connection could be made because the target machine actively refused it - Microsoft SQL Server, Error: 10061 - You were able to reach the DB instance but the connection was refused. This is often caused by the user name or password being incorrect.

Deleting a DB Instance

Once you have connected to the sample DB instance that you created, you should delete the DB instance so you are no longer charged for it.

To delete a DB instance with no final DB snapshot

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the Instances list, choose the DB instance you wish to delete.

  3. Choose Instance Actions, and then choose Delete from the dropdown menu.

  4. Choose No in the Create final Snapshot? drop-down list box.

  5. Choose Yes, Delete.