Creating a Microsoft SQL Server DB instance and connecting to it - Amazon Relational Database Service

Creating a Microsoft SQL Server DB instance and connecting to it

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.

Important

Before you can create or connect to a DB instance, make sure to complete the tasks in Setting up for Amazon RDS.

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

You can use Easy create to create a DB instance running Microsoft SQL Server with the AWS Management Console. With Easy create, you specify only the DB engine type, DB instance size, and DB instance identifier. Easy create uses the default settings for the other configuration options. When you use Standard create instead of Easy create, you specify more configuration options when you create a database, including ones for availability, security, backups, and maintenance.

In this example, you use Easy create to create a DB instance running the Microsoft SQL Server database engine with a db.t2.micro DB instance class.

Note

For information about creating DB instances with Standard create, see Creating an Amazon RDS DB instance.

To create a Microsoft SQL Server DB instance with Easy create

  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 in which you want to create the DB instance.

  3. In the navigation pane, choose Databases.

  4. Choose Create database and make sure that Easy create is chosen.

    
                        Easy create option
  5. From Engine type, choose Microsoft SQL Server.

  6. For DB instance size, choose Free tier.

  7. For DB instance identifier, enter sample-instance, or leave the default name.

  8. For Master username, enter a name for the master user, or leave the default name.

  9. To use an automatically generated master password for the DB instance, select the Auto generate a password box.

    To enter your master password, clear the Auto generate a password box, and then enter the same password in Master password and Confirm password.

    The Create database page should look similar to the following image.

    
                        Engine options
  10. (Optional) Expand View default settings for Easy create.

    
                        Easy create default settings

    You can examine the default settings used with Easy create. The Editable after database is created column shows which options you can change after database creation.

    • To change settings with No in that column, use Standard create.

    • To change settings with Yes in that column, either use Standard create, or modify the DB instance after it is created to change the settings.

    The following are important considerations for changing the default settings:

    • In some cases, you might want your DB instance to use a specific virtual private cloud (VPC) based on the Amazon VPC service. Or you might require a specific subnet group or security group. If so, use Standard create to specify these resources. You might have created these resources when you set up for Amazon RDS. For more information, see Provide access to your DB instance in your VPC by creating a security group.

    • If you want to be able to access the DB instance from a client outside of its VPC;, use Standard create to set Public access to Yes.

      If the DB instance should be private, leave Public access set to No.

  11. Choose Create database.

    If you chose to use an automatically generated password, the View credential details button appears on the Databases page.

    To view the master user name and password for the DB instance, choose View credential details.

    
                        Master user credentials after automatic password generation

    To connect to the DB instance as the master user, use the user name and password that appear.

    Important

    You can't view the master user password again. If you don't record it, you might have to change it.

    If you need to change the master user password after the DB instance is available, you can modify the DB instance to do so. For more information about modifying a DB instance, see Modifying an Amazon RDS DB instance.

  12. For Databases, choose the name of the new Microsoft SQL Server DB instance.

    On the RDS console, the details for new DB instance appear. 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.

    
                        Screen capture of the DB instance details

Connecting to your sample SQL Server DB instance

In the following 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 Backing-up, wait until it shows Available. The status updates without requiring you to refresh the page. This process can take up to 20 minutes.

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

To connect to a DB instance using SSMS

  1. Make sure that your DB instance is associated with a security group that provides access to it. For more information, see Provide access to your DB instance in your VPC by creating a security group.

    If you didn't specify the appropriate security group when you created the DB instance, you can modify the DB instance to change its security group. For more information, see Modifying an Amazon RDS DB instance.

  2. 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.123456789012.us-east-2.rds.amazonaws.com. Also, note the port number. The default port for SQL Server is 1433. If yours is different, write it down.

      
    				            Connect to a Microsoft SQL Server DB instance
  3. Start SQL Server Management Studio.

    The Connect to Server dialog box appears.

  4. Provide the following 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.

  5. Choose Connect.

    After a few moments, SSMS connects to your DB instance.

For more information about connecting to a Microsoft SQL Server DB instance, see Connecting to a DB instance running the Microsoft SQL Server database engine. For information on connection issues, see Can't connect to Amazon RDS 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.

      
								Object Explorer displaying the system databases

    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.

  2. 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

After 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. From Actions, choose Delete.

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

    1. Choose Modify.

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

    3. On the Scheduling of modifications card, choose Apply immediately. Then choose Modify DB instance.

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

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

  7. 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.

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

    • Select 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.

    • Enter "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.