SQL Server : Reporting Services - Report Server Architecture

10/14/2010 9:49:50 AM

Reporting Services combines a mix of Microsoft technologies to create a scalable server-based reporting architecture. You can view this as a middle-tier server in your enterprise planning scenarios. It is one part Web server and one part database server, with a dash of .NET managed code to bring all the pieces together. Using Visual Studio as the base development platform is somewhat challenging for a business user but very comfortable for the seasoned developer. Business-savvy users are better suited using the report model and Report Builder tools, which are also built into the .NET Framework.

Internet Information Services

The Web server portion of Reporting Services requires Microsoft Internet Information Services (IIS). You will experience significantly improved performance using IIS 6.0, which is included in Windows Server 2003. You gain even more substantial improvements using the 64-bit version of Windows Server and SQL Server. Configure the server to support ASP.NET 2.0. (This happens automatically during installation.) Two virtual directories are created in the same application pool (Figure 1).

Figure 1. IIS virtual directories for Reporting Services

It is a good practice to keep the Reporting Services directories in a separate pool on IIS 6.0 so that they can be stopped and started without affecting other Web sites on the host machine. One of the virtual directories is the Report Manager application, which is written using the same API and Web service capabilities available to custom developers. The second Web site is the Web service interface, where all the heavy lifting occurs. Encrypting report delivery with SSL is optional but highly recommended if your reports have sensitive data. Keep in mind that SSL has a slight performance impact.

Both virtual directories point to folders under the Reporting Services installation. Note that these live on the C drive by default. Some enterprises have policies that require all applications to be installed on drives other than the root. Reports are actually stored in a database, so these folders do not grow much in size other than their error logs.

You can change the virtual directory names and target site by using the Reporting Services Configuration tool. The default names are Reports for Report Manager and ReportServer for Web services.

SQL Server Databases

Reporting Services creates two SQL databases. One stores reports, report models, shared data sources, resources, and metadata managed by the server. It is called ReportServer by default but can be changed using the Reporting Services Configuration tool. Use this technique to move reporting from one server to another, repoint a test instance to production, or have multiple servers share one database for a scale-out cluster. The other database is called ReportServerTempDB; it stores temporary tables and data snapshots. This database can grow significantly with heavy use and should be closely monitored.

Note that the table structure in these databases can change over time, so writing directly to the tables is not recommended. Use the SOAP Web services and rendering extensions instead.

Windows Services

A single Windows service called SQL Server Reporting Services runs unattended on the report server. It is responsible for managing, scheduling, executing, rendering, delivering, and granting access to reports. It should be set to run automatically at startup and restart automatically on failure. The service can run under the local system account or a domain-level account.

Subscriptions rely on the SQL Server Agent scheduled jobs function. The reporting service polls the metadata tables in the database periodically to determine whether a report job is ready to run. The job is actually entered into SQL Server Agent and set to run at a scheduled time. If one of the services fails, subscriptions might not complete. Make sure that both services are running if you depend on the subscription feature.

All the SQL Server services are monitored and managed through SQL Server Configuration Manager. You can get to this through the SQL Server menus or the Computer Management console (Figure 2).

Figure 2. Windows Services in Computer Management console

Events and Logs

The Windows application log contains information about general events. Look for the Report Server, Report Manager, and Scheduling and Delivery Processor event sources. The Performance log has counters to track number of reports and other metrics. Use the RS Web Service counters to monitor report server performance, and use the RS Windows Service counters to track monitored operations and report delivery.

The report server trace logs are text files stored in the Reporting Services installation LogFiles folder. There are four types of files:

  • ReportServerService_<timestamp>.log traces worker threads.

  • ReportServerService_main_<timestamp>.log traces management threads.

  • ReportServerWebApp_<timestamp>.log details Report Manager tasks.

  • ReportServer_<timestamp>.log traces Web service calls.

You can customize the tracing details in the report server configuration files. These files are very detailed and not easy to read, but they do contain the information needed to debug or troubleshoot execution and security issues.

Numerous files in the SQL database store information about report execution and delivery. You can extract this information into a table format that is easy to query and report. Use the supplied extract packages and sample reports in the Report Samples.

Deployment Modes

The Reporting Services architecture can support small workgroups or large enterprise accounts. A single server can run the reporting engine and a copy of SQL Server that houses the reporting databases. You can split these over two servers by using a remote instance of SQL Server for the database portion. You might do this for disk space, memory, or processor usage reasons.

Combining Reporting Services with Windows SharePoint Services or SharePoint Portal Server is popular. If both exist on the same server, install Reporting Services with the Files Only option. You must run the SharePoint STSADM configuration utility to exclude the Reporting Services virtual directories. This prevents security and ISAPI filter issues. Use the Reporting Services Configuration tool to complete the settings for the report services and databases. Make sure the SharePoint and Reporting Services virtual directories run in different application pools on IIS 6.0 or later.

SQL Server Enterprise Edition and Developer Edition support a scale-out deployment model. Multiple report servers rely on a shared database instance on a local or remote SQL Server. For optimum redundancy, run the SQL instance on a Windows Server failover cluster.

Internet facing scenarios are also supported with some custom configuration. Installing the report server and Report Manager outside the firewall allows full use of reporting features. There is no built-in forms-based authentication, so you must develop a custom security processing extension. A more secure approach is to have Report Manager live outside the firewall but keep Reporting Services inside the firewall. This mode does not support report drillthrough, e-mail subscription links, or Report Builder.

Note that an SQL Server license is required for all computers running Reporting Services. If the database, services, and Report Manager are split over three machines, three licenses are required.


Reporting Services is designed for extensibility. You can use the .NET Framework to build fully trusted managed code assemblies that provide delivery, data processing, rendering, and security capabilities. Report Service is typically managed by using the full-featured SOAP-based Web service interface. For even more detail, consider using the WMI provider.

Delivery Extensions

Reporting Services includes delivery extensions for SMTP e-mail and network file shares. These are closely tied to subscriptions. When a user subscribes to a report or runs a data-driven subscription, notifications are generated and a delivery extension is called with the parameters set in the subscription. The delivery extension is responsible for rendering reports and sending the data through some communications channel. The rendering logic and delivery mechanism are completely controlled by the extension developer. An XML configuration file defines the extension components on the report server and Report Manager. Your .NET assemblies are installed in the reportserver\bin folder.

Data Processing Extensions

Reporting Services data processing extensions retrieve data from a connected source. The built-in extensions connect to SQL Server, OLE DB, ODBC, and Oracle. This covers a large percentage of the available data sources in any organization. Some custom applications might require data sources not handled by OLE DB and ODBC. These might include scientific or industrial devices, older computing systems, or proprietary technology. There are well-defined interfaces that must be coded in a managed code assembly. The required classes include connection, command, and datareader. The latter fills a dataset that is consumed by the rendering engine. The XML configuration file is deployed to the report server and Report Designer in BI Development Studio or Visual Studio.

Rendering Extensions

Reporting Services rendering extensions create device-specific layout information from a report definition. The built-in extensions include various HTML flavors, Excel, PDF, TIFF, CSV, and XML, as described earlier. Consider using variations of these built-in extensions or XSLT transforms to create proprietary output. If your needs are more specific, you can use a well-known set of interfaces called the Rendering Object Model, which provides a collection of classes to an extension developer. Managed code iterates through these classes to produce an output stream suitable for the target device. Deploy the completed assembly to the reportserver/bin folder and create an entry in the XML configuration files.

Security Extensions

Reporting Services security extensions authenticate and authorize users and groups for report administration and delivery. The built-in extensions work with basic Windows security providers such as Active Directory. Consider writing a managed code extension for forms-based external Web site access or integration with existing security providers. Implement the LogonUser and CheckAccess methods based on the well-known interfaces.

WMI Provider

The Windows Management Instrumentation (WMI) interfaces are the bedrock of the Microsoft Dynamic Systems Initiative suite of products that includes Operations Manager and Systems Management Server. Reporting Services supports numerous WMI classes that allow discovery, control, activation, and monitoring of Reporting Server instances. These classes are defined in the root\Microsoft\SqlServer\ReportingServices\v8 namespace.

Report Integration

Reporting Services is a powerful platform for report creation, delivery, and management. As this technology matures, numerous ISVs are integrating reports into their custom products. The SOAP-based Web service, open RDL and RDS file formats, extension classes, and WMI support make a wonderful foundation for the skilled developer.

Microsoft is a leading implementer in this field. It offers reporting links to many of its popular application products. Among the more popular ones are the Web parts designed for the SharePoint portal suite. They make customized Web-based report delivery a snap. Other prominent products that use the full Reporting Services feature set include Microsoft Operations Manager (MOM), Data Protection Manager, and some Dynamics offerings. A new Systems Center Reporting release combines Analysis Services cubes with Reporting Services to create a comprehensive suite of infrastructure management reports. It marries the log and metric data from MOM with the inventory and deployment details of Systems Management Server. This is an excellent example of the evolutionary integration work done by Microsoft product developers.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone