How to: Create a Subscription for a Non-SQL Server Subscriber (SQL Server Management Studio)

  • Article

Transactional replication and snapshot replication support publishing data to non-Microsoft SQL Server Subscribers. To create a subscription for a non-SQL Server Subscriber:

  1. Install and configure the appropriate client software and OLE DB provider(s) on the SQL Server Distributor. For more information, see Oracle Subscribers and IBM DB2 Subscribers.
  2. Create a publication using the New Publication Wizard. For more information about creating publications, see How to: Create a Publication and Define Articles (SQL Server Management Studio) and How to: Create a Publication from an Oracle Database (SQL Server Management Studio). Specify the following options in the New Publication Wizard:
    • On the Publication Type page, select Snapshot publication or Transactional publication.
    • On the Snapshot Agent page, clear Create a snapshot immediately.
      You create the snapshot after the publication is enabled for non-SQL Server Subscribers to ensure that the Snapshot Agent generates a snapshot and initialization scripts that are suitable for non-SQL Server Subscribers.
  3. Enable the publication for non-SQL Server Subscribers using the Publication Properties - <PublicationName> dialog box. This topic provides more information about this step.
  4. Create a subscription using the New Subscription Wizard. This topic provides more information about this step.
  5. (Optional) Change the pre_creation_cmd article property to retain tables at the Subscriber. This topic provides more information about this step.
  6. Generate a snapshot for the publication. This topic provides more information about this step.
  7. Synchronize the subscription. For more information, see How to: Synchronize a Push Subscription (SQL Server Management Studio).

Note

This process applies to Publishers running Microsoft SQL Server 2005 and later.

To enable a publication for non-SQL Server Subscribers

  1. Connect to the Publisher in SQL Server Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Publications folder.

  3. Right-click the publication, and then click Properties.

  4. On the Subscription Options page, select a value of True for the option Allow non-SQL Server Subscribers. Selecting this option changes a number of properties so that the publication is compatible with non-SQL Server Subscribers.

    Note

    Selecting True sets the value of the pre_creation_cmd article property to 'drop'. This setting specifies that replication should drop a table at the Subscriber if it matches the name of the table in the article. If you have existing tables at the Subscriber that you want to keep, use the sp_changearticle stored procedure for each article; specify a value 'none' for pre_creation_cmd: sp_changearticle @publication= 'MyPublication', @article= 'MyArticle', @property='pre_creation_cmd', @value='none'.

  5. Click OK. You will be prompted to create a new snapshot for the publication. If you do not want to create one at this time, use the steps described in the next "how to" procedure at a later time.

To create a subscription for a non-SQL Server Subscriber

  1. Expand the Replication folder, and then expand the Local Publications folder.

  2. Right-click the appropriate publication, and then click New Subscriptions.

  3. On the Distribution Agent Location page, ensure Run all agents at the Distributor is selected. Non-SQL Server Subscribers do not support running agents at the Subscriber.

  4. On the Subscribers page, click Add Subscriber and then click Add Non-SQL Server Subscriber.

  5. In the Add Non-SQL Server Subscriber dialog box, select the type of Subscriber.

  6. Enter a value in Data source name:

    • For Oracle, this is the transparent network substrate (TNS) name you configured.
    • For IBM, this can be any name. It is typical to specify the network address of the Subscriber.

    The data source name entered in this step and the credentials specified in step 9 are not validated by this wizard. They are not used by replication until the Distribution Agent runs for the subscription. Ensure that all values have been tested by connecting to the Subscriber using a client tool (such as sqlplus for Oracle). For more information, see Oracle Subscribers and IBM DB2 Subscribers.

  7. Click OK. On the Subscribers page of the wizard, the Subscriber is now displayed in the Subscriber column with a read-only (default destination) in the Subscription Database column:

    • 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 Initial Catalog property of the DB2 connection string, which can be entered in the Additional connection options field described later in this process.

  8. On the Distribution Agent Security page, click the properties button (…) next to the Subscriber to access the Distribution Agent Security dialog box.

  9. In the Distribution Agent Security dialog box:

    • In the Process account, Password, and Confirm password fields, enter the Microsoft Windows account and password under which the Distribution Agent should run and make local connections to the Distributor.
      The account requires these minimum permissions: member of the db_owner fixed database role in the distribution database; member of the publication access list (PAL); read permissions on the snapshot share; and read permission on the install directory of the OLE DB provider. For more information about the PAL, see Securing the Publisher.

    • Under Connect to the Subscriber, in the Login, Password, and Confirm password fields, enter the login and password that should be used to connect to the Subscriber. This login should already be configured and should have sufficient permissions to create objects in the subscription database.

    • In the Additional connection options field, specify any connection options for the Subscriber in the form of a connection string (Oracle does not require additional options). Each option should be separated by a semi-colon. The following is an example of a DB2 connection string (line breaks are for readability):

      Provider=DB2OLEDB;Initial Catalog=MY_SUBSCRIBER_DB;Network Transport Library=TCP;Host CCSID=1252;
PC Code Page=1252;Network Address=MY_SUBSCRIBER;Network Port=50000;Package Collection=MY_PKGCOL;
Default Schema=MY_SCHEMA;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/NT;
Persist Security Info=False;Connection Pooling=True;

      Most of the options in the string are specific to the DB2 server you are configuring, but the Process Binary as Character option should always be set to False. A value is required for the Initial Catalog option to identify the subscription database.

  10. On the Synchronization Schedule page, select a schedule for the Distribution Agent from the Agent Schedule menu (the schedule is typically Run continuously).

  11. On the Initialize Subscriptions page, specify whether the subscription should be initialized and, if so, when it should be initialized:

    • Clear Initialize only if you have created all objects and added all required data in the subscription database.
    • Select Immediately from the drop-down list in the Initialize When column to have the Distribution Agent transfer snapshot files to the Subscriber after this wizard is completed. Select At first synchronization to have the agent transfer the files the next time it is scheduled to run.

  12. On the Wizard Actions page, optionally script the subscription. For more information, see Scripting Replication.

To retain tables at the Subscriber

  • By default, enabling a publication for non-SQL Server Subscribers sets the value of the pre_creation_cmd article property to 'drop'. This setting specifies that replication should drop a table at the Subscriber if it matches the name of the table in the article. If you have existing tables at the Subscriber that you want to keep, use the sp_changearticle stored procedure for each article; specify a value 'none' for pre_creation_cmd. sp_changearticle @publication= 'MyPublication', @article= 'MyArticle', @property='pre_creation_cmd', @value='none'.

To generate a snapshot for the publication

  1. Expand the Replication folder, and then expand the Local Publications folder.

  2. Right-click the publication, and then click View Snapshot Agent Status.

  3. In the View Snapshot Agent Status - <Publication> dialog box, click Start.

When the Snapshot Agent finishes generating the snapshot, a message is displayed, such as "[100%] A snapshot of 17 article(s) was generated."

See Also

Concepts

Non-SQL Server Subscribers

Other Resources

How to: Create a Subscription for a Non-SQL Server Subscriber (Replication Transact-SQL Programming)

Help and Information

Getting SQL Server 2005 Assistance