Data Loader

How to Migrate On-Premises MS SQL database to Amazon Cloud

Nowadays most of the companies are switching to Cloud Computing because of the many benefits it provides like

 

 

The most popular providers of databases on the Cloud are Amazon Web Services RDS, Microsoft Azure, Google Cloud 


Our tool Data loader can be used to migrate / sync data from your on-premise local databases to Cloud without writing a single piece of code.

 

 

You can migrate On-premises MS SQL, MySQL, Oracle, FoxPro, MS Access or CSV to Amazon RDS MySQL, Amazon Aurora (MySQL compatible), Amazon MS SQL Server, Amazon Oracle, Amazon MariaDB on the cloud.

 

migrate databases to amazon cloud 

 

In this article, we will show you step by step from scratch how to create a new MySQL instance and a database in Amazon AWS RDS and then load the data from local MS SQL database to MySQL database hosted on Amazon Web Server (AWS)

 

Lets' suppose we have a database Northwind in our local premises MS SQL Server and it has some 19 tables and we want to transfer these tables to MySQL on amazon aws rds

 

Before you begin, you need to have an account in Amazon AWS. If you don't have an account then you can create a new Free Trial account. Even for Free Trial account amazon will ask you for your Credit Card for verification. It will charge and reverse a small minimal amount for authentication.

 

Assuming we have an amazon account. The first thing we need to do is create MySQL DB Instance in Amazon cloud. 

 

So let's begin, Fire up your browser and navigate to Amazon AWS and login into your Amazon aws RDS console

aws console

 Scroll down and click on RDS in "Database" section

amazon aws rds

Then you will get the following screen. Click on DB Instances as shown below

amazon aws db instance

Then you get to the following screen. In this click on Create Database button on top right side as shown below

create database amazon aws rds

Then Amazon AWS RDS will prompt you to choose the Database Engine which you want to create. The available choices are

amazon rds available database engines

Since we wanted a MySQL database, click on MySQL option and scroll down and click Next.

Note: You can check the "Only enable options eligible for RDS Free Usage Tier" checkbox if you are using Free Trial. This will help in preventing unnecessary billing for using paid features. The Amazon RDS Free Tier helps users to acquaint themselves about managed database service in the cloud. Besides this, it also helps in testing existing applications or developing new applications.

new mysql database in amazon

After clicking the Next button, you will be prompted to specify several database settings for the new MySQL database which you are about to create.

Since we check the Free Tier option in the previous screen, we will get only those options which are allowed in Free Tier.

aws mysql database details

 

You can accept the default values for most of the parameters. Like MySQL license, MySQL version, DB Instance Class, Allocated Storage Type etc.

You just need to enter the following

mysql db creation settings

Enter these settings according to your choice and do remember and note down the Master Username and Password. You will need to enter this username and password later on to connect to this MySQL instance.  After entering these values, click Next.

When you click Next button, Amazon RDS will show you a "Configure advance settings" page as shown below

amazon aws configure db

In this page, accept all the default values and scroll down and enter the name of New MySQL database which you want to create. Since after creating the instance we need at least one database, so here please specify the name of the new MySQL database. Here we have chosen "db1" you can choose whatever you like and click "Create Database"

mysql db name

After you click "Create Database" button you will get the following screen and it will take some time to create a new database instance and a new database.

new database creation

After sometime, you can click on RDS - > DB Instance in Amazon aws console and you will find the MySQL instance created.

aws mysql instance

Now to connect to this MySQL instance from other software application such as Data Loader we need to know this instance address. To know the address click on the mysql1 instance in the above page, and you will get to this

mysql database connect settings

Here we have circled the important settings. These are Endpoint, Port, Security group.

The Endpoint is the host address for this MySQL instance, You will need to type in this as hostname in third party applications such as Data Loader.

Next is port which is default 3306.

Next important thing is the Security Group. The amazon aws will by default add a security group to allow connection to this MySQL instance from your own IP address. That means only you can connect to this MySQL instance. If you want to allow access to this MySQL instance from other IP addresses then you need to add a new inbound rule to allow other IP's.  To modify the INBOUND security rule click on Security above on right-hand side in the above page and click Inbound Tab and click Edit as shown below

inound security rule

Now according to your choice you can edit the existing rule or add new rule. Here we will edit the existing rule to allow connections from anywhere

inbound rule

Select Anywhere as shown above and click the Save button.

This finishes our installation of MySQL on Amazon Cloud and we are now ready for transferring data to this database.

Since we wanted to transfer tables from our local MS SQL Server database to MySQL database on cloud, we will now start Data Loader tool (If you do not have this tool, then you can download and install it by clicking here Download Data Loader).

Start Data Loader and select MS SQL Server as Source and MySQL as the target as shown below

dataloader to migrate data to cloud

 

Click Next Button, Data Loader will prompt you to connect to your MS SQL Server database. Type in your MSSQL hostname, username, password and click connect button. Once connected successfully, you can select your desired database from download list and once you select the desired database in the dropdown list, it will show the tables in that database on the right-hand side, You can select the tables you want to migrate by clicking on this list. Since we want to transfer all tables in Northwind Database we have selected all the tables,  please see below

table to transer to amazon rds

After selecting the tables which needs to be migrated, click on Next button and Data Loader will now prompt you to connect to the Target MySQL database

Here we need to enter the Endpoint-which we have noted earlier, as hostname and Master username and password which we have specified at the time of creation of MySQL Instance

The Endpoint in our case is : mysql1.c6yqsetfdsre.ap-south-1.rds.amazonaws.com

Port is: 3306

Username: <master username which you have chosen at instance creation. See above>

Password: <master password which you have chosen at instance creation. See above>

After entering the required fields as shown below click on Connect button and select "db1" database from dropdown list and click Next.

connect to mysql cloud

Data Loader will now show you the "Source and Destination Tables" screen. In this screen you can specify loading options and other settings by clicking "Col Mapping" button for each table. Since this is optional we can skip this by clicking Next button

source and target tables list

In the Next screen save the session and you can specify the Interval for scheduling. For the sake of this demo just accept the default and click Next

save session dataloader

Now, in the final screen just press Start button to start the transfer.

start transfer from mssql to mysql on amazon aws rds

That's it. Thanks for reading!