Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two machines, each with a default instance of SQL Server 2008 installed; each exhibiting a different behavior.

  • On PC1, when I run sqlcmd -Lc, I do not see PC1 in the list of SQL servers on the local network when the SQL Browser Service is not running. When I start the SQL browser service, PC1 appears in the list.
  • On PC2, when I run sqlcmd -Lc, I see PC2 in the list, whether or not SQL Browser Service is running. If SQL Browser Service is running, I also see PC2\SQLEXPRESS.

Why does PC1's default SQL instance not show up in the list of SQL Servers?

share|improve this question
up vote 7 down vote accepted
+50

Why does sqlcmd -Lc does not show the local instance?

and in your case even if sql server service is working.

ok first will talk about what sqlcmd -Lc does

it "Lists the locally configured server computers, and the names of the server computers that are broadcasting on the network" for reference

and a note to keep in mind is

"Because of the nature of broadcasting on networks, sqlcmd may not receive a timely response from all servers" for reference

SQL server browser service is the service that Broadcasts and upon startup, SQL Server Browser starts and claims UDP port 1434.

if you unplug your machine from the network and type sqlcmd -Lc, you wont be able to see your machine listed, because If it doesn't get a reply from other available SQLs , it doesn't list itself.

Now to know more about why sometimes sql server doesnt show up, you need to know about UDP and how sql server uses it to discover other sql servers

UDP is a connection-less protocol UDP does use ports, it allows multiple processes to be using UDP at the same time. Since it's connection-less, any connection-like properties between two systems must be handled by the application. It's a fairly small protocol and therefore takes up relatively little bandwidth on the wire.

SQL Server, uses UDP in two ways

To discover other SQL Servers that are out there. To discover how to connect to a named instance.

now after knowing how sql server service works and how it uses UDP here below is list of reasons why you dont see your local instance showing up

  1. The UDP communications gets "lost" on the way.

  2. UDP port 1434 are blocked on the network with firewall or windows firewall or IPSEC policy

These might be the reasons and i suggest using a product like sqlping to discover your sql on the network, because if it doesnt show up it doesnt mean its not there, its just the way its broadcasting is the problem


Updates:

Make sure (HideInstance Flag is No)

1)In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for , and then select Properties. On the Flags tab, in the HideInstance box, make sure its No.

2) Make sure (Sql Browser Service is Active) go to SQL Server Configuration Manager, check properties for SQL Browser service, in Advanced tab, make sure it is active & restart it Activate Sal browser service

3) Finally try this registry Key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQL Browser\SsrpListener

make sure it has value 1 if you cant find it under 90 folder, search for SsrpListener

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.