Configure replication
In this tutorial, we will configure an Azure SQL Database as the push subscriber in a one-way transactional or snapshot replication topology.
Supported configurations
- Firstly, Azure SQL Database can only be the push subscriber of a SQL Server publisher and distributor.
- Secondly, 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.
- Thirdly, the distribution database and the replication agents cannot be placed on a database in Azure SQL Database.
- Lastly, snapshot and one-way transactional replication are supported. Peer-to-peer transactional replication and merge replication are not in support.
Replication Architecture
Scenarios
Typical Replication Scenario
- Firstly, create a transactional replication publication on a SQL Server database.
- Secondly, on SQL Server use the New Subscription Wizard or Transact-SQL statements to create a push to subscription to Azure SQL Database.
- Lastly, 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. However, with a SQL Managed Instance publisher, you can also use a database backup to seed the Azure SQL Database subscriber.
Data migration scenario
- Firstly, use transactional replication to replicate data from a SQL Server database to Azure SQL Database.
- Secondly, redirect the client or middle-tier applications to update the database copy.
- Lastly, stop updating the SQL Server version of the table and remove the publication.
Limitations
The following options are not in support for Azure SQL Database subscriptions:
- Firstly, copy file groups association, table partitioning schemes and index partitioning schemes.
- Secondly, copy user defined statistics, default bindings, rule bindings, fulltext indexes and XML XSD.
- Thirdly, copy XML indexes, permissions, spatial indexes, filtered indexes, data compression attribute and sparse column attribute.
- After that, convert filestream to MAX data types, hierarchyid to MAX data types and spatial to MAX data types.
- Lastly, copy extended properties
Reference: Microsoft Documentation