Share via

Non-SQL Server Subscribers

  • Article

The following non-SQL Server 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 SSIS.

Caution

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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

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-SQL Server Subscribers:

General Considerations

  • Replication supports publishing tables and indexed views as tables to non-SQL Server 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-SQL Server Subscribers, whereas for Microsoft SQL Server Subscribers, it is set to the owner of the corresponding object in the publication database.

  • If a publication will have SQL Server Subscribers and non-SQL Server Subscribers, the publication must be enabled for non-SQL Server Subscribers before any subscriptions to SQL Server Subscribers are created.

  • By default, scripts generated by the Snapshot Agent for non-SQL Server 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-SQL Server 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-SQL Server Subscribers, the Distribution Agent uses a value of [(default destination)] for the subscription database (the -SubscriberDB parameter for the Distribution Agent):

  • If the SQL Server 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-SQL Server Subscribers must be reinitialized.

  • The only constraints supported for all non-SQL Server 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 SQL Server 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-SQL Server 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-SQL Server database has a smaller maximum row size than SQL Server, you must ensure that the published schema and data do not exceed this size.

  • Tables replicated to non-SQL Server 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

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

  • SQL Server offers two snapshot formats: native bcp-mode and character-mode. Non-SQL Server Subscribers require character mode snapshots.

  • Non-SQL Server Subscribers cannot use immediate updating or queued updating subscriptions, or be nodes in a peer-to-peer topology.

  • Non-SQL Server Subscribers cannot be automatically initialized from a backup.

See Also

Heterogeneous Database Replication
Subscribe to Publications