A SQL Server instance is the installation/deployment of a SQL Server Database Engine, together with any other components such as Analysis Services or Reporting Services, in a computer. It is called instance because SQL Server can be installed several times on the same computer and; therefore, each installation represents a new “instance” of SQL Server in the computer.
- Instances are completely independent and isolated from each other including server-level roles. The domain of server-level roles is the instance where they were created. You can think of server-level roles as “instance-level” roles since they actually act at an instance level. A good example of a server-level role would be “sysadmin” where each instance would have its own sysadmin.
- Similarly, as instances are independent from each other, all configuration modifications made on an instance do not affect others. For example, if you change the collation setting on SQL_INSTANCE_A this will not change the collation setting of SQL_INSTANCE_B. Alternatively, if you want to change the collation on all instances of SQL Server then you have to do it for every instance.
- The maximum number of stand-alone SQL server (2012/2008/2005) instances that can be installed, per computer, is 50 (http://technet.microsoft.com/en-us/library/ms143432.aspx). I assume (it’d be great if anyone can confirm this) that it is, in theory, possible to have 50 instances of each SQL Server version in the same computer, say 50 of 2012, 50 of 2008R2, and 50 of 2005.
- There are two types of instances, the default instance and the named instances. There can only be on default instance and, theoretically, you should be able to have as many as 49 named instances.
Instances are a really powerful feature of SQL Server as they add an extra layer of abstraction to the database engine in terms of management. As each instance has its own data files, systems tables, and system users, effectively, we are talking about of almost completely independent and isolated database servers (I say almost as they still share the same computer).