Create and Connect to a Microsoft SQL Server Database

with Amazon RDS

Introduction

In this tutorial, you will learn how to create a Microsoft SQL Server database Instance (we call this an instance), connect to the database, and delete the DB instance. We will do this using Amazon Relational Database Service (Amazon RDS) and everything done in this tutorial is Free Tier eligible.

 Cost to Complete

 Services Used

Manage Your AWS Resources

Implementation

  • Enter the RDS Console

    When you click here, the AWS management console will open in a new browser window, so you can keep this step-by-step guide open. When this screen loads, find RDS under Database and click to open the Amazon RDS Console.

    AWS Management Console

  • Create a Microsoft SQL Server DB Instance

    In this step, we will use Amazon RDS to create a Microsoft SQL Server DB Instance with db.t2.micro DB instance class, 20 GB of storage, and automated backups enabled with a retention period of one day.  As a reminder, all of this is free tier eligible.

    a. In the top right corner of the Amazon RDS console, select the Region in which you want to create the DB instance.

    Note: AWS Cloud resources are housed in highly available data center facilities in different areas of the world. You have the ability to choose which Region to host your Amazon RDS activity in.

    In the top right corner of the Amazon RDS console, select the Region in which you want to create the DB instance.

    b.   In the Create database section, choose Create database.

    In the Create database section, choose Create database.

    c.  You now have options to select your engine. For this tutorial, click the Microsoft SQL Server icon. Select SQL Server Express Edition, leave the default value of engine version and license, and click the Free Tier template.

    Select SQL Server engine

    d. You will now configure your DB instance. Enter the configuration settings listed below:

    Settings:

    • DB instance identifier: Type a name for the DB instance that is unique for your account in the Region you selected. For this tutorial, enter myrdstest.
    • Master username: Type a username that you will use to log in to your DB instance. We will use masterUsername in this example.
    • Master password: Type a password that contains from 8 to 41 printable ASCII characters (excluding /,", and @) for your master user password.
    • Confirm password: Retype your password.

    Instance specifications:

    • DB instance class : Select the default, d b.t2.micro --- 1 vCPU, 1 GIB RAM . This equates to 1 GB memory and 1 vCPU. To see a list of supported instance classes, see Amazon RDS Pricing.
    • Storage type: Select General Purpose (SSD). For more information about storage, see Storage for Amazon RDS.
    • Allocated storage: Select the default of 20 to allocate 20 GB of storage for your database. You can scale up to a maximum of 64 TB with Amazon RDS for SQL Server.
    • Enable storage autoscaling: If your workload is cyclical or unpredictable, you would enable storage autoscaling to enable RDS to automatically scale up your storage when needed. This option does not apply to this tutorial.

    Settings View

    e. You are now on the Connectivity section, where you can provide additional information that RDS needs to launch the Microsoft SQL Server DB instance. Expand the dropdown below to see the example settings for your DB instance.

    Network & Security

    • Virtual Private Cloud (VPC): Select Default VPC. For more information about VPC, see Amazon RDS and Amazon Virtual Private Cloud (VPC).


    Additional connectivity configurations

    • Subnet group: Choose the default subnet group. For more information about subnet groups, see Working with DB Subnet Groups.
    • Public accessibility: Choose Yes. This will allocate an IP address for your database instance so you can directly connect to the database from your own device.
    • Availability zone: Choose No preference. See Regions and Availability Zones for more details.
    • VPC security groups: Select Create new VPC security group. This will create a security group that will allow connection from the IP address of the device that you are currently using, to the database created.
    • Port: Leave the default value of 1433.


    Microsoft SQL Server Windows Authentication

    • Directory: Leave this option disabled.

    Settings View

    In the Additional Configurations section:

    Database options

    • Database name: Type a database name that is 1 to 64 alpha-numeric characters. If you do not provide a name, Amazon RDS will not automatically create a database on the DB instance you are creating.
    • DB parameter group : Leave the default value. For more information, see Working with DB Parameter Groups.
    • Option group: Leave the default value. Amazon RDS uses option groups to enable and configure additional features. For more information, see Working with Option Groups.

    Encryption
     This option is not available in the Free Tier. For more information, see Encrypting Amazon RDS Resources.

    Backup

    • Backup retention period: You can choose the number of days to retain the backup you take. For this tutorial, set this value to 1 day.
    • Backup window: Use the default of No preference.

    Monitoring

    • Enhanced monitoring: Use the default of Enable enhanced monitoring. Enabling Enhanced Monitoring will give you metrics in real time for the operating system (OS) that your DB instance runs on. For more information, see Viewing DB Instance Metrics.

    Settings View

    Maintenance

    • Auto minor version upgrade: Select Enable auto minor version upgrade to receive automatic updates when they become available.
    • Maintenance Window: Select No preference.

    Deletion protection
     Clear Enable deletion protection for this tutorial. When this option is enabled, you can't delete the database.

    Click Create database .

    Settings View

    f. Your DB Instance is now being created.  Click View Your DB Instances.

    Note: Depending on the DB instance class and storage allocated, it could take several minutes for the new DB instance to become available.

    The new DB instance appears in the list of DB instances on the RDS console. 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 a database on the DB instance.

    Feel free to move on to the next step as you wait for the DB instance to become available.

    Your DB Instance is now being created. Click View Your DB Instances

  • Download a SQL Client

    Once the database instance creation is complete and the status changes to available, you can connect to a database on the DB instance using any standard SQL client. In this step, we will download Microsoft SQL Server Management Studio Express, a popular client for SQL Server.

    a. Go to the Microsoft Download Center - Microsoft SQL Server Management Studio Express page and click Continue.

    Note: Remember to download the SQL client to the same device from which you created the RDS DB Instance. The security group your database is placed in is configured to allow connection only from the device from which you created the DB instance.

    Download Microsoft SQL Server Management Studio Express

    b. You will be prompted to register with Microsoft -- this step is not required for download.  You can skip registration by selecting the radio button next to 'No, I do not want to register. Take me to the download' and click Next on the popup modal.

    Then, select the SQL Management Studio you need and click Next to start your download.

    Select download type

  • Connect to the Microsoft SQL Server Database

    In this step, you will connect to the database you created using SQL Server Management Studio.

    a. Once you have completed your download, install and open the program. A dialog box appears.  Enter the following:

    • Server type: Select Database Engine
    • Hostname: Copy and paste the hostname from the Amazon RDS console as shown in the screenshot to the right. Afterwards, change the colon between the DNS and port number to a comma. For example, your server name should look likesample-instance.cg034hpkmmjt.us-east-1.rds.amazonaws.com,1433
    • Username: Type in the username you created for the Amazon RDS database.  Our example is 'masterUsername.'
    • Password: Enter the password you used while creating the Amazon RDS database.

    Click Connect.

    Dialog box options

    b. You are now connected to the database. In the SQL Server Management Studio, you will see various schema objects available in the database. Now you can start creating tables, insert data, and run queries.

    Various schema objects available in the database

  • Delete the DB Instance

    You can easily delete the Microsoft SQL Server DB Instance from the Amazon RDS console. It is a best practice to delete instances that you are no longer using so that you don't keep getting charged for them.

    a. Go back to your Amazon RDS Console.  Select Databases , choose the instance that you want to delete, and then select Delete from the Actions dropdown menu.

    Select Delete under Actions

    b. You are asked to create a final snapshot and to confirm the deletion. For our example, do not create a final snapshot, acknowledge that you want to delete the instance, and then click Delete.

    Note: Deleting your DB Instance may take a few minutes

    Delete screen

Was this tutorial helpful?

Thank you

Please let us know what you liked.

Close

Sorry to disappoint you

Is something out-of-date, confusing or inaccurate? Please help us improve this tutorial by providing feedback.

Close

Congratulations!

You have created, connected to, and deleted a Microsoft SQL Server Database Instance with Amazon RDS.  Amazon RDS makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you up to focus on your applications and business.