Partilhar via

Migration from SQL Server to Azure SQL Database Using Transactional Replication

  • Artigo

Written by: Josh Gnanayutham, Program Manager, SQL Engineering

Introduction

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.

  1. Before migration, ensure that the database is compatible with Azure SQL Database. Not all SQL Server features are supported in Azure SQL Database.
  2. Provision and Configure Azure Resources.
  3. Rehearse migration steps in a test environment to ensure the migration will go smoothly.
  4. Test the migrated Azure SQL Database to see if it performs as expected.
  5. Operationalize migrated database.

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.

About Transactional Replication

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.

Migrate the Database

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:

  1. Set up distribution
  2. Create publication
  3. Create subscription

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:

  • Configure Distribution
  • Select snapshot folder
  • Grant publisher access to the distributor server
Using SQL Server Management Studio (SSMS)
  1. Connect to server you are replicating in Object Explorer
  2. Right click on the Replication folder and select Configure Distribution
  3. On the Distributor page, select “Server Name” will act as its own distributor. Then click next. Note that using the publisher as its own distributor may cause a performance impact on the server, depending on the amount of data you’re replicating and on the server resource headroom. If the performance impact is unacceptable, you can use a remote distributor, but it will add complexity to management and administration. The distributor must have network access to your Azure SQL Database. This usually means allowing outbound internet access on the distributor. However, if you have an ExpressRoute link to the target Azure region, internet access is not necessary.
  4. If the SQL Agent isn’t running on the SQL Agent Start page, select Yes to configure the SQL Agent to run automatically.
  5. Select a Snapshot folder to store your initial snapshot. Creating a snapshot involves taking a BCP copy of every replicated table. Make sure the location you choose has enough space for this. By default, snapshot data is uncompressed, even if you use data compression in the database. While using compressed snapshots is possible, that carries significant limitations.
  6. Use the defaults on the remaining pages of the wizard.
  7. Click Finish to enable the distribution.
  8. After this you’ll have to give the publisher access to the distributor. If you are using a remote distributor located on a different server from that of your publisher, you’ll need to set up a password.
Using Transact-SQL
  1. Execute sp_get_distributor to determine if the server is already configured as a Distributor. If the value of installed in the result set is 0, execute sp_adddistributor at the Distributor on the master database. Specify the name of the distribution database for @database.
  2. At the Distributor, which is also the Publisher if you’re using local distribution, execute sp_adddistpublisher, specifying the folder that will be used as default snapshot folder for @working_directory. If you are using a remote distributor, the steps will be a little different. Note that the distribution server must have network access to your Azure SQL Database.
  3. At the Publisher, execute sp_replicationdboption. Specify the database being published for @dbname, the type of replication for @optname (publish) , and a value of true for @value.
More Details

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)
  1. Connect to the Publisher in SSMS, and expand the server node.
  2. Expand the Replication folder and right-click the Local Publications folder.
  3. Click New Publication. If your server is not configured as a publisher, you will be prompted to do that.
  4. Select your publication database, then click next.
  5. Select Transactional Publication, then click next.
  6. Select the articles you want to publish, then click next. You can publish everything, or select specific tables.
  7. You have the option to filter rows, this step is not necessary, click next. You can use this to filter out unnecessary data.
  8. Select when you would like to create a snapshot. In this blog, we will select Immediately for the sake of simplicity. Then click next. The snapshot is for the initial synchronization between the publisher and subscriber. You can also schedule your snapshot creation for later.
  9. Click Security Settings near the Snapshot Agent box. Select Run under the following Windows account and enter your credentials. Under Connect to the Publisher, select Impersonate. Click ok to confirm and return to the original Agent Security page. Check the checkbox at the bottom and click next.
  10. Select Create the publication and then click next
  11. Name the publication and you’re done.
Using Transact-SQL
  1. Execute sp_replicationdboption (Transact-SQL) to enable publication of the current database using transactional replication.
  2. Determine whether a Log Reader Agent job exists for the publication database. If a Log Reader Agent job exists for the publication database, proceed to step 3. If you are unsure whether a Log Reader Agent job exists for a published database, execute sp_helplogreader_agent (Transact-SQL) at the Publisher on the publication database. If the result set is empty, create a Log Reader Agent job. At the Publisher, execute sp_addlogreader_agent (Transact-SQL). Specify the Microsoft Windows credentials under which the agent runs for @job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the Microsoft SQL Server login information for @publisher_login and @publisher_password.
  3. Execute sp_addpublication (Transact-SQL). Specify a publication name for @publication, and, for the @repl_freq parameter, specify a value of continuous for a transactional publication.
  4. Execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 3 for @publication and the Windows credentials under which the Snapshot Agent runs for @snapshot_job_name and @password. This creates a Snapshot Agent job for the publication. When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure.
  5. Add articles to the publication. For more information, see Define an Article.
  6. Start the Snapshot Agent job to generate the initial snapshot for this publication. For more information, see Create and Apply the Initial Snapshot.
More Details

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)
  1. Connect to the Publisher in SSMS and expand the server node.
  2. Expand the Replication folder, and then expand the Local Publications folder.
  3. Right-click your publication and click New Subscriptions.
  4. Select your publication and click next. Select Run at Distributor and click next.
  5. Only push subscriptions are supported for Azure SQL Database.
  6. Click Add Subscriber and connect to the Azure SQL Database logical server you are migrating to.
  7. Select the Subscription Database, this is where the data will be replicated. Note that this database is expected to have been created in advance, with an appropriate edition and service level. Then click next.
  8. Click … and select the option to connect to the subscriber using SQL Server login. Enter the credentials. Connect to the Distributor by Impersonating. Then click next. In the context of replication, you can only connect to Azure SQL Database using SQL Server Authentication.
  9. Use the defaults on the remaining pages of the wizard.
  10. Click Finish to create the subscription.
Using Transact-SQL
  1. Do the following at the Publisher on the publication database.
  2. Execute sp_helppublication to see if push subscriptions are enabled. If the value of allow_push is 1, push subscriptions are supported. If the value of allow_push is 0, execute sp_changepublication, specifying allow_push for @property and true for @value.
  3. Execute sp_addsubscription. Specify the @publication, @subscriber and @destination_db. Specify a value of push for @subscription_type.
  4. Execute sp_addpushsubscription_agent. Specify the @subscriber, @subscriber_db, and @publication parameters. The SQL Server credentials under which the Distribution Agent at the Distributor runs for @job_login and @job_password.When creating a push subscription at a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).
More Details

You can see more details on setting up your Subscription here.

After Migration

After migration you have a few more things to do:

  • Verify successful migration
  • End Replication

End Replication

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)
  1. Connect to the Publisher in SSMS and expand the server node.
  2. Expand the Replication folder, and then expand the Local Publications folder.
  3. Right-click your publication and click Delete.
  4. Click Yes.
Using Transact-SQL
  1. Do the following at the Publisher on the publication database.
  2. Execute sp_droppublication at the Publisher on the publication database. Specify the @publicationDB and @publication parameters.

Verify Successful Migration

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.

Limitations

There are some limitations to when transactional replication can be used for migration. For complete documentation go here. The following configurations are supported:

  • Only push subscriptions are supported.
  • The distribution and replication agents cannot be on Azure SQL Database.
  • Only one-way transactional replication is supported. Peer-to-peer, bi-directional and merge are not supported.
  • SQL Server 2012 or later

Conclusion

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.