In this blog, we are going to cover how to Migrate SQL Server To Azure SQL Database. Migration is inevitable, necessary, and highly desirable to move from traditional system to modern system so let’s get deep dive into the migration techniques using Azure Migrate-Cloud.
Topics, we’ll cover:
- Azure Database Migration
- Types of Migration In Azure
- Migration Assistant In Azure
- Hands-on To Perform Offline Migration From SQL Server To Azure
What Is Azure Database Migration Service
- The Azure Migration service can be used to perform database migration from a source onto a target.
- This source can vary from using SQL Server on an Azure VM, or SQL Server in an on-premises data center, or using Oracle database as the source.
- The target can be an Azure SQL VM, Azure SQL Database, Azure Cosmos DB.
- You can perform an offline migration here the application downtime will begin at the same time the migration starts and if you want to limit the downtime, you can perform an online migration.
Migration Types
1) Online Migration
This migration type is used when you have to limit the downtime to the time required to move to the new environment when the migration completes.
The following table shows the Azure data migration service supports online migration.
Target | Source | Support |
Azure SQL DB | SQL Server | No |
RDS SQL | No | |
Oracle | No | |
Azure SQL DB MI | SQL Server | Yes |
RDS SQL | No | |
Oracle | No | |
Azure SQL VM | SQL Server | No |
Oracle | No | |
Azure Cosmos DB | Mongo DB | Yes |
2) Offline Migration
This migration type is used when your application downtime begins at the same time that the migration starts. It is recommended when your downtime is acceptable.
The following table shows the Azure data migration service supports offline migration.
Target | Source | Support |
Azure SQL DB | SQL Server | Yes |
RDS SQL | No | |
Oracle | No | |
Azure SQL DB MI | SQL Server | Yes |
RDS SQL | No | |
Oracle | No | |
Azure SQL VM | SQL Server | Yes |
Oracle | No | |
Azure Cosmos DB | Mongo DB | Yes |
Azure Migration Assistant
- The data migration assistant can be used to check if an existing database can be migrated to a new version of SQL Server or onto the Azure SQL Database.
- This tool can be used to detect any compatibility issues that could block the migration process.
- It can also discover new features in the target platform that the database benefits from after the upgrade.
- The feature recommendations are organized into categories of performance, storage, and security.
Hands-on To Perform Migrate SQL Server To Azure SQL Database
In Azure to migrate the very simple table from the SQL server instance on this azure virtual machine onto our existing Azure SQL database so go forward and choose a database for this particular example in which you want to migrate.
1.) Go to the search bar and search for the Azure database migration service then hit on create button.
2.) Then in the next wizard it will ask for the subscription and resource group in the product details and in the instance details section fill the migration service name, location, set service mode to azure.
3.) In the instance details section it will ask for the pricing tier it will offer two-tier standard and premium select as per your need (Note: If you want minimal downtime then go for the premium tier.)
4.) Then click Next in the networking section to make sure this migration service will create a virtual network for us this virtual network is used to spin up the resources that are going to be used in the migration service itself and move next and hit on create button.
5.) And let’s come back when you have a migration service in place and click on add new migration project.
6.) Give the project name, source server type from where your data is coming, select the target server type as Azure SQL database and choose a type of activity so it is an offline data migration activity then hit create and run the activity button.
So this will create the activity in your database migration project.
7.) Then in the activity window in the select source tab give the instance name where your source SQL Server is residing i.e; in VM copy the IP address and paste it into it. Here select the authentication type and give the user credentials and also select the trust server certificate and click next.
8.) Then in the select target tab input, the target server name is Azure SQL Database, Authentication type as SQL Authentication, and give the user credentials.
9.) Then in the map to target database tab select your destination database.
10.) In the configure migration settings choose your table and go onto summary and give the activity name and you can hit the start the migration.
11.) And last then go to the management studio and go to the Azure SQL database and run the query now you see that all of our data is now in place in Azure SQL Database.
Frequently Asked Questions
Q.1) What is Azure Migrate?
Azure Migrate provides a centrally located hub to discover, assess, and migration of your on-premises apps and workloads to the Azure Cloud. This hub provides the third-party ISV offerings and Azure Migrate tool for assessment.
Q.2) What can I do with Azure Migrate?
It can supports VMware VMs, Hyper-V VMs, Physical Servers, and other virtualized VMs with the help of azure migrate we can discover, assess and migrate these to the cloud.
Q.3) When transferring data to azure migrate is it encrypted before transmission?
Azure Storage provides automatic encryption to its storage (encryption-at-rest).
Q.4) Is there any rollback option available after migration?
Once your on-premises machine was shut down and your final migration completes on the Azure SQL database that you can’t perform the rollback to the on-prem environment.
Q.5) How do I know if my Virtual Machine was successfully migrated to SQL Server To Azure SQL Database?
You can view and manage your Virtual Machine from the Virtual Machine page.
Related/References
- Exam DP-300: Microsoft Azure Database Administrator Associate
- Microsoft Certified Azure Database Administrator Associate | DP 300 | Step By Step Activity Guides (Hands-On Labs)
- Azure SQL Deployment Options | SQL Managed Instance | SQL Database| SQL On VM
- Azure Data Science And Data Engineering Certifications: DP-900 vs DP-100 vs DP-200/DP-201
Next Task For You
We will cover all the exam objectives related to how to perform migrations, Hands-On Labs, and practice tests in our Azure Database Administrator training program. If you want to begin your journey towards becoming a Microsoft Certified: Azure Database Administrator Associate by checking our FREE CLASS.
Leave a Reply