Introduction

For migrating databases from an on-premise SQL Server to Azure Sql Database, Transactional Replication has become more and more popular. This approach has some important advantages: you may maintain a live copy of your production data in Azure SQL Database, and reduce the downtime for the actual switch to the cloud to a minimum. Ideally, you would only have to stop activity on the on-premise database, wait until the replication agents have transferred the last changes, then redirect your applications to the cloud database. You can also test your applications against Azure SQL Database on live production data to ensure that they will continue to work after the switch.

The article Migration from SQL Server to Azure SQL Database Using Transactional Replication gives you an overview of the major steps and benefits, and additional links to topics that will help you with setting up the replication topology.

This article here provides you with some practical advice that we have collected from previous support cases and internal testing.:

Considerations for setting up Azure SQL Database subscriptions

Applying Snapshots

With Azure SQL Database subscriptions, you are limited to applying regular snapshots to the subscriber database. It is not possible to restore a backup from the publisher database, so you cannot use the sp_addsubscriber ... @sync_type = 'initialize with backup' or 'initialize from lsn'.

Do not use manual subscriber initialization

In theory, you could load the subscriber data through alternate methods, like importing the database from a BACPAC file, or loading the data with SSIS or BCP. But this runs a very high risk that your replication will fail later on, because the publisher and subscriber data might no longer match. This may happen if the publisher data changes while or after exporting the data, and before the import into the subscriber has completed.

Typical errors would be:

20598 The row was not found at the Subscriber when applying the replicated command
2601 Cannot insert duplicate key row in object 'xxx' with unique index 'zzz'
2627 Violation of xxx constraint 'zzz'. Cannot insert duplicate key in object

Choosing the DTU Performance level of the Subscriber database

The initial synchronization is depending directly on the performance level of the subscriber database. Applying the snapshot is usually very I/O-intensive and will quickly overwhelm the lower scaling levels. Don't even think about Basic. Standard S3 may work for smaller databases. For anything meaningful, go directly to the highest Premium level you can afford. Choose a P2 as a minimum, and a P6 for best performance. PremiumRS might be an option as well.

The idea behind that is to transfer the snapshot as quickly as possible. This reduces the risk of transient errors e.g. because of an internet hickup or a reconfiguration of the subscriber database. Any retry or resend of the snapshot is usually more expensive than getting it right in the first attempt.

After the snapshot has been applied, you may scale down again to the intended DTU level.

Consider creating several publications instead of a single large publication

If your publisher database contains one or more large tables, you may think about splitting them into separate publications. This has the advantage that you can (re-)initialize a subset of your data and thus get  better control of the subscriber load. It avoids the issue that the transfer of larger objects may delay or prevent the transfer of all other objects.

Consider data compression

If some of your tables are are really large, it might be a good time to think about data compression. If you compress your large tables e.g. with:

ALTER TABLE [dbo].[tablename] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)

you may then maintain the compression while replicating the tables to the subscriber. You simply have to configure the corresponding schema_option for your article, e.g.:

exec sp_addarticle ... @schema_option = 0x000000040803509F

This schema_option relates to:

0x400000000 Replicates the compression option for data and indexes. For more information, see Data Compression.