How Do You Inventory Your SQL Servers?

Question

How Do You Inventory Your SQL Servers?

dbaforever

Hall of Fame

Points: 3740
More actions
May 27, 2011 at 1:53 pm

#389606

hello everyone,
For those of you that manage 25 or more database servers, how do you keep track of server names, which version is installed, database names, SP levels etc?
Are you using 2008's Policy Management feature or something else?
Thanks for any ideas. John

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

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

ninad.soman0 Ten Centuries Points: 1111 More actions · Answer 1

Hi,

In 2008 you can register the server names on one SSMS instance and form a server group. Once the server group is formed right click and then you can write one query to get the sp,edition, version details -

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

This query will be run against each registered instance and will return the results in a table format.

I haven't used policies feature, but find the above method handy.

You will have to give time for registering them though. Easy to login to the instance once you have the servers registered.

I use a traditional spreadsheet to maintain the server names and its related details. 🙂

Thanks,

Ninad Soman

dbaforever Hall of Fame Points: 3740 More actions · Answer 2

Thanks for the good idea, Ninan.

Supposedly (I have not tried it yet), by using Policy-based management, you can schedule it to "poll" or retrieve this kind of information and it will pull it from ALL SQL Servers on your network.

That's what I like about it, that you do not have to register or add your new servers.

I need to learn more about 2008's policy-based management.

Thanks again. john

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

I'm not sure that PBM is the right answer. Its a good way to verify that servers are in compliance with policies but I haven't seen it as a way to collect information like that particularly well.

In various assignments I have seen a lot of home built systems that look at the servers each day. In most cases all that system is told is the name of the server and it does the rest.

I have been working on a project for codeplex to do that. Hopefully have something up within a month.

CEWII

dbaforever Hall of Fame Points: 3740 More actions · Answer 4

Elliott,

What you have done, retrieving info for each server name, is the direction I have been headed. That gives the advantage of inserting the info into tables that you can then report from.

A BIG disadvantage, at least for me, is that you have to write dynamic SQL code. All those quotes, wow.

With PBM, it can generate a text report. You then have to import that into a database. And that's my goal - to have table-based info about my database servers.

Thanks, John

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

Using very little dynamic SQL, my solution is SSIS based. And no linked servers, which are not stable enough for my purposes.

CEWII

ninad.soman0 Ten Centuries Points: 1111 More actions · Answer 6

Came across a white paper available about working with PBM in SQL 2008

http://msdn.microsoft.com/en-us/library/dd938891(v=sql.100).aspx

looks interesting ...

Thanks,

Ninad

dbaforever Hall of Fame Points: 3740 More actions · Answer 7

Well I like what I hear, Elliott. You have piqued my curiosity. Are you using CLR?

Please elaborate. All my servers are either 2005 or 2008 with several of those having the Express edition.

and Thanks, John

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 8

I use some CLR in the SSIS packages but not much otherwise. There isn't really any need to use SQLCLR for this. I generate a list of servers and then use a ForEach loop component to walk through the list. My project has several "features", one of them is machine/sql metadata.

CEWII

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 9

As a side note I am planning on support for SQL 2000 and above and all editions. 2005/2008 support is usually the same, 2000 sometimes requires mods.

CEWII

dbaforever Hall of Fame Points: 3740 More actions · Answer 10

Impressive. Sounds like you have developed a tool that you could sell. But anyone can buy software. I want to build it myself. I've had this dream for a couple of years, just have not had the time to get serious.

John

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 11

There is a certain accomplishment in building one. There is more to it than I originally thought and it took a whole lot longer to build and debug than I thought it would. I never planned on selling it. But it would provide a nice base to build a wider solution on.

I am currently working on debugging the environment variable collection feature and re-architecting the credential store. I'll probably start on reports soon.

CEWII