Replication to Azure SQL Database
You can configure an Azure SQL Database as the push subscriber in a one-way transactional or snapshot replication topology.
This article describes the use of transactional replication in Azure SQL Database. It is unrelated to active geo-replication, an Azure SQL Database feature that allows you to create complete readable replicas of individual databases.
- Azure SQL Database can only be the push subscriber of a SQL Server publisher and distributor.
- The SQL Server instance acting as publisher and/or distributor can be an instance of SQL Server running on-premises, an Azure SQL Managed Instance, or an instance of SQL Server running on an Azure virtual machine in the cloud.
- The distribution database and the replication agents cannot be placed on a database in Azure SQL Database.
- Snapshot and one-way transactional replication are supported. Peer-to-peer transactional replication and merge replication are not supported.
To successfully replicate to a database in Azure SQL Database, SQL Server publishers and distributors must be using (at least) one of the following versions:
Publishing to any Azure SQL Database from a SQL Server database is supported by the following versions of SQL Server:
- SQL Server 2016 and greater
- SQL Server 2014 RTM CU10 (12.0.4427.24) or SP1 CU3 (12.0.2556.4)
- SQL Server 2012 SP2 CU8 (11.0.5634.1) or SP3 (11.0.6020.0)
Attempting to configure replication using an unsupported version can result in error number MSSQL_REPL20084 (The process could not connect to Subscriber.) and MSSQL_REPL40532 (Cannot open server <name> requested by the login. The login failed.).
Types of replication
There are different types of replication:
|Replication||Azure SQL Database||Azure SQL Managed Instance|
|Standard Transactional||Yes (only as subscriber)||Yes|
|Snapshot||Yes (only as subscriber)||Yes|
- Only push subscriptions to Azure SQL Database are supported.
- Replication can be configured by using SQL Server Management Studio or by executing Transact-SQL statements on the publisher. You cannot configure replication by using the Azure portal.
- Replication can only use SQL Server authentication logins to connect to Azure SQL Database.
- Replicated tables must have a primary key.
- You must have an existing Azure subscription.
- The Azure SQL Database subscriber can be in any region.
- A single publication on SQL Server can support both Azure SQL Database and SQL Server (on-premises and SQL Server in an Azure virtual machine) subscribers.
- Replication management, monitoring, and troubleshooting must be performed from SQL Server rather than Azure SQL Database.
@subscriber_type = 0is supported in sp_addsubscription for SQL Database.
- Azure SQL Database does not support bi-directional, immediate, updatable, or peer-to-peer replication.
Typical Replication Scenario
- Create a transactional replication publication on a SQL Server database.
- On SQL Server use the New Subscription Wizard or Transact-SQL statements to create a push to subscription to Azure SQL Database.
- With single and pooled databases in Azure SQL Database, the initial data set is a snapshot that is created by the Snapshot Agent and distributed and applied by the Distribution Agent. With a SQL Managed Instance publisher, you can also use a database backup to seed the Azure SQL Database subscriber.
Data migration scenario
- Use transactional replication to replicate data from a SQL Server database to Azure SQL Database.
- Redirect the client or middle-tier applications to update the database copy.
- Stop updating the SQL Server version of the table and remove the publication.
The following options are not supported for Azure SQL Database subscriptions:
- Copy file groups association
- Copy table partitioning schemes
- Copy index partitioning schemes
- Copy user defined statistics
- Copy default bindings
- Copy rule bindings
- Copy fulltext indexes
- Copy XML XSD
- Copy XML indexes
- Copy permissions
- Copy spatial indexes
- Copy filtered indexes
- Copy data compression attribute
- Copy sparse column attribute
- Convert filestream to MAX data types
- Convert hierarchyid to MAX data types
- Convert spatial to MAX data types
- Copy extended properties
Limitations to be determined
- Copy collation
- Execution in a serialized transaction of the SP
Create a publication and a push subscription. For more information, see:
- Create a Publication
- Create a Push Subscription by using the server name as the subscriber (for example N'azuresqldbdns.database.windows.net') and the Azure SQL Database name as the destination database (for example AdventureWorks).