Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

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'

Print | posted on Wednesday, June 07, 2006 12:59 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: SQL Server 2005 instance number

Can you reference where you found this info ?
6/8/2006 7:28 AM | JOn
Gravatar

# 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.
6/8/2006 9:34 AM | Tara
Gravatar

# re: SQL Server 2005 instance number

How can i verify how many instance i have in a sql server 2005
7/14/2006 10:28 AM | humberto
Gravatar

# re: SQL Server 2005 instance number

How Will I get the unique sql server instance Id?
9/11/2006 5:26 AM | Prashant
Gravatar

# 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
10/7/2006 6:38 PM | Don
Gravatar

# 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.
10/8/2006 8:51 PM | Tara
Gravatar

# 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
10/8/2006 9:51 PM | Don
Gravatar

# re: SQL Server 2005 instance number

I need this new instance for the witness setup, any help will be greatly appreciated.
10/8/2006 9:53 PM | Don
Gravatar

# 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.
10/9/2006 9:24 AM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET