1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL Server Reposting Services SSRS 2008 Architecture

Discussion in 'SQL Server' started by MinalS, May 9, 2015.

  1. Architectural components

    In SQL Server Reporting services 2005, there are two services as windows service and web service. The windows service was responsible for report processing, scheduling, and delivery, database maintenance, and extensibility. The web service worked with SOAP, URL access.

    In SSRS 2008, one windows service is used. There are no dependencies on the IIS. The windows service contains all the one present in the web service. The IIS functionality is replaced with the native SQL server components.

    The reporting services consist of web based report manager client, web server and background processing. The report server web service works with ASP.NET, SOAP, and URL interfaces. The report processing and report models are handled.

    The tools like report builder, report designer, SQL server management studio, configuration tool play role similar to SQL 2008.

    Report Server

    The report server is the important component of the reporting services in the windows service. It contains the following components:
    1. Windows service: It provides user with the report scheduling and delivery services. They are useful in designing, saving, managing and executing the reports.
    2. Report Manager: It provides user with the front end report server items and manages the web service.
    3. Web service: It provides the access to the report server using the report builder.
    Report Processing

    Report server consists of two core processors as report processor and scheduling and delivery processor. The core processors are not modified or extended. They maintain the integrity of the reporting system. They have extensions for handling authentication, data processing, rendering and delivering operations.

    Report server processes request for reports after authentication from the layer and accesses the report items and data.

    Report server backend

    The report server stores the folders and files as the computer file system. The reports are saved as .rdl extension. They are published and saved in the report server database. The report can be retrieved from the Report Server database for use in applications or presentations.

    The deployment uses two SQL server relational databases for internal storage. The database names are Report server and ReportServerTempdb. The Tempdb server stores temporary data, session information, and cached reports.

    The T-SQL commands, rs.exe script utility, database command prompt utilities, the reporting services configuration tool are used for the reporting services database related activities. They cannot be renamed. It stores procedures containing name of the primary database.

    Reporting Services and database engine

    The SQL Server management studio is used for both report server and reporting services database. Click File, Connect Object browser. The connect to server window is shown below:


    The Report Server databases are in the database engine. The report server users are Administrators, Everyone, or System. SSRS uses a role based security and authentication. The Security node consists of the role for the services. If there are built in roles, they cannot be deleted. Every role is permitted to carry out the tasks assigned to it.

    The Content Manager Properties page contains everything it can perform. The role can be customized by the user. The report manager can be used for assigning the role to the user.


    Report Manager

    It is a web application for administering a report server instance from the remote location over HTTP. There are several uses of the report manager as mentioned below:
    1. Organizing items on the server by creating, securing, and maintaining the folders.
    2. The reports can be viewed and printed. The subscriptions can be set up for the user
    3. The report related items as execution properties, parameters are configured
    4. The report models dependent on analysis services and relational data sources
    5. The linked reports are created for reusing the existing reports
    6. The data driven subscriptions are created for combining the large number of clients
    The Report manager can be started using the Reporting services configuration tool.
    Click Start, All Programs, Microsoft SQL Server 2008, Configuration tools, reporting services configuration tool.

    Report Builder

    The report builder is targeted to the business users. The user who has good knowledge of the data but no detailed knowledge of database and programming experience. The report builder design tool utilizes the business model for using the data available.

    The Report builder interface is similar to the Office products like Excel, Access and word. They have similar report templates and are not different from the reports built elsewhere. The same API is used for managing and securing the data. The META data of the model is saved with a report definition file.

    Report Builder 2.0

    The Report Builder 2.0 has rich, client side report authoring application. It supports the creation and designing of the reports. The file can be saved at any location with .rdl extension. The deployment of the report generation consists of following steps.
    1. Define the data to be used
    2. Define the layout for data display
    3. Preview to test display
    4. Save the report
    5. Publish the report to the Reporting services server or SharePoint site
    Report Builder features

    The Report Builder 2.0 provides number of features which are useful for the user. Some of them are as explained below:
    1. It provides flexible connection to the data. It can contain relational data, multidimensional data, XML data, and custom data
    2. It provides connectivity to different data source providers like OLE DB data provider, ODBC data sources, Microsoft .NET managed data providers, table valued functions
    3. It supports the Ad hoc reporting used for creating and saving reports, links to sub reports, parameter based filtering, model based reports.
    4. It supports variety of vendor data like Oracle, Hyperion, etc
    5. It provides multiple presentation and display formats. It supports HTML, XML, EXCEL, PDF, CSV, and WORD
    6. It supports report navigation and helps add bookmarks and document maps for navigating large reports

    Rendering the extensions support the export formats available in the SSRS. They contain three different extensions.
    1. It renders data to the user. Only data is displayed to the user. It supports CSV and XML data
    2. It provides hard page break renderers. They support TIFF and PDF
    3. It provides soft page break renderers. They support MS Excel, Word, MHTML, and report viewer controls
    Visual Studio 2008 support for reporting

    Visual Studio 2008 is integrated with SQL Server 2008. The Visual Studio 2008 provides functionality available in BIDS and the ability to integrate the reports for both web and windows programs.

    Visual Studio 2008 supports report authoring and capability to work with reports and report models. The reports have RDL extension and need a report server to process it. Visual Studio supports developing applications that are hosted on IIS servers. Later, they are processed locally without the report server. They can be deployed for intranet web distribution from the IIS server.

    The Report Viewer controls are available for this purpose. There are two report viewer controls present, one for web applications and other for windows applications. User can create reports that can be viewed by the user.

    ReportViewer Controls

    The reports in windows or web applications using the ReportViewer controls are deployed to the web server or Report server. The controls have two modes of processing reports. The remote processing mode is the preferred way for processing reports on the server. In local mode, reports can be processed even if the reporting services are not installed but copy of the report can be accessed.

    ReportViewer control for Windows applications

    User can start the windows project by clicking File, New, Project in the Visual Studio 2008 application. The New Project window is as shown below:


    The default name is present in the application. User can replace by particular name. The .NET framework can be modified per choice. Once the defaults in the .NET framework and the location are modified, a project folder will be created with the Windows Form.

    The MicrosoftReportViewer control can be added by double clicking the control in the toolbox. User can drag and drop the control in the form. The following image shows the control and the items in the Smart Tasks to be modified.


    The design time helps user to make choices from the properties of the control. The Properties window is accessible by right clicking the control in Form1 and selecting properties from the drop down.


    ReportViewer control for Web applications

    User can start the web site project by clicking File, New, Web Site in the Visual Studio 2008 application. A new web site window is as shown below:


    The ASP.NET website on which the the ReportViewer control is placed; there are two options for reporting. They are ASP.NET reports website and ASP.NET crystal reports website.

    The Microsoft Report Viewer control can be added to the form from the toolbox. The aspx page contains the Microsoft ReportViewer control.


    Business Intelligence Support

    User can access the BIDS from Microsoft SQL Server 2008 application. Click Start, All Programs, Microsoft SQL Server 2008, SQL Server Business Intelligence Development Studio application.


    The Report Server Project Wizard and Report Server Project provide the Report project. The Report Model Project results creation of report model used for authoring ad hoc reports through the report designer tool.

    The Report Server processes these reports. They can be integrated into the application using the three methods as mentioned below:
    1. Report server web service is the standard way for developing report applications. Report manager is a web client that consumes the report server web service. The API exposes the needs for accessing and modifying the reports.
    2. The report viewer control helps in accessing reports deployed on the server
    3. The URL are easily sent as links by email for users for viewing the reports
    Report Server Configuration File

    The data about the report server web service, the report manager, background processing are saved in the XML configuration file. The reporting services execute in a single process in the config file. The file is present in the ‘C:\ProgramFiles\Microsoft SQL Server\MSRS10.<InstanceName>\ReportingServices\ReportServer\rsreportserver.config

    The rsreportserver.config file consists of the following items. The ReportServer installed on the machine. The details of the installation are mentioned below:
    1. DSN: It provides the connection string to the database engine hosting the report server databases. The value is added to the Config file when the report server database is created.
    2. Connection type: There are two types of connection as Default and Impersonate.
    3. LogOnUser, LogOnDomain, LogOnCred: They are used to store the information used for connecting to report server from a domain account
    4. InstallationID: It is an identifier used fort he report server installation.
    5. SecureConnectionLevel: It relates the security level for the report server and the manager. There are three values, 0 is the least one.
    6. MaxActiveReqForOneUser: It is the maximum number of reports a user can process at a same time
    7. RunningRequestAge: It is used to state the interval in seconds when the status of a running job is changed from new to running
    8. DisplayErrorLink: It is an error that occurs when the link to more details of the error is available as a hyperlink
    9. Authentication types: It defines different types of authentication allowed for the Report server. The default settings are automatically added. The custom values are set by editing a text editor. The values can be RSWindows Negotiate, RSWindows Kerberos, RSWindows Basic and Custom
    10. Service: It is used to specify the service settings applicable for the service. The terms are self defining. Some are explained at the installation time.
  2. Izaan

    Izaan New Member

    Oct 16, 2007
    Likes Received:
    Trophy Points:
    Nicely explained.

Share This Page