SQL Server 2000 Instances and Upgrade Paths

Before we can talk about upgrade paths we need to discuss default and named instances. When you install more than one instance of SQL Server 2000 you end up with multiple instances that operate independently (for the most part). You can have a maximum of 16 instances each operating with their own memory space and settings.

The default instance is identified by the computer’s network name. SQL Server 6.5, 7.0, and 2000 can all act as the default instance, but only one default instance can operate at a time (this is called “version switching”) if multiple versions are installed. An application that uses client software from versions earlier than SQL Server 2000 can access only a default instance.

Named instances are identified by the computer’s network name followed by the instance name ( servername\instancename ). Only SQL Server 2000 can act as a named instance, but you can have many named instances operating at the same time. You can also Run SQL Server 6.5 or 7.0 as the default instance with one or many SQL Server 2000 named instances running at the same time. There are a few restrictions on named instance names:

  • You can’t use “Default” or “MSSQLServer”
  • Named instances are limited to 16 characters
  • The first character must be a letter (A-Z, a-z), an ampersand (&), or an underscore (_)
  • You can’t use punctuation marks, dashes, asterisks, or spaces
  • The name is NOT case sensitive

Instances Summary:

What version(s) of SQL server can run as this particular type of instance?
Default Instance: 6.5, 7.0, 2000
Named Instance:    2000 only

Can multiple instances of this particular type operate at the same time?
Default Instance: No*
Named Instance: Yes

How is this particular type of instance identified?
Default Instance: servername
Named Instance: servername\instancename

* You can still have multiple versions of SQL server installed on the computer, but only one of the versions can operate at a time.

For more information about instances have a look at the following link and its related articles: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_runsetup_2xmb.asp

Once you understand the concept of default instances and named instances understanding the available upgrade paths to SQL Server 2000 is not difficult:

  • An installation of SQL Server 6.0 can’t be upgraded directly to SQL Server 2000, you must first upgrade to SQL Server 6.5 or 7.0. Once the databases have been upgraded you can then upgrade to SQL Server 2000 or install SQL Server 2000 along side 6.5 or 7.0
  • SQL Server 6.5 and 7.0 can be upgraded directly to SQL Server 2000
  • SQL Server 2000 can be installed as the default instance along side SQL Server 6.5 but only a single version can be active at a time. You can use “version switching” to switch between the different versions of SQL Server.
  • SQL Server 2000 can be installed as a named instance in addition to the default instance of SQL Server 6.5, 7.0, or 2000 installed on the computer. One default instance and a number of named instances can operate at the same time.