Share via

  • Article

How to: Create a Publication from an Oracle Database (Replication Transact-SQL Programming)

After the Oracle database has been configured as a Publisher, you can create a transactional or snapshot publication the same way that you would from a Microsoft SQL Server Publisher, by using system stored procedures.

To create an Oracle Publication

  1. Configure the Oracle database as a Publisher. For more information, see Configuring an Oracle Publisher.

  2. If a remote Distributor does not exist, configure the remote Distributor. For more information, see How to: Configure Publishing and Distribution (Replication Transact-SQL Programming).

  3. At the remote Distributor that the Oracle Publisher will use, execute sp_adddistpublisher (Transact-SQL). Specify the Transparent Network Substrate (TNS) name of the Oracle database instance for @publisher and a value of ORACLE or ORACLE GATEWAY for @publisher_type. Specify the security mode used when connecting from the Oracle Publisher to the remote SQL Server Distributor as one of the following:

    • To use Oracle Standard Authentication, the default, specify a value of 0 for @security_mode, the login of the replication administrative user schema you created on the Oracle Publisher during configuration for @login, and the password for @password.

      Security noteSecurity Note

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

    • To use Windows Authentication, specify a value of 1 for @security_mode.

      Note

      To use Windows Authentication, the Oracle server must be configured to allow connections using Windows credentials (for more information, see the Oracle documentation); and you must be currently logged in with the same Microsoft Windows account you specified for the replication administrative user schema..

  4. Create a Log Reader Agent job for the publication database.

    • If you are unsure whether a Log Reader Agent job exists for a published database, execute sp_helplogreader_agent (Transact-SQL) at the Distributor used by the Oracle Publisher on the distribution database. Specify the name of the Oracle Publisher for @publisher. If the result set is empty, then a Log Reader Agent job must be created.

    • If a Log Reader Agent job already exists for the publication database, proceed to step 5.

    • At the Distributor used by the Oracle Publisher on the distribution database, execute sp_addlogreader_agent (Transact-SQL). Specify the Windows credentials under which the agent runs for @job_login and @job_password.

      Note

      The @job_login parameter must match the login supplied in step 3. Do not supply publisher security information. The Log Reader agent connects to the Publisher using the security information provided in step 3.

  5. At the Distributor on the distribution database, execute sp_addpublication (Transact-SQL) to create the publication. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

  6. At the Distributor on the distribution database, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 4 for @publication and the Windows credentials under which the Snapshot Agent runs for @job_name and @password. To use Oracle Standard Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the Oracle login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.