how or what tools can I use to return a list of SQL servers in our network

Question

how or what tools can I use to return a list of SQL servers in our network

sqlfriend

SSC Guru

Points: 52588
More actions
May 26, 2011 at 3:25 pm

#250812

What are the options for how to pull a list of SQL servers (and their versions, service pack, services installed for exmaple integration service or reporting service etc) that exsit in our network?
We already have a list but kind of old, so want to generate a new list to see how many servers we have, it may includes versions like sql server 2000, 2005, 2008 etc.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Warwick Rudd Ten Centuries Points: 1220 More actions · Answer 1

Sqlping will allow you to discover all servers and work stations on your network with SQL server installed. You could then use powershell to connect to all of the servers returned and collect the appropriate info from each instance.

MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Warwick rudd (5/26/2011)
Sqlping will allow you to discover all servers and work stations on your network with SQL server installed. You could then use powershell to connect to all of the servers returned and collect the appropriate info from each instance.
MCT MCITP Database Admin 2008

Great... do you have such a Powershell script or a URL that does?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Shabba SSCertifiable Points: 5007 More actions · Answer 3

There are quite a few tools out there but one of the best in my opinion which was free the last time I looked was http://www.quest.com/discovery-wizard-for-sql-server/. Although as already mentioned you could create a powershell script to give you exactly what you want.

Cheers,

Mark

george sibbald SSC Guru Points: 104210 More actions · Answer 4

run this from the command line to return all sql instances in the same domain

sqlcmd -L

edit - sorry did not read question properly :blush:, this only returns instance names

---------------------------------------------------------------------

Shabba SSCertifiable Points: 5007 More actions · Answer 5

Shabba

SSCertifiable

Points: 5007

May 27, 2011 at 7:23 am

#1330973

Shame on you George! 😀

george sibbald SSC Guru Points: 104210 More actions · Answer 6

Shabba (5/27/2011)
Shame on you George! 😀

I still got a point!

is this any use

http://qa.sqlservercentral.com/articles/64016/

read the discussion as well

---------------------------------------------------------------------

sqlfriend SSC Guru Points: 52588 More actions · Answer 7

Thanks, that is helpful.

I also found we have a discovery software used by our network group, which I can use some queries to pull from to get SQL server lists.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Shabba (5/27/2011)
There are quite a few tools out there but one of the best in my opinion which was free the last time I looked was http://www.quest.com/discovery-wizard-for-sql-server/. Although as already mentioned you could create a powershell script to give you exactly what you want.
Cheers,
Mark

Warwick Rudd said the same thing in one of the posts above. Everyone says "create a powershell script" but no one ever posts such a thing. I beginning to think that the people who make such recommendations haven't actually created one for themselves. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 9

sqlfriends (5/26/2011)
What are the options for how to pull a list of SQL servers (and their versions, service pack, services installed for exmaple integration service or reporting service etc) that exsit in our network?

Altiris would do that - not only for SQL Server but for whatever software you have out there in your network. In my experience you may be surprised of the stuff you will find out there.

As a plus you can use Altiirs to push patches as appropriate.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

sujitkmishra Mr or Mrs. 500 Points: 504 More actions · Answer 10

Approach:

It seems you have already got the list of SQL Servers in your network using Discovery.

Get the server names in a table.

Loop through the servers and run queries on using openrowset. Also can use linked servers to achieve the same.

Server property -- serverpropery(propertyname)

Infact you can get all server information you need with minimal overhead. You can use command line wmi to get the hardware details as well.