How to set up an SQL 2008 x64 Replication with Oracle Publisher
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:
- Configuring an Oracle Publisher
- How to: Create a Publication from an Oracle Database (SQL Server Management Studio)
2. Install “Oracle 64-bit ODAC 11.2 Release 3 (18.104.22.168.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 :
- 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.