Azure SQL Database is a fully managed service. With this platform as a service (PaSS) option, organizations can reduce management overhead and quickly deliver highly resilient services.
Azure SQL Database uses the latest stable version of the Microsoft SQL Server database engine (Currently similar to SQL Server 2019).
Azure delivers predictable performance at multiple service levels that provide dynamic scalability with no downtime.
It adds MS SQL monitoring, built-in intelligent optimization, global scalability availability, and advanced security options — all with near-zero administration.
Azure SQL Database currently resides in more than 50 data centers around the world. With more data centers coming online regularly, enabling you to run your database in a data center near you.
With so many on-premises implementations at customer sites, how do you migrate from the traditional on-premises SQL Server implementation to modern Azure SQL Database technologies and benefit from what cloud database services can offer?
We will guide you through the thought process and steps required to migrate your SQL Server database workloads from on-premises to Azure-based cloud services.
Figure 1 – Migration process overview.
Stage 1 – Pre-migration
Discovery
The first stage of the SQL migration roadmap is to initiate and discover.
The inventory constitutes what data is available, where it is located, what platforms it resides on, and the size of the data.
Applications will often utilize several databases or integrate with other applications that have their databases.
We need to know the database dependencies to other databases to group them together according to these relationships logically.
Once made the logical groupings by relationship, we can use them to form batches of databases for migrating up to Azure.
How to choose the right target platform
When looking to choose an appropriate target platform, there are considerations to be made about the usage scenarios, the used features, and the total Cost of Ownership.
Figure 2- Available Azure Database Platforms.
Azure SQL Database single databases and elastic pools
Azure SQL Database is ideally suited for customers developing new SaaS multi-tenant applications.
There are enough differences between Azure SQL Database single databases and elastic pools and on-premises SQL Server.
It is not usually trivial to lift-and-shift on-premises database workloads to Azure SQL Database.
Similarly, third-party applications do not yet support the Azure SQL Database platform. Some SQL features may not be there, however.
The Platform-as-a-Service nature of Azure SQL Database dramatically reduces administration and management costs over the more traditional SQL Server (on Azure IaaS topology). Most of the required work is completed silently in the background for you by Microsoft Operations.
This is evident at the scale where considerable savings in time and effort can be made.
Azure SQL Database Managed Instance
Managed Instances are suitable for customers looking to migrate one or more applications from on-premises, or VM/hosted, self-built, or ISV provided, with as low migration effort as possible.
Would be appropriate for use if the application surface area is instance scoped and requires features not available in Azure SQL Database such as:
- SQL Agent,
- MSDTC,
- DQS,
- MDS,
- Database Mail,
- Filestream,
- Filetable,
- Polybase.
Additional features include support for Linked Servers and support to new Azure cloud services such as Threat Detection.
SQL Server on Azure VMs
Virtual machines can help customers that need to customize the operating system or the database server. Customers that have specific requirements in terms of running third-party apps side-by-side with SQL Server (on the same VM).
Additionally, Azure VMs support local instances of SSRS, SSAS, and SSIS. VMs impose higher compute, storage, and management costs over the Azure SQL Database offerings but grants control across the SQL Server and infrastructure.
Assessment using Database Migration Assistant (DMA)
When the data sources have been identified, the next step is to assess the on-premises SQL Server instance(s) migrating to Azure SQL database(s). You need to understand the gaps between the source and the target.
Data Migration Assistant (DMA) is a freely downloadable tool from Microsoft that is installed and executed locally. It detects compatibility issues that can impact database functionality before attempting to migrate to a new version of SQL Server or on to Azure SQL Database. DMA also provides recommendations on how to remediate those issues.
Armed with this information, you must fix the root cause or implement an alternate methodology for each highlighted issue.
The assessment and fix processes are then repeated until the source database passes all DMA tests, at which point the schema of the source database can be deployed to the target database in the cloud with a high degree of confidence.
Figure 3 – Assessment and fix workflow using SQL Server Database Migration Assistant (DMA)
If your database is not using advanced SQL Server features such as MSDTC, MDS, or QTS, then Azure SQL Database (or Azure SQL Database elastic pools) would be the right choice. Microsoft Operations takes care of most of the infrastructure management, drastically reducing administrative overhead costs.
Unfortunately, not all SQL Server components currently have Azure data services equivalent.
SSRS currently has no direct cloud-based equivalent, but reports could be rewritten based around Microsoft Power BI.
SSAS can be migrated to Azure Analysis Services, which is mostly compatible with recent versions of SQL Server Analysis Services Enterprise Edition.
SSIS packages can be invoked using stored procedures in the Azure Data Factory.
Alternatively, SSIS, SSAS, and SSRS can be deployed using SQL Server on an Azure VM.
Assess SQL workload criteria performance requirements
It is essential to understand if each workload is a high or low user of resources and gauge how many Azure resources will be required post-migration.
If you are looking to transition to SQL Server on Azure IaaS VMs, this might simply amount to matching the number of computing cores currently allocated to those on the target platform.
If moving to Azure SQL Databases, this might require computing the number of Database Transaction Units (DTU) or virtual cores (vCores) needed for each database.
Azure SQL Database provides two different models for measuring and purchasing compute: DTU-based and vCore-based.
Depending on the deployment model of Azure SQL Database, you can select the purchasing model that fits your needs. You can find more details about azure sql server pricing here.
Convert
After assessing the source database instance(s) you are migrating, for heterogeneous migrations, you need to convert the schema to work in the target environment.
Since migrating from SQL Server to Azure SQL Database is a homogeneous migration, the Convert phase is unnecessary.
Stage 2 – Migration (cloud migration)
Offline versus online migrations
When you migrate SQL Server databases to Azure by using the Azure Database Migration Service, you can perform an offline or an online migration.
With an offline migration, application downtime begins when the migration starts.
For an online migration, downtime is limited to the time required to cut over to the new environment when the migration completes.
It’s recommended to test an offline migration to determine whether the downtime is acceptable; if not, perform an online migration.
Often the acceptable downtime or maintenance window stipulated by the application owner will dictate which migration method needs to be used, with a corresponding migration tool to match.
For critical workloads (zero downtime), the use of transactional replication can copy most of the data to Azure in the background. Then keep the target data in-step with the source data until a switchover can occur.
SQL Server Management Studio can be used to establish this copy process. For applications that can afford some downtime, the Azure Database Migration Service should be used to perform the initial assessment and migrate the data consistently and correctly.
Finally, SQL Server Management Studio can be used to export the data and schema of a database in the form of a BACPAC file.
For more extensive databases, the time taken to export and import the BACPAC can be considerable. So this method is best suited for low priority workloads with large maintenance windows available.
Figure 4 – SQL Migration options.
SQL Migration using SQL Server Transactional Replication
Transactional Replication gradually migrates a SQL Server database to the cloud, while leaving production servers online. When SQL Replication is too slow, please check our guide on how to performance tune sql replication.
As new transactions are created at the source, these are migrated to the target database, keeping the source and target in lock-step.
This approach allows for a high level of availability as the only downtime involved will be switching over the application to point to the newly migrated Azure SQL Database.
It’s also suited for hybrid scenarios where a partial or gradual migration is desired.
It can be configured using SQL Server Management Studio (SSMS) or T-SQL statements. With the Azure SQL Database set up as a push subscriber of the source SQL Server publisher.
The required distribution database and replication agents cannot be placed on the SQL Database that is being migrated. The source database must meet the requirements for transactional Replication and be compatible with Azure SQL Database.
All versions of SQL Server from SQL Server 2012 and later are supported.
To use this solution, configure your Azure SQL Database as a subscriber to the SQL Server instance that you wish to migrate.
The transactional replication distributor synchronizes data from the database to be synchronized (the publisher) while new transactions continue to occur.
Once the synchronization is complete, and the data is ready for a switchover, then change the connection string of your applications to point to the Azure SQL Database and publish the application to production.
As transactional Replication finishes, any changes left on your source database and all your applications point to the new Azure SQL Database. Then you can uninstall transactional Replication.
Figure 5 – Migrating SQL Server to Azure using Transactional Replication.
SQL Server Migration using Azure Data Migration Service (DMS)
Azure Data Migration Service is a fully managed migration service created to enable migrations from multiple database sources to Azure Data platforms with minimal downtime.
Azure DMS couples together multiple Microsoft migration engines such as the Database Experimentation Assistant (DEA), the Data Migration Assistant (DMA) and SQL Server Migration Assistant (SSMA) to cover a wide range of scenarios.
Azure DMS is accessed via the Azure Portal, where an instance can be created based on different regions with a variety of vCore options available.
By assigning more vCores to the service, you can provide for faster migrations to meet your intended timeline, but at the expense of added cost.
DMS supports migrating to all service options of Azure SQL Database (Single, Elastic, and Managed Instance) as well as SQL Server on an Azure IaaS Virtual Machine.
From there, it’s possible to create projects that allow you to perform source assessment, schema, data conversion, and validation activities, which help prepare the source for migration.
Migration tasks can also be created easily, such as proof of concept migrations and automation scripts. More info here.
Figure 6 – Creating an Azure Database Migration Service
SQL Migration using data-tier application export/import (BACPAC)
The BACPAC file encapsulates the database schema as well as the data stored in a database application that can be simple imported to an Azure SQL Database.
Workloads using the source database need to be taken offline during the export process to ensure that the exported BACPAC file contains all data in a complete and consistent state.
It means that scheduled outages will be required to export a BACPAC file, which may need a substantial amount of time. Hence, migration using BACPAC files is only suitable for smaller databases.
Other migration techniques would be better suited to minimize downtime.
For more information, check the Microsoft documentation: Import a BACPAC file to a database in Azure SQL Database or Azure SQL Managed Instance.
Data sync and Cut-over
You need to ensure that you captured and applied all changes in the source to the target in near real-time during the data sync phase.
The source you are migrating continues to change, drifting from the target in terms of data and schema after the one-time migration occurs.
After applying all changes in source to the target, you can cut-over from the source to the target environment.
For detail on the specific steps associated with performing a cut-over, see this perform migration cutover guide.
Stage 3 – Post-Migration
After you have completed the SQL Migration, it is very crucial for reconciling any data accuracy and completeness, as well as uncover performance issues with the workload.
You need to go through tests and tasks to ensure that everything is functioning as smoothly and efficiently as possible.
For additional detail about specific steps, see this post-migration validation and optimization guide.
And if you decide that all of this is way too complex, you don’t have any people to dedicate to the SQL Server migration; please reach out to us.
We do multiple massive and smaller SQL Server migrations to cloud Azure and AWS services for public (and smaller) companies every year.
We’d love to help!
2 Responses
Need some detail info for migration enterprise Oracle database to Azure Synapse. Could you forward some case studies and implementation details.
Hi Prakash,
We don’t have any case studies around migrating Oracle to Azure Synapse.
The closest what we have is two case studies about SQL Server migrations. There are here and here.
BTW, we have a SQL Server Migrations service, if you are interested.