Non-SQL Server Subscribers
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.
| 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):
- For Oracle, a server has at most one database, so it is not necessary to specify the database.
- For IBM DB2, the database is specified in the DB2 connection string. For more information, see How to: Create a Subscription for a Non-SQL Server Subscriber (SQL Server Management Studio) and How to: Create a Subscription for a Non-SQL Server Subscriber (Replication Transact-SQL Programming).
- 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. - SQL Server Management Studio does not support enabling SQL Server 2000 publications for non-SQL Server Subscribers. To enable a SQL Server 2000 publication for non-SQL Server Subscribers, use the Create Publication Wizard, which is available in SQL Server 2000 Enterprise Manager. In the Create Publication Wizard, on the Specify Subscriber Types page, select Heterogeneous data sources, such as Oracle or Microsoft Access.
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.
- Non-SQL Server Subscribers support only those schema changes that conform to the syntax of the database at the Subscriber. For more information about schema changes, see Making 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
Concepts
Heterogeneous Database Replication
Subscribing to Publications