Finding lost or forgotten SQL Servers
-
Comments posted to this topic are about the item Finding lost or forgotten SQL Servers
There is an exception to every rule, except this one...
-
I'd have started with a cmd window and
sqlcmd -Lbut that's an interesting technique, thanks 🙂
-
a648 (4/19/2011)
I'd have started with a cmd window andsqlcmd -Lbut that's an interesting technique, thanks 🙂
But this article is about finding instances regardless of whether SQL Browser is running...
-
nice article. I like the ability to schedule the scan with the agent. Will be very helpful @ my current employer. Thanks.
-
Great example, I will try it out, since I am also interested in findning computers where SQL Server is installed but stopped.
However, since you are using 'NET VIEW' to list computers, you will only see the computers on the same subnet as the computer you are running the script, right? (At least that is the way it works for me, I don't know if it is a configuration issue with broadcasting, I get the same result with SQLCMD -L). A way to get computers from separate subnets would be to query the AD, for example like this:
-
I have been using SQLPing for years (http://sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx), but this method is also interesting.
-
The last script builds a job that executes every Monday at 9am 🙂
There is an exception to every rule, except this one...
-
I like the script. The one thing I don't like is that you also get computers that have services like the "SQL Server VSS Writer".
-
@Michael.eklof -- This script pulls in all server names across all domains for me, please let me know if this is not the case for you.
There is an exception to every rule, except this one...
-
What Michael said is true, Servers on another VLAN will not be included.
-
I tried sql ping for a while, the command line version, but it takes forever because it needs to look at each IP.
It returns a nice report of what version and all but it started to fail for some reason so I gave it up.
I also found that using the sqlcmd -L is problematic in that it only reports on servers on the subnet that you run it on.
My current solution is to use powershell against AD and it runs very fast so I can run it each night and compare the results against my table of previously known sql servers.
I did some testing of turning sql services off on a test server and the powershell script did not report the server if the service was off.
This is probably not as important as finding a new one that someone installed but forgot to tell you about.
-
Haven't considered VLAN's, thanks.
There is an exception to every rule, except this one...
-
Maybe you can add it on your next script 😉
-
I second the motion on SQLPing. I've never had it fail in my enterprise of 130+ SQL Servers. It's slower because it uses several methods of finding a SQL Server, whether it's started or not.
-
I prefer the use of SQLPing (especially command-line) or the Quest Discovery Wizard (which can also be used command-line as it licenses components of SQLPing). The reason it can take longer is it uses all known methods of discovering SQL Servers:
- UDP request against port 1434
- TCP request against port 1433
- TCP request against port 2433
- Registry scan
- SCP scan
So if your account doesn't have rights to the system being scanned, or they've done something to attempt to hide the computer, SQLPing may still pick it up when a typical PowerShell script won't. Another option is to use nMap, which does fingerprinting and can identify SQL Servers hiding on non-standard ports (or SQL named instances running on dynamic ports) where the user account being used doesn't have appropriate rights.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply