Default Instance and Named instance using the same environment SQL 2008

Question

Default Instance and Named instance using the same environment SQL 2008

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

dan-572483 SSCoach Points: 15870 More actions · Answer 1

This is correct - you have 1 instance.

I ran into this confusion recently. On my home computer I have an instance of SQL2008 R2 Express I installed some time ago, and I recently installed SQL 2008 Evaluation edition.

I thought the Express instance was the default, and the 2008 instance a named instance because I gave it a name during install, and it appears in SQL Configuration Manager as SQL Server (SQL2008EVAL).

But when I tried to connect to these instances from a VM, I could only connect to the 2008 Eval instance using the IP and the Express Edition not at all. After turning on Browser service, I was able to connect to the Express instance using <IP>\SQLEXPRESS. This proves that 2008 instance is the default instance and the Express instance is named.

So apparently:

1) Express edition installs by default as a named instance called SQLEXPRESS.

2) Non-Express editions install as a default instance if there isn't a default instance already, even if you specify an instance name during install.

Interestingly, in SQLMS I can connect to the eval edition using the machine names only, or <machinename>\SQL2008EVAL.

Ash SSC Eights! Points: 947 More actions · Answer 2

sqldev(default) SQLdev\dev(named)

It seems like you have alias (sqldev) created for the named instance(SQLdev\dev) that you have installed. We had this setting here too. Why this was setup because sometime application can't connect to the named instance and can't recognized the "\" option in the connection string.

One way to check if this is same you have....

1) Try running the below code.

EXEC master..xp_instance_regenumvalues

@rootkey = N'HKEY_LOCAL_MACHINE',

@key = N'SOFTWARE\\Microsoft\\MSSQLServer\\Client\\ConnectTo'

--If SQL 32 bit installed on 64 bit machine (WOW64) mode, then use the below syntax to check the alias:

EXEC master..xp_instance_regenumvalues

@rootkey = N'HKEY_LOCAL_MACHINE',

@key = N'SOFTWARE\\Wow6432Node\\Microsoft\\MSSQLServer\\Client\\ConnectTo'

2) If no output comes, another way to check that is,

connect to default instance (sqldev); right click on it and open server properties --> in the general tab on right hand side you will see it's actual real name..If it is sqldev\dev ; then that proved that you have an alias setup.

Hope this helps,

Thanks,

AB

mhg1063 Say Hey Kid Points: 682 More actions · Answer 3

Thanks all for your assistance. I believe you're right AB. While the sql did not return a result. I connected then did a right click on the properties. I posted an attachment.