Migrating to Azure SQL
There are a variety of methods available for migrating a database from an on-premises SQL Server instance to Azure SQL. Migrating a database to a SQL Server on Azure VM is relatively straightforward unless you are upgrading from an older version of SQL Server and need to update any deprecated features. The following migration options are commonly included:
- Taking a backup of the on-premises database and storing it in Azure Blob storage. Restore the database backup from Azure Blob storage to the SQL Server on Azure VM using RESTORE DATABASE FROM URL.
- If the on-premises instance is a primary in an Always On AG, add the SQL Server on Azure VM as a secondary. Once the data is synchronized to the SQL Server on Azure VM, perform a failover so that the SQL Server on Azure VM is the new primary.
- Configure transactional replication so that the on-premises SQL Server instance is a publisher and the SQL Server on Azure VM is a subscriber. Once the data is replicated to the SQL Server on Azure VM, update application connection strings and point users to the database in Azure.
Migrating to Azure SQL Database or Azure SQL MI requires more planning and consideration due to compatibility differences between on-premises SQL Server and PaaS Azure SQL. Even though Azure SQL MI is nearly 100 percent compatible with on-premises SQL Server, there are still some feature differences between the two that could cause migration issues. The same can be said about the differences between an on-premises instance of MySQL and PostgreSQL and Azure Database for MySQL and Azure Database for PostgreSQL. This is where a service such as the Azure Database Migration Service can provide data that makes the migration planning process much easier.
The Azure Database Migration Service (DMS) is a fully managed service that can be used to discover any potential compatibility issues and migrate the database once those issues are addressed. It uses the Data Migration Assistant (DMA) to detect compatibility issues and make recommendations on how to address them. DMA is also useful for migrations to a SQL Server on Azure VM by discovering compatibility issues between an older version of SQL Server and a newer version on the Azure VM. DMA can be used to assess versions of SQL Server ranging from SQL Server 2005 to the most up-to-date version. After addressing any compatibility issues, DMA can be used to migrate the database’s schema to streamline data migration with DMS.
DMS can be used for offline and online migrations. Offline migrations refer to application downtime beginning as soon as the migration starts. Application cutover is a manual process and must be performed by the user. Offline migrations are available for migrations to Azure SQL Database, Azure SQL MI, SQL Server on Azure VM, Azure Cosmos DB, Azure Database for MySQL, and Azure Database for PostgreSQL. DMS can also limit downtime by handling the application cutover process through an online migration. Online migrations are only available for migrations to Azure SQL MI, Azure Cosmos DB, and Azure Database for PostgreSQL.