Create a Publication from an Oracle Database

Article
06/30/2017

This topic describes how to create a publication from an Oracle database in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

In This Topic

Before you begin:

Prerequisites
To create a publication from an Oracle database, using:

SQL Server Management Studio

Transact-SQL

Before You Begin

Prerequisites

Before creating a publication, you must install Oracle software on the Microsoft SQL Server Distributor, and you must configure the Oracle database. For more information, see Configure an Oracle Publisher.

Using SQL Server Management Studio

Create a snapshot or transactional publication from an Oracle Database with the New Publication Wizard.

The first time you create a publication from an Oracle database, you must identify the Oracle Publisher at the SQL Server Distributor (you do not need to do this for subsequent publications from the same database.). Identifying the Oracle Publisher can be accomplished from the New Publication Wizard or the Distributor Properties - <Distributor> dialog box; this topic shows the Distributor Properties - <Distributor> dialog box.

To identify the Oracle Publisher at the SQL Server Distributor

In SQL Server Management Studio, connect to the SQL Server instance that the Oracle Publisher will use as a Distributor, and then expand the server node.
Right-click the Replication folder, and then click Distributor Properties.
On the Publishers page of the Distributor Properties - <Distributor> dialog box, click Add, and then click Add Oracle Publisher.
In the Connect to Server dialog box, click the Options button.
On the Login tab:
1. Enter the Oracle database instance name or select Browse for more in the Server instance combo box.
2. Select Oracle Standard Authentication (recommended) or Windows Authentication.
  
  If you select 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.
3. If you select Oracle Standard Authentication, enter the login and password of the replication administrative user schema you created on the Oracle Publisher during configuration.
On the Connection Properties tab, select a Publisher type of Gateway or Complete.

The Complete option is designed to provide snapshot and transactional publications with the complete set of supported features for Oracle publishing. The Gateway option provides specific design optimizations to improve performance for cases where replication serves as a gateway between systems. The Gateway option cannot be used if you plan to publish the same table in multiple transactional publications. A table can appear in at most one transactional publication and any number of snapshot publications if you select Gateway.
Click Connect, which creates a connection to the Oracle Publisher and configures it for replication. The Connect to Server dialog box closes and you are returned to the Distributor Properties - <Distributor> dialog box.

Note

If there are any problems with the network configuration, you will receive an error at this point. If you experience problems connecting to the Oracle database, see the section "The SQL Server Distributor cannot connect to the Oracle database instance" in Troubleshooting Oracle Publishers.
Click OK.

To create a publication from an Oracle database

Connect to the SQL Server instance that the Oracle Publisher will use as a Distributor, and then expand the server node.
Expand the Replication folder.
Right-click the Local Publications folder, and then click New Oracle Publication.
On the Oracle Publisher page of the New Publication Wizard, select the Oracle Publisher. If the Oracle Publisher is not displayed, click Add Oracle Publisher, which takes you through the steps from the previous procedure.
On the Publication Type page, select Snapshot publication or Transactional publication.
On the Articles page, select the database objects you want to publish.

Optionally, filter out table columns by expanding a table and then clearing the checkbox for one or more columns. Click Article Properties to view and modify article properties and to specify alternative data type mappings if necessary. For more information about data type mappings, see Specify Data Type Mappings for an Oracle Publisher.
On the Filter Table Rows page, optionally apply filters to publish a subset of data from one or more tables.
On the Snapshot Agent page, clear Create a snapshot immediately only if you have created all objects and added all required data in the subscription database.
On the Agent Security page, specify credentials for the Snapshot Agent (for all publications) and the Log Reader Agent (for transactional publications). The agents run and make connections to the SQL Server Distributor using the context of the Microsoft Windows account you specify. The agents make connections to the Oracle database using the context of the account you specified as the replication administrative user schema. For more information, see Configure an Oracle Publisher.

For more information about the permissions required by each agent, see Replication Agent Security Model and Replication Security Best Practices.
On the Wizard Actions page, optionally script the publication. For more information, see Scripting Replication.
On the Complete the Wizard page, specify a name for the publication.

Using Transact-SQL

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

Configure the Oracle database as a Publisher. For more information, see Configure an Oracle Publisher.
If a remote Distributor does not exist, configure the remote Distributor. For more information, see Configure Publishing and Distribution.
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.
  
  Important
  
  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..
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.
At the Distributor on the distribution database, execute sp_addpublication (Transact-SQL) to create the publication. For more information, see Create a Publication.
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.

Share via