Replication to SQL Database
SQL Server replication can be configured to Azure SQL Database.
- The SQL Server can be an instance of SQL Server running on-premises or an instance of SQL Server running in an Azure virtual machine in the cloud. For more information, see SQL Server on Azure Virtual Machines overview.
- SQL Database must be a push subscriber of a SQL Server publisher.
- The distribution database and the replication agents cannot be placed on SQL Database.
- Snapshot and one-way transactional replication are supported. Peer-to-peer transactional replication and merge replication are not supported.
SQL Database Managed Instance (preview) supports publisher and distributor databases. For more information, see Replication with SQL Database Managed Instance.
The publisher and distributor must be at least at one of the following versions:
SQL Server 2017 (14.x)
SQL Server 2016 (13.x)
SQL Server 2014 (12.x) SP1 CU3
SQL Server 2014 (12.x) RTM CU10
SQL Server 2012 (11.x) SP2 CU8
SQL Server 2012 (11.x) expected in SP3
Attempting to configure replication using an older 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.).
The SQL Database subscriber must be at least V12 and can be in any region.
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 SQL Database portal.
Replication can only use SQL Server authentication logins to connect to SQL Database.
Replicated table must have a primary key.
You must have an existing Azure subscription and an existing SQL Database V12.
A single publication on SQL Server can support both 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 the on-premises SQL Server.
Only push subscriptions to SQL Database are supported.
@subscriber_type = 0is supported in sp_addsubscription for SQL Database.
SQL Database does not support bi-directional, immediate, updatable, or peer to peer replication.
Typical Replication Scenario
Create a transactional replication publication on an on-premises SQL Server database.
On the on-premises SQL Server use the New Subscription Wizard or Transact-SQL statements to create a push to subscription to SQL Database.
The initial data set is typically a snapshot that is created by the Snapshot Agent and distributed and applied by the Distribution Agent. The initial data set can also be supplied through a backup or other means, such as SQL Server Integration Services.
Data Migration Scenario
Use transactional replication to replicate data from an on-premises SQL Server database to SQL Database.
Redirect the client or middle-tier applications to update the SQL Database copy.
Stop updating the SQL Server version of the table and remove the publication.
The following options are not supported for 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 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
Execution in a serialized transaction of the SP
Create a publication and a push subscription. For more information, see:
Create a Push Subscription by using the Azure SQL Database logical server name as the subscriber (for example N'azuresqldbdns.database.windows.net') and the SQL Database name as the destination database (for example AdventureWorks).
- Create a Publication
- Create a Push Subscription
- Types of Replication
- Monitoring (Replication)
- Initialize a Subscription