On-Premises SQL Database Migration To Microsoft Azure
Table of Content |
Introduction |
SSMS Supportability |
Prerequisites |
Summary |
Deleting DB |
Creating DB |
Backup Database |
Deploy Database to Windows Azure SQL Database |
Introduction:
Azure SQL Database is a relational database, provided as a managed service in Azure. It’s based on the latest stable version of the Microsoft SQL Server DB engine. You can use advanced query processing features, such as high-performance in-memory technologies and intelligent query processing.
SSMS Supportability
SSMS :
2012 Supported
2014 Not Supported
2016 Supported
SQL Migration from On-Premises to MS Azure
Prerequisites:
On-Premises SQL Server or instance is upgraded and up to date.
In MS Azure create SQL DB and SQL Instance (Server or VM)
Summary :
In place upgrade
Side by Side Migration
1: In place upgrade
In place upgrade have done reason being chances more of corruption and data lose.
2: Side by Side Migration
Have done Side by Side reason we will not disturb the environment
Note : This requires down time as users request to application or Logs should not truncate. So that DB is not corrupted or chances of Migration failure.
Let’s start with Side by Side Migration.
Connect to SQL Server.
Here we have created SQL DB “Sample”. First we will delete and then we will proceed creating New Database and move the same to MS Azure
Deleting DB
Creating DB
Creating Emp table and inserting values will be done using Query
Open SSMS and click New Query as shown below:
Command :
Use MASTER
GO
DROP DATABASE [Sample]
Click F5 to execute the command if you want to test before executing click Ctrl+F5

Good DB got deleted let’s create DB
DB Name : TEST_DB
Command:
CREATE DATABASE TEST_DB
Click F5 to execute the command

DB got created, we need to declare or provide the variable and creating EMP Table using below commands
USE TEST_DB
GO
CREATE TABLE EMPLOYEE
(EMPID INT PRIMARY KEY,
EMPNAME VARCHAR (50),
DOJ DATETIME,
SALARY MONEY,
PH BIGINT,
[ADDRESS] VARCHAR(150)
)
For Verification click Ctrl+F5, if no errors select and click F5 as shown below:

Table got created let’s insert the values
Command :
INSERT INTO EMPLOYEE
(10, ‘PRAVEEN’ 01/25/2010′, 100000, 1231243423, ‘HYDERNAGAR’)
GO
Insert the values based on your requirement, post inserting you can execute using F5

EMPLOYEE details been inserted
Let’s Proceed with Migrating to MS AZURE SQL DB
Before proceeding with Migration good to take Backup of the DB on Safe note
Right Click on DB -> Click Tasks -> Click Back up…

Destination by default it will show C:\ drive this can be changed based your requirement.
Here I have changed the Path to E:\ drive under “Backup” folder

After completion of Back up
Right click on DB click on “Deploy Database to Windows Azure SQL Database”

Deploy Database window is opened
Click Next

Now here we need to provide the connection details
Server name (Name as in SQL Server Azure), Authentication (Windows or SQL), Login & Password
Click Connect

Summary will be provided
Make sure everything is provided like Source DB and Target DB
And Click Finish

Note: SSMS 2014 is not supported make sure you either do Migration Using SSMS 2012 or 2016)

Click Close
Now go back to SQL Server in MS Azure and check the DB will be deployed and yes it’s online.

Now confirm the Database under SQL Instance under the overview. Also check the Tables and employee details has been moved.
Post confirmation route your Application and User connections to the DB in Azure.
After confirmation that your flow is good we can have the cut over plan and proceed with Decommissioning the old server and DB in On-Premises SQL.
Thanks,
Praveen Kumar
MCSE – Cloud Platform and Infrastructure