Instance and database (Oracle vs SQL Server)

Posted on

Question :

I have been using Oracle up to now, where:

  • instance -> process + memory
  • database -> physical datafiles

Normally one instance maps to one database. Inside a database, there can
be many tablespaces (system , users etc).

However, I am not so sure about SQL server. It seems to me that for SQL Server, one instance = multiple databases? Or one instance = multiple schemas?

In Oracle, one database contains the system, sysaux, temp tablespaces and the user tablespace. In short, both system and user are in the same database.

In SQL Server, it seems like there is one system database and that can serve multiple user databases?

Answer :

I’m a DBA on Oracle and SQL Server.

The confusion is due to a mismatch of semantic and an extra level in SQL Server’s hierarchy but there’s more than that!

Note that we will talk only about single instance database in both instances (pun intended).

Instances

In Oracle, one instance will have an amount of memory allocated to it (SGA/PGA) and processes spawned (smon,pmon dw0X and so on). There are system views to monitor all of the stuff that will be created and used (V$ and dba_ views).

This is the structure we are familiar with in oracle.

And you already mentioned it, the database is where the physical files are. Typically moving to RAC makes the difference glaring.

In SQL Server, the instance works the same way:
A bunch of memory is allocated and there are system views (sys.) to monitor all of that stuff.

The instance has 4 system databases:

  1. system where the system views are.
  2. msdb database which also holds system related information. Typically that’s where you will find your backups related data. A bit of maintenance is required on it at least to flush old backups records.
  3. model, the aptly named database which is used as a model for every new database you will create. It’s a good idea to modify the data file settings (auto extend, initial size, backup mode) so all new database benefit from it.
  4. tempdb developed below.

The system database holds meta data information for every other database on SQL Server.

Temp space

In Oracle:

Temp tablespace (create temporary tablespace).
In oracle the temp tablespace is a spill zone for operations which can’t fit in the PGA (process memory, default at 200M (up to 11g), your hash join for example if bigger than 200M spills in temp tablespace, generally that’s a cause of slow performing query).

In SQL Server, there is a tempdb database located in the “system databases” section and it’s very actively used, much more than in oracle. Place it on very fast disks (SSD or fiber comes to mind)

Thus comes the 2nd large difference:

Objects

In SQL Server:

The objects (table,views), are not tied to a user as in oracle, but rather to a “database”.
The “database” holds (not own), the objects. At the creation time of the database, a data file is mentioned to store these objects.

In oracle:

A user creates objects which will land in a schema of the same name. Naming the users carefully becomes important.

And here comes the 3rd big difference:

Security

In oracle:

Users will be created and they will be given the right at the very least to create sessions so they can access objects they have been allowed to.
Or, they will have the right to create objects in their own schema (resource role), which will then create a schema in that name.

You may chose, and it’s a good practice, to create a tablespace and make it the default for each users able to create objects. If that’s not done, a default tablespace is used, generally “USER_DATA”.

In SQL Server:

Since the objects are not owned by a particular user in a schema, users must be first created, then privilege assigned. Typically, whoever will be the main user to do the object creation will be granted the “owner” role on a given database.

That user in SQL Server would be the equivalent in oracle of a schema owner but there may be more than one owner.

Security is where SQL Server shines over Oracle: SQL Server security can be tied to Active Directory which manages the desktop users. These desktop users can be tied to a user group say “read only”. It’s possible to add that active directory group to SQL Server and give it a “read only” access to your database.

When a user leaves your company, the Active Directory Admin delete that user and access to the database is lost as well.

Oracle can use LDAP but let’s face, it’s not very often well tied to the windows desktop users.

The 4th big difference.

Semantic

SQL Server has an extra layer. In a SQL Server database, objects may be organized in schemas. Schemas may have owners too. It introduces more fine grain security as well but while it’s used, it’s not common. I would say it’s also good practice to group your objects by schema early in the development stage to benefit later on from extra security abilities when needed.

Logs / Archived Logs

Finally archived logs work differently in oracle and SQL Server.
SQL Server has a log file created for each database, while oracle has archive log mode turned on at the instance level.

This is where the line blurs. For some aspects, SQL Server’s databases really are databases. In other instances they look more like schemas.

The archiving in SQL server is set with the option backup mode set to full. Then they are archived by performing a log backup. The log backup will require a full backup to take place before it starts working.

In Oracle, archived logs are archived automatically, then we back them up to a remote location.

Summary

Oracle:

Instance->User1->Schema1(named User1)->Tablespace1
                                     ->Tablespace2
        ->User2->Schema2(named User2)->Tablespace3
        ->User3->Granted "read only"->schema2.table1

SQL Server:

Instance->Database1->Schema1
        ->Database2->Schema2
        ->User1
        ->User2

        ->User1->Granted "owner"->Database1
        ->User2->Granted "read only"->Database2->Schema2

These are the things that come to my mind when I get to compare both.
I would love to hear more from others and add on/correct this answer.

Also I don’t have a particular preference for either.
I think they both do a great job and I enjoy working with both very much.

I don’t know Oracle but I will clarify what it means in SQL Server.

On one Windows Server there can be multiple instances of SQL Server (independent installations).

Each instance has their own databases (user databases and system databases).

It is important to know that each instance has its own system databases (master, model, msdb, tempdb). Instances can use different SQL Server editions, versions and have different products installed.

Some SQL Server components are shared between instances on one server (SQL Browser for example).

To access instance A from instance B you have to create a linked server on instance B that maps to instance A. You can also connect SQL Server with Oracle using this mechanism.

Each database can have multiple schemas. Schemas group tables, procedures, views and other database objects.

Sometimes a picture is worth a thousand words:
SQL Server Instances

In SQL Server an instance represents a single installation of the product and runs as a unique server process. Each machine can host multiple instances of SQL Server.

Each instance has:

  1. Its own binaries (in Oracle you can run multiple databases with the
    same binaries). This also means that each instance could be a different version/edition of SQL Server.
  2. Its own server process
  3. A set of system databases (master, model, msdb and tempdb)
  4. Its own configuration (services, registry keys…)

IMHO, the best comparison would be with Oracle 12c. In Oracle 12c the database is like a SQL Server instance, and each container within that 12c database would be like a SQL Server database. In both Oracle 12c with containers and SQL Server you can detach a container or database and reattach it to another instance or database. One real difference is that with SQL Server you can create as many databases in an instance that you want. But if you want to create containers in Oracle 12c you need to pay for the container feature. You might also need to have Enterprise.

Leave a Reply

Your email address will not be published.