Differences Between Instance and Database Engine

Differences Between Instance and Database EngineInstances of the SQL Server Database Engine must be installed using setup.a.)    Databases are contained on the instance, and can be created using Transact-SQL (CREATE DATABASE) which is very fast.b.)    Server resources such as CPU and memory can be configured for each instance, but not for each database. A common reason for multiple instances, is to keep one database from taking resources away from another.c.)     Administrator access can be configured for each instance. Though access to each database can be configured, admins (members of the sysadmin fixed server role) have access to all databases on the instance. A common reason for multiple instances, is have security isolation of one set of databases from another.d.)    Some elements are configured at the instance level; SQL Server Agent, jobs, logins, linked servers, replication, Policy Based Management, to name a few. Separating these configuration elements can be a reason to have multiple instances, but I'm not sure that's very common.e.)     A common reason for multiple instances, is to keep one or more databases on an old service pack level (or whole version level) until applications can be fully tested.SQL Server is composed of three main parts: An engine, which is the software started by a few Windows services that performs lookups, sorts, and other actions; meta-data such as the master and msdb system databases; and user databases where data is stored. The master database contains the information that the engine reads when it starts up. It includes such things as security settings, file locations, sort orders, and database locations. The msdb database contains the information used by the SQL Server Agent program and information about maintenance plans. Yet another system database, called model, is the "template" from which other databases are created. Finally, the tempdb database is the "scratch" area that the engine software uses. This format holds true for all versions of SQL Server, although other control mechanisms are also implemented as Dynamic Link Libraries, or DLL’s.AdvantageAdvantage is that since some of the files that run the Instance are duplicated, you can apply service packs separately to each Instance. That way you can host several applications on the same hardware that require different service pack levels.The Default InstanceWhen you install SQL Server on a system, one copy of the software can be (but doesn’t have to be) designated as the Default Instance. It doesn’t have a special network name; it works by using the name of the computer. So in any client tools or programs, when you want to talk to a Default SQL Server Instance you just enter the name of the computer it runs on, like BUCKTEST for the BUCKTEST Windows Server. The network information for the Default Instance for named pipes connections is \\.\pipe\sql\query. The Default Instance TCP/IP port is 1433.SQL Server is made up of various files and services. The names of the Default Instances services are MSSQLServer for the engine and SQLServerAgent for the SQL Server Agent. If you have older SQL client applications that only use the computer name, then you can still use those against the Default Instance.Named InstancesInstalling the software again gives you another Instance, called a Named Instance. With Named Instances, you get another copy of the SQL Server Engine and the SQL Server Agent services. For named Instances, the names of the SQL services become MSSQL$NameOfInstance and SQLAgent$NameOfInstance. That way, you can start the various named Instances separate from each other. You can use different accounts to start the various services, which allows you to control security better. For instance, you can set up a directory that only certain accounts can access, and then assign one of those accounts to start an Instance. The administrator of that Instance can then send the backup files there, but nowhere else.Note:Each named Instance has a different location for program files and data files that is different from that of the other Instances of SQL Server\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL (Executableand\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data (Data files