Multi Server Query SSRS Report using Central Management Servers

Problem

We would like to have a single SQL Server Reporting Services (SSRS) report that will display SQL Servers versions for all of our SQL Servers that are registered in Central Management Server.

Solution

Sometimes it is not easy to have results from different data sources in one data set. To help with the solution we will create two reports – a parent report and a sub-report with a dynamic data source connection. The parent report will display current versions of all managed SQL Servers and it will look as though it is a single table.

Dynamic Connection String

In this report we will use a dynamic connection string. Starting with SQL Server 2005 Microsoft introduced Expression-based Connection Strings. You can have report parameters that will allow you to select different SQL Server names and use this parameter in the data source expression:

="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks

Create the Subreport

  1. Create a blank report named “_Demo_SubReport_1”
  2. Add the new parameter to the report:
    • Set name to “isp_SQLServer”
    • Set “Select parameter visibility” to “Hidden”
    • Set “Available Values” to  – “None”
    • “Default Value” – “None”
    • Data type – “Text”
  3. Create the new Data Source:
    • Give it any name (we will set it to “Dynamic_DataSource”)
    • Select “Embedded connection” and click on the “Expression” button:
      Data Source

    • Enter the following expression for the Connection string (note that we use the report’s parameter as Server Name in the connection string):
      ="data source=" & Parameters!isp_SQLServer.Value & ";initial catalog=master"

    • Make sure that the Credentials are set to the authentication that you will be using to run the report
  4. Create the new Dataset using “Dynamic_DataSource” created in step 3:
    Dataset

    • Use this query (or other query that you want to run against all SQL Servers):
      SELECT  @@SERVERNAME AS Server_Name, 
 SERVERPROPERTY ('ProductVersion') AS [Version], 
 SERVERPROPERTY ('Edition') AS [Edition]

    • Manually add fields under “Dataset Properties” (we have to add them manually because we are using a dynamic database connection):
      Dataset Fields

  5. In the Design View of the report:
    • Add a table with three columns using the Dataset created in the step 4:
      Tablix Properties

      Tablix

    • Resize the columns: “Version” to 1.5 inches, “Server_Name” and  “Edition” to 3 inches (we will use these sizes later in the parent report)
    • Delete the title (column names) row:
      &nbsp
      Delete the Title row
    • Reduce the report’s area to fit the table’s height:
      Final subreport
  6. Save the report.

Creating the Parent Report

  1. Create the new blank report named “_Demo_Report_1”
  2. Add the new Shared Data Source to the project:
    Shared Data Source

    This could be connection to the msdb database on your Central Management Server (CMS) or connection to another data source that contains your SQL Servers’ names. In our example we will use connection to the CMS server.

  3. Create the new report’s Data Source using the Shared Data Source created in step 2:
    using the Shared Data Source created in the step 2

  4. Add Dataset using the Data Source from step 3:
    Add Dataset using the Data Source from the step 3

    Use the query below to get SQL Server names registered in CMS (or use the query to your own data source with SQL Server names):

    SELECT s.name FROM dbo.sysmanagement_shared_registered_servers s

  5. In design view add to the report a List from the Toolbox items. This will create a Tablix report item. Set the “DataSetName” to the “Servers_List” dataset created in the step 4 using the Tablix item’s property:
    &nbsp
    Tablix

  6. Drag the “Subreport” item from the Toolbox to the Tablix area:
    Subreport
  7. Right click the “Subreport” and set the following properties:
    • “All_Versions” as the “Name”
    • Select “_Demo_SubReport_1” from the “Use this report as a subreport” drop-down list:
      Subreport

    • Under the “Parameters” property add the new parameter and select “isp_SQLServer” under the “Name” column and select “[name]” as the “Value”:
      Subreport parameter

    • Set the height of the Subreport the same as the height of the Tablix.
  8. Add three textboxes above the Subreport. These textboxes will be the columns titles:
    • Resize the textbox similar to the columns in Subreport: “Version” to 1.5 inches, “Server_Name” and  “Edition” to 3 inches
    • Select all three textboxes and set background color and borders:
      Subreport title

      Subreport title properties
  9. Save and preview the report:
    Final Report

Customizing the Report

To make the report more flexible you can add a parameter to the parent report and select a group or parent group in CMS:

CMS Groups

In order to do this replace the “Servers_List” dataset’s query with this:

WITH RegServers (parent_id, server_group_id, name ) AS 
(
    SELECT parent_id, server_group_id, name 
     FROM dbo.sysmanagement_shared_server_groups 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT e.parent_id, e.server_group_id, e.name 
     FROM dbo.sysmanagement_shared_server_groups AS e
         INNER JOIN RegServers AS d
         ON e.parent_id = d.server_group_id 
)
SELECT s.name, g.parent_id, sg.name as parent_group_name, g.server_group_id, g.name as group_name
 FROM RegServers AS g JOIN dbo.sysmanagement_shared_registered_servers AS s
  ON s.server_group_id = g.server_group_id
 JOIN dbo.sysmanagement_shared_server_groups AS sg 
  ON g.parent_id = sg.server_group_id
WHERE g.parent_id = @p_RegServer_ParentGroup -- OR g.server_group_id = @p_RegServer_Group

Use a similar dataset for the parameter’s query:

WITH RegServers (parent_id, server_group_id, name ) AS (
    SELECT parent_id, server_group_id, name 
     FROM dbo.sysmanagement_shared_server_groups 
    WHERE parent_id IS NULL
    
    UNION ALL
     
    SELECT e.parent_id, e.server_group_id, e.name 
    FROM dbo.sysmanagement_shared_server_groups AS e
        INNER JOIN RegServers AS d
        ON e.parent_id = d.server_group_id 
)
SELECT DISTINCT  g.parent_id, sg.name as parent_group_name 
 -- OR g.server_group_id, g.name as group_name
 FROM RegServers AS g JOIN dbo.sysmanagement_shared_registered_servers AS s
  ON s.server_group_id = g.server_group_id
 JOIN dbo.sysmanagement_shared_server_groups AS sg 
  ON g.parent_id = sg.server_group_id

Next Steps

  • Run different queries in a Subreport (or use different Subreports) to gather information across your SQL Servers and display it as a single table in one report.
  • Run multi-server queries using CMS as in this tip or using Registered Servers. This will give you similar results, but you would not be able to export the results to PDF, to schedule report, to use specific formatting or to use other SQL Server Reporting Services features.
  • Read more about creating and using subreports here.
  • Use this technique to generate the SSRS documentation for your databases. Schedule the report to run every month to have the most current configurations. Keep report snapshots to be able to review configuration changes.
  • Download the RDL files for this tip.

Related Posts

One comment

  1. Thanks for the great tip! I couldn’t get the header to line up just right so I added the column header in the subreport with a visiblity expression on the header row to only show it if it was the first row (based on the server list query row order)

Leave a Reply

Your email address will not be published. Required fields are marked *