August 12, 2010 at 8:44 am
I've been tasked as part of an audit to find all SQL instances on our network, this may sound like it should be a simple thing to do, but I work in a software house with multiple instances used by various projects as well as our general infrastructure type SQL Servers.
I've noticed that when using the register new SQL server wizard in SQL 2K that I get a list of machines which is generated from some form of detection, I've noticed a number of workstations that only have enterprize console installed, being that this feature appears to exist within SQL Server, I'm wondering if there is a script I can put together that will detect SQL server instances and tell me what it's found (SQL Server Versions and whether client tools or full blown SQL) anyway I'm currently at a loss without checking on a machine by machine basis.
Any information as to how I can do this will be greatly received, thanks in advance.
August 12, 2010 at 8:51 am
open cmd and run
sqlcmd -L
if you want output of this in text file, lets say in c drive then :-
sqlcmd -L >c:\serverlist.txt
----------
Ashish
August 12, 2010 at 9:04 am
SQLPing is a utility that can help with this as well. The site appears to be down now, but it's a nice utility.
Note that this only gets you SQL Servers that respond to the network checks at UDP 1434. If they are down, or they don't respond, or the network guys have blocked this, then it won't work.
The only reliable method is to query each machine's registry or services for SQL services installed or running, but that's really a job for SMS or Operations Management Server.
August 12, 2010 at 9:07 am
ashish.kuriyal (8/12/2010)
open cmd and runsqlcmd -L
if you want output of this in text file, lets say in c drive then :-
sqlcmd -L >c:\serverlist.txt
Cool tip. Didn't even think about using SQLCMD for this.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 12, 2010 at 9:20 am
ashish.kuriyal (8/12/2010)
open cmd and runsqlcmd -L
if you want output of this in text file, lets say in c drive then :-
sqlcmd -L >c:\serverlist.txt
Hi thanks for this, it has certainly cut down the list of machines, is there a way of querying each machine for what it has installed ie SQL 2k, 2K5, 2K8 whether it has full SQL or MSDE / Express editions or just client tools?
August 12, 2010 at 9:28 am
From Steve: "The only reliable method is to query each machine's registry or services for SQL services installed or running, but that's really a job for SMS or Operations Management Server."
Having this done through SMS or MOM will get you that information. Else you would have to set something up to connect to each instance and get that information. So, the registry is the way to go.
Also as Steve noted, only those that are online will respond. So, not a complete listing. Certainly a nice quick look though.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 12, 2010 at 9:28 am
once you get the serverlist in text file then you can use that to check server version and other details by running query which you will run on any specific database.
put one .cmd file which will use for loop in server.txt file and lets say you have the script which you tested on individual server for getting all required detail with the name of versioninfo.sql
then in cmd file call this sql file.
if you want the result again in some text file then use the .bat file and call .cmd file here and take output in text file ,e.g., result.txt
----------
Ashish
August 12, 2010 at 10:08 am
Thanks again Ashish, I think I understand what you mean.
If I write the results from SQLCMD -L to a text file then write a batch file to query the top machine with a version discovery query and get that output to a text file then delete the top entery in above text file, looping the batch file until the above text file is empty?
that seems do able, not sure if that's what you mean but works in my head, I'll let you know how I get on.
August 12, 2010 at 10:48 am
Steve Jones - Editor (8/12/2010)
SQLPing is a utility that can help with this as well. The site appears to be down now, but it's a nice utility.Note that this only gets you SQL Servers that respond to the network checks at UDP 1434. If they are down, or they don't respond, or the network guys have blocked this, then it won't work.
The only reliable method is to query each machine's registry or services for SQL services installed or running, but that's really a job for SMS or Operations Management Server.
Actually, SQLPing V3.0 uses at least 8 different methods to find SQL Servers. It will also scan a range of IP address (0-255) to discover SQL Servers. I have found it to be very effective at discovering SQL Server instances, and have never found a better tool for this. It is very effective at finding SQL Server even when you have no access to the server.
Unfortunately, it is a gui based program, so you have to enter each subnet in the gui that you want to search. This can be a real chore for a large network.
August 12, 2010 at 10:56 am
no problem....just bit more explanation with code what I was saying earlier...
in bat file
for /f %%G in (serverlist.txt) do sqlcmd -S %%G -E -i serverversions.sql
or if the server need to be provided by use id and password then you can use syntax like :-
sqlcmd -S servername -U loginid -P password -i serverversions.sql
and you can use cmd to call this bat file like :-
@echo off
setlocal
rem I am using these script as generic one to connect on all production server and generate script as per requirement.
for /f "tokens=1-5 delims=/ " %%d in ("%date%") do set timestamp=%%d-%%e-%%f
sql.bat>> "Ashish_%timestamp%.txt".
if need any help further in this, let me know
----------
Ashish
August 12, 2010 at 11:57 am
ashish.kuriyal (8/12/2010)
open cmd and runsqlcmd -L
if you want output of this in text file, lets say in c drive then :-
sqlcmd -L >c:\serverlist.txt
This only works on SQL Servers that the workstation can see in broadcast mode and should not be considered reliable for "all" SQL Servers on a network. Steve's post tells you what you need to know.
August 12, 2010 at 3:06 pm
You can also download MAP 5.0. This is the Microsoft Assesment and Planning Toolkit. It's free and does a comprehensive investigation of your environment. You pretty much point it at your system and tell it what to look for.
For SQL it returns Instances installed, versions and editions, databases, sizes, logins,....
Pretty much everything you want.
The latest version has just been released and you can get it here: http://technet.microsoft.com/en-us/library/bb977556.aspx.
Its a 10MB file, install it on a workstation, but be aware that it installs SQL Express or at least needs a database. I haven't used it myself, but a collegue installed and tested it this week and what impressed with what it returned.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 13, 2010 at 2:21 am
Thanks for all the help, glad I posted this now got some stuff to be getting on with, Think I'll try MAP 5.0 first as this seems like the simplest solution and can use for other apps as well, will probably investigate the other solutions that have been suggested for thoroughness and to give me something to cross check against.
Thanks Again, I'll post back what I find in case you have to perform something similar
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply