Skip to main content

How to Create SQL Server Database in AWS RDS



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,

Create-Database.png

For Engine type, Select Microsoft SQL Server

 Microsoft-SQL-Server.png

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.

SQL-server.png 


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.

 Credentials-settings.png

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

 DB-instance-size-(1).png


You may also Like: Tracking S3 Bucket Changes using Lambda Function

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

 Security-and-Data-threat-issues.png

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.

 subnet-and-or-Security-group.png

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.

 SQL-Database.png

 

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.

Connecting-to-SQL-Server-DB.png

Select the file to be downloaded and then click Next

 SQL-client.png

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

 Database-Connection.png

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.

 SQL-Server-Authentication.png


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.

SQL-DB-server.png

 
Click Finish.

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

 MS-SQL-Server.png

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


Get the best AWS cloud platform services from Zenesys.                                                              

Source: https://www.zenesys.com/blog/how-to-create-sql-server-database-in-aws-rds               

Location: 140 Starlight Trail, Wimberley, TX 78676, USA

Comments

Post a Comment

Popular posts from this blog

Creating S3 Buckets using Terraform

In this blog post, we will learn to create S3 Buckets using Terraform - Infrastructure as Code. Table of Contents ● What is Terraform? ● What is S3? ● Installation of Terraform ● Installation of AWS CLI ● Configuring AWS CLI ● Create Working directory for Terraform ● Understanding Terraform files ● Creating Single S3 Bucket ● Creating multiple S3 Buckets PreRequisites ● Installation of Terraform ● Installation of AWS CLI ● IAM user with Programmatic access  What is Terraform? ● Terraform is a tool to create , delete and modify the resources. ● Supported clouds such as AWS, Azure and GCP , IBM cloud etc. What is S3? S3 stands for Simple Storage Service. Amazon S3 has a simple web services interface that you can use to store and retrieve any amount of data, at any time, from anywhere on the web.  Installing Terraform 1. Using binary package (.zip) 2. Compiling from source Install Terraform From the link provided above, Download the suitable packag...
Post a Comment
Read more

10 Best Python Libraries For Machine Learning

With the increase in the markets for smart products, auto-pilot cars and other smart products, the ML industry is on a rise. Machine Learning is also one of the most prominent tools of cost-cutting in almost every sector of industry nowadays. ML libraries are available in many programming languages, but python being the most user-friendly and easy to manage language, and having a large developer community, is best suited for machine learning purposes and that's why many ML libraries are being written in Python. Also, the python works seamlessly with C and C++ and so, the already written libraries in C/C++ can be easily extended to Python. In this tutorial, we will be discussing the most useful and best machine-learning libraries in Python programming language. 1. TensorFlow : Website: https://www.tensorflow.org/ GitHub Repository: https://github.com/tensorflow/tensorflow Developed By: Google Brain Team Primary Purpose: Deep Neural Networks TensorFlow is a library developed by the G...
Post a Comment
Read more

Determining ROI for Your RPA Project

  Nowadays, every business needs to reach seamless Business Process Automation and offer the best possible customer experience. In 2021, the old-school way of going by your business won’t cut it. The introduction of RPA services (Robotic Process Automation) is crucial in simplifying your business processes. Its benefits are difficult to overstate.  Since it is non-disruptive, the implementation of RPA is not as complex as it appears to be. However, calculating the Return On Investment for RPA automation isn’t straightforward. You have to consider things that you won’t even realize are countable. Calculating the costs and gains for RPA services can put you in a pickle at times. But fret not.  This article will shed light on everything you need to know and consider before calculating the ROI for your RPA project.  How to Calculate ROI for your RPA Project?  1. Determine Your Automation Goals Before you start automating business processes with RPA services, you nee...
Post a Comment
Read more