Integrating Monitoring with SQL Server Reporting Services

  • Article

This sample shows how to use SQL Server Reporting Services to create rich interactive reports using the monitoring data that Windows Server AppFabric collects. Included are two sample reports that give you enhanced visibility into the WCF service operations that AppFabric monitors. You will learn about two of the most useful views that are exposed through the AppFabric monitoring database. This sample will work with any application. We recommend the Common AppFabric Sample Application, which was created for use with AppFabric samples. To find this application, navigate to the <samples>\SampleApplication\OrderApplication folder, where <samples> is the path under which you have installed the AppFabric samples.

Note

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Prerequisites

  • SQL Server Reporting Services

  • Report Builder 2.0 (Optional download if you want to be able to edit the reports or view the details of how they were created.)

  • At least one WCF or WF service hosted in AppFabric.

Important

There must be data in the monitoring database. The reports will not function without monitoring data.

Important

The aggregationEnabled setting in the (root) SYSTEMDRIVE%\Windows\Microsoft.NET\Framework\v4.0.xxxxx\Config\web.config file should be changed to false before trying this sample as in the following example.

<microsoft.applicationServer>
  …
  <collectors>
    <collector name="" session="0">
      <settings retryCount="5" eventBufferSize="10000" retryWait="00:00:15" samplingInterval="00:00:05" aggregationEnabled="false" />
    </collector>
  </collectors>
  …
</microsoft.applicationServer>

Sample Files

This sample comes with the following two report files:

  • OperationOverview.rdl

  • OperationDrilldown.rdl

Setting Up and Running This Sample

  1. Install SQL Server Reporting Services. For more information, see https://msdn.microsoft.com/en-us/library/ms143736.aspx.

    Important

    Be sure to perform a Native Mode Installation as opposed to a SharePoint Integrated Mode installation.

    Be sure to install the Database Engine, Reporting Services, and Management Tools.

    SQL Server 2008 installation installs Report Builder 1.0 along with Reporting Services. You will need to download and install Report Builder 2.0 in order to modify these reports.

  2. Use Reporting Services Configuration Manager to configure the Report Server and Report Manager. This is usually accessible via Start->All Programs->Microsoft SQL Server 2008\Configuration Tools. See this MSDN topic for reference: https://msdn.microsoft.com/en-us/library/ms159624.aspx

  3. Navigate to your Report Manager URL (usually https://localhost/Reports/Pages/Folder.aspx). Use the New Data Source feature to add a reference to the AppFabric monitoring database.

    Important

    You must run your browser with administrator privileges for the report to display properly.

    • Name: MonitoringDatabase

    • Enable this data source: Selected

    • ConnectionString: Data Source=localhost;Initial Catalog=ApplicationServerMonitoring;Integrated Security=SSPI;

      Note

      You will need to create this database using the AppFabric configuration tool or using the AppFabric PowerShell Cmdlets

    • Windows Integrated Security: Selected

    • From the Report Manager, add a new folder called DublinReports.

  4. From the Report Manager, use the Upload File feature to add the two report files (.rdl) included with this sample. Make sure you add these to the DublinReports folder. When asked to specify the uploaded name of the files, reuse the file names without the .rdl extension.

  5. Browse to the OperationOverview report by clicking the OperationOverview item in the DublinReports folder. Depending on your Report Server configuration you may need to run your browser as an administrator in order to see the reports.

Important

If you encounter the following error you may need to refresh the data source: Error Message: The report server cannot process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference).

Steps to refresh the data source:

  1. From the page with the error, navigate to the Properties tab.

  2. From the Properties tab, click Data Sources in the left pane.

  3. From the Data Sources pane, make sure that A Shared data source is selected.

  4. From the Data Sources pane, click Browse under the A Shared data source option.

  5. From the Data Source Selection page, navigate to and select Home->MonitoringDatabase, and then click OK.

  6. From the Data Sources pane, click Apply.

  7. From the Data Sources pane, navigate to the View tab.

If the data source is pointing to a valid monitoring database then the report should populate correctly.

Understanding This Sample

The OperationOverview report shows information regarding the WCF service operations that AppFabric has monitored. You can refresh the report at any time to get the latest data from the monitoring database. From within the OperationOverview report you can click any of the operation names in the tables to get more details about each operation. You can open the .rdl files in Report Builder 2.0 to get a better understanding of how the monitoring database views were used to create the reports.

Removing This Sample

To remove the sample artifacts you must uninstall SQL Server.

Other Resources

SQL Server Reporting Services Home Page: https://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx