"Sacrificing minions: Is there any problem it can't solve?" -Xykon, Order of the Stick webcomic
As it states in my profile, I work in both Oracle and SQL Server on a daily basis. When I was first introduced to SQL Server back in 2008, I had a great deal of trouble wrapping my mind around some of the concepts, since some of them were so strange and counter intuitive to what I was used to.
So, as an exercise in both self-review and in an effort to put something like this 'out there', I thought I might start a series of postings comparing and contrasting Oracle to SQL Server.
This could be looked at as an "Oracle for SQL Server DBA's", or alternatively, "SQL Server for Oracle DBA's", depending upon who's reading it and I'll try to cover both sides of the story.
To get the ball rolling, there are two concepts that form the core of a relational database management system. The terms are used often and just as often are probably used incorrectly. Database and Instance.
What is a database and what is an instance ?
Not surprisingly, the definitions vary depending on what system you're working with
Let's start with Oracle. The Oracle Documentation (Oracle Database Concepts - Oracle 11g Release 2 - E25789-01) defines an instance as "An instance is a set of memory structures that manage database files. The instance consists of a shared memory area, called the system global area (SGA), and a set of background processes. An instance can exist independently of database files."
So, from an Oracle perspective, the instance is the memory processes that manage the files that make up the database.
The database is defined, from the same document, as "A database is a set of files, located on disk, that store data. These files can exist independently of a database instance.".
The two are closely related, and the term "Oracle database" is often used to refer to both under the same umbrella, but in the purest sense, they are separate and distinct.
There are three different types of files that make up a database.
- Data Files - these contain, funnily enough, your data, as well as the corresponding indexes and any other database objects (procedures and functions, triggers etc).
- Control Files - these contain the metadata about the physical structure of the database - names and locations of data files and the name of the database, among other things.
- Redo Log Files - these are a set of two or more files that record transactional changes made to the data in the database. As they fill, they are written out to archive log files and rotate back through the other files in the redo log group.
Also of note is that each instance (set of memory processes) supports one and only one database (at the basic level. Real Application Clusters - RAC - can change that, where you have multiple instances accessing a single set of files, but that is outside of the scope of what I'm talking about here).
The figure below is a representation of what makes up and Oracle instance and database (also from the same Oracle document). It also shows the assorted processes and everything else associated with the database, which is not what we're talking about here. For now, just look at the groupings indicated as Instance (memory) and Database (files).
It is important to note that you can create as many instances as you like from a single set of binaries installed on a server.
SQL Server considers an instance to the be the installation of binaries on a server. Your first install on a server is usually set up to be the Default Instance on the server (but it doesn't have to be). If you install the software a second or third time, these become separate named instances. Each instance can the a Named instance if desired.
Each installed instance has its own set of memory processes when started.
They key difference between the two is that Oracle can spawn multiple sets of Oracle instances (memory processes) from each Oracle software install. SQL Server has a one to one relationship between memory processes and SQL Server software installation.
Buck Woody (blog|twitter) wrote an article talking about SQL Server instances in much more detail than I plan on talking about here, if you're interested in the finer points.
What about SQL Server databases ? A SQL Server database is defined as one or more data files and a log file. In SQL Server, one instance can support multiple databases.
Another difference to be noted here, is that while Oracle rotates Online redo logs through a group in a loop and writes out old changes to archive log files, the SQL Server transaction log will continue to grow and grow and grow unless the database is running in Simple Recovery Mode or a full backup is taken, at which time the log is reset and writing starts again from the beginning of the file.
Upon installation, SQL Server contains several system databases that contain objects shared by all other databases in the instance. From Books Online, the SQL Server system databases are:
- master - Contains all the instance wide metadata of the instance - logins, endpoints, linked servers, system configuration settings.
- model - Used as a template for creating all databases in the instance.
- msdb - Used by SQL Server agent for scheduling jobs. Also used by database mail
- tempdb - Used for temporary user objects for all databases. Also is the row version store for some isolation levels.
- Resource Database - a read only database that contains all of the system objects in SQL Server.
I did find an interesting article on SQL Server Central that covered some of what I've written about here and has a summary at the end mapping the system databases of SQL Server to appropriate Oracle entities.
A similarity between both systems is that it is possible to run multiple applications out of a single database, where they could be separated by different schemas, but I haven't seen that done a whole lot. Separating applications into different databases does seem to make maintenance tasks somewhat easier.
Next Time: Some of the Architectural differences between Oracle and SQL Server.
