Consideration—Backup to URL Performance
When taking a backup to blob storage, the limiting factor will typically be bandwidth on your internet connection. However, if that isn’t the case, then striping a backup over multiple files will help drive higher throughput on the network due to multiple writer threads. Managed Instance can also make use of multiple threads when performing the restore operation, which results in higher throughput for the restore operation.
Database Migration Service
The Azure Database Migration Service (DMS) is an Azure service that will automate a large portion of the process when it comes to moving your databases from on-premises to Managed Instance. There are two different migration types available with DMS—online and offline. The main difference between the two is the amount of downtime during each migration.
There are a few infrastructure prerequisites for a successful DMS migration, including vNet connectivity between the source and destination systems. DMS also needs a file share location that the SQL Server and DMS can access to manage the transfer of data from on-premises to the Azure.
Azure Database Migration Service—Offline migration to Managed Instance
Offline DMS Migration
The offline migration scenario supported by DMS is the only option available when you make use of the General Purpose tier with DMS. Here, the term offline refers to the fact that the source system will be unavailable from the time the migration starts and until it completes.
Online DMS Migration
When using the business-critical tier of DMS, you have the option to perform an offline migration or an online migration.
Online, in this case, is a bit of a misnomer given that there’s some downtime associated with this migration option. However, the time window for the downtime is related to the time taken to reconfigure the application to connect to the new database location. “Online” refers to the fact that the source system will remain active and able to process transactions while the data is being synchronized between the source and the target.
The online migration option is very useful for larger systems or as part of the test and validation phase because of how it interacts with the source system. For those who aren’t comfortable supporting Transactional Replication as a technology, DMS is a viable alternative for migrating the database. This option also has the added benefit of requiring negligible post-migration clean-up activity, unlike when using Transactional Replication.
One of the main selling points of Managed Instance is that it supports a very high degree of parity with on-premises SQL Server. One of these areas of parity is that it supports all roles in the Transactional Replication topology.
The use of Transactional Replication for database migrations does rely on the database supporting this configuration. Restrictions about the use of Transactional Replication can be found in the Microsoft documentation.
If Transactional Replication is a viable option for your system, then it’s strongly recommended that you use a push subscription to the Managed Instance. Post-migration operations needed to decommission the source and replication topology will be minimal. The diagram below shows an example of the architecture for using Transactional Replication to migrate a database to Managed Instance. More information about how to configure replication with Managed Instance can be found in the Microsoft documentation article “Configure replication in an Azure SQL Database managed instance database.”
Transactional Replication to Managed Instance—Push subscription
Azure SQL Database
Azure SQL Database doesn’t support restoring native backup files to the service. In this situation, there are several options that you can utilize instead. When we covered the DMA earlier in this guide, it was used to perform an analysis to identify migration blockers. It can also migrate SQL Server databases from on-premises SQL Server to Azure SQL Database. There’s also the DMS, Transactional Replication push subscriptions, and creating a BACPAC export/import from SQL Server to Azure SQL Database.
Let’s look at how each of these works below.
At its core, a BACPAC is a file that contains the schema and data for a database that has been exported from SQL Server. The use case for migrating a database to Azure SQL Database via BACPAC is quite specific; you should consider the following before utilizing this option:
Size of the source database (smaller is better)
Connectivity to Azure (vNet access isn’t required for deployment of BACPAC)
Allowable downtime (you will need to stop the application processing during the export phase for consistency)
Although it’s possible to deploy a BACPAC via SSMS or SQLPackage.exe, the blob storage process is less susceptible to network connectivity issues.
The export, upload, and create process can be scripted with PowerShell. Automating the process not only reduces the chance of migration issues but also allows the process to be tested many times ahead of the actual migration to validate the approach and confirm timing for the overall activity, which you can feed back into your planning documents.
The key stages for this process are as follows:
Export database as a BACPAC using SQLpackage.exe
Copy the BACPAC file to Azure Blob storage
Create an Azure SQL Database using the New-AzSqlDatabaseImport cmdlet in the AZ PowerShell module
Overall, this process is reliable and easy to achieve with minimal overhead. However, it does mean that the tolerance for downtime during the migration process needs to be considered. The generation of the BACPAC file can take some time in the event of a very complex database schema or large volume of data.
The DMA has two project types—the analysis project, which helps you identify blockers to migration to selected targets, and a migration project. The migration project allows you to copy the database schema and data from the source system and move it to Azure SQL Database.
Migrate database to Azure SQL Database with Database Migration Assistant
The DMA migrations can either be performed via the client application or scripted using the command line. However, if you would like to automate the migration, there are better options available in the form of the DMS from Microsoft. The syntax that you need to use for the DMA command line can be onerous and verbose, so it’s quite easy to end up with unmanageable scripts when automating the migration of a database with DMA.
If you’re migrating one or two small databases to Azure SQL Database, the DMA can be an extremely useful tool.
The DMS also supports migrating a database from on-premises SQL Server to Azure SQL Database. As with the DMS scenario we discussed for Managed Instance, the service needs VNet access to the source database server (either a VPN or Azure ExpressRoute connection). You can then perform the migration using the offline/online models.
There isn’t a requirement for the Azure SQL Database to have an VNet endpoint, as DMS will make use of the public endpoint to connect to the Azure SQL Database. In this scenario, it’s important to ensure that the Azure SQL Server is configured to allow other Azure services to access it. More information about how to configure the Azure SQL Database server firewall rules can be found in the online documentation from Microsoft.
Azure IaaS VM
The Azure PaaS offerings provide a wealth of capabilities. But there are still going to be reasons to migrate to an IaaS solution with on-premises SQL Server. These can be due to constraints such as third-party vendor applications that only support specific versions of SQL Server.
With SQL Server 2008/2008 R2 end of life in July 2019, migrating to an IaaS solution can be a shortcut to replace aging servers without a large outlay. In extreme situations in which you can’t migrate off either of these versions, Microsoft will provide an additional two years of security updates if SQL Server 2008 is running on an Azure VM.
There are several routes to moving on-premises databases to IaaS VMs. You can use both DMA and DMS to perform a migration to Azure IaaS VMs. If you’re using newer versions of SQL Server (SQL Server 2012 or newer), you can utilize Availability Groups to replicate the data. This migration model also helps with the application configuration, as the listener can be used to ease the application migration from a connectivity perspective.
The other option is to use Azure Site Recovery (ASR) to migrate VMs directly to Azure. Let’s look at the backup and restore and ASR methods.
Backup and Restore
You have likely been using backup and restore as a method for moving databases between SQL Servers for an exceptionally long time. It’s one of the most versatile options that can help with moving some of the largest databases by making use of full, differential, and transaction log backup options.
There are two main routes for using backup and restore to move SQL Server databases between servers. The first is to perform it manually via scripted methods such as T-SQL or PowerShell with the dbatools module. The second is to make use of log shipping within SQL Server. This allows for the priming of the target environment and can drastically reduce downtime for migration activities.
The option you choose to use will depend largely on the connectivity between on-premises systems and Azure. If the two environments are connected via either site-to-site VPN or Express Route, then both options are open, irrespective of whether you have extended your Active Directory (AD) domain. If the two environments are distinct, then you will restrict yourself to the first option and have to manual perform the migration activities via Azure Blob storage. It’s possible to automate these activities, but it will involve additional services and configuration to make work smoothly.
Backup and restore SQL Database to IaaS VM
Native Backup and Restore
When using native backup and restore methods for migrating a SQL Server database to an IaaS VM, the best route is via Azure Blob storage. This allows for both connected and disconnected environment configurations. It’s highly recommended that you make use of the dbatools PowerShell module, which give you a wider range of options when scripting and subsequently automating the process.
Using Azure Blob storage as part of this process means there’s no need to allocate temporary storage to your SQL Servers or stand up dedicated infrastructure to handle the backups. Azure Blob storage can be created and destroyed with negligible impact to the target environment.
For connected hybrid environment in which the network and AD have been extended to Azure, having connectivity to both the source and destination makes this process a lot easier. Azure Blob storage would take the place of a network share that you would use. At this point, the process is the same as that used for migrating to Managed Instance via blob storage. This is where making use of PowerShell comes to the fore. If the version of SQL Server at the source supports backup to URL, then simply use Backup-DbaDatabase from the source to blob storage and Restore-DbaDatabase from blob storage. If the version of SQL Server doesn’t support native backup to URL, then you have to back up to a local directory and then move the file to blob storage using the AZ PowerShell module before completing the restore. This approach is typically easier than using a combination of T-SQL and PowerShell, even with the ability to execute T-SQL via PowerShell.
In disconnected environments, where the Azure portion is distinct from the on-premises environment, the method is the same. However, the implementation differs, as you will need to connect to the target systems via a public route such as RDP to perform the restore operations. It isn’t advisable to expose the SQL Server systems to the Internet via public IP addresses on Azure VMs.
Transaction Log Shipping
If you have a connected hybrid environment, then transaction log shipping is a viable option for migrating databases to Azure VMs. Log shipping is a tried-and-tested technology for creating copies of databases; it can leverage many of the new features, such as backup encryption introduced with SQL Server 2014. However, it doesn’t support the native use of Azure Blob storage for holding the backup files.
There are three options to configure log shipping regarding storage for backups:
Provision the required storage on the SQL Servers that will hold the transient backup files.
Stand up a dedicated file server within the environment that can be accessed by both source and target servers.
Make use of an Azure file share and connect it to the source and target SQL Servers (if the operating system supports the use of this capability).
Whether migrating to a single Azure VM or to a multi-replica Always On Availability Groups configuration, log shipping can add a lot of value to the process. Once the storage is provisioned for the backup files, then it’s simply a case of using the native log shipping feature in SQL Server to configure the primary and secondary servers.
SQL Server log shipping’s ability ship to multiple secondary systems to prime an Availability Groups configuration can really simplify the deployment. It means that all replicas will be at the same state so that when the primary replica is brought online, then the secondary replicas are simply added to the Availability Groups.
Now you need to determine the timing for the shutdown of the application, wait for the final logs to be shipped, and then configure the target system. Once that’s done, the application(s) can be reconfigured to work with the new servers and begin processing again.
Azure Site Recovery
Azure Site Recovery (ASR) is a DR solution that allows for physical servers or Hyper-V and VMware VMs to be replicated from on-premises to Azure and converted to Azure IaaS VMs. This capability can also be applied to migrating VMs to Azure without needing to build and configure new servers to host databases. This mechanism isn’t one that would typically be recommended. However, when you have particularly complex SQL Server configurations deployed with additional software components, ASR can be the safest way to move to Azure. It’s especially relevant should you need to remain on your current SQL Server version and configuration. In the case of SQL Server 2008, ASR can provide a get out of jail free card, where you can migrate the VM to Azure and receive the additional two years of support for security updates while you plan to upgrade to newer versions.
Shared storage isn’t present in Azure VMs, so FCI aren’t typically used. It’s possible to use FCI in the cloud; however, it requires additional storage replication technology such as Storage Spaces Direct or another third-party solution.
There are a few constraints around what can be migrated from on-premises to Azure. Learn more about the supported configurations here. One of the restrictions that is likely to impact SQL Server systems is that clustered disks aren’t supported for migrations. As such, on-premises FCI configurations can’t be migrated to Azure VMs and an alternative mechanism will need to be identified.
No matter which platform that you plan to migrate to, the fundamental process is the same. You want to move databases from one place to another as effectively as possible with minimal input. DMS is often is the first port of call for those looking to migrate to the Azure platform, no matter what your target is. The ability to manage the projects in the service and leverage the online migration option means that it’s one of the most effective options when looking to combine it with automation routines.
In the event that you’re not able to leverage DMS, there are still many other ways to migrate SQL Server systems to Azure.
Although this section of the guide has focused on the migration technology options, it’s still important to note that data testing and validation should be undertaken during the final migration. The tests and checks that you defined to ensure that the migration is successful need to be completed as part of the actual migration task, too. This will ensure that risks to the migration process are minimized.