Migrate SQL Server Database to Azure Database

Know Step-by-Step process to Migrate an on-premises SQL Server Database to Azure.The software gives two options to export in SQL Database. One as 'With Only Schema' & other is 'With Schema & Data' via SysTools SQL Server to Azure Database Migrator

STEP 1

Go to Start Menu » Programs » SysTools® SQL Server to Azure Database Migrator Tool. The following screen will appear after that:

sql to azure migration


STEP 2

From the software menu, click on “Open” to browse the desired MDF file for migration.

open


STEP 3

Now, select the .mdf file for migration and click on “Open”.

open mdf file


STEP 4

Select the scan mode.
Check the Auto detect option, if you don't know the SQL MDF File version.

auto-detect


STEP 5

You can also select the SQL Server Version of your MDF File manually.

Note: Selecting the wrong version may result in inconsistency.

sql version


STEP 6

Select NDF Options which is the second tab. Select the first radio button if you want to detect NDF file automatically.

Important Note: The NDF file must be associated with the Primary database, i.e., the selected MDF file.

sql version


STEP 7

Choose the second radio button to add NDF files manually, Then Click on Add Files / Add Folder option to add the NDF files for migration.

choose ndf file


STEP 8

After selecting the NDF file. Click Ok

open ndf file


STEP 9

The software will show a summarized detail of the selected MDF files and an automatic scanning of the MDF files will take place. Click Close

scanning process completed



STEP 10

You can also save the scanned MDF file in .str file. If you want to save .str file, then click on "Yes" button given on the prompt screen, otherwise click No.

str format


STEP 11

Select the location for saving .str file and click on Save:

save str file


STEP 12

Preview database records of MDF and NDF files: Software will show the entire data of MDF and NDF files such as Tables, Triggers, Stored procedures, Views, etc.

preview sql data before migration


STEP 13

There will be a preview of all database objects before Migration. Click on Migrate to begin migration process of SQL Database to Azure Database.

migrate mdf data


STEP 14

After clicking on Migrate, you will get 'Migration Options' panel. You need to fill the required fields to migrate your SQL data to Azure Database

Pre-Requisies:

1. Client Internet IP (Your IP) should be added in Firewall settings of Azure SQL Server.

2. Destination database should be present in Azure SQL Server.

Note: Here is how to find authentication details to Connect with Azure SQL Database

migrate-options


STEP 15

Fill the Server Name of Azure SQL Server Database. The Authentication mode is taken by default as SQL Server Authentication.

Azure SQL Authentication


STEP 16

After this, Fill login credentials for SQL Server. Fill Username and Password of Azure SQL Server Database.

Login-credentials


STEP 17

Click on Connect to test whether Azure SQL Server Database is connected successfully. The green tick indicates the Server connection is established successfully.

connect


STEP 18

Click on Drop Down button and the software will show the list of all the existing databases. Select the database on which you want to migrate.

Select Azure Database


STEP 19

In Step 3, you have preferences column in which you can select the database objects to migrate in Azure SQL Database.

Migrate selective database objects.


STEP 20

For Exporting, the software gives you an option to export the migrated data 'With Only Schema' or 'With Schema & Data'.

With Schema/ With Schema or Data


STEP 21

Export With Schema & Data


If you want to save the schema & data of the selected database then select With Schema & Data option. Also if you want to migrate deleted records then click on the checkbox of 'Migrate Deleted Records'

With schema & data

'Migration Completed' pop up will appear stating migration is successfully completed. Click 'Ok' to proceed.


export with schema


Software will display the scanning report of migrating data as 'With Schema & Data'

export with schema confirmation


After clicking on 'Close' software gives you option to save your migrated data in .csv format. If you want to save data in .csv format Click Yes

Save csv report


Choose the desired location to save your migrated data in CSV.

CSV Report generated


You can check the csv report by opening it.

CSV Report generated


The database objects has been migrated successfully. You can view them in below images:

Migrated Tables:

Migrate tables in Azure


Migrated Views:

Migrate views in Azure


Migrated Stored Procedure:

Migrate stored procedure in Azure


STEP 21

Export With Only Schema


If you migrate the database components (Tables/Views/Stored Procedures etc.) with only schema, then the software will only migrate the schema/structure of database components. Choose 'With Only Schema' option and Click on Migrate

Migrate-with-schema

'Migration Completed' pop up will appear stating migration is successfully completed. Click 'Ok' to proceed.

Migration Complete

Software will display the scanning report of migrated data as 'With Only Schema' option.

With Only Schema

After clicking on 'Close' software gives you option to save your migrated data in .csv format. If you want to save data in .csv format Click Yes

Save as csv format

Choose the desired location to save the report of migrated data in CSV.

Save csv in desired location

You can check the csv report by opening it.

CSV Report

The database objects has been migrated successfully.

Database Schema:

Migrated data in Azure