Friday, June 26, 2009

FAQ : What is an instance in SQL Server and Oracle

SQL Server Instance :
In SQL Server you can have multiple instance running on same physical machine. It means, you will have multiple database engine (windows services) running on the same machine. Each instance of the SQL Server database engine has its own set of system and user databases that are not shared between instances. Each instance will have its own memory area and other resources. There are two types on instances in SQL Server, Default instance and Named Instance. You will have only one Default instance on a machine but may have more than one (depends upon the edition of SQL Server what you have, it can be anything upto 50) Named Instance on a machine.

Why we need multiple instance of SQL Server?
The reason can be technical or non-technical (political). Ie. If you want to have more resources allocated for a particular department(read database(s)) than other department(s) you may go for different instances per department (Exception : In SQL Server2008 onwards there is resource governor feature which can control resource allocation). Another technical reason would be if one of your applications is highly tempdb intensive then you may go for different instances because each instance will have its own tempdb.

Political reason is, generally SA privilege may not be sharable between departments and each department would like to have its own SA, then you may go for different instances.

Oracle Instance.
Instance in Oracle is not similar to Instance in SQL Server. Generally, people uses Instances and Databases in Oracle as synonyms which is incorrect. One need to understand the distinction to understand the Oracle Architecture. To explain Instance in Oracle you need to understand what is Database in oracle too. In short, Databases are PHYSICAL and Instances are LOGICAL. Instance consists of memory structures and process in the server. Oracle has an area of shared memory called System Global Area (SGA) and each process have its own private memory area called Program Global Area (PGA). Instance to Database is ONE TO ONE. Ie An Instance can be part of only one Database but database can have multiple Instances (I never see some thing like this in my short stint as Oracle developer).

Why you would go for multiple instance in Oracle?
I would like to have the answer for this question from viewer. I do have some idea, but not convinced as the way SQL Server reasoning for Multiple instance on same machine.

No comments:

Locations of visitors to this page