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 data synchronization–What are the other ways? May 17, 2013

Posted by msrviking in Architecture, Configuration, Data Integration, Heterogeneous, Integration, Integration Services, Replication.
Tags: , , , , , , , ,
2 comments

In this series I have been talking about environment setup, configuring the box with appropriate drivers, setting up Oracle Publisher and then short post on Transactional Replication. While doing this I had my bit of issues in setting and configuring the whole workflow so a parallel thread was running in my head asking only one question – “Is this the only way to synchronize data from Oracle?”.

The only one option which was looking viable was using SSIS, and with SSIS 2012 lot more features have come in to help the situation. I haven’t worked on using SSIS – CDC for Oracle per below links but intend to do it sometime.

Here are the few links I found when I Googled around on this topic,

1. Microsoft SQL Server 2012 Change Data Capture for Oracle by Attunity Topics (http://social.technet.microsoft.com/wiki/contents/articles/7644.microsoft-sql-server-2012-change-data-capture-for-oracle-by-attunity-topics.aspx)

2. CDC for Oracle in SQL Server 2012 (http://blogs.msdn.com/b/mattm/archive/2012/03/26/cdc-for-oracle-in-sql-server-2012.aspx)

3. CDC in SSIS for SQL Server 2012 (http://www.mattmasson.com/2011/12/cdc-in-ssis-for-sql-server-2012-2/)

4. Installing Microsoft SQL Server 2012 Change Data Capture for Oracle by Attunity (http://social.technet.microsoft.com/wiki/contents/articles/7647.installing-microsoft-sql-server-2012-change-data-capture-for-oracle-by-attunity.aspx)

5. Change Data Capture (CDC) for Oracle video now available (http://www.mattmasson.com/2012/09/change-data-capture-cdc-for-oracle-video-now-available/)

6. SQL Server 2012 CDC for Oracle – a Review of One Implementation (http://blogs.msdn.com/b/dfurman/archive/2013/04/15/sql-server-2012-cdc-for-oracle-a-review-of-one-implementation.aspx)

7. Troubleshoot CDC Instance Errors in Microsoft Change Data Capture for Oracle by Attunity (http://social.technet.microsoft.com/wiki/contents/articles/7642.troubleshoot-cdc-instance-errors-in-microsoft-change-data-capture-for-oracle-by-attunity.aspx)

Although I say that SSIS is the best option and yet to be tested I wanted to list the differences of implementation using Replication and SSIS. So I built a quick comparison table between Replication and SSIS from perspective of DDL and Performance to start, and this is work-in-progress. As of now I am unable to judge the timeline I should tie to close this table of comparison with other factors included. Nevertheless I am thinking this should help you folks too, just in case.

Feature Category

Feature

SSIS

Repli-cation

Notes

DDL /Schema Changes

– New column addition

– Change in mapped (source) column name

– Change in data type of source column name

– Drop column in the source table

Yes (customized coding and SMO)

No

– SSIS – SMO based programming can help in detecting changes at the source and help in remapping in destination

– Replication – Subscriber has to be dropped /deleted, Publisher should add new table or regenerate the snapshot in case of Oracle publisher

Performance

– On source table or system

Impacted

Impacted

– Replication – Triggers are created on the published tables to track data change at row level. Such triggers could be overhead to Oracle published tables. Triggers are created by SQL Server replication to capture data changes

– SSIS – the impact on the performance on the source table is lesser comparatively to replication since no triggers are created for tracking the changes of data. This could be achieved using CDC with SSIS 2012 for Oracle

– On destination table or system

Impacted

Impacted

– Replication – in case of large data changes (inserts and updates) transactional replication could cause performance bottleneck on the destination /subscriber tables because of DML statements cause lock escalation on the tables. Another option for large data changes is to use snapshot replication

– SSIS – the impact is lesser and can be controlled by batch operations

Synchro

nization

– Real-time

Supported

Supported

– Replication – transactional replication helps in achieving the real-time data synch up between Oracle and SQL Server however if the data changes are large then this could impact the performance. There is very minimal latency of data synchronization

– SSIS – with CDC for Oracle using SSIS 2012, real-time synchronization is achievable without any performance overhead, since no triggers are created on Oracle tables, and the changes in data doesn’t need any configuration or maintenance or setup issues because of the capabilities of CDC to create change tables in SQL Server. The data captured as part of changes is applied by SQL Server through the CDC tables

Please feel free to share your thoughts or comments. Happy reading.

Cheers!

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!

CDIs-Non Functional Requirement-Utilization Benchmarks May 16, 2013

Posted by msrviking in Architecture, Business Intelligence, Data Integration, Integration.
Tags: , , , , , , ,
add a comment

In this post I shall share my thoughts on how these questions would help in designing system to meet the response time, and performance. Some of these are about the history of the existing systems and few are about the future requirements. Lot of English, eh, so straight message is that answers to these questions will help me in gathering “baselines and benchmarks” of the past and future. And once I get to know I should have

  1. A system designed that performs with low latency (quick response time) for any operation
  2. Built an infrastructure (especially servers) that would meet future performance requirements

Let me group these questions based on purpose and tag with some key words, for easy understanding.

Q# Question Purpose – Keywords
1 How many rows are created every day in transactional DBs? To get current baselines on how many rows are generated from business transactions – row size, storage space, disk size, CPU size, computation power
2 What is the size of data that is generated across LOBs? To get current baselines on how many rows are generated from business transactions per line of business – row size, storage space, disk size
3 What is the agreeable processing time of data in data hub To understand future benchmarks of the data hub – CPU sizing
4 How stale can the reporting data be? To understand future benchmarks of the data hub – storage sizing
5 How much of the processed data and data used for reporting has to be archived /purged?
To understand future benchmarks on how the archived data would be used in reporting system – server sizing, storage sizing
6 How many years of data have to be archived in the system?
To understand future benchmarks on how the archived data would be used in reporting system – server sizing, storage sizing
7 How many years of yester year’s data have to be processed? To understand future benchmarks on how the archived data would be used in reporting system – server sizing, storage sizing, CPU sizing, RAM sizing
8 What will be year on year growth of data in the transactional source system?
To understand current baselines and future benchmarks on the data that needs to be processed – server sizing, storage sizing, CPU sizing, RAM sizing
9 What could be the other sources of data that could be added during a period of 1-2 years, and how much of these data sources could provide data to data hub?
To understand current baselines and future benchmarks on the data that needs to be processed – server sizing, storage sizing, CPU sizing, RAM sizing
10 What are the agreeable database system downtime hours? To understand future benchmarks on how quick the data processing should complete – CPU sizing, RAM sizing
11 What is the possible backup and recovery time required for the Data Hub and Reporting system?
To understand on how quickly should the whole data hub and BI system be back up and running – servers sizing, storage sizing, CPU sizing, RAM sizing
12 How many users will be added year on year for the reporting system, and what are the types of users?
To understand future benchmarks on how the reporting and data hub system resources will be utilized – servers sizing, storage sizing, CPU sizing, RAM sizing
13 Are there any other external systems that would require the data from data hub?
To understand current baselines of data processing and BI systems – server sizing
14 How many rows of the transactional /business system needs to be processed for the Data Hub?
15 How much data is currently processed for reports?
16 What type of data processing queries exist in the system which provide Static /Ad-Hoc reports?
17 What types of reports are currently available, and what is the resource usage?
18 What are the query profiling inputs for these data processing queries /reporting queries?

  • CPU usage
  • Memory usage
  • Disk usage
To understand current baselines of data processing and BI systems – server sizing

In the follow up post for this NFR I shall let you all know on how I translated as part of design and server sizing. Until then happy reading.

Please feel free to share your comments.

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!

Oracle to SQL Server Replication-A bit of insight May 13, 2013

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

In last couple of posts in the series, I had shared an approach and also appropriate pre-requisites for setting up an Oracle Publisher based Transactional Replication setup. Today in this post, I shall talk about how different it is to replicate data from SQL Server and Oracle.

Just before I started working on this assignment I recollected several of my past experiences where I had configured SQL Server Replication setups in all the topologies, and based on that I safely presumed that it won’t be that different with Oracle Publisher too. Well, I was partly right, SQL Server replication setup was, and is easy because the wizard helps one to configure publisher, distributor and replication quickly. I would think and also voice that its same for what I had worked on in this assignment except that I had to establish successful connection with Oracle and then let SQL Server do its job of configuring the publisher, distributor and Transactional Replication (pull-subscription).

The difficult part was having the appropriate Oracle client and OLE DB drivers installed, and please refer to my post which has links to the downloads from Oracle Technology Network (OTN).

I would like to share few internals on Oracle Publication from SQL Server, and without this the significance of configuration such type of publication wouldn’t be much.

1. Configuring Oracle Publisher is a new feature starting from SQL Server 2005, and works with Oracle 9i onwards.

Before SQL Server 2005, I remember well we had used SQL Server 2000 DTS packages to pull data to synchronize data between SQL Server and Oracle, and this way got replaced with Oracle Publication using Replication, and this is nice. The use of DTS package wasn’t straightforward because we needed few staging tables to capture the data because of DML change in Oracle published (source tables) and these were to be propagated into SQL Server tables through an UPSERT method. Although this worked well, but had the whole bunch of problems associated initially, before the system settled with that type of data changes that need to be captured. And the with this new feature in SQL Server 2005 I think we have similar setup happening, but everything is handled by SQL Server, and that’s why I said its pretty neat and nice.

2. Data from Oracle could be synched up with SQL Server either through Snapshot Replication or Transactional Replication.

Please note the difference in here between these two type of Replications when configured with Oracle Publisher. At a configuration and working level its almost similar with SQL Server Publisher yet I thought I will put up couple of notable points for anyone’s quick reference.

– A snapshot replication takes snapshot of published tables and applies them as a whole on the subscription end. Simple, eh, and this is run by a Snapshot Agent job which gathers the table data in the form of schema scripts, and publishes for subscriptions to consume. No “change tracking triggers” are created on the publisher tables.

– A transactional replication tracks change in each row of the published tables, through “triggers” created on the tables. The triggers that are created on published tables captures the DML changes and pushes the data into change tracking table and the Log Reader agent reads, and moves the changed data for publishing to subscribers through an intermediary Distribution database.

So transactional replication creates database triggers on the published tables and data is tracked into change tracking tables on the Oracle end. The changed data then is read into distribution database through Log Reader Agent running on SQL Server. This link has details on how the whole process happens, but let me share a snapshot of the tables that are created on the Oracle database.

Here are few snapshots of the system tables, change tracking tables and the triggers created when Oracle Publisher is configured.

Figure.1.

Replication tables created on Oracle schema

ScreenShot003

Figure.2.

Change Tracking Tables to track changes of data on published tables

ScreenShot004

Figure.3.

Trigger that is created on the publisher table

ScreenShot002

With this I am thinking you should be well briefed on what happens when an Oracle Publisher is configured with Transactional Replication. Of course, I didn’t talk much on the Snapshot Replication with any pics, because I couldn’t setup one during this assignment.

In the next post I will walk you through configuration of Oracle Publisher, Distributor.

Until then happy reading, and please feel free to comment.

Cheers!

SQL Express Edition in Production? Yes you could, but No you shouldn’t.. May 10, 2013

Posted by msrviking in Uncategorized.
Tags: , , , , ,
add a comment

A very important realization when one of my team mates came up to me and asked this question – Can we deploy SQL Server Express Edition in Production?

At first I said, “No”, and I was very confident of it. But because of some misled information I had in my mind I was wrong. So I had to understand who else does this and how..but strongly I don’t recommend SQL EE in Production and you would do it only if you are not too serious about the data you deal in production /business.

Here are few links which talk on this topic, and you may also feel confident of taking decision like this based on your requirements.

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/46ad8aa8-2097-49df-984c-58634e1ecfa2

http://social.msdn.microsoft.com/forums/en-us/sqlexpress/thread/4619FD15-13C3-4914-B1DD-B65D3154BD5B

http://stackoverflow.com/questions/180537/sql-express-for-production

Oracle to SQL Server Replication-Pre-Requisites May 10, 2013

Posted by msrviking in Configuration, Heterogeneous, Integration, Replication.
Tags: , , , , ,
2 comments

In the first post on this topic I had shared my tiny plan or approach on how to get the Oracle to SQL Server replication done. Today I shall talk a bit about on why I listed the steps, and also share the links of forums, blog posts I had used to work on each step.

1. Environment setup

This was the first step where I had to decide what is that I could have running on that box at an OS level, the hardware configuration I would need to run an instance of Oracle and SQL Server. So here are the specs I chose for bringing up this demo-able replication setup quickly.

  • A virtual machine, this is the fastest and best way to have POCs setup for understanding the internals and share it with teams.
  • A virtual machine with enough memory, CPU power and hard disk space. What would be the configuration for this? I knew I wasn’t showing anything on performance when there is data being replicated between systems, but a simple walk-through to understand the pain of having replication done from Oracle to SQL Server.Here are the specs,
    • Memory /RAM > 4GB
    • CPU > Dual core processor
    • Hard Disk space > 150GB
  • The VM with a an appropriate OS. While I was deciding on these specs I thought it doesn’t make sense to configure replication between Oracle and SQL Server running on the same box, and that too on Windows. So there was a little change of course of what should run where. That made me to pick Windows Server 2008 R2 to run SQL Server 2012 with SP1, and Oracle on Linux box. Now the challenge to find a box running with Linux and Oracle on top of it. I got some help because I had one of the boxes with Oracle on Linux, running for another customer so I had to just use it after few requests with the team.

Here is the link that tells you about software and hardware requirement to run SQL Server 2012 SP1 Developer Edition (this edition has Replication Features). We know about this link, nevertheless I thought sharing won’t cost me anything and could help anyone of us.

2. Installation of Oracle

As mentioned earlier I wanted to have the setup with Oracle on Linux and “Oracle edition that supports replication setup”. Just take a note of the last sentence in quotes, by saying that I wanted Oracle Standard /Enterprise Edition to support Oracle Gateway option which is part of these editions, and this option is not available in Oracle Express Edition. Oracle Gateway needs to be installed separately without any license in the same box of Oracle instance or a different box. I didn’t want to get into too many installations for a quick POC hence chose an Oracle Enterprise edition instance which was already up and running on Linux, 64 bits environment. Made my job precise and easy, eh!

All this said I shall still share the links on the Oracle Editions, and the license information /feature availability per Edition.

Oracle Standard Edition (SE), and Enterprise Edition (EE) features availability

http://docs.oracle.com/cd/E11882_01/license.112/e10594/editions.htm#CJACGHEB

Oracle Express Edition (EE)

http://docs.oracle.com/cd/B25329_01/doc/license.102/b25456/toc.htm#BABJIJCJ

Another point that I realized although didn’t or probably won’t be of great importance is that you have only an Oracle 32-bit Express Edition on Windows, and no 64-bit is available. I checked, and verified that there is no such installation. Of course I didn’t have to bother much because I dropped the idea of running an Oracle XE on Windows, rather a EE, 64 bit on Linux. Nevertheless, in case you are hunting for one for whatever reason I am thinking this should help you to stop the mad hunt. Here is the link where it certifies that no 64-bit.

https://forums.oracle.com/forums/thread.jspa?threadID=2277728&tstart=0

3. Installation of SQL Server

This was simplest because this was done a zillion times earlier, except that I had to be choosy in features list when I was installing the SQL Server instance on Windows Server 2008 R2 64 bit. The feature I had to make sure that was available was Replication as part of installation, and that too was done with ease. The pre-requisites were taken care when I chose the OS, Hardware for this piece of work, but make sure  you have latest service pack. I haven’t seen anything break when I tried with SQL Server 2012 w/o SP, though.

4. Configuration of Oracle publisher and SQL Server Transactional Replication – Subscriber

Under this heading I won’t write about how the configuration was done but the intent is to list down the pre-requisites.

  • What is needed before Oracle publisher is configured?
    • Oracle Database Client (Oracle Database 11g Release 2 Client) on Windows box where SQL Server 2012 instance is running and you could get this from here. Please remember the bit version you would like to download, and trust me this is the most important step to establish a connection from SQL Server on Windows with Oracle on Linux. You mess or miss this step for whatever reason you will realize that it was simple step, and bad mistake.
    • 64-bit Data Access Components to be installed on SQL Server box. You will get that installable from here, and you will want to choose the right one based on your environment. I had to pick this one 64-bit ODAC 11.2 Release 5 (11.2.0.3.20) for Windows x64.
  • What is needed for SQL Server Transaction Replication – Subscriber setup? Primarily everything done in the first step is good enough to establish a connection with Oracle, and then configuration a publication – subscription (pull) is quick.

I shall share finer details under each of the configuration steps and the intent in this post was to tell what you should have installed before you start putting the Oracle Publisher – SQL Server Transactional Replication – Subscriber in place.

At the end this link – Configuring an Oracle Publisher talk about same steps I have put in here, but falls short of pointing to right places of appropriate downloads.

I hope these help, and please feel free to comment.

Cheers!

Oracle to SQL Server Replication – First steps May 6, 2013

Posted by msrviking in Configuration, Heterogeneous, Integration, Replication.
Tags: , , , ,
1 comment so far

There was a request in my shop to setup a Transactional Replication on SQL Server 2012 and the publisher would be Oracle. A known setup in SQL Server – SQL Server, but not straight if Oracle is the publisher. The setup was to be such a way that nothing should be done on Oracle end, and everything should be configured from SQL Server. Sounds good and easy, eh! This was my first thought, but post this exercise I realized that it definitely isn’t that straight forward too and needs a bit of planning, careful installations of dependent features, and at the end has helped me refresh Replication setup knowledge which I didn’t apply for last few years.

In this post I shall share steps to configure transactional replication on SQL Server 2012, and the publisher is Oracle 11g enterprise edition running on Linux.

As mentioned earlier firstly I had to put in my thoughts to see what are the steps are involved, what are the other features or dependent components I need to install before I configure replication. I will try my best to post in few images of installation of any important step, otherwise it would be more of text writing.

Here is the high level activity I put in for sake of clarity and to know where I am. This helps and helped me to judge on how much time would I take to configure the whole setup, whatever in-depth experience I have. A bit of planning and picking the right steps (an approach) did help me avoid some rework.

  1. Environment setup
  2. Installation of Oracle
  3. Installation of SQL Server
  4. Configuration of Oracle Publisher
  5. Configuration of SQL Server transactional replication-subscriber

I shall share few thoughts against some of the above steps, because I realized everything requires few pre-requisites. For e.g. an environment is the box that is needed to run both SQL Server and Oracle, installation of appropriate version and edition of Oracle or SQL Server, drivers and client network /connectivity tools, connecting to Oracle through SQL Server replication and publishing the tables. All sound easy and familiar but things go bad and take few hours more than expected how much so you plan and jot your points.

I shall write details in the next post as part of this series, so stay tuned.

Cheers.

CDIs-Non Functional Requirement-Reliability May 6, 2013

Posted by msrviking in Architecture, Business Intelligence, Data Integration, Integration.
Tags: , , , , , ,
add a comment

This is a strange NFR, but very important. The word reliable is more of English and looks less applicable in here?! Well, no. We are talking of reliability with the meaning and importance of data reliability in a data integration system – CDI. Please take a note of a point which I had mentioned earlier that in a CDI there is no change in meaning of data, but trying to align across organization perspective of what the data in the CDI system means and how true it is. It is necessary that all business, and technical teams agree with the definition of these data terms. After this is agreed, the technical teams have to implement logics – business, solution in the system to bring that “value and truth” to the data or information that is consolidated from different sources.

In this post, I shall share few thoughts on the questions that we need to find answers.

  • What is the tolerance level or percentage of erroneous data that is permitted?

With the above context set, this question should gather information in order to plan error handling and threshold levels in case of any issue. The usual way is to have a failed process restarted, or aborted. Now to do this we need to identify the tolerance (threshold levels) and handle to get the essential, business valid data.

  • Is there any manual process defined to handle potential failure cases?

There should always be a process which restarts any failed or aborted process due to technical or business validation errors, should this be done done manually or automated. In less tolerant systems this process should be either semi-automated or fully-automated, and manual in some cases. A known fact and familiar thought too, but we should elicit this from the stakeholders explicitly to understand the business information at the same level.

  • Under what conditions is it permissible for the system/process to not be completely accurate, and what is the frequency/probability of such recurrence?

This question is not to be mistaken that we could ignore certain levels of error in the system and could be unattended. Rather this pointer helps in understanding what are the processes that could be considered as business critical, and the inputs gathered here could and should be used in designing a solution to handle errors – especially the business across all the modules based on importance that is agreed with the business stakeholders.

Although there just three questions in this NFR, all inputs work in tandem with other NFRs helping to build in a less-erroneous, fault-tolerant and robust CDI. There is lot of business importance to these questions which actually feeds into the solution building, designing of components, modules, and data model.

So please share your thoughts or feel free to comment on this NFR.

Cheers!