Three wheels on my wagon, but how many SQLServers on my network ?

Three wheels on my wagon, but how many SQLServers on my network ?

This post is in direct response to a question I was asked today, and I thought I\’d post it up because:

1.      My cell phone doesn\’t get on with social networking sites

2.      If I write it down, I stand a fighting chance of remembering it

and

3.      Hey, its Thursday. Who needs and excuse to blog anyway ?

The question posed was in relation to the SQLCMD command-line switch -L, which (according to MSDN and others) will tell you the names of all SQLServers on your network. Really ? Actually, not quite, which is why the question was asked in the first place. When run from a laptop, none of the actual real servernames were returned, just local instances on colleagues\’ laptops. To clarify, that means MyLaptop\\INSTANCE1 was visible, but ServerInDataCenter\\INSTANCE1 was not.

Let me be the first to admit that I\’m no expert in all matters SQLServer (well, *some* matters, but definitely not all), so what follows is mainly conjecture, but it all hinges around what you consider to be *your* network.

Most corporate entities these days will have workstation IP address ranges (typically 192.168.x.x with a subnet mask of 255.255.x.x), and will also have different server IP address ranges (typically 10.1.x.x with various masks etc).

Whilst both of these addresses relate directly to NICs (in most cases) that are physically connected by copper / fibre, basic principles of TCP/IP state that they are not on the \’same\’ network.

SQLCMD -L will look around your \’local\’ network, 192.168.x.x, and will get all the SQLServers broadcasting on that network ONLY. To get to the physical servers on other network segments, you\’d have to use DNS to resolve the hostname to IP address, and then you can connect (after passing through several gateways etc.).

It is my postulation that SQLCMD won\’t use DNS to break out of the local network when used with the -L switch, and therefore cannot return details of any remote servers. So, if you run it on a laptop, you get one set of results, and if you run it on a server, you get another.

Which sort of answers the question. Sort of. At least, I hope it helps out a little.

Back soon…