Monday, November 5, 2012

SQL Saturday 174 - Follow Up Oracle Transactional Replication Answers

Thank you to everyone that attended my session on Conquering Reporting by Scaling Out SQL Server. It was a packed room and thank you especially for the high marks on the evaluations. It was my first time presenting in the first session slot and was at maximum caffeine slope on the curve during the session ;-)

There were two questions related to Oracle and Transactional Replication.

Questions:

1. Are the tables locked in Oracle for Publishing as they are in SQL Server when using an Oracle Publisher?

2. Can you have an Oracle database as the subscriber?

Great questions. I tried to setup an Oracle publisher once many years ago and the Oracle DBA team did not want to do it because it required a bunch of extra objects and instead wanted to use a third party tool called Data Mirror. That doesn't answer the questions though so here is what I found out.

It is important to note that Oracle Publishers are being deprecated from SQL Server in a future release. You can create an Oracle Publisher from SQL 2005 to SQL 2012 using Oracle 9i and above. SQL Server wants folks to use SQL Server Integration Services (SSIS) and Change Data Capture (CDC) as the methods going forward from what I've read.

Answers:

1. No they are not locked.  DDL changes can be done to Oracle Publisher tables at will, but they will not show up at the subscriber unless the publication is reinitialized. Further, Oracle Publishers are setup different with triggers created on Oracle tables then other tables that capture the changes that log the changes and get pushed to the distributor.  

References:
http://msdn.microsoft.com/en-us/library/ms152499.aspx  (Info on Oracle & Transaction Replication)


2. Yes you can have an Oracle database as a subscriber. You cannot have pull subscriptions you can only have push subscriptions.

References:


Great questions, and sorry for the delay in posting the answers.

No comments:

Post a Comment