Skip to content

How to set up an SQL 2008 x64 Replication with Oracle Publisher

January 20, 2011

Here’s a summary of my experience to set up a replication in SQL Server 2008 R2 64-bit with Oracle database as a publisher. I went through up to the point of setting up Oracle Publisher successfully. Then I stopped after realizing our Oracle ERP database tables didn’t have primary keys (can you believe it?) and MS SQL Server’s Transactional Replication cannot be set up for tables with no primary key. Too bad.

1. Read these two MSDN articles before starting:

2. Install “Oracle 64-bit ODAC 11.2 Release 3 (11.2.0.2.1) for Windows x64  [Released December 28, 2010]“. See my other post about installation detail and possible error/solution. 

3. Configure Oracle creating a new schema ‘Replicator’ using SQL*PLUS and this script from MSDN.

4. Check the connection in SQL*PLUS using the Oracle replication account :

 SqlPlus OracleSchema/Password@OracleServerTNSName 

5. Follow “How to: Create a Publication from an Oracle Database (SQL Server Management Studio)

  • During the wizard, it will warn you to put a network share name for replication data folder path. No need to stop the wizard. Open a Windows Explorer and set up a sharing alias for the replication data folder. eg) Share name “\\SQL2008ServerName\repldata”  for “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData” if you haven’t change the default replication data folder.
  • You may get an error “Unable to connect Oracle database server ‘OracleTNSName’ using the Oracle OLEDB provider OraOLEDB.oracle. For addition information, see SQL Server Error 21626 in Troubleshooting Oracle Publishers in SQL Server Books Online. (Microsoft SQL Server, Error:  21626)” if you haven’t follow the step I mentioned in the other post when you install ODAC components in SQL Server. In summary, the solution for this error was to add an aditional folder structure like “ORACLE_HOME\Network\Admin” and add two TNS config files (sqlnet.ora,tnsnames.ora) into that folder. 

Good luck.

Advertisements
One Comment leave one →
  1. November 27, 2011 4:16 AM

    Greate post. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: