Replication between SQL Server 2012 and Oracle

Replication has 3 roles: Publication, Distribution and Subscription.
The source is from Oracle, and I want the data from Oracle push to SQL Server and not updatable.
Publication: Oracle
Distribution: SQL Server A
Subscriptions: SQL Server A ( I installedont the same Server/Instance), you can add/change it if you want.

Prerequisites:

1. Install the Oracle client software and OLE DB provider on the Microsoft SQL Server Distributor
2. One Oracle Database Connection name/password
3. Install a feature of replication of SQL Server
4. Design a shared folder and set up security (I’ll talk about it later)
5. You can do most of jobs on SQL SSMS

1) Install the Oracle Client software, download site

• Choose “64-bit ODAC 12c Release 2(12.1.0.1.2) for Windows x64 (282MB)
• unzipp it and install by default
• 
• The 3rd chart is important but able to be changed later
• 
• Then it will create a file called tnsnames.ora

Be aware the settings of “PROTOCOL”, HOST (xxx.domain.com), PORT (default is 1521) ,SID or Service Name (Oracle database name)
In order to test the connection is available, go to CMD type this command

SQLPLUS ORACLELOGINNAME/PASSWORD@DBName

if successful, it will show "SQL>" prefix.  If not, please look at this website

2) Install Distribution on SQL Server A







Create a new Distributor


Set up Snapshot folder, I really recommend use Shared folder and UNC path. Because if you use specific driver letter path. Oracle won’t know "C:\xxxx" or "D:\xxxx" on Distribution server







It will prompt the login windows:
Server Instance: Oracle server name/Dbname. Be careful is "/", not "\"


Then chose publisher type:
 


Then it will show the diagram below

You can add multiple Publishers, but Publisher Oracle can only have one Distribution

i will check “Generate a script ….” for saving record later



Specify the Saved path


Success



Then it will create a Distribution database and it belongs to one of System Databases



Look at the Linked servers, SQL server will create 2 Linked servers
One for connecting to Oracle, one for Replication Distribution used

 

3) Install Publication

Due to Publication role will be on Oracle side, so we need to create a new Oracle Publication instead of New Publication





Oracle Publishers should be added already once you finished the Oracle Login Windows Authentication. If not, please repeat the step above.

Then the step will be a little bit different from New Publication.
There are only two Publication types: Snapshot and Transaction, let’s add Transactional publication first

(You need a primary key for each tables on Oracle, if some of tables are not, you have to compromise using Snapshot Publication to fit your requirements)


Articles: it will take a while depends on how many data you have. The abstraction of the name is related to the names of Publication , Distribution that like the books.But it actually indicated "Tables"


Then choose your tables you want to replicate, and you can set up the properties or Data Mappings for these tables


Filter Table Rows, you can filter data to feed what the subcriptions need


Snapshot Agent:
Even if you choose Transaction Publications, it still needs to create a snapshot first to initialize the subscriptions. For this case, I set up both of options later due to I didn’t create the subscriptions yet


Agent Security:
This step will be a little tricky. For easy to set up (not recommend), you can choose SQL Agent Service Account to execute both of Snapshot and Log reader agent. (impersonate)
But also you need to add the account into the shared folder permission and folder permission

Next -> Next –> Next -> Success

As well as no we create a Snapshot publication for non-primary key tables.
The install steps are similar as creating Transactional Publication

4) Install Subscriptions

You can create it on the same SQL Server or on the other SQL server, just try it.
But the important is the folder permission


Choose distributor and Publications

Distribution Agent Location: PUSH or PULL? I won’t spend time to explain it, you can just read it on MSDN



Subscibers:
Create a new user database before you did it. Do not choose Distribution System Database
Distribution Agent Security, it impacts some of securities for SQL server. If you use Local SQL Agent Service account to execute the Distribution but the Subscriber is on the other Server. You would use another SQL Server Login account to connect to the Subscriber server.




Initialize Subscriptions:
Immediately, if it’s a brand new subscriber, you can do it. But it doesn’t mean you couldn’t do it for existing tables, but it will cause Table locked
At first synchronization, usually it will need a snapshot initialed first.


Done.

5) Troubleshooting

"The initial snapshot for publication xxxx is not yet available"

And the subsciber has not received data yet.

• Restart the SQL Agent Service
• Check Job log to see if any error messages
• Folder permission (most likely this issue), please add Agent Service account as Full control
• Stop all the last running replication jobs and Re-Initial Subscribers to kick the old snapshot out
• See the reports to look at Agents of Snapshot and Log reader agent

6) Extended reading

• Create a Publication from an Oracle Database
• Configure an Oracle Publisher