SQL Server Inventory of Services
I have 23 SQL Server databases. I need to populate an inventory database that contains information about the SQL Server instances and the machines they are running on.
I would like to create a view that uses t-SQL to poll the instance to find out what SQL Server Services are installed (SSIS, SSRS, Extc.) I am working with SQL Server 2000 and 2005 versions.
How can I do this? What system view, stored procedure can I use to check for services installed/and or running?
I would like to create a view that uses t-SQL to poll the instance to find out what SQL Server Services are installed (SSIS, SSRS, Extc.) I am working with SQL Server 2000 and 2005 versions.
How can I do this? What system view, stored procedure can I use to check for services installed/and or running?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks that was very helpful for the SQLServer 2005 instances. As for SQLServer 2000 I will have to dig deeper for those. I only have 7 left anyway the bulk are in 2005. The sys.databases was a great save
For openers, you need to have a login that has access to all of the servers. Then you you are going to have to develop two separate sets of queries, one for SQL Server 2005 (and later) and one for SQL Server 2000. I would recommend approaching this from the standpoint of creating an SSIS package (since, hopefully, sometime in the near future you will have migrated your SS2000 databases to SS2005/2008/Denali) to do the processing.
The manner in which you obtain the data from SQL Server 2000 and SQL Server 2005 (and later) are different. That leads to some issues with regard to trying to make a single process to acquire the data. As I said, I am currently working on this problem myself. I have established a means for building a Data Dictionary database on each instance and I am working on an SSIS package to process the various instances and consolidate the data into a separate instance's database.
This is not a trivial task that, as far as I know, can be answered with a single query (upon which you could build a view).