Skip to content

Latest commit

 

History

History
111 lines (71 loc) · 9.64 KB

non-sql-server-subscribers.md

File metadata and controls

111 lines (71 loc) · 9.64 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords
Non-SQL Server Subscribers
Non-SQL Server Subscribers
MashaMSFT
mathoma
08/29/2017
sql
replication
conceptual
updatefrequency5
subscriptions [SQL Server replication], non-SQL Server Subscribers
heterogeneous data sources, non-SQL Server Subscribers
heterogeneous data sources
heterogeneous database replication, non-SQL Server Subscribers
non-SQL Server Subscribers, about non-SQL Server Subscribers
heterogeneous Subscribers
heterogeneous Subscribers, about heterogeneous Subscribers
Subscribers [SQL Server replication], non-SQL Server Subscribers
non-SQL Server Subscribers

Non-SQL Server Subscribers

[!INCLUDE SQL Server]

The following non- [!INCLUDEssNoVersion] Subscribers can subscribe to snapshot and transactional publications using push subscriptions. Subscriptions are supported for the two most recent versions of each database listed using the most recent version of the OLE DB provider listed.

Heterogeneous replication to non-SQL Server subscribers is deprecated. Oracle Publishing is deprecated. To move data, create solutions using change data capture and [!INCLUDEssIS].

Caution

[!INCLUDEssNoteDepFutureAvoid]

Database Operating System Provider
Oracle All platforms that Oracle supports Oracle OLE DB provider (supplied by Oracle)
IBM DB2 MVS, AS400, Unix, Linux, Windows excluding 9.x Microsoft Host Integration Server (HIS) OLE DB provider

Oracle version information:
[!INCLUDEssNoVersion] supports the following heterogeneous scenarios for transactional and snapshot replication:

  • Publishing data from [!INCLUDEssNoVersion] to non-[!INCLUDEssNoVersion] Subscribers.

  • Publishing data to and from Oracle has the following restrictions:

Replication 2016 or earlier 2017 or later
Replication from Oracle Only support Oracle 10g or earlier Only support Oracle 10g or earlier
Replication to Oracle Up to Oracle 12c Not supported

Heterogeneous replication to non-SQL Server subscribers is deprecated. Oracle Publishing is deprecated. To move data, create solutions using change data capture and [!INCLUDEssIS].

For information about creating subscriptions to Oracle and IBM DB2, see Oracle Subscribers and IBM DB2 Subscribers.

Considerations for Non-SQL Server Subscribers

Keep the following considerations in mind when replicating to non- [!INCLUDEssNoVersion] Subscribers:

General Considerations

  • Replication supports publishing tables and indexed views as tables to non-[!INCLUDEssNoVersion] Subscribers (indexed views cannot be replicated as indexed views).

  • When creating a publication in the New Publication Wizard and then enabling it for non-SQL Server Subscribers using the Publication Properties dialog box, the owner of all objects in the subscription database is not specified for non-[!INCLUDEssNoVersion] Subscribers, whereas for [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Subscribers, it is set to the owner of the corresponding object in the publication database.

  • If a publication has [!INCLUDEssNoVersion] Subscribers and non-[!INCLUDEssNoVersion] Subscribers, the publication must be enabled for non-[!INCLUDEssNoVersion] Subscribers before any subscriptions to [!INCLUDEssNoVersion] Subscribers are created.

  • By default, scripts generated by the Snapshot Agent for non-[!INCLUDEssNoVersion] Subscribers use non-quoted identifiers in the CREATE TABLE syntax. Therefore, a published table named 'test' is replicated as 'TEST'. To use the same case as the table in the publication database, use the -QuotedIdentifier parameter for the Distribution Agent. The -QuotedIdentifier parameter must also be used if published object names (such as tables, columns, and constraints) include spaces or words that are reserved words in the version of the database at the non-[!INCLUDEssNoVersion] Subscriber. For more information about this parameter, see Replication Distribution Agent.

  • The account under which the Distribution Agent runs must have read access to the install directory of the OLE DB provider.

  • By default for non-[!INCLUDEssNoVersion] Subscribers, the Distribution Agent uses a value of [(default destination)] for the subscription database (the -SubscriberDB parameter for the Distribution Agent):

  • If the [!INCLUDEssNoVersion] Distributor is running on a 64-bit platform, you must use the 64-bit version of the appropriate OLE DB provider.

  • Replication moves data in Unicode format regardless of the collation/code pages used on the Publisher and Subscriber. It is recommended that you choose a compatible collation/code page when replicating between Publishers and Subscribers.

  • If an article is added to or deleted from a publication, subscriptions to non-[!INCLUDEssNoVersion] Subscribers must be reinitialized.

  • The only constraints supported for all non-[!INCLUDEssNoVersion] Subscribers are: NULL, and NOT NULL. Primary key constraints are replicated as unique indexes.

  • The value NULL is treated differently by different databases, which affects how a blank value, an empty string, and a NULL are represented. This in turn affects the behavior of values inserted into columns with unique constraints defined. For example, Oracle allows multiple NULL values in a column that is considered unique, whereas [!INCLUDEssNoVersion] allows only a single NULL value in a unique column.

    An additional factor is how NULL values, empty strings, and blank values are treated when the column is defined as NOT NULL. For information about addressing this issue for Oracle Subscribers, see Oracle Subscribers.

  • Replication-related metadata (transaction sequence table) is not deleted from non-[!INCLUDEssNoVersion] subscribers when the subscription is removed.

Conforming to the Requirements of the Subscriber Database

  • Published schema and data must conform to the requirements of the database at the Subscriber. For example, if a non-[!INCLUDEssNoVersion] database has a smaller maximum row size than [!INCLUDEssNoVersion], you must ensure that the published schema and data do not exceed this size.

  • Tables replicated to non-[!INCLUDEssNoVersion] Subscribers will adopt the table naming conventions of the database at the Subscriber.

  • DDL is not supported for non-SQL Server Subscribers. For more information about schema changes, see Make Schema Changes on Publication Databases.

Replication Feature Support

  • [!INCLUDEssNoVersion] offers two types of subscriptions: push and pull. Non-[!INCLUDEssNoVersion] Subscribers must use push subscriptions, in which the Distribution Agent runs at the [!INCLUDEssNoVersion] Distributor.

  • [!INCLUDEssNoVersion] offers two snapshot formats: native bcp-mode and character-mode. Non-[!INCLUDEssNoVersion] Subscribers require character mode snapshots.

  • Non-[!INCLUDEssNoVersion] Subscribers cannot use immediate updating or queued updating subscriptions, or be nodes in a peer-to-peer topology.

  • Non-[!INCLUDEssNoVersion] Subscribers cannot be automatically initialized from a backup.

See Also

Heterogeneous Database Replication
Subscribe to Publications