Help - 2 instances !

[sqlcasey]

Hi,

The other DBA here set up a server with 2 instances of SQL Server 2005. I'm not sure he set it up right, because when I look at the file structures, there are 3 directories:

MSSQL.1
MSSQL.2
MSSQL.3

Question 1) Shouldn't they be named by their instance name so they can be identified?

Aside from that, I also realize, I have no idea how to properly set up my aliases on my client.

For example, for aliases, I use:

SERVERNAME\instance1_name
SERVERNAME\instance2_name

However, when I make a connection to both of these aliases in Mgmt Studio, they are in fact pointing to the same instance... as shown when I run sp_helpfile - both "instances" point to the exact same directory - MSSQL.1, so I can only assume they are in fact are the same instance but by different alias names.

Question 2) How am I supposed to name the aliases so that they point to the correct instance?

Question 3) How can I even tell WHAT the correct instance names are? Is there a query I can run?

THANKS MUCH

 

[ptheriault]

I SQL server 2005 you no longer have the $instance name folder. Each instance gets a folder in the order of install. For example
The .1 is always the default instance.
If you have installed analysis services with your default instance it will create a .2 folder.
Your named instance will be the .3 folder.

This can get really confusing expecially if you have more than 3 instances.

Check to see if analysis services are installed.



- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

If both instances are pointing to the same folder and all the DBs on both instances are "sharing" data files (double-check this), then I can only assume you don't actually have 2 instances so much as you have 2 registrations in SSMS for the same instance.

But check the DB properties after you've checked out Paul's suggestions. As far as I know, you can still only have 1 default instance of SQL on the server and all others have to be named.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

The .1 folder will be the first service installed, which may not be the default instance.

Each service that you install will get it's own folder. The default instance will have one, SSIS will have one, SSRS will have one, SSAS will have one; all depending on what services you have installed. Each named instance will also have one.

Check the services control pannel (services.msc) on the server and see what services are installed. If you look at the path to the executable in the services control pannel it will tell you which service is located in which folder.

When dealing with more than one instance it's important to make sure that you always have the database files in the correct folders or you will go nuts tracking stuff down. Trust me on this one, I've got a dev server with 9 instances including the default instance and SSIS.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sqlcasey]

I figured out what the problem was, after searching various boards.

The SQL Browser service must be running in order to connect to the name instance, and

The name instance in the form of SERVERNAME\instance, when you set up your alias.

This was the info I needed