A High Level Comparison Between Oracle and SQL Server
Instances, Databases & Tablespaces
Perhaps the first architecture level difference between SQL Server and Oracle is in the concept of instances and databases.
An instance in SQL Server terms means a self contained application service that involves operating system files, memory structures, background processes and registry information. An instance is represented by a service in Windows and can be either in a running or stopped state. When running, an instance occupies a portion of the server’s memory and also spawns a number of background processes.
Central to a SQL Server instance are its databases. A SQL Server database is the repository of data and the program code for manipulating that data. If an instance is not running, databases within it cannot be accessed.
There are two types of SQL Server databases: system databases and user databases. When a SQL Server instance is first installed, five system databases are created: master, model, msdb, tempdb and resource. If there is more than one SQL Server instance running in a machine, each instance will have its own dedicated set of system databases. An instance cannot start if any of its system databases except msdb is inaccessible or corrupted. User databases on the other hand are created by DBAs and developers after the instance has been installed and the system databases have started. These are the databases that store an organisation’s business information.
So in short, a SQL Server instance will always include databases (even if it is only the system ones) and a database will always be associated with one (and only one) instance.
At the physical level, a SQL Server database is represented by a set of operating system files residing in the server’s disk system. There are two types of database files: the data file and the transaction log file. At the very minimum, a database will have one data file and one transaction log file. A data file is the main repository of information in a SQL database. A transaction log file on the other hand records the changes that have been applied to the data. This file is required by SQL Server for system recovery. A data or log file will always belong to a particular database: no two databases can share the same data or log file. If the database is large, it can have multiple data files. Multiple data files in a database can be logically grouped into structures known as filegroups.
With Oracle, it works in somewhat reverse direction. When Oracle starts, it works just like SQL in that a portion of the server’s memory is allocated for its operation. This memory area, known as the System Global Area (SGA), is divided into a number of distinct structures. Along with the memory space, a number of background processes are also started that interact with the SGA. Together, the memory space and the processes constitute an Oracle instance. Note that the Oracle database is still not in the picture. In fact an Oracle instance could be running perfectly okay without its database even being online or accessible. When installing Oracle, there is an option to install only the software and to create the database later.
A database in Oracle is a collection of operating system files. Unlike SQL Server, an Oracle database does not represent the logical grouping of objects; rather it is a single, collective term for a number of files on the disk that primarily hold data.
The files that make up an Oracle database can be classified into three types: data file, redo log file and the control file. Data files are where all the data resides. There can be any number of data files in an Oracle database. Redo log files are like SQL Server transaction logs in that they record every change made to the data and is used for system recovery. Control files are a special kind of file that contains small but vital pieces of information about the database. Without this file, the instance will not be able to open the database.
Apart from data, redo log and control files, the database will also contain a parameter file, password file and optionally, archive log files. We will discuss about each type of Oracle database files shortly.
When an Oracle system starts, at first the instance is created in the memory. The instance then connects to the database residing on disk and finally opens the database for user interaction. When the system is shut down, the instance is erased from memory: all the memory structures and the processes are gone, but the database will still exist in the disk, albeit in a closed state. As said previously, it is possible to have the Oracle instance running without opening the database – this is a major difference from SQL Server where an instance cannot start without its systems databases being online first. However, just like SQL Server, it is impossible to connect to an Oracle database if the instance has not started.
Generally the relationship between an Oracle instance and its database is one-to-one. An instance will have one database associated with it. A database on the other hand can have one or more instances accessing it. A standalone Oracle installation will comprise of a single instance accessing a single database. Oracle installations configured as RAC (Real Application Cluster) will have multiple instances running in different machines accessing the same database in a shared disk.
So where does the logical grouping of database objects lie within Oracle? In SQL Server, this logical grouping is done by the database itself. For Oracle, it is done through something called tablespaces. An Oracle tablespace is a logical structure that groups together tables, views, indexes and other database objects. For example, your production Oracle database may have one tablespace dedicated for the HR application and another tablespace for payroll. Each tablespace is physically represented by one or more data files on disk and form a part of the database. The database is logically made up of a number of tablespaces and the tablespaces in turn are physically made up of one or more data files. So Oracle’s equivalent for a SQL Server database is a tablespace.
And since they are so similar in their functions, the process of creating a database in SQL Server is quite similar to creating a tablespace in Oracle. Whether creating a database or a tablespace, the DBA needs to specify a name first. The DBA then assigns one or more data files to the database or tablespace and specifies the initial size and growth increments for each file.
Just like a SQL Server user database can be made offline or read-only, so can be an Oracle user tablespace. And just like one or more data files in a SQL Server user database can be made read-only, one or more data files in an Oracle user tablespace can be marked offline.
However, databases and tablespaces do differ in the following areas:
In SQL Server, data files can be logically
grouped into filegroups. Oracle tablespaces do not have this concept.
In SQL Server databases, each database will
have its own transaction log and the log file properties will need to be
specified during database creation. For Oracle, transactions for the whole
database (that means for every tablespace) are recorded in one redo log.
Consequently, there is no provision to create individual log files for
· For SQL Server, the database can be created with simple recovery mode. Simple recovery mode means the inactive portion of the database log will be truncated after each checkpoint. Oracle has a similar concept which we will discuss later – but it is not possible to configure that property for individual tablespaces.