Home » Cloud

Category Archives: Cloud

Translate:

Archives

SQL Database Migration On-Premises to Microsoft Azure

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

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

 

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 now declaring or providing the variable and creating EMP Table

 

 

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, ‘RAMESH’ 01/25/2010′, 100000, 1231243423, ‘MIYAPUR’)

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.

Upcoming article SQL Database Instance Alert Creation in Microsoft Azure.

 

Thanks,

Praveen Kumar

MCSE – Cloud Platform and Infrastructure

Translate »