How to know if an insance is a default instance or a named instance?

Question

I obtain a list of instance names by using the method in this article:

http://blogs.msdn.com/b/askjay/archive/2011/10/11/how-can-i-get-a-list-of-installed-sql-server-instances.aspx

On my computer, there are two instance names under registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

That is:

SQLEXPRESS   
MSSQLSERVER

Then how to know whether the above two instances are both named instance or one of them is default instance?

Thanks

Answer 1

check this on both server

SELECT CONVERT(char(20), SERVERPROPERTY('servername')) ServerName,

CONVERT(char(20), SERVERPROPERTY('InstanceName')) instancename

in my computer, default instance instancename column gives "NULL"

http://msdn.microsoft.com/en-IN/library/ms174396.aspx

Answer 2

On my computer, there are two instance names under registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

That is:

SQLEXPRESS   
MSSQLSERVER

Then how to know whether the above two instances are both named instance or one of them is default instance?

SQLEXPRESS  is instance name, MSSQLSERVER is default.

---

Saeid Hasani [sqldevelop]

Answer 3

You can also check this from services

go to start-> run type services.msc

in the services window look for SQLServer service. It will show instance name also within brackets. If its MSSQLSERVER then its default instance other wise its named instance  showing instance name within braces.

See example below

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 4

I obtain a list of instance names by using the method in this article:

http://blogs.msdn.com/b/askjay/archive/2011/10/11/how-can-i-get-a-list-of-installed-sql-server-instances.aspx

On my computer, there are two instance names under registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

That is:

SQLEXPRESS   
MSSQLSERVER

Then how to know whether the above two instances are both named instance or one of them is default instance?

Thanks

Hi,

Generally when you don't give name to SQL server instance or select default instance during installation  it takes MSSQLSERVER as a default name so where ever you see MSSQLSERVER its always default instance. SQLEXPRESS will be SQL express instance( although you can rename it) all other instances would be named accordingly. SO MSSQLSERVER is default instance all other which appear will be named instances

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

My TechNet Wiki Articles

Answer 5

Ping with instance name and IP address.

pinging only ip not ping with instance it is named instance.

Default instance ping's with both (name/IP).

CMD:> osql -L  you will receive instance name available in machine or network.

---------------------------------

Thanks

Ramu A |