MS SQL Server with AWS RDS - Connect your local Relational Database to Cloud

This tutorial will guide you to connect your local SQL Server database to the AWS Relational Database Service(RDS).
It will make it an easier dev environment for your project with the cloud Relational Database.

Prerequisites

1. AWS Account - You can have 12 months free tier which gives you free access for specific services for 12 months.
2. Visual Studio 2019 / Microsoft SQL Server Management Studio (v18.4)

1. Go to RDS in AWS Console.

2. Find & Click Create database

       We are going to create a database instance.

 

3. We create this using Standard Create Method

4. In Create database page, Choose Microsoft SQL Server and SQL Server Express Edition.

It is better to use the Free tier template on the same page under the Templates section. (Choose according to your need)

5. Set up db instance settings giving DB instance identifier, Master username, and Master Password. Remember these credentials.

6. Choose db instance size which is only one available for Free tier (db.t2.micro).

7. Go to Additional Connectivity Configurations under the Connectivity section.

Under Additional Connectivity Configurations,

Select Yes under the Publicly Accessible Section. This will allow your application to access the RDS instance publicly.  

Then choose Create New under VPC Security Group and give a name for your security group.

8. Leave all the other settings as it is & Click Create Database. We can see creating it in our Databases Section in RDS. It will take a few minutes.

We have created our AWS RDS instance (Yeyyyyy....). Now we have to connect our local SQL Server database.

We can use either Visual Studio or Microsoft SQL Server Management Studio. I use VS 2019 & MS SQL Server Management Studio version 18.4.

9. First, go to the AWS RDS instance we created and get the Endpoint and the Port number.

 

10. Open MS SQL Server Management Studio and go to
Object Explorer -> Connect -> Database Engine

It will open the following window.

11. Enter your credentials. 

Note: 
Server name should be <endponit>,<portnumber> as following image.
Then click Connect.

Now we can see the RSD instance in our Management Studio. 😎☁☁

When we go to the Monitoring section of our instance(awsrsd -> Monitoring) in AWS Console we can see the connection established was recorded.

Optional - Connecting with Visual Studio 2019

10. We can also connect the RDS instance with Visual Studio. (So you won't need Microsoft SQL Server Management Studio)

Go to View -> SQL Server Object Explorer

11. Click the following icon to add a new server.

It will open the following window.

12. Enter your credentials. 

Note: 
Server Name should be <endponit>,<portnumber> 
Authentication should be SQL Server Authentication.
Leave Database Name as <default>.
Then click Connect.

Here you can see the RDS instance has successfully connected. (Yeyyyyy....)

Now you can work with the cloud server like your local database.

Note: 

Your Security Group of AWS RDS is based on your IP. If your IP has changed it will show this server error 258.

Try editing Inbound rules of the Security Group. 

1. Go to Security Groups of the instance.

2. Select your Security Group & Select Edit inbound rules from Actions dropdown in the top right corner.

3. Change Source, Custom to MyIP.  

Then click Save rules.

Then you will see this message and you will be able to connect to the instance.

Go through the connecting process (10) again.

I hope this article will be helpful for the dotNet devs who work with SQL Server. 

Your questions, comments are also welcome.

See you next week!