mountainss Cloud and Datacenter Management Blog

Microsoft Hybrid Cloud blogsite about Management

#Microsoft Azure Hub-Spoke model by Enterprise Design 3 of 4 Data Migration #Azure #SQL

Leave a comment


Hyper-V Clusters front tier with SQL Clusters in the Backend

SQL assessment and Data Migration to Azure

This blogpost is about SQL assessment and Data Migration to your Azure design in the Cloud in a secure way.
Before you begin with your Data assessment and getting your workloads together with Microsoft Azure ServiceMaps, I wrote these blogposts about Microsoft Azure HUB – Spoke model by Enterprise Design :

  1. Microsoft Azure Hub-Spoke model by Enterprise Design 1 of 4
  2. Microsoft Azure Policy and BluePrints Overview (Extra Blogpost)
  3. Microsoft Azure Hub-Spoke model by Enterprise Design 2 of 4 “Lift and Shift”

For Microsoft SQL databases there are different Azure Solutions in the Cloud possible, but first you need to know which versions of SQL do you have and how are they running now in your Datacenter?

SQL 2014 Virtual Guest Cluster with Shared VHDX

Here you can see a totally different SQL Cluster configuration, running on Hyper-V instead of physical Server nodes like you can see in the first picture with SQL 2008 R2 Clusters.
When you have a CMDB of your SQL versions running in your Datacenter, you can compare it with these SQL versions on this Great website.

What is also important to know, in which compatibility mode is your SQL Server running? Because you can have a recent SQL version but it’s running in a old compatibility version for the application.

SQL versions with Compatibility matrix

When you have all the insights of your SQL workload on-premises like :

Then you want to know to which Microsoft Azure SQL solution will I migrate my data ?

When you do a “Lift and Shift” first to the Azure-HUB subscription for the complete workload (Virtual Machines + SQL Databases) then you can implement SQL Always-On in Azure.

SQL Always-ON Availability Group

More information about SQL Always-On in Availability Groups in Azure

Or you can migrate to Azure SQL (PaaS) directly.
Later in this blogpost you see the Options with Microsoft Azure Data Migration Assistant (DMA)

Test & Acceptance and Production Azure Spoke

When you have “Lift and Shift” your workload to the Azure-HUB landing zone, then you can do the Optimize of your solutions included SQL to the Test & Acceptance and Production Spoke. For this it’s important where and how your SQL Backend is landing in Microsoft Azure by Design.

Microsoft Azure Data Migration Assistant (DMA)

Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server.

Azure SQL Data Migration Assistant

In the following Step-by-Step Guide we will Migrate a SQL 2016 SP2 Database to a Microsoft Azure SQL Database (PaaS):

first you have to download Microsoft Azure SQL Data Migration Assistant here

Click Next.

Click Next

Click Install

Ready for Assessments and Migrations.

  1. Here you can choose between the Assessment or the Migration.
  2. Here you can Choose for your Azure Target SQL Solution :
    – Azure SQL Database
    – Azure SQL Database Managed Instance
    – SQL Server on Azure Virtual Machines
    – SQL Server

Select the options for the Assessment.

In the following steps we will migrate the SQL 2016 SP2 database to Azure SQL :

Connect to the local SQL Instance and Select your Database

Connect and select your Azure SQL Database.

Select the Schema objects to migrate into Azure SQL

Here you see the Script to Deploy Schema.

Schema migration in progress

Schema Migration is Done, now you Click on Migrate Data

Select the Tables to Migrate and click on Start data Migration

Data Migration in progress

The SQL 2016 SP2 Migration from On-premisses to Azure SQL is Successful Completed 🙂

Connected to Azure SQL Database with my Data.

The SQL Query editor is a browser query tool that provides an efficient and lightweight way to execute SQL queries on your Azure SQL Database or Azure SQL Data Warehouse without leaving the Azure portal. This quickstart demonstrates how to use the Query editor to connect to a SQL database, and then use Transact-SQL statements to query, insert, update, and delete data in the database.

Here is my Data in Azure SQL with Query Editor of the Azure Portal.

This is just one Scenario with Azure SQL Data Migration Assistant. What you have learned is that you must have your Azure SQL Solution in place by Architectural Design before you do the SQL Data Migration.

Here you find more information about Data Migration to Microsoft Azure :

Microsoft Azure Data Migration Guide

 

Here you find Microsoft Azure Migration Center

Conclusion :

Microsoft Azure Architecture design like a Hub-Spoke model for example is important to have in place before you do your Data Migration to the Azure Cloud. You got different SQL Solutions in Microsoft Azure, like SQL Always-On in availability Groups and Microsoft Azure SQL Database with or without Managed Instances. Choose for the best scenario in your own Design. My next blogpost in this Serie will be on Optimize your Azure workloads
How can you make your solution smarter, more intelligent for your business and in Azure costs cheaper with Great benefits! Here we can think out of the box to get the best 😉

Advertisements

Author: James van den Berg

I'm Microsoft Architect and ICT Specialist and Microsoft MVP Cloud and Datacenter Management

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.