Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
This post might be helpful. – Vérace Feb 25 at 12:58
    
not really. i know what it means in oracle, but i am asking about sql server - system database serving multiple user database. – tiongmaru Feb 25 at 13:00
    
Hmm... You wrote "Normally one instance maps to one database". This isn't the case - one can have multiple databases (i.e. schemas) associated with one instance. This isn't clear from your question. It is true that on many production systems the instance may only be running one schema (think Oracle Business Suite for example). – Vérace Feb 25 at 13:05
    
yeap. one instance map to one database in oracle. inside one database, there can be many schemas. but in SQL server, it seems like one instances can have many databases – tiongmaru Feb 25 at 13:25
    
A schema is a database as most people understand the concept - i.e. a collection of linked tables (with FK relationships and ACID). FKs and ACID aren't supported between different schemas on the same instance - ergo they are "databases" (as understood by the layman). We may be dancing around the head of a pin here, but I think it's good that these matters are clarified. – Vérace Feb 25 at 14:03
up vote 9 down vote accepted

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 their 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.

share|improve this answer
    
Great answer! I used to teach SQL server to oracle DBAs and they always find the "extra layer" very confusing. – spaghettidba Feb 25 at 20:10
    
marvellous explanation, so the "system databases" are holding metadata for not just 1 user database, but multiples of them ? – tiongmaru Feb 26 at 2:02
    
Exactly. In particular, the master system database holds the metadata for databases, logins, instance configuration and every other instance level object. Msdb holds configuration for SQL server agent (the scheduler) and other non relational features. – spaghettidba Feb 26 at 7:27
    
Yeah. I just realized that I totally forgot to add the SQL Server Agent, which is very central in SQL Server. Also forgot to talk about procedures also favored in SQL Server. Will add these later. – Nicolas de Fontenay Feb 26 at 14:15
    
@tiongmaru that's correct. – Nicolas de Fontenay Mar 4 at 14:17

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.

share|improve this answer
    
thanks for reply, I have edited my main question, please take a look – tiongmaru Feb 25 at 12:26

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...)
share|improve this answer
    
indeed, a picture says a thousand words... indeed.. can i also say in that case, in order to have a consistent backup/recovery, i would need to backup the "system" databases together with the "user databases"? – tiongmaru Feb 26 at 2:06
    
Each database is consistent on its own: separate data files, separate transaction log files, separate backup and so on. If the whole instance suffers a hardware failure and has to be recovered, you will need backups for every database, including system ones. The only exception is tempdb, which can't be neither backed up nor restored – spaghettidba Feb 26 at 6:11
    
sorry, but i thought the system database contain metadata about the user databases ? so if i have backup system database and user database on day 1, created another user database2 on day2 and backup user database2 on day3. assuming on day 4 there is a failure and there is a need to restore user database2. can i restore user database2 backup together with day1 system database backup ? – tiongmaru Feb 26 at 6:37
    
It's not only metadata. System databases contain also configuration data for the instance and other features, such as SQL server agent. Databases can be backed up and restored independently, so if you have a system database backup from day 1 and a user database backup from day 2, you can restore them independently. Restoring a user database also creates its metadata if missing. – spaghettidba Feb 26 at 7:23
    
so we can restore a user database with a totally older/unrelated/new system database ? – tiongmaru Feb 26 at 7:38

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.