Friday, September 22, 2006

SQL Server guys' problem of learning Oracle - Part 2

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.


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.


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.
Post a Comment