Written by: Josh Gnanayutham, Program Manager, SQL Engineering
As users are increasingly moving their data to the Azure cloud, migration from SQL Server to Azure SQL Database is a common task. There are many migration methods and they each have their pros and cons. This blog post will explore how to migrate your database using Transaction Replication . It will also cover the limitations.
As a prerequisite to this article, we recommend looking at the Azure SQL Database documentation on migration , which summarizes the different options you have. This will help determine if Transactional Replication is a good option for you. Keep in mind that you should refer to the Azure SQL Database documentation for the most up to date information.
Transactional replication is useful for migration when
The following are the major tasks associated with migration.
This document will focus on step 3, and the aspects of migration which are unique to Transactional Replication. We recommend you use the steps from the blog post on Migrating from SQL Server to Azure SQL Database using Bacpac Files to prepare your database for migration. It will guide you through database compatibility and the provisioning and configuration of Azure resources (Steps 1 and 2). Note that regarding compatibility, Transactional Replication is a little bit more flexible than migration with bacpac files. For a bacpac to be used, the entire database must be compatible with Azure SQL Database, and not contain any broken object references. With Transaction Replication, you can omit incompatible or broken parts of the database if they are unnecessary. This can be done when you define your publication.
Transactional replication involves three main components. They are the publisher, the distributor, and the subscriber. Transactional replication starts with a snapshot of the original database. After the initial snapshot is created, all changes to published objects and data in the original database (the publisher) are propagated to the new database (the subscriber) by the distributor, guaranteeing transactional consistency.
With transactional replication, you will suffer little to no downtime, assuming you’re using concurrent snapshots . With concurrent snapshots, you can continue using your original database while the snapshot is being created. After this, transactional replication will keep the subscriber up to date with minimal latency, so you can switch to using your new database in the cloud whenever you want. Note that in the case of highly intensive workloads, downtime may still be advised for snapshot creation, in order to prevent resource contention from affecting the application.
There are some features that Transactional Replication does not support when the subscriber is in Azure SQL Database. If you are using any unsupported features, Transactional Replication may not work. For more details on Transactional Replication, you can look at the full documentation . documentation.
After you’ve determined that your database is compatible with Azure SQL Database and that Transactional Replication fits your needs, you can begin migration.
The basic migration tasks are as follows:
In the following sections, we’ll walk through each of these steps in more detail.Set Up Distribution
The distributor is responsible for controlling the processes which move your data between servers. When you set up distribution, SQL will create a distribution database. Each publisher needs to be tied to a distribution database. The distribution database holds the metadata for each associated publication and data on the progress of each replication. For transactional replication, it will hold all the transactions than need to be executed on the subscriber.
To set up distribution you will:
For more details about configuring Distribution, go here .Create Publication
The publisher is the database where all data for migration originates. Within the publisher, there can be many publications, though in the context of migration to Azure SQL Database, only one publication is typically used. These publications contain articles which map to database objects, including tables, that need to be replicated. Depending on how you define the publication and articles, you can replicate either all or a part of your database. Note that for each table, it is possible to replicate just a subset of rows by defining a filter for the corresponding article.Using SQL Server Management Studio (SSMS)
You can see more details on creating your Publication here .Create Subscription
In a replication topology, the subscriber is the server which receives data and transactions from the publication. Each publication can have many subscriptions, though in the context of migration to Azure SQL Database, only one subscription is typically used.Using SQL Server Management Studio (SSMS)
You can see more details on setting up your Subscription here.
After migration you have a few more things to do:
The easiest way to end replication is to simply delete the publication. When you delete the publication all subscriptions are automatically deleted.Using SQL Server Management Studio (SSMS)
After migration is complete, verification is a vital step. You should ensure that your data was correctly and completely migrated before you start using your new database. See the verification section of this blog for some quick sample queries to help you verify success. To be more thorough you can use data compare in SSDT, but this will be time consuming.
There are some limitations to when transactional replication can be used for migration. For complete documentation go here . The following configurations are supported:
In this blog post, we covered migration from on premises SQL Server to Azure SQL Database using transactional replication. This is a common migration scenario, especially in cases where minimal downtime is required. This blog post will be useful to organizations preparing to migrate to Azure SQL Database.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.