Take the 2-minute tour ×
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.

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
    
Is the PC1 instance configured to listen on a non-default port? What protocols does the instance have active (check the SQL Network Management tool)? –  Remus Rusanu May 29 '12 at 9:35
    
@RemusRusanu - looks normal - has TCP/IP enabled, port no 1433. –  Shaul Behr May 29 '12 at 9:59
    
i ran sqlcmd -Lc and my local PC was showing regardless of whether SQL Browser Service, so i restarted my sql server service and then tried sqlcmd -Lc, it only shows when the sql service is started, its like it was cached somewhere and after restarting the sql service it started behaving right –  AmmarR May 29 '12 at 11:43
    
did @AmmarR answer resolve your problem or is further troubleshooting required? –  Jeremy Thompson Jun 11 '12 at 3:48
    
@JeremyThompson - no, despite the very useful and interesting information he posted, I still haven't resolved the problem. –  Shaul Behr Jun 11 '12 at 8:10

1 Answer 1

up vote 4 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
    
PC1 has a default instance on port 1433. Connection works fine. It just doesn't show up when I try show a list of all SQL servers on the network (sqlcmd -Lc). –  Shaul Behr May 29 '12 at 10:02
    
in your questions you said it doesnt show up with sql browser service is disabled, but When u start the SQL browser service it shows up, and this is a normal behavior, but for PC2 you said your named instance doesn't show up. the sql browser service is responsible for showing up, but you need to understand how it works with named instances, "Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry...." check this site msdn.microsoft.com/en-us/library/ms181087.aspx and see section "How SQL Server Browser Works" it explains –  AmmarR May 29 '12 at 11:38
    
The question is not on PC2's named instance. The question is why PC1's default SQL instance does not show up in the list of SQL Servers when I run sqlcmd -Lc –  Shaul Behr May 29 '12 at 13:54
    
ok i just want to make sure am understanding you right, you said PC1 does not show up in the list, even if sql browser service is enabled –  AmmarR May 29 '12 at 16:34
    
No, PC1 does show up if SQL Browser service is running. –  Shaul Behr May 30 '12 at 6:42

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.