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 :
- Microsoft Azure Hub-Spoke model by Enterprise Design 1 of 4
- Microsoft Azure Policy and BluePrints Overview (Extra Blogpost)
- 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?
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 :
- SQL Cluster Solutions, Build versions, Compatibility Levels
- Maptoolkit report
- Azure Service maps for the complete chain.
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
Ready for Assessments and Migrations.
- Here you can choose between the Assessment or the Migration.
- 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 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 😉