Analytics Vidhya
Published in

Analytics Vidhya

How to use RDS service in AWS (SQL SERVER) for Machine learning?

AWS

The database is the backbone of any programming language. If you working with Machine Learning it becomes very complex to manage the database because machine learning requires a lot of computational power and fast, Scalable, secure and durable database. In this post, we will learn how can we use the SQL SERVER database in AWS RDS service which is a fast, Scalable and secure database. Let's get started.

There are several ways through which we can make a SQL SERVER service in AWS.

  • EC2 (We all have to install licenses, SQL SERVER, and other things).
  • AMI (AMI provides a snapshot of SQL SERVER in a bundle that we can use).
  • RDS (RDS is a service managed by AWS for all the databases like MySQL, SQL server etc).

In this blog, we will use the RDS service. Login to your AWS account if you haven’t AWS account just create a free tier account.

Login to aws account

Once you logged in You will get this type of screen search for the RDS service and click on it.

NOTE: Don't forget to select the appropriate Region, in this case, I used N.Virginia,

After click on it, you will be directed to the dashboard there select the create database option and click on it.

Create a database
  • Then you have to choose how you want to create your database standard create or easy create. We will prefer Standard create because In this we will do settings according to our requirements. In Easy create AWS will do those things for us which can be modified later.
  • Select the SQL SERVER.
DATABASE SELECTION
  • Select the edition that you required and version as you can see in this license will be included otherwise we manually have to set up this thing in EC2 virtual machines.
  • From template select the free tier eligibility.
  • Set the Database instance name this could be anything.
  • Set the username and password and note down somewhere.
  • Inside connectivity selects the VPC and subnet as default.
  • Select the publicly accessible so that we can our database outside our VPC.

Select the default security group or you can create your own security group, select the availability zone and database port. I left as default for ease purposes. Leave Microsoft SQL server windows authentication as default.

  • I left the additional configuration you can leave it. There are just a timezone and other information.
  • Click on Create Database button

It should start creating the database now. Great job!

Select the database on the left-hand side you will see your Instance name scroll right to see the status

You will see it is creating and backing up when it will be done you will see here available status. when it is available click on the your database instance name.

When the database will be active you will see your endpoint and port.

When it is available lets try to connect with our database. When all setup is done we will have these things.

  • endpoint
  • Username
  • password

Let's use a visual studio for connection testing. Go to server explorer -> right click on data connection select add connection

  • ADD endpoint -> server name
  • Username -> username
  • password -> password
  • Click on the arrow button on (enter a database name) you will see the database name for your SQL server in RDS.
  • Click on test connection by selecting on a database or create new

If everything is fine you will be prompted with Connection successful message.

I have seen some people are having trouble while connecting with the database. It is because the Inbound rule is not being setup.

To do so follow the steps:

Go to your database instance in RDS: Inside the security, the group select the INBOUND rule. A new page will open.

Here click on edit you will see a new page and set up the following settings:

  • type -> all traffic
  • protocol -> all
  • Source -> anywhere

Save it and then try to connect with the SQL SERVER everything will work fine.

This database is very fast and scalable. We can create as many replicas in multiple regions so it is durable and very good service. This can be used to store a large amount of dataset which can be used with Machine learning which is the most important step of anything.

Reference: https://stackoverflow.com/questions/44682864/cannot-connect-to-rds-sql-server-database-using-management-studio

Hope you enjoyed!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Wakeupcoders

Wakeupcoders

We make your business smarter and broader through the power of the internet. Researcher | Web developer | Internet of things | AI | www.wakeupcoders.com