SQL Reporting Services Integration with System Center Configuration Manager 2007 R2

[This post comes to us from Bhaskar Krishnan and gives an overview of the new SQL Reporting Services feature and the benefits it provides over the ASP-based reporting point in Configuration Manager and previous releases.]

Background

Prior to Configuration Manager 2007 R2, the reporting solution provided out of the box was a custom ASP-based solution that included around 384 reports that got installed as part of the site server installation. Running any of the reports required the user to install a role called "Reporting Point" using the site role wizard. The site role wizard would basically create an ASP web site hosted under IIS and would allow users to run any of the reports from the ASP web site. However customers frequently ran into timeout issues with the web page timing out while attempting to render "expensive" reports - by expensive I mean reports returning large amount of rows from the Configuration Manager database.

http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/4c7d2829-3a8f-44d5-9b37-a4c3a128d126/

In addition, the solution offered little flexibility when it came to supporting standard reporting functionalities like the ability to export the reports to various formats, to add custom branding to the reports (by branding I mean changing the look and feel of the reports) and more importantly scheduling the reports to run at specific times and allowing users to subscribe to those reports.

SQL Reporting Services, with its industry standard reporting solution, provided all of the benefits and proved to be a more cost effective and scalable reporting solution for System Center Configuration Manager.

Benefits of using SQL Reporting Services

SQL Reporting Services provides tremendous flexibility in terms of monitoring report execution performance, tuning report execution parameters and a boat load of value add functionality like:

1. Ability to export reports to any other formats like Word, Excel, PDF etc.
2. Ability to create report subscriptions that can be scheduled to run at specific times and send out reports to interested people. A good user scenario around this would be to create a report subscription for the Software Updates reports and schedule them to run late on Tuesday night or early Wednesday morning after all the "patch Tuesday" updates are applied to all systems.
3. Report authoring experience is very much enhanced with the tools that come with SQL Reporting Services like SQL Report Designer. You could either create report models or create SQL-based reports and run them off of the SQL Reporting Server.
4. Timeouts can be configured on a per-report basis depending on which reports potentially return large amounts of data.
5. Since the reports are standard SQL Reporting Services reports, they can be easily imported and exported from one SQL Reporting server to another.
6. A common request from customers is to be able to run reports off of a Configuration Manager database replica before enabling them on the production environment. This is a gem of a functionality that can be easily accomplished by simply making the data source for the reports point to any valid Configuration Manager database; in this case point the data source of the reports to the database replica and once they have been verified just change the data source to point to the actual production database. This proves to be very useful for benchmarking environments.
7. Report branding is another frequently requested functionality by many customers. This commonly entails customizing the look and feel of reports by changing fonts, font sizes, custom logos etc. With the ability to create custom reports using SQL Reporting Services, customers can now apply their own report branding to the reports.
8. SQL Reporting Services provides the functionality to enable report caching to facilitate lower execution times on subsequent report execution requests. The cache timeout value can be configured appropriately depending on how often you expect the report data to change.
9. Report snapshots that are an alternative to report caching and can be scheduled to execute at specific times. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database, and shows the data and layout that were current for the report at the time the snapshot was created.

Integration of SQL Reporting Services with Configuration Manager 2007 R2

With Configuration Manager 2007 R2, we introduced a new site role called "Reporting Services Point" that facilitates reporting using SQL Reporting Services 2005/2008. This is accomplished via a conversion wizard that ships with Configuration Manager 2007 R2 and allows the user to convert all the Configuration Manager reports that currently exist on that site server to SQL Reporting Services based reports and deploy them to the SQL Reporting Server.

Site Role Installation and Configuration

The following outlines the overall workflow in getting a SQL Reporting Services based reporting point up and running:

1. Pre-requisites: Any machine having a valid SQL Reporting Server 2005/2008 instance running on it.
2. Run the site role wizard and install the "Reporting Services Point" on the SQL Reporting Server. The site role wizard asks for a root folder name which is basically the folder on the reporting server under which all the reports will be deployed.
3. Once the site role wizard is completed successfully, you should see the server appearing under the Reporting Services node under the Reporting node in the administration console.
4. Right click on the server and launch the "Copy Reports Wizard"
5. Run through the "Copy Reports Wizard" and select all the reports that you want to convert to SQL Reporting Services based reports.
6. The wizard will then go through the selected reports, convert them into SQL Reporting Services based reports and deploy them to the reporting server under the folder specified in step 2. above.
7. The copy reports wizard groups all the reports based on report categories creates a folder for each report category and deploys the reports under the respective report category folder.
8. Once all the reports are deployed, you can see all the report folders in the administration console and run any of the reports from any of the folders. You have the option of running the reports from within the administration console or run the reports directly from SQL Reporting Services using the SQL Report Manager (web UI). The SQL Reporting server report manager URL has the following naming convention:

For the default SQL Reporting Server instance the URL to access the report and report folders would be:

http://[ReportServer]/Reports

For named SQL Reporting server instances the URL would be:

http://[ReportServer]/Reports_[InstanceName]

Other functionalities provided within the Configuration Manager administration console

1. Report subscription wizards to create subscriptions for any of the Configuration Manager reports
2. Report authoring tools :

• Model based report wizard

The Configuration Manager 2007 R2 release ships two out-of-the-box report models one for Client Health Reporting and the other for Software Updates Management. The model based report wizard facilitates users to create custom reports using these report models.

• SQL Based report wizard

The SQL based report wizard facilitates SQL savvy users to specify SQL queries and generate reports off of these queries.  The wizard presents the users with a list of all available Configuration Manager database views and the corresponding columns to facilitate users to formulate SQL queries more easily and make the process less prone to errors and typos.

FAQs

1. Can the new Reporting Services role co-exist with the old reporting point on the same site server?

Answer: Yes

2. What are the minimum permissions required to be able to view and run Configuration Manager reports?

Answer: The security model for this feature relies on SQL Reporting Server's role based access model. Basically the user needs Browser privileges to be able to view and run the reports from the reporting server. In addition to having access to the report server the user will also need read access to the Configuration Manager database.

o    How to grant access to reporting server:

http://msdn.microsoft.com/en-us/library/ms156034(SQL.90).aspx

o     Pre-defined roles:

http://msdn.microsoft.com/en-us/library/ms156465(SQL.90).aspx

3. Does Configuration Manager 2007 R2 support SQL Reporting Services 2008?

Answer: Yes - Configuration Manager 2007 R2 release supports both SQL Reporting Services 2005 and SQL Reporting Services 2008. If you plan to use SQL Reporting Services 2008, you only need one hotfix to avoid an incorrect status message from being bubbled up to the site server. The hotfix details are available here: http://support.microsoft.com/kb/957576/

That is pretty much it for my first overview post and I earnestly look forward to your comments, feedback and queries. If anything particularly interests you from the above post, please let me know and I will be more than happy to provide more specific details in my next post.

- Bhaskar Krishnan

This posting is provided "AS IS" with no warranties, and confers no rights.