Instance and database (Oracle vs SQL Server)
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:
- system where the system views are.
- 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.
- 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.
- 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:
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:
- 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.
- Its own server process
- A set of system databases (master, model, msdb and tempdb)
- Its own configuration (services, registry keys...)