Tara Kizer Blog

Tara Kizer

SQL Server 2005 instance number

If you've installed multiple instances of SQL Server 2005 on the same database server, perhaps you've noticed Microsoft's naming convention for the file locations.  They are now using MSSQL.n, where n is the instance number.  We have 4 instances installed on a database server.  One of them is the default instance; the other 3 are named instances.  Here is what our directory structure looks like:

E:\MSSQL.1\MSSQL\...

E:\MSSQL.2\MSSQL\...

E:\MSSQL.3\MSSQL\...

E:\MSSQL.4\MSSQL\...

This quote, from this article, explains the naming convention:

The first instance ID generated is MSSQL.1; ID numbers are incremented for additional instances as MSSQL.2, MSSQL.3, and so on. If gaps occur in the ID sequence due to uninstalls, ID numbers are generated to fill them. The most recently installed instance may not always have the highest instance ID number.

I didn't install the default instance until I was done installing the named instances, so the default instance is MSSQL.4. 

Today, I was restoring databases onto this server from a SQL Server 2000 server.  I needed to change the path to the database files as the source paths were E:\MSSQL$InstanceName\DATA\, but I now wanted them to be E:\MSSQL.2\MSSQL\DATA\ since MSSQL.2 corresponds to the instance where I am performing the restore.

But is there an easy way to find out which instance name belongs to which instance number? 

The values are stored in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL.  SQL Server has an undocumented stored procedure that allows you to read the registry, so we can use that to easily figure this out.

Here is one way to find out which instance number belongs to which instance name:

EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', 'InstanceName'

Legacy Comments


JOn
2006-06-08
re: SQL Server 2005 instance number
Can you reference where you found this info ?

Tara
2006-06-08
re: SQL Server 2005 instance number
JOn,

Which part do you need referenced?

I provided a link to the article that I quoted which explains the instance numbers.

humberto
2006-07-14
re: SQL Server 2005 instance number
How can i verify how many instance i have in a sql server 2005

Prashant
2006-09-11
re: SQL Server 2005 instance number
How Will I get the unique sql server instance Id?

Don
2006-10-07
re: SQL Server 2005 instance number
Hey guys,

I have a named instance defined, I want to install another instance of sql server 2005 (I have a standard edition) however when i start the setup install again, it does not give me any such option. Is there a special switch on setup .. please help me

Tara
2006-10-08
re: SQL Server 2005 instance number
You probably haven't made it far enough through the wizard yet then as it will ask you if you want to install the default instance or a named instance. If you want to install a named instance, it then asks for the name of it.

Don
2006-10-08
re: SQL Server 2005 instance number
Hi Tara,

Sorry I wasn't clear for the first time, actually i ran through the setup once and created one instance; which worked great and on top of this, I installed SP1 and began mirroring. Now I want to create another instance, so I ran the setup again but this time, setup didn't let me create a new instance... So I wondering if there is a special switch that I need to use during setup.

Regards

Don
2006-10-08
re: SQL Server 2005 instance number
I need this new instance for the witness setup, any help will be greatly appreciated.

Tara
2006-10-09
re: SQL Server 2005 instance number
You must not be selecting the right options in the wizard then. Please state what you are selecting in each screen.