How to start migration MS SQL Databases to the Azure Cloud

For one of my clients, I had to prepare a presentation on how to start migrating SQL Server databases to Azure Cloud.

My goal was to start with the basics and answer the questions that both myself and the client should ask at the beginning of the migration process.

I would like to share my thoughts and approach to the process of migrating databases. I decided to make a checklist with questions.

Here are a couple of questions that can be asked

• Where is the data and how do we connect to it? (linked server, cross-database queries, dependencies, …)
• How big is the environment (how much data is there?) that allows choosing the right method
• Restrictions? (maintenance window, migration online, offline, downtime…)
• Where do you want the data to go? (Azure SQL, SQL Server VM, Managed Instance)
• Can we stage the data to minimize downtime?
• Is the connection to the cloud stable?
• What about permissions?
• Does the data need to be encrypted?
• Outage (e.g. disconnection to the cloud) how to act in such a situation? Alternative connection e.g. Express Route + Site-to-Site VPN
• Is the product supported for migration?
• How to get the data from on-prem to the cloud?
• Which technology (method) should be chosen?
• Scalability – what if you need to migrate multiple databases together at the same time. e.g SharePoint, database dependencies?
• Will the permissions be migrated as well or do we need some additional steps?
• Is it possible to migrate online?
• It is possible to stage the data (allows short downtime and outage).

The next step is to select the appropriate migration method.

• DACPAC /BACPAC
• Backup / Restore (MI only possible with Blob Storage)
• Transaction Replication (Azure SQL Database)
• Availability Group (SQL Server on VM + Secondary in Azure)
• Log Shipping
• Azure Database Migration Service (paid)
• Azure Data Box
• Data Migration Assistant (MI only possible with Blob Storage)
• Bulk Load (only data transfer)
• SSIS (too complex?)

After we have answered the questions above, we can begin to plan and determine some things

• Know the steps (Step-by-Step)
• Know the duration
• Check authorization and encryption if necessary
• Effective communication
• Act proactively (recognize potential problems that may occur e.g. Murphy’s Law)
• Understand the infrastructure
• Test the component and connections
• Error analysis and elimination
• Monitoring

This is my approach to the migration process and I am open to criticism and discussion, so feel free to contact me.
Hopefully, this will make it easier for some people to start planning their MS SQL Server database migration to the cloud

Cheers

Andrzej