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

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.

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.

• 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'.

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."