Friday, March 19, 2010

Did you know...

SQL Server started supporting multiple instances in the 2000 edition.

SQL Server Express Editions always install as a Named Instance, even if you only have one. The instance name, by default, is SQLEXPRESS.

The MSDE Edition of SQL Server 2000 installs as a Default Instance.

In SQL Server 2000, the TCP/IP port for a named Instance moves to 1434, and SQL Server 2000 directs the client to the proper Instance using SQL Server Resolution Protocol (SSRP).

In SQL Server 2005, Microsoft introduced a new service called the SQL Browser. This service listens on UDP port 1434 and directs the connection to the proper dynamically chosen TCP/IP port.

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. In SQL Server 2000, the directories look like this:

executable/program files:
\Program Files\Microsoft SQL Server\MSSQL$NameOfInstance\Binn
data files:
\Program Files\Microsoft SQL Server\MSSQL$NameOfInstance\Data

In SQL Server 2005 and higher, the files have a different structure. For the Default Instance:

executable/program files:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
data files:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

The maximum number of Instances supported in SQL Server 2000 and 2005 is 16 for the standard editions and lower, and 50 for the Enterprise Editions in 2005 and even more in later versions and higher edition numbers

No comments: