Replication to SQL Database

THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server replication can be configured to Azure SQL Database.

Supported Configurations:

  • 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.

    Versions

  • 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.

  • To use all the features of SQL Database you must be using the latest versions of SQL Server Management Studio and SQL Server Data Tools.

    Remarks

  • 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.

  • Only @subscriber_type = 0 is supported in sp_addsubscription for SQL Database.

  • SQL Database does not support bi-directional, immediate, updatable, or peer to peer replication.

    Replication Architecture

    replication-to-sql-database

    Scenarios

    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.

    Limitations

    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 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

  • Copy permissions

Limitations to be determined