How to: Create a Publication from an Oracle Database (SQL Server Management Studio)
Create a snapshot or transactional publication from an Oracle Database with the New Publication Wizard. 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 Configuring an Oracle Publisher.
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:
- Enter the Oracle database instance name or select Browse for more in the Server instance combo box.
- 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. - 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 How to: Specify Data Type Mappings for an Oracle Publisher (SQL Server Management Studio).
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 Configuring 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.
See Also
Concepts
Configuring an Oracle Publisher
Publishing Data and Database Objects
Other Resources
How to: Create a Publication from an Oracle Database (Replication Transact-SQL Programming)