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
The UDP communications gets "lost" on the way.
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
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
