How To Create SQL Server Database In AWS RDS

In this blog post , We will see how to create Microsoft SQL Server database in AWS RDS.

What Is SQL Server?

SQL Server is a relational database management system developed by Microsoft.

With the help of Amazon RDS , We can easily setup , Scale and operate SQL server in the aws cloud.

We can deploy multiple editions of SQL server such as Express , Enterprise , Web and Standard.

We don’t need to purchase license for SQL separately , As the Amazon RDS for SQL server supports the “License Included” licensing model.

Creating SQL Server Database

Prerequisites

We can either use the existing VPC and the subnet group to create the aurora db cluster. Else we can,

Create a VPC with Public and Private subnets

Create Custom subnet group from new VPC.

To create subnet groups , Login to RDS console.

In the navigation pane , Choose Subnet groups

Click Create DB Subnet group

provide a name for the subnet group and then choose the VPC that you have created.

For Add subnets , We must choose atleast 1 subnet from different availability zones and then click Create

Lets start creating the SQL server Database.

From the RDS Console , In the navigation pane , Choose Databases

Click Create database

In the Create database page , We have to choose a database creation method.

Lets Choose Standard Create

For Engine options , Choose Microsoft SQL Server

For Edition , Choose SQL Server Express Edition

For Version , Choose the engine version as per the requirement.

License will be included.

For Templates , For testing purpose , Choose Free tier

Under Settings , Type a name for the DB Instance

Under Credentials settings , Set a master username and the password for the DB instance

For DB instance size , As we have chosen only free tier eligible instance , We have DB instance size of db.t2.micro with 1 vCPU and 1 GB RAM.

For Storage , Choose the type of storage and the amount of storage to be allocated with the DB instance.

We can Enable Storage auto scaling , To automatically scale up the storage of the RDS instance when needed.

For Connectivity , Choose the VPC (Default or Custom VPC) where the DB instance should be launched.

Under Additional connectivity configuration , Choose the Subnet group that you have created. If you are using default VPC , Choose the default subnet group.

For Publicly accessible , Choose No , It is always recommended to host the databases private.As we don’t want to expose the databases to the public network.

For VPC security group , Create a new security group , Allowing port 1433 only to the certain Instances or subnets.

For Microsoft SQL Server Windows Authentication , Leave it to default.

Under Additional configuration ,

If required , We can configure Backup strategy , Encryption , Monitoring and Logging.

And finally click Create database

The SQL Server DB instance is created.

Connecting To SQL Server DB

Once the DB is created.

We can connect to the DB instance using the Standard SQL client tool on Windows or using DBeaver on Linux.

Standard SQL Client can be downloaded from the below link.

https://www.microsoft.com/en-us/download/details.aspx?id=22985

Click Download ,Choose the file you want to download and click Next

The file should be downloaded now.

Lets connect to the SQL server DB instance.

From the RDS console , Click the DB name and Under Connectivity & Security , you can find the DB endpoint.

We already know the details of master username and password of the DB instance and the port 1433.

In this tutorial , I am using DBeaver to connect with SQL server.

Click Next Database Connection , Choose SQL Server and click Next

In the General section , We need to provide the following details.

Host : DBendpoint

Port : 1433

Database/Schema : master [SQL Server comes with default system databases such as master, model , msdb and tempdb]

Authentication : SQL Server Authentication

And provide user name and Password details.

and click Test Connection , You should get the below response.

If you face any issue , Check the security group of the RDS instance , Or temporarily make the RDS instance Publicly Accessible , Just for Testing.

Keeping the DB instance in the Public subnet or Public Accessible is not recommended/

Click Finish.

You should see the following page , Which means we have ssuccessfully connected with the SQL Server DB instance.

Conclusion

We have successfully created SQL Server Database instance using AWS RDS console and we are able to connect with the SQL server.

Hope you find it helpful.

Please do check out my other articles.