In the first part, I briefly wrote that there are fundamental differences between Oracle and SQL Server and in this episode we'll look at a few examples. The intention is to make it easier for those who know about one product and would like to know about the other. It's also my intention to conclude that designing applications to be database agnostic is not a good practice.
In my opinion, there are two reasons for facing difficulties when someone (SQL Server DBA or Programmer) wants to transition to Oracle and vice versa.
1- Similar terminology but different implementations.
2- Utilizing the database in applications the same way.
Terminology
The most similar and confusing term between these two DBMSs, I believe, is “Instance”.
What is called instance in SQL Server is a collection of windows services that manages SQL Server Databases. You can have more than one database attached to an instance at any point of time.
In Oracle, however, an instance is a little bit different and so the way Oracle DBAs manage it.
Unlike SQL Server each instance in Oracle can mount and open only one database at any point of time. If you want to have more than one database open and running simultaneously, you’ve got to have another instance. Creating an instance in Linux came easier to me than creating one on Windows. In Windows every instance is represented by a service and so to have a new instance you ought to create a new service using a command-line tool. But in Linux, you need to take care of a few files and folders and then start it up.
Also each Oracle instance needs a "parameter" file to start working called Server Parameter File (SPFile). This file defines the attributes and characteristics of the starting instance.
Each SQL Server instance comes with a few databases including "System" using which instance manages other databases. But since an Oracle instance can open one database at a time, at the creation time, is nothing more than a few files and folders.
Applications
In applications that utilize SQL Server it's common to open a connection to database for each executing statement. If you are going to do six queries, you might well see six connections. SQL Server was designed that way - much like Windows was designed for multithreading, not multiprocessing. In Oracle, whether you want to do six queries or six hundred queries, the maximum number of connections you want to open is one. Each Oracle connection was designed to handle multiple sessions. So a single Oracle connection requires much more memory than a single SQL Server connection. Essentially, what's common practice in a SQL Server database is simply an incorrect approach against an Oracle database.
To conclude, as an architect or developer, it's important to remember that when porting applications from one database to another, you ought to take into consideration the time that is needed to refine and adjust application's architecture to be coherent with the database's architecture. I notice development teams choose development frameworks specifically because it helps their application to be database neutral without understanding the implications. Even Oracle provides two separate database management applications for Linux and Windows even though Java runs in both. That's not because Oracle can't ship one application. It's solely because Windows and Linux are fundamentally different. One uses processes (Linux) and the other threads.
If you are in the same situation and having problems, please don’t hesitate to contact me and share your problems.
4 comments:
Dear friend,
I appreciate your complementary comment.
You are right. Authentication mechanism is different too. As I just wanted to point out the issue, I used that sample.
Regarding the second part, I believe it is more DBAs’ fault to let developers touch a production database. I can’t say it is potentially insecure just because it's easy to pick up.
But you could probably say ease of use makes DBAs lazy enough to not think of securing the database.
And about the last part, I like to say DBA tasks in Oracle can be done with more control, not easier.
Anyway, thanks for dropping by. I like to post about many technical topics here and I really like to see your comments in my future Oracle posts.
The picture has been taken in Malaysia, the time that I was there. It's a symbolic French castle. Now I'm in Vancouver - BC - Canada.
Hi Amir
How R U?
I wanna start learning Oracle. can you give me an advice about Jumpstart book about Oracle?
(do you have any e-book about it?)
You have a red book in home "Oracle 9i jumpstart", is that ok for beginners?
Amin,
If you e-mail me that book's table of content, I might be able to tell you how good is that to start up with.
The book that I've mentioned in this post, is a good book for start, but not from scratch. You have to know a least a little bit about Oracle.
Post a Comment