How to Migrate On-Premise SQL Servers to Azure SQL Database
Azure SQL Database is a fully managed service that allow you to focus on rapid app development and accelerate your time to market, rather than allocating precious time and resources to managing virtual machines and infrastructure.
Azure delivers predictable performance at multiple service levels that provides dynamic scalability with no downtime, built-in intelligent optimization, global scalability and availability, and advanced security options — all with near-zero administration.
Azure SQL Database currently resides in 38 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.
Stage 1 – Pre-migration
Discovery
The first stage of the sql migration roadmap is 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 own databases. We need to know the database dependencies to other databases to logically group them together according to these relationships.
Once the logical groupings by relationship have been made, 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.
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 that 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 yet.
The Platform-as-a-Service nature of Azure SQL Database greatly reduces administration and management costs over the more traditional SQL Server on Azure IaaS topology, as most of the required work is completed silently in the background for you by Microsoft Operations.
This is evident at scale where considerable savings in time and effort can be made.
Azure SQL Database Managed Instance
Managed Instances are good 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 supports 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, as well as customers having 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 on-premises SQL Server instance(s) migrating to Azure SQL database(s) to understand the gaps between the source and 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 a good choice as Microsoft Operations takes care of most of the infrastructure management drastically reducing administrative overhead costs.
Unfortunately, not all SQL Server components currently have an 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 largely compatible with recent versions of SQL Server Analysis Services Enterprise Edition.
SSIS packages can be invoked using stored procedures in 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 important 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 looking to transition to SQL Server on Azure IaaS VMs, this might simply amount to matching the number of compute 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. More details 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
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 technologies can copy most of the data to Azure in the background and then keep the target data in-step with the source data until a switch-over 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 in a consistent and correct manner.
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 larger 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.
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 how to performance tune sql replication.
As new transactions are created at the source, these too 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.
To use this method, 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 switch over, 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.
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 easily imported to an Azure SQL Database.
To ensure the exported BACPAC contains all data in a complete and consistent state, workloads using the source database need to be taken offline during the export process, so that transactions are not being made while exporting.
This means that scheduled outages will be required to export a BACPAC file, which may need a substantial amount of time, so migration using BACPAC files is only good for smaller databases.
This limitation might be a moot point as the time taken to export larger databases to a BACPAC file, copy the BACPAC file to Azure Blob Storage, and then import the BACPAC file to an Azure SQL Database can be substantial and other migration techniques would be better suited to minimize downtime.
Data sync and Cut-over
During the Data sync phase, you need to ensure that all changes in the source are captured and applied to the target in near real time.
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 all changes in source have been applied to the target, you can cut-over from the source to the target environment.
Stage 3 – Post-Migration
After you have successfully 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.
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 for public (and smaller) companies every year.
We’d love to help!