(For more resources related to this topic, see here.)

Structural design of SQL servers and SharePoint environment

Depending on the business and the resources available, the various servers may be located in distributed locations and the Web applications may also be run from Web servers in a farm and the same can be true for SharePoint servers. In this article, by the word architecture we mean the way by which the preceding elements are put together to work on a single computer. However, it is important to know that this is just one topology (an arrangement of constituent elements) and in general it can be lot more complicated spanning networks and reaching across boundaries.

The Report Server is the centerpiece of the Reporting Services installation. This installation can be deployed in two modes, namely, Native mode or SharePoint Integrated mode. Each mode has a separate engine and an extensible architecture. It consists of a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations. Once deployed in one mode it cannot be changed to the other. It is possible to have two servers each installed in a different mode.

We have installed all the necessary elements to explore the RS 2012 features, including Power View and Data Alerts. The next diagram briefly shows the structural design of the environment used in working with the article:

Primarily, SQL Server 2012 Enterprise Edition is used, for both Native mode as well as SharePoint Integrated mode. As we see in the previous diagram, Report Server Native mode is on a named instance HI (in some places another named instance Kailua is also used). This server has the Reporting Services databases ReportServer$HI and ReportServer$HITempDB. The associated Report Server handles Jobs, Security, and Shared Schedules. The Native mode architecture described in the next section is taken from the Microsoft documentation. The tools (SSDT, Report Builder, Report Server Configuration, and so on) connect to the Report Server. The associated SQL Server Agent takes care of the jobs such as subscriptions related to Native mode.

The SharePoint Server 2010 is a required element with which the Reporting Services add-in helps to create a Reporting Services Service. With the creation of the RS Service in SharePoint, three SQL Server 2012 databases (shown alongside in the diagram) are created in an instance with its Reporting Services installed in SharePoint Integrated mode. The SQL Server 2012 instance NJ is installed in this fashion. These databases are repositories for report content including those related to Power Views and Data Alerts.

The data sources(extension .rsds) used in creating Power View reports (extension.rdlx) are stored in the ReportingService_b67933dba1f14282bdf434479cbc8f8f database and the alerting related information is stored in the ReportingService_b67933dba1f14282bdf434479cbc8f8f_Alerting database. Not shown is an Express database that is used by the SharePoint Server for its content, administration, and so on. RS_ADD-IN allows you to create the service. You will use the Power Shell tool to create and manage the service.

In order to create Power View reports, the new feature in SSRS 2012, you start off creating a data source in SharePoint library. Because of the RS Service, you can enable Reporting Services features such as Report Builder; and associate BISM file extensions to support connecting to tabular models created in SSDT deployed to Analysis Services Server. When Reporting Services is installed in SharePoint Integrated mode, SharePoint Web parts will be available to users that allow them to connect to RS Native mode servers to work with reports on the servers from within SharePoint Site.

Native mode

The following schematic taken from Microsoft documentation (http://msdn.microsoft.com/en-us/library/ms157231.aspx) shows the major components of a Native mode installation:

The image shows clearly the several processors that are called into play before a report is displayed. The following are the elements of this processing:

• Processing extensions(data, rendering, report processing, and authentication)
• Designing tools(Report Builder, Report Designer)
• Display devices(browsers)
• Windows components that do the scheduling and delivery through extensions(Report Server databases, a SQL Server 2012 database, which store everything connected with reports)

For the Reporting Services 2012 enabled in Native mode for this article, the following image shows the ReportServer databases and the Reporting Services Server. A similar server HI was also installed after a malware attack. The Report Server is implemented as a Microsoft Windows service called Report Server Service.

SharePoint Integrated mode

In SharePoint mode, a Report Server must run within a SharePoint Server (even in a standalone implementation). The Report Server processing, rendering, and management are all from SharePoint application server running the Reporting Services SharePoint shared service. For this to happen, at SQL Server installation time, the SharePoint Integrated mode has to be chosen. The access to reports and related operations in this case are from a SharePoint frontend.

The following elements are required for SharePoint mode:

• SharePoint Foundation 2010 or SharePoint Server 2010
• An appropriate version of the Reporting Services add-in for SharePoint products
• A SharePoint application server with a Reporting Services shared service instance and at least one Reporting Services service application

The following diagram taken from Microsoft documentation illustrates the various parts of a SharePoint Integrated environment of Reporting Services. Note that the alerting Web service and Power View need SharePoint Integration.

The numbered items and their description shown next are also from the same Microsoft document. Follow the link at the beginning of this section.

The architectural details presented previously were taken from Microsoft documentation.

Item number in the diagram	Description
1	Web servers or Web Frontends (WFE). The Reporting Services add-in must be installed on each Web server from which you want to utilize the Web application feature such as viewing reports or a Reporting Services management page for tasks such as managing data sources and subscriptions.
2	The add-in installs URL and SOAP endpoints for clients to communicate with application servers through the Reporting Services Proxy.
3	Application servers running a shared service. Scale-out of report processing is managed as part of the SharePoint farm and by adding the service to additional application servers.
4	You can create more than one Reporting Services service application with different configurations, including permissions, e-mail, proxy, and subscriptions.
5	Reports, data sources, and other items are stored in SharePoint content databases.
6	Reporting Services service applications create three databases for the Report Server, temp, and data alerting features. Configuration settings that apply to all SSRS service applications are stored in RSReportserver.config file.

When you install Reporting Services in SharePoint Integrated mode, several features that you are used to in Native mode will not be available. Some of them are summarized here from the MSDN site:

• URL access will work but you will have to access SharePoint URL and not Native mode URL. The Native mode folder hierarchy will not work.
• Custom Security extensions can be used but you need to use the special purpose security extension meant to be used for SharePoint Integration.
• You cannot use the Reporting Services Configuration Manager (of the Native mode installation).You should use the SharePoint Central Administration shown in this section (for Reporting Services 2008 and 2008 R2).
• Report Manager is not the frontend; in this case, you should use SharePoint Application pages.
• You cannot use Linked Reports, My Reports, and My Subscriptions in SharePoint mode.
• In SharePoint Integrated mode, you can work with Data Alerts and this is not possible in a Native mode installation.
• Power View is another thing you can do with SharePoint that is not available for Native mode. To access Power View the browser needs Silverlight installed.
• While reports with RDL extension are supported in both modes, reports with RDLX are only supported in SharePoint mode.
• SharePoint user token credentials, AAM Zones for internet facing deployments, SharePoint back and recovery, and ULS log support are only available for SharePoint mode.

For the purposes of discussion and exercises in this article, a standalone server deployment is used as shown in the next diagram. It must be remembered that there are various other topologies of deployment possible using more than one computer. For a detailed description please follow the link http://msdn.microsoft.com/en-us/library/bb510781(v=sql.105).aspx.

The standalone deployment is the simplest, in that all the components are installed on a single computer representative of the installation used for this article. The following diagram taken from the preceding link illustrates the elements of the standalone deployment:

Reporting Services configuration

For both modes of installation, information for Reporting Services components is stored in configuration files and the registry. During setup the configuration files are copied to the following locations:

• Native modeC:Program FilesMicrosoft SQL ServerMSRS11.MSSQLSERVER
• SharePoint Integrated modeC:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15WebServicesReporting

Follow the link http://msdn.microsoft.com/en-us/library/ms155866.aspx for details.

Native mode

The Report Server Windows Service is an orchestrated set of applications that run in a single process using a single account with access to a single Report Server database with a set of configuration files listed here:

Stored in	Description	Location
RSReportServer.config	Stores configuration settings for feature areas of the Report Server Service: Report Manager, the Report Server Web Service, and background processing.	<Installation directory> Reporting Services ReportServer
RSSrvPolicy.config	Stores the code access security policies for the server extensions.	<Installation directory> Reporting Services ReportServer
RSMgrPolicy.config	Stores the code access security policies for Report Manager.	<Installation directory> Reporting Services ReportManager
Web.config for the Report Server Web Service	Includes only those settings that are required for ASP.NET.	<Installation directory> Reporting Services ReportServer
Web.config for Report Manager	Includes only those settings that are required for ASP.NET.	<Installation directory> Reporting Services ReportManager
ReportingServicesService. exe.config	Stores configuration settings that specify the trace levels and logging options for the Report Server Service.	<Installation directory> Reporting Services ReportServer Bin
Registry settings	Stores configuration state and other settings used to uninstall Reporting Services. If you are troubleshooting an installation or configuration problem, you can view these settings to get information about how the Report Server is configured.   Do not modify these settings directly as this can invalidate your installation.	HKEY_LOCAL_MACHINE SOFTWARE Microsoft Microsoft SQL Server <InstanceID> Setup and HKEY_ LOCAL_MACHINE SOFTWARE Microsoft Microsoft SQL ServerServices ReportServer
RSReportDesigner.config	Stores configuration settings for Report Designer. For more information follow the link http://msdn.microsoft.com/en-us/library/ms160346.aspx	<drive>:Program Files Microsoft Visual Studio 10 Common7 IDE PrivateAssemblies
RSPreviewPolicy.config	Stores the code access security policies for the server extensions used during report preview.	C:Program Files Microsoft Visual Studio 10.0 Common7IDE PrivateAssembliesr

First is the RSReportServer configuration file which can be found in the installation directory under Reporting Services. The entries in this file control the feature areas of the three components in the previous image, namely, Report Server Web Service, Report Server Service, Report Manager, and background processing.

The ReportServer Configuration file has several sections with which you can modify the following features:

• General configuration settings
• URL reservations
• Authentication
• Service
• UI
• Extensions
• MapTileServerConfiguration (Microsoft Bing Maps SOAP Services that provides a tile background for map report items in the report)
• Default configuration file for a Native mode Report Server
• Default configuration file for a SharePoint mode Report Server

The three areas previously mentioned (Report Server Web Service, Report Server Service, and Report Manager) all run in separate application domains and you can turn on/off elements that you may or may not need so as to improve security by reducing the surface area for attacks. Some functionality works for all the three components such as memory management and process health.

For example, in the reporting server Kailua in this article, the service name is ReportServer$KAILUA. This service has no other dependencies. In fact, you can access the help file for this service when you look at Windows Services in the Control Panels shown. In three of the tabbed pages of this window you can access contextual help.

SharePoint Integrated mode

The following table taken from Microsoft documentation describes the configuration files used in the SharePoint mode Report Server. Configuration settings are stored in SharePoint Service application databases.

Stored in	Description	Location
RSReportServer. config	Stores configuration settings for feature areas of the Report Server Service: Report Manager, the Report Server Web Service, and background processing.	<Installation directory> Reporting Services ReportServer
RSSrvPolicy.config	Stores the code access security policies for the server extensions.	<Installation directory> Reporting Services ReportServer
Web.config for the Report Server Web Service Registry settings	Stores configuration state and other settings used to uninstall Reporting Services. Also stores information about each Reporting Services service application.   Do not modify these settings directly as this can invalidate your installation.	HKEY_LOCAL_MACHINE SOFTWARE Microsoft Microsoft SQL Server <InstanceID> Setup   For example instance ID: MSSQL11.MSSQLSERVER and HKEY_LOCAL_MACHINE SOFTWAREMicrosoft Microsoft SQL Server Reporting Services Service Applications
RSReportDesigner. config	Stores configuration settings for Report Designer.	<drive>:Program Files Microsoft Visual Studio 10 Common7 IDE PrivateAssemblies

Hands-on exercise 3.1 – modifying the configuration file in Native mode

We can make changes to the rsreportserver.config file if changes are required or some tuning has to be done. For example, you may need to change, to accommodate a different e-mail, change authentication, and so on. This is an XML file that can be edited in Notepad.exe (you can also use an XML Editor or Visual Studio). You need to start Notepad with administrator privileges.

Turn on/off the Report Server Web Service

In this exercise, we will modify the configuration file to turn on/off the Report Server Web Service. Perform the following steps:

1. Start Notepad using Run as Administrator.
2. Open the file at this location (you may use Start Search| for rsreportserver.config) which is located at C:Program FilesMicrosoft SQL ServerMSRS11.KAILUAReporting ServicesReportServerrsreportserver.config.
3. In Edit Find| type in IsWebServiceEnabled. There are two values True/False. If you want to turn off, change TRUE to FALSE. The default is TRUE.Here is a section of the file reproduced:

   <Service>
   <IsSchedulingService>True</IsSchedulingService>
   <IsNotificationService>True</IsNotificationService>
   <IsEventService>True</IsEventService>
   <PollingInterval>10</PollingInterval>
   <WindowsServiceUseFileShareStorage>False
   </WindowsServiceUseFileShareStorage>
   <MemorySafetyMargin>80</MemorySafetyMargin>
   <MemoryThreshold>90</MemoryThreshold>
   <RecycleTime>720</RecycleTime>
   <MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
   <MaxQueueThreads>0</MaxQueueThreads>
   <UrlRoot>
   </UrlRoot>
   <UnattendedExecutionAccount>
   <UserName></UserName>
   <Password></Password>
   <Domain></Domain>
   </UnattendedExecutionAccount>
   <PolicyLevel>rssrvpolicy.config</PolicyLevel>
   <IsWebServiceEnabled>True</IsWebServiceEnabled>
   <IsReportManagerEnabled>True</IsReportManagerEnabled>
   <FileShareStorageLocation>
   <Path>
   </Path>
   </FileShareStorageLocation>
   </Service>

4. Save the file to apply changes.

Turn on/off the scheduled events and delivery

This changes the report processing and delivery. Make changes in the rsreportserver.config file in the following section of <Service/>:

<IsSchedulingService>True</IsSchedulingService>
<IsNotificationService>True</IsNotificationService>
<IsEventService>True</IsEventService>

The default value for all of the three is TRUE. You can make it FALSE and save the file to apply changes. This can be carried out modifying FACET in SQL Server Management Studio (SSMS), but presently this is not available.

Turn on/off the Report Manager

Report Manager can be turned off or on by making changes to the configuration file. Make a change to the following section in the <Service/>:

<IsReportManagerEnabled>True</IsReportManagerEnabled>

Again, this change can be made using the Reporting Services Server in its FACET. To change this make sure you launch SQL Server Management Studio as Administrator. In the following sections use of SSMS via Facets is described.

Hands-on exercise 3.2 – turn the Reporting Service on/off in SSMS

The following are the steps to turn the Reporting Service on/off in SSMS:

1. Connect to Reporting Services_KAILUA in SQL Server Management Studio as the Administrator. Choose HODENTEKWIN7KAILUA under Reporting Services. Click on OK.

   

2. Right-click on HODENTEKWIN7KAILUA (Report Server 11.0.22180 –HodentekWin7mysorian).

   

3. Click on Facets to open the following properties page

   

4. Click on the handle and set it to True or False and click on OK. The default is True. It should be possible to turn Windows Integrated security on or off by using SQL Server Management Studio. However, the Reporting Services Server properties are disabled.