jump to navigation

Oracle to SQL Server Replication-Design considerations May 21, 2013

Posted by msrviking in Configuration, Data Integration, Design, Heterogeneous, Integration, Replication.
Tags: , , , , , , ,
add a comment

I am thinking I have covered all and best of the points for one to configure a transactional replication between Oracle and SQL Server in all my previous posts. In this post I would like to give my 2 cents on design considerations and limitations of Oracle publishers that I had to keep in mind during this exercise. There is a neat list of other points published in this Technet article.

  1. Configuration of Oracle Publisher with options

    Per the above article I learnt that the Oracle publishing can be done using the Oracle Gateway and Oracle Complete options. Few things that make difference these two options is that Oracle Gateway provides better performance but doesn’t let multi-transactional publications of a single to-be or published table. Whereas in the case of Oracle Complete option the table can be published in several publications, but lesser performance.

    I tried digging in Google to see on how these two options work as described, because I didn’t try on the setup I built to find the difference. I didn’t find any reliable sources that talk about difference in detail but one of the posts by Richard Douglas (Editor in Chief of SQL ServerPedia) gives a short description “Gateway is for high performance replication solutions. Complete is when you want to transform your data or to do row filtering”. I might have to try and see what it means in here.

    I remember seeing row filtering in a transaction replication using the option of Oracle Gateway, and I am yet to find out on how this is different when Oracle Complete is chosen. For now I shall park for future tries on this limitation and share my findings.

  2. It’s quite obvious with this point (supports few objects) because the similarities that Oracle and SQL Server have at object level implementation are few. So,

    What are the objects that are supported?

    1. Tables
    2. Index-organized tables
    3. Indexes
    4. Materialized views (as tables)

    What are those objects that cannot be replicated, but can exist on published tables?

    1. Domain-based indexes
    2. Function-based indexes
    3. Defaults
    4. Check constraints
    5. Foreign keys
    6. Storage options (tablespaces, clusters, etc.)

    What are the objects that cannot be replicated at all?

    1. Nested tables
    2. Views
    3. Packages, package bodies, procedures, and triggers
    4. Queues
    5. Sequences
    6. Synonyms

    Okay with this list we know what can be replicated, what cannot be replicated? I picked the list from the above post, but was hunting to find out why we can’t replicate these objects. One simple reason that keeps coming to my mind is that “some of the above objects in the ‘cannot’ list don’t have equivalents in SQL Server”. Okay, so aren’t there workarounds for these objects? For example Packages have bundle of programmed objects like packaged variables, cursors and few others which have to be converted through a specific process, and this specific process can’t be through Replication. That exercise could be migration of objects from Oracle to SQL Server, and surely can’t be part of Replication topology. Does this apply for simple objects also? I don’t have any technical reason to explain for this too so concluding it with my 2 cents. Meanwhile I shall see if I can find better “reasons”.

    Here is a link from Microsoft on what it all means to convert some of the above listed objects. This was one of the reasons that I helped me to conclude logically that this is part of migration from Oracle to SQL Server and can’t be part of Replication at a conceptual or architectural level.

Since my setup was to test the new Oracle Publisher I didn’t have to keep tab on other design considerations, but I am sure I will have to deal with others too when this gets on to full deployment mode than a POC.

I hope this short post helps you folks out there, and happy reading.

The usual ask – please feel free to share your thoughts or comments.

Cheers!

Advertisements

Oracle to SQL Server Replication – Configure Transactional Replication w/ Pull Subscription May 16, 2013

Posted by msrviking in Configuration, Data Integration, Heterogeneous, Integration, Replication.
Tags: , , , , , ,
add a comment

This post is going to be short because I am thinking I shouldn’t give my bit of explanation on how to configure a Transactional Replication in SQL Server against Oracle Publisher because there is no difference that I could share.

Instead let me share few links that are well explained by few other SQL Server experts on how to configure SQL Server Transactional Replication except that you have to pick already configured Oracle Publisher in the publication list.

So here are those for your reference and as guidance.

The last link is the source for me to work on the design, implementation and troubleshooting of Oracle Publisher – SQL Server Transaction Replication w/ Pull Subscription. In few of my concluding posts of this series I shall talk about the design, deployment and issues I had faced.

Happy reading.

Cheers!

Oracle to SQL Server Replication-Configure Oracle Publisher May 15, 2013

Posted by msrviking in Configuration, Data Integration, Heterogeneous, Integration, Replication.
Tags: , , , , ,
add a comment

In today’s post as part of this series I will share few steps I diligently followed to configure Oracle publication. As first step certain pre-requisites have to be addressed on SQL Server box and the Oracle instance.

1. Installing Oracle client and OLE DB provider on SQL Server system

I am not too much of an Oracle guy, but have fair deal of experience to identify annoying issues that rise from connecting between SQL Server and Oracle. So make sure you have the Oracle client and OLE DB provider installed appropriately on the SQL Server box.

I didn’t play much with changing environment variable paths, tnsnames.ora, so I let the installations happen all by default path, and the end I wanted SQLPLUS let me connect to the Oracle instance from the box.

So did it happen with me? Yes, after a bit of tussle I did manage to have Oracle connected and that was the first relieving moment only because I knew if I cross this first major hurdle others (configuring publisher, replication) would be easy steps. Here is the snapshot of the SQLPLUS that gave me a bit of smile, and the next one was in waiting (Oracle Publication).


There is one more tiny part to be done on SQL Server box, is to set read and execute permissions to the Oracle (client and provider) folders against the service account that runs SQL Server, and for obvious known reasons.

2. Create Replication administrative user, schema

I used the commands from this blog post and the post has decent inline comments on what each command does and what does it expect from you when you run this on Oracle instance.

Remember, that you logon using sysdba privileges (per recommendation), and in my case I managed to get hands on the sys login of the Oracle instance. The script has bunch of commands that needs to be executed on SQL Server instance too and that’s to configure Oracle Publication, Distributor and Replication with Pull Subscription.

I didn’t run all those commands because I wanted to try through the GUI way of configuring the publication. So feel free to pick your path, however make sure you are following each step until configuring the Replication administrative user, creating a user schema, and grant appropriate permissions. If you miss any you might have trouble in making the Oracle side of publication work properly and I am sure you will be clueless for a little while and may revisit the steps you have executed.

This step was not difficult and upon completion I know I was getting into the world of troubleshooting SQL Server to Oracle connectivity.

I shall paste the steps – commands for your quick reference and are exact copy from the above link.

  • Logon to ORACLE database as SYSDBA and follow the below steps
    • Create Tablespace to Store SQL Server Replication Metadata Objects

<Sample Script>

Create Tablespace <SQL_REPLUSER_TBL>

datafile ‘<Path>\<SQL_REPLUSER_TBL>.dbf’
size 512M autoextend on
next 512M maxsize
unlimited
;

</Sample Script>

  • Create SQLReplUser on the ORACLE Database and make the tablespace created in step 1 above as the user default tablespace

<Sample Script>

–***************************************************************************

— Copyright (c) 2003 Microsoft Corporation

— File:

— oracleadmin.sql

— Purpose:

— PL/SQL script to create a database user with the required permissions

— to administer SQL Server publishing for an Oracle database.

— &&ReplLogin == Replication user login

— &&ReplPassword == Replication user password

— &&DefaultTablespace == Tablespace that will serve as the default tablespace for the

— replication user.

— The replication user will be authorized to allocate UNLIMITED space

— on the default tablespace, which must already exist.

— Notes:

— This script must be run from an Oracle login having the authorization to

— create a new user and grant unlimited tablespace on any existing tablespace. The

— login must also be able to grant to the newly created login the following authorizations:


— create public synonym

— drop public synonym

— create sequence

— create procedure

— create session

— create table

— create view


— Additionally, the following properties are also required for transactional publications.


— create any trigger


— All of the privileges may be granted through a role, with the exception

— of create table, create view, and create any trigger. These must be

— granted explicitly to the replication user login. In the script, all grants are

— granted explicitly to the replication user.


— In addition to these general grants, a table owner must explicitly grant select

— authorization to the replication user on a table before the table can be published.


–***************************************************************************

ACCEPT
ReplLogin
CHAR
PROMPT
‘User to create for replication: ‘;

ACCEPT
ReplPassword
CHAR
PROMPT
‘Replication user passsword: ‘
HIDE;

ACCEPT
DefaultTableSpace
CHAR
DEFAULT
‘SYSTEM’
PROMPT
‘Default tablespace: ‘;

— Create the replication user account

CREATE
USER
&&ReplLogin
IDENTIFIED
BY &&ReplPassword
DEFAULT
TABLESPACE &&DefaultTablespace
QUOTA
UNLIMITED
ON &&DefaultTablespace;

— It is recommended that only the required grants be granted to this user.


— The following 5 privileges are granted explicitly, but could be granted through a role.

GRANT
CREATE
PUBLIC
SYNONYM
TO
&&ReplLogin;

GRANT
DROP
PUBLIC
SYNONYM
TO
&&ReplLogin;

GRANT
CREATE
SEQUENCE
TO
&&ReplLogin;

GRANT
CREATE
PROCEDURE
TO
&&ReplLogin;

GRANT
CREATE
SESSION
TO
&&ReplLogin;

— The following privileges must be granted explicitly to the replication user.

GRANT
CREATE
TABLE
TO
&&ReplLogin;

GRANT
CREATE
VIEW
TO
&&ReplLogin;

— The replication user login needs to be able to create a tracking trigger on any table that is

— to be published in a transactional publication. The CREATE ANY privilege is used to

— obtain the authorization to create these triggers. To replicate a table, the table

— owner must additionally explicitly grant select authorization on the table to the

— replication user.


— NOTE: CREATE ANY TRIGGER is not required for snapshot publications.

GRANT
CREATE
ANY
TRIGGER
TO
&&ReplLogin;

EXIT

</Sample Script>

  • Grant Select privilege on table you would like to publisher to <SQLReplUser> created in the step (b) above

<Sample Script>

–Execute the below statement from SQL*PLus

Grant
Select
on
<Owner>.<Table> to <SQLReplUser>;

</Sample Script>

3. Configuring Oracle Publication

This is the one I did multiple times, and when I say multiple times it’s for these reasons

  • Connecting to Oracle instance is very demanding process and anytime it could bounce back as a surprise failure although the instructions are followed to configure the Oracle client or OLE DB providers
  • The failed connectivity errors raised by Oracle through OLE DB provider will be gobbled by SQL Server. What does that mean? If there is logon failure, Oracle instance /service id resolution failure, or user schema access issues – all these have one type of error although there are bunch defined on MSDN.

So watch out on what type of error you could be getting and what reason. It’s almost like reading the errors through your inner sense, huh. That was a tough ask so I requested a team mate to build a small .NET code using the OLE DB provider and anything I do on the wizard I imitated on the code and I was able to find the cause of errors and made things work quicker. Thanks to the team mate for pitching in on right time to fix some “hidden errors”.

Here are the snapshots that would you see when configuring Oracle Publication.

  1. Configure Distributor


    After few screens which are default for configuring a distributor with any type of replication you will have to end with this step at the end. I opted to have SQL Server box which was supposedly to be subscriber as distributor.


  2. Configure Oracle Publication




    From here you would see a configured Oracle Publisher with the name that is mentioned as part of Server Instance. This is one of the most important step which confirms that the Oracle Publication is configured on SQL Server. I was relieved after configuring the publisher, and this didn’t come that easy at the first attempt.

    I hope all these help for configuring the Oracle Publisher, and the next step would be a Transactional Replication with a Pull Subscription. I shall share configuring Transactional Replication details against the Oracle publication in the upcoming posts, however there is nothing new once the publisher is done and is similar with that of SQL Server publication.

    I am also intending to share some important tips on design considerations, troubleshooting and performance of Oracle Publication.

    Until then, happy reading.

    Cheers!