Monitor and Analyze Multiple SSRS Instances with SQLScrubs

The following article details how to analyze and monitor multiple SQL Reporting Services instances with SQLScrubs. SQLScrubs Community Edition is available for free and can be found on CodePlex. All extraction for instance related data is managed by the SSIS package and stored in the datawarehouse provided. You can analyze in your own reports, enhance some of the free reports to do this for you, or simply upgrade to the Premium Edition of SCRUBS for SQL Server 2008 which provides over 15 additional reports with built-in analysis by instance.

Note that these instances of SSRS 2008 can be on entirely different servers, and you can cull the logs from both SharePoint Integrated installations as well as Native SQL Server installations. As long as the server running the job can connect and has sufficient priviledges to the Report Server database, you can cull the log and consolidate your analysis and optimization.

Prerequisites:

You must have SQLScrubs for SQL Server 2008 installed on a SQL Server. If you don’t have them, get the bits here. The following method can be used for both Community and Premium Editions.

1. Launch SQL Server Management Studio, and browse to the SQL Server Agent jobs.
2. Right Click the job “SummitCloud SCRUBS – SSRS Log Feeder” and select Properties.
3. On “Select a Page“, click Steps
4. Click the New… button
5. Provide details for the step
   1. Step Name: Provide a useful name, perhaps the instance name of Reporting Services 2008
   2. Type: SQL Server Integration Services Package
   3. Run As: Select the Service Account, or a Proxy account if needed for your environment
   4. Package Source: File System
   5. Package: Select the location of the SCRUBS SSIS pacakge. By Default, this is c:\Program Files\SummitCloud\SCRUBS\SSIS\SCRUBS_SSRS_Log_Feed.dtsx
      
6. With the Step Properties still open, select the Data Sources tab.
7. Confirm that the Destination Connection Manager is checked, and pointing to your SQLScrubs datawarehouse.
8. Check the Source Connection Manager, and modify the Connection String to point to the instance or server of Reporting Services 2008 to be imported. Ensure the service that runs this job has appropriate access and credentials to read data from the Execution Log.
9. Click OK
10. On the Job Step List, Select the first (default Step), and click the Edit… button
11. Change the On Success Action to : Go to the next Step.
12. Click OK to save the Step settings
13. Click OK to save the Job.

To learn more about SQLScrubs, visit our solution center.