Over the last few weeks I have been working on the task of bringing an Oracle database into my SQL Server landscape. The basic process that needs to be accomplished is to get this Oracle database pumped into the existing SQL Server structure primarily to address the reporting aspect and requirements. The software that utilizes the Oracle database could easily sit in their own little world without much need for us to go outside that shell but given my completely SSRS reporting structure, there is a need to build off the new data store for the users taking into account the completely native SQL Server backend.
Oracle can easily bring itself into SSRS as a data source. In fact I highly recommend only doing that if you find yourself with this task. The company I currently work with however, require that the data be backed up, available in all sources and backed up again. To resolve the businesses needs I chose to bring the entire Oracle database into a SQL Server database nightly so the data was readily available and easily accessed by the skills of my group. In my experience as a SQL Server professional, when Oracle is introduced into a SQL Server environment, the last thing you want to do to your developers and Jr. DBAs is install the Oracle client on all their machines and then throw SQL*Plus at them. There is a reason that it’s hard to find cross dressing Oracle/SQL Server Experts. They mix like UNIX and Windows OS.
The major key that will adjust the process of how you obtain a complete extract from Oracle to SQL Server is the size of data and the network structure. One thing you should think about prior to starting the actual setup of this type is what data do you really need? In the case when there is a set based data result you can export in a 24 hour period by date, you can look at this process as a type of wannabe warehouse ETL process. Use SQL Server as your holding tank in which you retain the critical data that is required to recover and supply the business with the data. SQL Server Integrated Services is a tool that can quickly get you up and running on the type of export I’m outlining above. With SSIS you can work with the Oracle design to bridge the data into a SQL Server design. This gives you an upper hand over writing queries that may not be efficient in T-SQL to extract data for reporting by means of doing the conversion to the SQL Server design at export/import stages.
Now that I’ve laid down the plan, I want to make one hardened statement. “Never install the Oracle client on a production database server that holds other databases that are critical to the company”.
You may be asking why I say that without much room for the maybe situations. Here are the facts. You are probably reading this because you are a SQL Server shop and have little to no Oracle experience or an Oracle DBA on hand. In my experience, the Oracle client is not an easy task for SQL Server DBAs to plug in and get going. I have done this 4 times in my career and still have a hard time with it. There are basic steps involved to get Oracle working from remote servers. Install the client, setup TNSNAMES and a few other minor requirements just to get a functioning SQL*Plus session. On each installation those steps never went the same and were never direct for me. The reasoning behind this is, we as SQL Server DBAs are not Oracle infrastructure experts. Spending a day reading up on how the client and Oracle instance work together on Windows (possibly to UX) is not efficient to call anyone an expert on any process. Don’t approach this task as if you are. It has failure and hardships written all over it.
If you have the resources and budget available, create a job server installation that can assist in making the implementation of the Oracle instance onto your SQL Server instances easier to manage and troubleshoot. Job servers are something I push for in all environments for SQL Server. There is performance factors that come to mind when putting major job tasks on production database server that have the task of serving data to the business. It is an easy resolution to a growing or already large integration services group to move them off to an instance that is designated and configured to handle the different needs of SSIS and what you are doing with it.
So even with my poor diagramming abilities, you can see the safety barrier between the two different systems.
Don’t get me wrong on everything I’ve written either. I am not an Oracle hatter. Yes, I have chosen the SQL Server DBA path because it’s just that much better (:P). I have worked on DB2, Oracle and SQL Server. All have their strengths and all have weaknesses.
When the time comes for you to think about making them all work together, I hope this short blog of a possible mix will help you get the gears going. It’s critical in the process of integrating different database servers into your environment to ensure they do not affect the abilities of them working optimally.
8 Comments
At least you don’t have to deal with FoxPro or Access……Excel is another painpoint..but I heard that you are the Excel master and that you actually like XNF (Excel Normal Form) data sets 🙂
Don’t even get me started about SalesForce or PeopleSoft
Seriously…. heterogeneous, disparate database systems are always a pain in the neck… luckily we have tools these days like SSIS and JitterBit
Out of curiosity…did you increase packet size when copying large amount of data?
Yes, I set much higher but I also have a line designated for data so I was able to do that without killing the network IO for others
One thing I’m curious is:
How did you transformed the rest of Oracle objects? (e.g. Sequences, Triggers, Constraints etc)
Was this the manual that your team will be working on?
BTW: I know your pain about tnsnames.ora and setting the listeners on networks but once you work with Oracle for a while you get the hang of it.
I for one would like to work in an environment w/ Oracle and SQL server … haven’t found it yet. (and yes I am a little weird) 🙂
One thing that is more painful is setting up Oracle Linked servers and assigning security over those… that one is pretty messed up!
Unfortunately there was nothing standard about the Oracle database that I had to deal with (but is there ever?!). I was forced to handle constraints with key tables in my SQL Server DB in order to retain my integrity of the data. On import I have to custom join based on look ups what data is required to come in and bring the relating data along with it. I made several passes with about 4 different methods in SSIS to handle this and it came down to a very basic way of using the SQL Server side joined to the Oracle side on keys to run down to several data flow tasks with a few needs of script transforms and then simple data conversions. All in all I have an almost replica of Oracle in SQL Server with about 20 added tables to handle the poor design issues. The Direct import of all the tables from Oracle to SQL Server is really just that. Every table is brought over as a snapshot in time of the Oracle database. During the extract I take the data that is required to perform on the SQL Server side as it needs to and make it relational in my own design. The one thing this did to my development time was lengthen the validation and UAT process of the finish process and objects the user accepts.
I don’t want to point the finger of bad database design but this one was horrid. Keys were hidden in long char columns that forced my substring abilities to the end while keeping the import quick. The only thing I had on my side was the fact I could limit the import to a point in time and the hours on which the data was changing is a set shift. If the process changes and the operation of this one database goes 24/7, the entire ETL side will have to be revisited and I will look to replication I think or go with query tuning over ETL tuning. On a side note, the only major hassle was date conversions when it came to data type issues. I think anyone that has done this feels that though.
I actually set a linked server up for the only purpose of validations while doing all this work and I know what you’re saying. Very painful!
I think it’s enjoyable putting this kind of new object into the daily mix that you feel comfortable with. New challenges are alway exciting but yes, that is weird 😉
I feel your pain with the Oracle client. It *is* difficult to install. Maybe/Probably that’s because of my familiarity with SQL Server and its tools but regardless…I found it hard!!
-Jamie
Oh man, every time I’ve set up the Oracle client it’s been hell. It takes me hours every time and I always end up with strange issues. The last time I wanted to install an Oracle instance and the latest version of SQL Developer. The SQL Developer version from the Oracle full install was apparently not the same as the latest version, and I had all sorts of problems trying to decide things like, do I put the two installs in the same home? Wait, which home? Evidently there are two kinds of homes (I can’t even remember them now). I had to research for many minutes at each step in the install process just to understand what the heck I was being asked.
I also totally dislike how the Oracle software has to do everything all its own way. They can’t use standard Windows controls like the rest of the planet, no, they have to roll their own controls that don’t work the same and so add yet more confusion and stumbling around (things like combo boxes and scroll bars and other controls that didn’t accept my normal keyboard shortcuts I like to use).
Connecting to SQL Server is as easy as knowing its network name and instance name (though usually there isn’t one) and having some credentials. Oracle’s text setup file tnsnames requires arcane crazy syntax, and this seems like something that should go the way of ini files: right into the void as an outdated technology from 15 years ago.
Comparing how easy it is to set up a SQL Server instance with or without client tools, and the ease of connecting to a new server, earns a huge black eye for Oracle in my opinion. I’m not an Oracle DBA and don’t plan to ever be one; but I do need to work with Oracle databases sometimes, and I think it shouldn’t be that hard.
I definitely enjoyed reading your insight and learning from your blogsite. Thank you for the interesting and informative article. – Diego