How to Migrate Databases to Azure SQL Server with Transactional Replication?

Many enterprises are moving their on-premises workloads to the cloud, and SQL Server to Azure SQL Database migration is becoming increasingly common. Such enterprises typically have large databases with hundreds of GBs of data and a significant number of database objects. Moreover, the business-critical operations of large enterprises depend on the databases in the on-premises servers. This implies that they cannot afford long downtimes during the migration.

While there are several methods to migrate SQL Server databases to the cloud, moving a database that is already in production can be challenging for most businesses. In this blog post, we will focus on using transactional replication to migrate on-premises SQL Server databases to Azure SQL Databases with minimal downtime.

How Does Transactional Replication for SQL Server Migration Work?

In an SQL database transactional replication migration, there are three subjects in the replication process. The Publisher is the source, which in this case is the on-premises SQL Server. The Subscriber is the replication target, which in this case will be the Azure instance. Finally, the Distributor, which can either be an Azure instance or a SQL Server, is the coordinator for the replication.

Migration by transactional replication is done using three SQL Agent jobs. The Snapshot Agent gets the initial database snapshot, the Log Reader Agent tracks subsequent changes, and the Distribution Agent applies the database changes to the target. Depending on the replication model (push or pull), the Distributor can be deployed either on the Publisher or Subscriber side. Once the Distributor synchronizes the data between the Publisher and the Subscriber, the data and the schema appear in the Azure SQL Database, at which stage the on-premises to Azure SQL migration can be completed, and the connection string of applications can be changed to point to the new Azure SQL Database.

How To Setup a Transactional Replication for Migrating Database from SQL Server to Azure?

Transactional replication requires SQL Server Management Studio (SSMS) to remain synchronized with updates to Azure and SQL Database. Follow the steps below for migrating on-prem SQL Server to Azure SQL DB using the transaction replication workflow.

• Use the Microsoft Data Migration Assistant (DMA) to check on the database in the on-premises SQL Server can be migrated to Azure and resolve compatibility issues if any.
• Use the Azure Portal to create an Azure SQL database shell.
• As a part of the transactional replication setup, configure the on-prem SQL Server as the Distributor, and a new publication for the databases to be migrated to Azure.
• Create a Push subscriber and connect it to the newly created Azure SQL Database.
• Run the snapshot agent and synchronize the on-premises database with Azure.
• Test the application connectivity to the Azure SQL database, and remove replication.
• Schedule a cutover migration to move the production database to the Azure SQL database.
• Shut down the application and ensure that the replication is in sync. The replication can be removed and the on-prem production instance can be shut down after the cutover migration is complete.
• Make appropriate changes to the application configuration and perform functional testing after connecting to the Azure SQL database.

Apps4Rent Can Help in Migrating On-Premises SQL Server to Azure

One of the major challenges of migrating databases using transactional replication from on-premises SQL Server to Azure is compatibility issues that could arise due to the version of the server and the complexity of the database. Troubleshooting these issues could require advanced technical skills.

As a Tier 1 Microsoft CSP, Apps4Rent provides managed Azure services to help enterprises migrate their on-premises SQL server to an appropriate Azure solution. Alternatively, we can host SQL Server in our top-tier SSAE 16 datacenters or Azure as a transitory solution. Contact our Microsoft-certified SQL Server consultants, available 24/7 via phone, chat, and email for assistance.