TechRid

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