Skip to content

replication-to-sql-database.md

Latest commit

 

History

History
157 lines (94 loc) · 7.58 KB

replication-to-sql-database.md

File metadata and controls

157 lines (94 loc) · 7.58 KB
title Replication to SQL Database | Microsoft Docs
ms.custom
SQL2016_New_Updated
ms.date 06/29/2016
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
replication
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
SQL Database replication
replication, SQL Database
ms.assetid e8484da7-495f-4dac-b38e-bcdc4691f9fa
caps.latest.revision 15
author BYHAM
ms.author rickbyh
manager jhubbard

Replication to SQL Database

[!INCLUDEtsql-appliesto-ss2016-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:

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

Create a Publication
Create a Push Subscription
Types of Replication
Monitoring (Replication)
Initialize a Subscription