Share via

  • Article

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

Transactional and snapshot replication support publishing data to non-SQL Server Subscribers. You can create push subscriptions to non-SQL Server Subscribers programmatically using replication stored procedures. For information about supported Subscriber platforms, see Non-SQL Server Subscribers.

Security noteSecurity Note

When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

To create a push subscription for a transactional or snapshot publication to a non-SQL Server Subscriber

  1. Install the most recent OLE DB provider for the non-SQL Server Subscriber at both the Publisher and Distributor. For the replication requirements for an OLE DB provider, see Non-SQL Server Subscribers, Oracle Subscribers, IBM DB2 Subscribers.

  2. At the Publisher on the publication database, verify that the publication supports non-SQL Server Subscribers by executing sp_helppublication (Transact-SQL).

    • If the value of enabled_for_het_sub is 1, non-SQL Server Subscribers are supported.

    • If the value of enabled_for_het_sub is 0, execute sp_changepublication (Transact-SQL), specifying enabled_for_het_sub for @property and true for @value.

      Note

      Before changing enabled_for_het_sub to true, you must drop any existing subscriptions to the publication. You cannot set enabled_for_het_sub to true when the publication also supports updating subscriptions. Changing enabled_for_het_sub will affect other publication properties. For more information, see Non-SQL Server Subscribers.

  3. At the Publisher on the publication database, execute sp_addsubscription (Transact-SQL). Specify @publication, @subscriber, a value of (default destination) for @destination_db, a value of push for @subscription_type, and a value of 3 for @subscriber_type (specifies an OLE DB provider).

  4. At the Publisher on the publication database, execute sp_addpushsubscription_agent (Transact-SQL). Specify the following:

    • The @subscriberand @publication parameters.

    • A value of (default destination) for @subscriber_db,

    • The properties of the non-SQL Server data source for @subscriber_provider, @subscriber_datasrc, @subscriber_location, @subscriber_provider_string, and @subscriber_catalog.

    • The Microsoft Windows credentials under which the Distribution Agent at the Distributor runs for @job_login and @job_password.

      Note

      Connections made using Windows Integrated Authentication always use the Windows credentials specified by @job_login and @job_password. The Distribution Agent always makes the local connection to the Distributor using Windows Integrated Authentication. By default, the agent will connect to the Subscriber using Windows Integrated Authentication.

    • A value of 0 for @subscriber_security_mode and the OLE DB provider login information for @subscriber_login and @subscriber_password.

    • A schedule for the Distribution Agent job for this subscription. For more information, see How to: Specify Synchronization Schedules (Replication Transact-SQL Programming).

    Security noteSecurity Note

     When creating a push subscription at a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server.