Step-by-step guide: migrating an on-premise database to Azure
Choosing Microsoft’s Azure platform to host your organisation’s databases can help to reduce costs and support a higher-functioning IT infrastructure. Follow our guide to successfully migrate an on-premise database to Azure.
Once you’ve assessed the pros and cons of switching your databases to Azure and the migration has been approved, migrating to the cloud could help you to reduce your organisation’s overheads, strengthen cyber security, and increase productivity. Follow the steps below to ensure that your database migration is as smooth as possible. Our partner, Simpson Associates’ Senior BI Consultant, David Mills takes us through the process.
Step 1: explore database compatibility
Before you begin a database migration to Azure, you will need to ensure your current on-premise database is compatible with the Azure database and plan how you are going to navigate any issues that do arise.
Compatibility issues occur when features that exist in an on-prem database – such as linked servers, trace flags or filestreams – are not available in the cloud. You can check for compatibility issues using Microsoft’s Data Migration Assistant.
If you do come across any issue with compatibility that can’t be resolved, then you may need to consider migrating to an SQL Instance on an Azure Virtual Machine or using an Azure Managed Instance.
Step 2: select the right Azure service model
The overall pricing, service tier, storage and performance of the Azure relies upon the service model you choose, so choosing the correct one in the first instance will save you time further down the line.
The database will need deploying as either an individual Azure SQL database, within an Elastic Pool, and Azure SQL Managed Instance, or within an Azure Virtual Machine.
Step 3: choose your Azure service tier
Selecting the correct service tier is important to achieve optimal performance and manage costs of the Azure database. Opting for a service level that is too high can result in wasted budget, while a service level that is too high will cause reduced function.
Although Azure allows the database to be scaled up or down to suit your changing requirements, choosing the correct service tier ahead of your migration is easy with Microsoft’s Azure SSQL Database DTU Calculator – an advisory tool based on CPU, IOPS and LOG utilisation of your existing on-prem database.
Step 4: identify your required disaster recovery level
Azure offer various levels of disaster recovery protection, including high availability and geo-replication across global data centres. What level of disaster recovery you select should be based on your organisation’s recovery point objective (RPO) and recovery time objective (RTO).
RPO is the acceptable amount of data loss measured in time, while RTO is the maximum acceptable amount of time that your database can be down. How much of each of these that you need will determine the level of disaster recovery that you require.
Step 5: devise a migration strategy
Before you migrate your on-prem database to Azure, you need to plan your migration strategy. The types of things you should consider include, choosing and online or offline strategy by establishing if database downtime required for the move is a possibility, and which migration tool you’re going to use.
There are several migration tools available to help you successfully migrate from on-prem to Azure, including:
- SQL Server Management Studio (SSMS)
- Database Migration Assistant (DMA)
- SQL Server Data Tools (SSDT)
- SQL Package.exe
- SQL Azure Migration Wizard
- Azure Database Migration Services
- Transactional Replication
For offline migrations that allow downtime, DMA and SSMS are highly rated tools, whereas Transaction Replication ‘pushes’ data from the on-prem database to Azure without any downtime.
Step 6: migrate a test database
It’s important to have a trial run of migrating the live database before officially migrating the live system to ensure that everything works as expected and to identify any potential issues. During the migration of your test copy, you should check for:
- Migration errors – if any of these are reported, you will need to analyse and fix them ahead of the official migration.
- How long it takes to migrate schema and the database – if the downtime is too long, you may want to consider an online migration.
- Any external application or user connection issues (VPN, firewalls etc.)
- Data readability – can data be read and written to the Azure database as expected via the application and direct SQL connections or roles.
- Performance level – do a test run of some of the actions that you may take on the database i.e. running a report and record the time it takes to assess if the performance level is acceptable.
Step 7: migrate your database
Once you’ve worked your way through each of the steps above, you are ready to successfully migrate your database to Azure.