Wednesday, August 11, 2010

How to create an extended SQL Server Configuration Report

By default has the SQL MP some nice Reports available. However, one of them, the SQL Server Configuration Report, could use a boost.

This report shows information on these twelve items related to the SQL Server Instance:

  1. Replication Distribution Database
  2. Master Database Log Location
  3. Error Log Location
  4. Replication Working Directory
  5. Master Database Location
  6. Authentication Mode
  7. Service Pack Version
  8. Audit Level
  9. Version
  10. Language
  11. Cluster
  12. Error Reporting Enabled

But by using the Microsoft Generic Report Library an ‘Extended SQL Server Configuration Report’ is easily created which adds these items to the default Report:

  1. Install Type
  2. Account Name
  3. Tools Path
  4. Edition
  5. Instance Name
  6. SQL Server Windows Service Name
  7. SQL Server Agent Windows Service Name

When this Report is created it can be published or saved to a MP in order to be used again with a simple double click on the mouse button. This posting is about how to create such an extended Report.

  • Open the SCOM Console with Admin Permissions;
  • Go to Reporting > Microsoft Generic Report Library > open the Custom Configuration Report;
  • Click the Add Group button > select ALL the available SQL 200x DB Engine Groups > Add > OK;
  • In the Report Fields section, select these options:
    Replication Distribution Database
    Master Database Log Location
    Error Log Location
    Replication Working Directory
    Master Database Location
    Authentication Mode
    Service Pack Version
    Audit Level
    Error Reporting Enabled
    Install Type
    Account Name
    Tools Path
    Instance Name
    SQL Server Windows Service Name
    SQL Server Agent Windows Service Name
  • In the From field, open the dropdown menu and select Advanced and create an offset of 7 days for instance;
  • Run the Report;
  • Tweak when needed;
  • Publish or save the report to a MP for later usage.

The SQL admins will love this report since it will tell them much about the configuration of the monitored SQL instances.

1 comment:

Vic said...

I was looking for possibility to get SQL Edition in report. That's exactly what I needed. Thanks a lot!