Migrate SQL Databases to Azure
Ajith Ramawickrama
.Net Core | C# | Azure | JavaScript | Microservices | D365 | Solutions Architect | Team Lead | Mentor
Many organisations migrating their on-premise workloads to the cloud to enhance scalability, reduce cost, improve security, enable disaster recovery, enable global accessibility, and improve operational efficiency. Cloud migrating is a complex set of tasks due to the process includes of migrating applications, databases, networks, storages, virtual machines, authentication ..etc. Migrating databases to cloud environments is more complex due to size of the databases, potential data loss, application down time, and security concerns. Data loss and prolonged application downtime can result in losing valuable customers to the business. In this article, I will be discussing about migrating on premise SQL databases to Azure, possible migration options, migrating databases without data losses and with minimum application downtime.
Problem Scenario.
I have a sample order database which consist of 3 tables. There is a sample .Net application generating sample data on this database in multiple threads. Size of the database is 50 GB approximately. I want to migrate this database to Azure SQL Database over the internet. Please do not that this is recommended to do using a site to site VPN connection in production environments. However that is out of the scope of this article.
Migration Steps
Migrating on-premise databases to the cloud involves few steps as illustrated in following image.
1) Required tools and Services
2) Assessment & Planning
In this stage, we have to identify the current workload and usage pattern, dependancies, target platform, required tools, and migration strategy.
2.1) Identifying current workload
The size of the database is 50 GB at the time of planning the migration. And each table has approximately number of rows as mentioned below
orders - 9.5 million
order-items - 48 million
addresses - 9.5 million
2.2) How to determine the migration strategy?
There are two migration stratergies that you can opt.
1) Online migration - You can migrate your database to Azure while your database and applications are fully operational. A little downtime will be required during the cutover stage. Suitable for applications where downtime matters.
2) Offline Migration - This is something similar to Lift and Shift method. You have to shutdown you applications and stop writing data to the database during the migration period. Suitable for applications where able to tolerate a significant downtime.
2.3) How to determine the target database type and SKU
There are three target database types you can choose from.
1) Azure Sql database - Supports only for offline migration
2) Azure Sql Managed Instance - Supports for both online and offline migration
3) Sql database on Azure VM - Supports for both online and offline migration
Choosing the target database type depends on few factors. But your migration strategy plays a significant role when choosing the target database due to all the target types does not support all the migration strategy.
Once you choose the correct migration strategy and the target database type the next step is to determine the correct database SKU. Azure Sql Migration extension for Azure Data Studio can be used to analyse your current workload and calculate the target database SKU.
2. Select the source database from the list of databases that you need to migrate to Azure. And then click Next
3. Click on Start data collection. This will analyse your current workload and generate the target database requirements. Do note that your application is fully operational when you collect performance data.
4. Let it run for at least 10 minutes to collect your performance data. This will provide a result with target database SKU recommendations for all the three database types as illustrated in following image.
5. You can click view details under the each recommendations and see the detailed recommendation result. I have chosen online migration as my application not able to tolerate a prolong downtime. Hence I have to choose the target database from either SQL Managed instance or SQL Server on Azure VM. I choose SQL Server on Azure VM due to the limitations of my Dev/Test Azure subscription. However, SQL Managed Instances could be a better choice for your applications due to: it's managed by Azure, scalability options, and disaster recovery options.
3) Execution.
Before starting the migration, ensure the following steps have been completed.
The next step is to execute the migration.
领英推荐
1. Click next on Azure Data studio and select the target platform
2. Click next and select the target Azure Sql VM.
3. The next step to select the Azure Data Migration Service. If you have not created it, you can crate by clicking on Create new button or you can create it on Azure portal.
As mentioned earlier, do note that you need to transfer a full database backup and available differential backups to Azure as a part of the migration. You can opt:
i) Transfer your initial full backups and differential backups to Azure blog storage directory - you can use Azure storage explorer for this, if your database it not too big. My database is 50 GB in size and Azure Storage Explorer would do the job.
ii) Transfer backup from your local network share- You need to configure self-hosted integration runtime if you choose this option.
Migrating database with backups stored in Azure blob storage.
In this option, there is no need of using self-hosted runtime. You can transfer your database backups to Azure blob storage container manually by using following steps
4. The next is to choose the storage account that you have saved your database backups.
10. Click next and then click Start Migration.
You will be able to see the migration status in Data Migration Extension dashboard.
In Azure portal, you will be able to see a new migration has been created and restoring is in progress.
If your database is big, and it's continuously writing data to the database during the migration in progress, take frequent transactional log backups and upload into the same blob container directory. This will help to minimise the cutover window.
4) Cutover
Once all the database backups are restored, the status of the migration will be changed to Ready for cutover status. At this point you have to shutdown your applications and stop writing data to your source database.
The next step is to take the final transaction log backup of your database after you shutdown your applications and transfer that backup to the same blob container directory. Once you uploaded the last transactional backup, the migration status will change to restoring status again in a short while. Once that backup also restored successfully, the migration status will turn back to Ready for cutover status. At this stage you can check the last applied backups by clicking the migration in Azure data migration service.
At this point the status of your target database is set to Restoring. If you ensure all the backups are restored, the next step is complete the cutover. Click on the complete cutover button.
Confirm that you have no more backups to restore and click on the complete cutover button. After a while, the migration status will become success and the status of the target database would become to the ready status.
By the time the migration was completed, the database size had grown to approximately 60GB, and the required cutover time was less than 20 minutes. The cutover time depends on, the SKU of the target database, how frequently you are taking transaction log backups while the migration in progress, and size of the last backup.
Validation
The next step is validate the target database to make sure it's identical with your source database. You can check the row count of each table or any other check sum method to validate the data integrity of the target database. If everything is in order, you can switch to the dew database and bring back your applications online.
Post Migration Monitoring.
After a successful migration, it is necessary to monitor the application and database continuously to make sure it meets all the performance, compliance, and security measures. Monitoring database on Azure will be discussing in a separate article.
Please share if you found this post useful.