SQL Server

Configuring SQL Server 2008 : Instances vs Default Instance

10/17/2010 5:48:06 PM
A SQL Server installation is referred to as an instance. Up to and including SQL Server 7.0, only one installation of SQL Server was possible on a server, but that restriction didn’t suit a number of deployment scenarios that customers required, including high-availability and consolidation.

With the release of SQL Server 2000, multiple installations of SQL Server were possible on a single server and were known as SQL Server instances. SQL Server 2008 continues with this model and with very few changes.

A default instance has much the same profile that SQL Server installations have had in past; you install SQL Server and then connect using the computer name of the server. Your Windows Server can only have one computer name, so you can only use it to connect to one SQL Server instance. This is called the default instance.

If you install additional instances of SQL Server, these are referred to as named instances. You connect to them using the <computername>\<instancename> format. For example, if you have a server named PLUTO and you install a named instance named SQL1, you would connect to that instance using PLUTO\SQL1. If you installed another named instance called SQL2, you would connect using PLUTO\ SQL2. If you had a default instance installed, you would connect using PLUTO.

Each instance is completely independent of any other instance and has its own set of services, databases, and configuration settings. All the components of a single instance are managed together, and service packs and patches are applied to all components within an instance.

During the installation process you have to specify a unique instance ID, which is used to define the directory structure, registry structure, and service names for that instance. This is new in SQL Server 2008. By default, the instance name that you specified is used as the instance ID. For the default instance, MSSQLSERVER is used as the instance ID.

In Figure 1 you can see the installation screen for a default instance showing the instance ID. You can also see that two named instances are already installed: PLUTO\SQL1 and PLUTO\SQL2. You don’t have to have a default instance installed to install or use named instances.

Figure 1. Installation Screen for Default Instance with Two Named Instances Already Installed

If you take a look at the directory structure after the installation in Figure 2, you’ll see separate directories for each instance as well as each installation of Analysis Services and Reporting Services. There are also three directories called 80, 90, and 100, which contain components that are shared across all instances such as the Management Tools. 80 and 90 hold SQL Server 2000 and SQL Server 2005 components for backward compatibility, and 100 contains the SQL Server 2008 shared components, including SQL Server Integration Services if it’s installed. SQL Server Integration Services is not instance aware, so you can only have one installation on a server.

Figure 2. Directory Structure for Three Instances with Analysis Services and Reporting Services

The default instance will listen on TCP port 1433 by default, and named instances use dynamic port allocation. The SQL Browser Service listens on UDP port 1434 and is responsible for determining the correct port when you try to connect. If the SQL Browser Service isn’t running, you will need to specify a unique TCP port for each instance to listen on using SQL Server Configuration Manager, which is detailed in the next section.

Once you know which port your instance listens on, you can specify it in your connection string. For example, if you configured PLUTO\SQL1 to listen on port 53000 you would connect using PLUTO\SQL1:53000. You don’t normally have to do this because the SQL Browser Service is installed and runs automatically, but it is sometimes disabled to tighten security.

Instance Limitations

Microsoft has officially tested and supports a limited number of instances per server. These limits are detailed in Table 1.

Table 1. Number of Supported SQL Server Instances per Server by Edition
SQL Server EditionSupported Instances

[*] Only 25 instances are supported on a Windows failover cluster

Performance Considerations

We have already discussed how each instance is completely independent of any other instance. This gives you the advantage of being able to run multiple SQL Servers on the same server with different instance level requirements, such as development and production instances, or separating international instances with different collation requirements.

The downside to using multiple instances are the resources required to run each instance. They all run separate services, have their own system databases, and require memory and disk space before you’ve even started using them. They have no knowledge of each other so they can’t dynamically share resources. PLUTO\SQL1 sees PLUTO\SQL2 as it would any other application that was installed on the server.

Typically, a single instance will provide the best performance because SQL Server is very good at managing all its own resources. Multiple instances require additional resources and can therefore impact the performance of a server if its resource constrained.

Using multiple instances is acceptable if you have enough resources available, but there are very few scenarios where they would give you a performance benefit; use them instead when you are forced to run separate, independent SQL Servers and want to utilize the resources on an existing server.

Other -----------------
- sp_configure and SQL Server Management Studio
- Configuring SQL Server 2008 : Database Mail
- Configuring SQL Server 2008 : Full-Text Indexing
- SQL Server 2008 : Working with Indexes
- SQL Server 2008 : Working with Constraints
- SQL Server 2008 : Working with Tables and Views
- SQL Server 2008 : Viewing and Modifying Data (part 3) - Creating Functions and Creating Triggers
- SQL Server 2008 : Viewing and Modifying Data (part 2) - Creating Stored Procedures
- SQL Server 2008 : Viewing and Modifying Data (part 1) - Creating Views
Most View
- Windows Phone 7 : Saving Pictures to the Web
- Parallel Programming with Microsoft .Net : Parallel Aggregation - The Basics
- Exchange Server 2010 : Planning for Messaging Security
- Windows Server 2008 : Using PowerShell to Manage Active Directory (part 1) - Using the Active Directory Module in Windows Server 2008 R2, Creating and Manipulating Objects in Windows Server 2008
- Securing Windows 7 : Thwarting Snoops and Crackers (part 2) - Locking Your Computer Manually, Automatically
- iPad SDK : PDF Generation
- Performing Administrative Tasks Using Central Administration (part 12) - Service Applications
- Exchange Server 2010 : Upgrading from and Coexisting with Exchange Server 2003 (part 4)
- Windows Server 2008 : Deploying Terminal Services Gateway
- SharePoint 2010 : Get Started with Social Features
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS