SQL Migration from On-Premises to MS Azure
On-Premises SQL Server or instance is upgraded and up to date.
In MS Azure create SQL DB and SQL Instance (Server or VM)
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
I 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
Creating Emp table and inserting values will be done using Query
Open SSMS and click New Query as shown below:
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
CREATE DATABASE TEST_DB
Click F5 to execute the command
DB got created now declaring or providing the variable and creating EMP Table
CREATE TABLE EMPLOYEE
(EMPID INT PRIMARY KEY,
EMPNAME VARCHAR (50),
For Verification click Ctrl+F5, if no errors select and click F5 as shown below:
Table got created let’s insert the values
INSERT INTO EMPLOYEE
(10, ‘RAMESH’ 01/25/2010′, 100000, 1231243423, ‘MIYAPUR’)
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
Now here we need to provide the connection details
Server name (Name as in SQL Server Azure), Authentication (Windows or SQL), Login & Password
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)
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.
Upcoming article SQL Database Instance Alert Creation in Microsoft Azure.
MCSE – Cloud Platform and Infrastructure