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
We can either use the existing VPC and the subnet group to create the aurora db cluster. Else we can,
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.
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/
You should see the following page , Which means we have ssuccessfully connected with the SQL Server DB instance.
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.