SQL Server Inventory of Services

Posted on 2011-05-02
Medium Priority
Last Modified: 2012-08-13
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?
Question by:Bitadmin
  • 2
LVL 22

Expert Comment

ID: 35513383
I am currently working on this very problem, myself.  

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).
LVL 22

Accepted Solution

8080_Diver earned 200 total points
ID: 35513486
You may have to determine the answers for each service separately:

For instance, SS2005's SSRS will require a ReportServer database so you can figure that, if one exists, the SSRS is (probably) installed.  I am not sure about the other services (e.g. SSIS and SSAS).

select * from sys.databases where name like 'ReportServer%'

Open in new window


Author Closing Comment

ID: 35516650
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question