Information from ALL SQL Servers

Question

Information from ALL SQL Servers

WhiteLotus

SSChampion

Points: 11963
More actions
October 8, 2018 at 5:47 pm

#373421

Hi

I would need to grab all information such as Total Memory ,number of CPU being used , SQL collation , SQL version , System Type such as 64 bit or32 bit from ALL SQL servers that I have.
I prefer not to do this manually ( meaning login into eachserver to collect the information ) as it will need lots of effort .
Wondering if you know the better way on how to do this ?

Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Two options, 3rd party tools are built for a reason. They do all the work so we don't have to. SQL Monitor will give you all that information and more.

2nd option, write a powershell script that connects to all your servers (you can provide a list) and runs a set of queries to retrieve that information (those are all standard counters). Build a table (or tables) to store the results. Also build reports and alerts to make the information you collect actionable. In short, build a monitoring tool.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

Perhaps a 3rd option. Rather than pulling the data from all instances, have each instance gather that info on itself and push it to a central location.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

WhiteLotus SSChampion Points: 11963 More actions · Answer 3

Grant Fritchey - Tuesday, October 9, 2018 4:09 AM
Two options, 3rd party tools are built for a reason. They do all the work so we don't have to. SQL Monitor will give you all that information and more.
2nd option, write a powershell script that connects to all your servers (you can provide a list) and runs a set of queries to retrieve that information (those are all standard counters). Build a table (or tables) to store the results. Also build reports and alerts to make the information you collect actionable. In short, build a monitoring tool.

thanks for the reply . much appreciated
How easy to complete the 2nd option ? ( using powershell ) . Any information or link related to this ?

WhiteLotus SSChampion Points: 11963 More actions · Answer 4

ScottPletcher - Tuesday, October 9, 2018 3:32 PM
Perhaps a 3rd option. Rather than pulling the data from all instances, have each instance gather than info on itself and push it to a central location.

thanks for the reply . much appreciated
how do we do this ?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

WhiteLotus - Tuesday, October 9, 2018 4:43 PM
thanks for the reply . much appreciated
How easy to complete the 2nd option ? ( using powershell ) . Any information or link related to this ?

Not as such no. I have several blog posts on how to poll servers using PowerShell and my book has four chapters on all the important counters and how to query them. Put that information together.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

Let's consider what can actually happen with some software that's actually capable of monitoring "enterprise wide" or even just "domain wide" if someone learns the password(s) for the user login(s) that the monitoring software uses. 😉

--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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Given the level of detail you need - any IT inventory tool would do that ?(there are tons available at CNET). You can get that from basic WMI queries as well. Of course - a lot of networks these days tend to frown on a lot of discovery type tools running so YMMV.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 8

I wouldn't write this anymore. It's too hard to manage the data, keep the queries up to date, and it becomes a job. I would rather use a tool of some sort. Paid, open source, etc. Use what others have written.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com