How to Create SQL Server Database in AWS RDS

October 1, 2021 Aman Balooni Cloud

In this blog post, We will learn to set up a Microsoft SQL Server database in RDS using the AWS console.

What is SQL Server?

The SQL Server is a RDBMS developed by Microsoft. Using Amazon RDS, It's easy to set up , manage and scale the SQL Database server in the AWS cloud.

There are multiple editions available such as Express edition, Enterprise edition, Web and Standard editions.

The SQL server database that we launch using Amazon RDS comes with the License.

Launching SQL Server Database

Prerequisites

The SQL Database can be created in an existing default VPC or we can set up a custom VPC and Subnets and then we can create a subnet group from the custom VPC and the subnets.

To Create a subnet group from the custom or new VPC.

Go to the RDS console.

On the left side, click Subnet groups

MicrosoftTeams-image-(8).png

Press Create DB Subnet group

Enter a name for the subnet group and then we have to choose a VPC

For Add subnets , We have to choose the subnets of different availability zones and then click Create.

Next step is to create the SQL Database server.

In the RDS Console , From the navigation panel, Click Databases

MicrosoftTeams-image-(9).png

Click Create database

On the Database creation page , We have to select a method of database creation.

Let's select Standard Create for this tutorial,

For Engine type, Select Microsoft SQL Server

Under Edition, Choose SQL Server Express Edition

SQL-Server-Express-(1).png

Under Version, Select the DB engine version that your application supports and as per your requirement.
The license will be included in the SQL server.

Under Settings , Enter a name for the SQL DB Instance

SQL-DB-Instance-(1).png

For Credentials settings , Enter a master username and the password.

For DB instance size, We can choose the size of the DB instance as per the requirement.

DB-instance-size-(1).png

Storage Autoscaling can be enabled, This is to ensure that the SQL DB server never goes out of disk space.

For Connectivity, Select the preferred VPC

In Additional connectivity configuration, choose the Subnet group, If you have chosen default VPC, then select the default subnet group.

For Public accessibility, Choose No. It is always recommended not to host the databases server in Public environment

As it may result in Security and Data threat issues.

For the VPC security group, click create a new security group,

And then open the port 1433 using which we can access the SQL DB server

Make sure port 1433 is allowed only to specific IP address, subnet and or Security group.

For Microsoft SQL Server Windows Authentication , We are not going to use Windows authentication , Hence leave it to default.

Under Additional configuration,

If it is required , We can configure settings such as Backup strategy, Encryption, Logging, Monitoring.

And Click Create Database,

The SQL Database server is created successfully.

Connecting to SQL Server DB

Connection to the SQL DB server can be established using the Standard SQL client tool on Windows OS and using the DBeaver on Linux OS.

Select the file to be downloaded and then click Next

The SQL client is Downloaded.

Lets try connecting to the SQL DB server. In the RDS console, Select the Database name

Under Connectivity & Security, Copy the Database Endpoint. As I am using Linux OS, In this tutorial, I am going to use DBeaver to make a connection with the SQL DB server.

Choose Next Database Connection, select SQL Server and then click Next

Under the General section, We need to pass the following information.

Host: Enter the DB endpoint here
Port: 1433
Database/Schema: master

SQL Database DServer has default system databases such as master, model , msdb and tempdb

Authentication: Select SQL Server Authentication

And enter the master username and Password.

and Press Test Connection, You must receive the below response.

Any issue Connecting to the DB server , Check the security group and allow the port 1433 for the specific IP address or the subnet.

For testing purpose, You can make the SQL DB server publicly accessible , which is again temporary.

Click Finish.

The following page represents that the connection to the SQL DB server is successfully established.

Conclusion

We have created SQL Database server in RDS using AWS console and also we are able to establish a connection to the SQL server successfully using the DBever client on the Linux OS.

Also, Read: Provisioning RDS Instances using Terraform