Skip to content

replication-to-sql-database.md

Latest commit

 

History

History
155 lines (97 loc) · 8 KB

replication-to-sql-database.md

File metadata and controls

155 lines (97 loc) · 8 KB

title: "Replication to SQL Database | Microsoft Docs" ms.custom: "" ms.date: "04/24/2016" ms.prod: sql ms.prod_service: "database-engine, sql-database" ms.component: "replication" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "replication" ms.tgt_pltfrm: "" ms.topic: conceptual helpviewer_keywords:
  • "SQL Database replication"
  • "replication, SQL Database" ms.assetid: e8484da7-495f-4dac-b38e-bcdc4691f9fa caps.latest.revision: 15 author: "MashaMSFT" ms.author: "mathoma" manager: craigg monikerRange: "= azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions"

Replication to SQL Database

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md]

[!INCLUDEssNoVersion] replication can be configured to [!INCLUDEssSDSFull].

Supported Configurations:

  • The [!INCLUDEssNoVersion] can be an instance of [!INCLUDEssNoVersion] running on-premises or an instance of [!INCLUDEssNoVersion] running in an Azure virtual machine in the cloud. For more information, see SQL Server on Azure Virtual Machines overview.
  • [!INCLUDEssSDS] must be a push subscriber of a [!INCLUDEssNoVersion] publisher.
  • The distribution database and the replication agents cannot be placed on [!INCLUDEssSDS].
  • Snapshot and one-way transactional replication are supported. Peer-to-peer transactional replication and merge replication are not supported.

Versions

  • The publisher and distributor must be at least at one of the following versions:

  • [!INCLUDEsssqlv14]

  • [!INCLUDEssSQL15]

  • [!INCLUDEssSQL14] SP1 CU3

  • [!INCLUDEssSQL14] RTM CU10

  • [!INCLUDEssSQL11] SP2 CU8

  • [!INCLUDEssSQL11] 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 [!INCLUDEssSDS] subscriber must be at least V12 and can be in any region.

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

Remarks

  • Replication can be configured by using [!INCLUDEssManStudioFull] or by executing [!INCLUDEtsql] statements on the publisher. You cannot configure replication by using the [!INCLUDEssSDS] portal.

  • Replication can only use [!INCLUDEssNoVersion] authentication logins to connect to [!INCLUDEssSDS].

  • Replicated table must have a primary key.

  • You must have an existing Azure subscription and an existing [!INCLUDEssSDS] V12.

  • A single publication on [!INCLUDEssNoVersion] can support both [!INCLUDEssSDS] and [!INCLUDEssNoVersion] (on-premises and [!INCLUDEssNoVersion] in an Azure virtual machine) subscribers.

  • Replication management, monitoring, and troubleshooting must be performed from the on-premises [!INCLUDEssNoVersion].

  • Only push subscriptions to [!INCLUDEssSDS] are supported.

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

  • [!INCLUDEssSDS] does not support bi-directional, immediate, updatable, or peer to peer replication.

Replication Architecture

replication-to-sql-database

Scenarios

Typical Replication Scenario

  1. Create a transactional replication publication on an on-premises [!INCLUDEssNoVersion] database.

  2. On the on-premises [!INCLUDEssNoVersion] use the New Subscription Wizard or [!INCLUDEtsql] statements to create a push to subscription to [!INCLUDEssSDS].

  3. 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 [!INCLUDEssNoVersion][!INCLUDEssISnoversion].

Data Migration Scenario

  1. Use transactional replication to replicate data from an on-premises [!INCLUDEssNoVersion] database to [!INCLUDEssSDS].

  2. Redirect the client or middle-tier applications to update the [!INCLUDEssSDS] copy.

  3. Stop updating the [!INCLUDEssNoVersion] version of the table and remove the publication.

Limitations

The following options are not supported for [!INCLUDEssSDS] 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

  • Copy collation

  • Execution in a serialized transaction of the SP

Examples

Create a publication and a push subscription. For more information, see:

See Also