Download code from GitHub
In this chapter, we will cover the following recipes related to installation and configuration of Microsoft SQL Server Reporting Services:
Selecting the edition
Installing Reporting Services with default configurations
Installing Reporting Services-related tools
Accessing Reporting Services Configuration Manager
Configuring the service account of Reporting Services
Configuring Reporting Services URLs
Configuring the Reporting Services database
Executing post-installation tasks
Stepping back to the year 2004, the release of Reporting Services as a post-release add-on to Microsoft SQL Server 2000 was a big in with the market as there were not many user-friendly reporting tools out there. Microsoft makes it easy and user-friendly; this worldwide-accepted catchphrase was expected by developers for the same and Microsoft kept the word again. Although it initially focused more on IT-driven reports, it became the most wanted and in-demand reporting tool for report designers and consumers.
Rosetta, the code name used for Reporting Services, was a perfect match. The Rosetta Stone found in 1799 explained the mystery of Egyptian hieroglyphics because it provided information needed to understand their history. Reporting Services does the same; it allows you to have the mystery or reasons for the success of your business revealed.
The first release of Reporting Services did not provide a wide range of data regions for addressing all Enterprise requirements, but it offered enough. However, the key was its architecture. It was designed to run under Internet Information Services as a middle-tier service, making it a server-based and web-based reporting platform. Reports designed with Reporting Services were able to be sourced by any type that has .NET managed provider, OLE DB provider, or ODBC provider. Since most Reporting Services processors and services were distributed, obtaining and processing data was efficient and extensible. The rich architecture used was supported with modern rendering extensions and delivered reports in HTML, PDF, and Excel. In addition to that, it allowed us to have our own extensions, making sure that reports were delivered as per the requirements and printing devices used.
Along with other Reporting Services releases, it was significantly improved and enhanced, making it the best reporting platform in the world. Before Microsoft SQL Server 2016, there were four more releases, with SQL Server 2005, 2008 R2, 2012, and 2014. Among all of these fantastic releases, 2005, 2008 R2, and 2016 are considered major releases of Reporting Services.
The latest release, Reporting Services 2016, is becoming the foremost reporting platform because of the features added, enhancements done on existing features, and the completeness in terms of modern reporting and BI platform. Since there were no major enhancements after 2008 R2, we thought that Reporting Services was about to retire, but the 2016 release showed us that it is still a part of the Microsoft BI ecosystem.
This chapter focuses on the installation and configuration of Reporting Services 2016. The recipes in this chapter explain typical installation and configurations as well as further and infrequent configurations that go beyond the traditional installation and configurations. You will be guided through the installation using recipes, and we will explain which sections are important, why they are important, and how they should be configured.
The edition of SQL Server 2016 should be selected based on your needs. SQL Server 2016 offers five downloadable editions, targeting specific business use cases for different prices and levels of capabilities. These five editions are Enterprise, Standard, Web, Developer, and Express. Generally, for an organization, you select one of the premium editions (which is Enterprise) or one of the core editions (which is Standard). There is another premium edition called Analytics Platform System (APS), formerly Parallel Data Warehouse (PDW); that will not be discussed in this book as it is beyond the scope. For an individual, it is always the Developer edition that has all functionality of the Enterprise edition. It can be downloaded and installed without paying Microsoft. Yes, it is free for developers and is licensed for use as a development or test system, not as a production server.
SQL Server 2012 and 2014 had an edition called Business Intelligence, which is no longer available with SQL Server 2016. You can try out Reporting Services with Enterprise Edition without purchasing, as it is available for a 180-day trial period. If you need to use the cheapest edition, which is Express, you need to have Express with Advanced Services.
Features supported by editions are different. For understanding, without discussing all features, we will see the features that are supported only by Enterprise edition.
The following features are supported only by Enterprise edition:
Mobile reports and KPIs
Data-driven report subscription
Scale out deployment
Alerting
Power view
Reporting Services maintain two databases: ReportServer database and ReportServerTempDB. It does not necessarily require the same edition for databases as you have used for Reporting Services. The following list shows you which editions of the Database engine can be used for specific editions of Reporting Services:
Enterprise edition of SSRS: Enterprise or Standard edition database engine
Standard edition of SSRS: Enterprise or Standard edition database engine
Web edition of SSRS: Web edition database engine (local only)
Express edition of SSRS: Express edition database engine (local only)
Developer edition of SSRS: Enterprise, Standard, or Developer edition database engine
Select the edition based on the capabilities required for your application. If you need the aforementioned items that are supported only by the Enterprise edition, then you have no option but select the Enterprise edition. If they are not required, then the Standard edition is enough for your implementation. Cost and limited budget are some other factors for selecting the Standard edition.
Select the Developer edition for development or testing, not as a production server.
Try using Web or Express, which have very limited features. These two editions are good for small-scale implementations.
You will be able to create, deploy, and consume reports with functionalities as per the features offered by the selected edition. Not only that, but the usage of processors of the system and memory consumption are based on the edition you have selected. Processors are more relevant to database engine. The Enterprise edition will use operating system maximum, but Standard and Web can utilize only four sockets or 24 cores. If you have selected, Express edition, it is limited to one socket or four cores.
Reporting Services consume memory for various operations. If the edition is Enterprise, it can consume operating system maximum. However, if the edition is Standard or Web, it consumes only 64 GB and, Express edition consumes only 1 GB.
For more information about feature comparison between editions, visit the following URL: https://msdn.microsoft.com/en-us/library/cc645993.aspx
Installing Reporting Services is not a complex process. It is a part of SQL Server installation; just like selecting any other component, Reporting Services can be selected as a required component. Reporting Services 2016 offers two modes: Native and SharePoint integrated. SharePoint integrated mode will be discussed in detail in Chapter 9 , SharePoint Integration. Hence, this recipe focuses on Native mode.
Native mode is the default Reporting Services mode. You can install Reporting Services by either launching the wizard or using the command line.
It is always better to check and see whether your system is ready for Reporting Services before installing it. This check-up can be done using a tool given with the installation, which is called System Configuration Checker.
You need to make sure that you have admin rights on the system that you intend to run the installation on. For that, you need to be a member of a local administrator group, or you need to have credentials of an account that has admin rights.
During the installation, you can decide whether the installation wizard should install files and configure Reporting Services with default configurations, or only install files. If you select Install only, then you need to do the configuration later using Reporting Services Configuration Manager. Selecting the Install and configure option allows you to use Reporting Services immediately after setup is finished. However, this option will not be available if Database Engine Services is not selected as part of the instance features or the database engine is not installed on the same instance.
Let's go through the specific steps and options you select during SQL Server installation when you do the installation for Reporting Services. Note that we will not go through every page in the installation wizard, but only the pages related to this recipe. The steps are as follows:
Once the installation wizard is launched by executing
setup.exe, it opens SQL Server Installation Center, which has multiple tabs. The default tab is opened, which is Planning, and the detail page has the link for System Configuration Checker. The following screenshot shows this:
Figure 1.01
When you click on System Configuration Checker, the tool goes through a set of predefined rules and tells you whether the system is ready for installation of SQL Server or not. The following screenshot shows the result of it:
Figure 1.02
If everything is okay, continue with the installation. When you see the Feature Selection page, make sure Database Engine Services and Reporting Services - Native are selected:
Figure 1.03
When you see Reporting Services Configuration, select the Install and configure option to install Reporting Services with the default configurations:
Figure 1.04
Now you can continue with the other pages and complete the installation.
Since the installation is done with the Install and configure option, it installs the following items/features for us:
Report Server service:
Report Server Web Service
Report Server Web Portal
Other background processing applications
Reporting Services Configuration Manager
Reporting Services command-line utilities
In addition to that, it configures following items too:
Service account for the Report Server service
Report Server Web Service URL
The Web Portal URL
Report Server databases
Service accounts for accessing Report Server databases
To verify the installation, you can access the Web Portal and see whether it works or not. The URL for the Web Portal is different based on the installation:
With a default instance:
http://MachineName:80/ReportsWith a named instance:
http://MachineName:80/Reports_InstanceName
This image shows the Web Portal configured with a named instance:
Figure 1.05
If you open Reporting Services Configuration Manager (this will be discussed in detail in the Accessing Reporting Services Configuration Manager recipe), you will see that some of the services related to Reporting Services are not configured. Unattended Execution Account, Email Setting, Encryption Keys, Subscription Settings, Scale-out Deployment, and Power BI integration are not configured as part of the installation, and they need to be configured manually using Reporting Services Configuration Manager.
Installing only Reporting Services does not make your environment ready for working with Reporting Services. In order to author reports, you need Report Designer, which provides an interface for designing reports with various data regions. Report Designer ships with SQL Server Data Tools (SSDT), which was previously called Business Intelligence Development Studio (BIDS) with SQL Server 2005, SQL Server Data Tools (SSDT) with SQL Server 2012, and Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio (SSDTBI) with SQL Server 2014. SSDT is based on Visual Studio and it provides not only Report Designer for building reporting solutions, but also many other interfaces for working with relational databases, Azure SQL databases, SSIS, and SSAS solutions.
Until SQL Server 2014, this tool was automatically installed with SQL Server, but now you need to download it separately and install it.
The other most important tool that is required is SQL Server Management Studio (SSMS). This is not just for Reporting Services; it's an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. This was part of the default installation with previous versions of SQL Server, but now it needs to be downloaded separately and installed as it is a standalone tool.
You can find links to download SSDT and SSMS using any search engine. But the Microsoft SQL Server team has given us an easier way of finding the relevant download page. These links are given with the SQL Server installation wizard, and this makes sure that you download the latest versions of SSDT and SSMS.
Note that the SQL Server installation page has a number of installations related to SQL Server, but we focus only on the tools related to this book. Here is the way of installing the related tools:
Execute the SQL Server setup.exe to open SQL Server Installation Center. Click on the Installation tab; this shows the various types of installation that can be done with it:
Figure 1.06
Click on Install SQL Server Management Tools to open the web page that provides links for downloading Management Studio. This page shows you the link for downloading the latest SSMS.
You need to click on Install SQL Server Data Tools to install SSDT.
Note
Note that at the time of writing this book, the version of SSDT is 14.0.60525.0. Microsoft recommends that you install Visual Studio 2015 prior to this, and this update will replace SSDT RTM in Visual Studio 2015. If Visual Studio 2015 is not installed, SSDT will install Visual Studio Integrated Shell.
Once both tools are installed, SSMS can be used to connect and manage Reporting Services instances and SSDT can be used to design and deploy reporting solutions. You can launch SSMS and connect with Reporting Services. We will talk more about this tool in Chapter 10, Administering and Managing Reporting Services.
You can create Reporting Services projects using SSDT. It allows you to create a new project, and select the Reporting Services template and Report Server Project type. You will see more on this in Chapter 2, Authoring Reports with SQL Server Data Tools.
There is another useful tool called Report Builder, which is specifically available for power users. This does not mean that it cannot be used by developers, but as it is more of a Microsoft Office-like tool, it is more relevant to power users and business users. This will be discussed in detail in Chapter 4, Authoring Reports with Report Builder.
A newly added tool called SQL Server Mobile Report Publisher is available with Reporting Services 2016. See Chapter 6, Authoring Mobile Reports with the Mobile Report Publisher, for more on it.
Reporting Services Configuration Manager is a tool that you can use to configure Reporting Services settings and related various components. It allows you to access the settings configured during the installation and modify them if required. In addition to that, you can use it to configure settings that are not available during installation.
Note
Note that you cannot use Reporting Services Configuration Manager to manage SharePoint integrated report servers. Prior to SQL Server 2012, SharePoint integrated reporting services instances were able to manage with it, but starting with SQL Server 2012, SharePoint integrated mode is managed and configured by using SharePoint Central Administration.
Unlike SQL Server Management Studio, Reporting Services Configuration Manager is version-specific. You cannot use a Configuration Manager installed for a specific version in your machine to manage another instance of Reporting Services that is not the same version, even if it is a lower version. As an example, if you have installed Reporting Services 2016 Configuration Manager, you cannot use it to manage Reporting Services 2014.
Most of the settings configured through Configuration Manager are stored in the rsreportserver.config file. Even though it is possible to make changes to the file directly without using Configuration Manager, you should not do it unless it is really required. If you have to, make sure you have copy of the file before making any changes.
Reporting Services Configuration Manager is installed with the default installation. It allows you to manage the following items:
Report Server service account: You can update the account that is already added or you can add a new account for the service
Web Service URL: You can modify or create new URLs for accessing Report Server Web Service
Database: You can create or configure the report server database for the Report Server. It also allows you to connect with a database that is already created and set with the required content
Web Portal URL: You can modify or create new URLs for accessing the Reporting Services Web Portal
Email settings: You can configure an SMTP server or gateway that Report Server can use to deliver reports to e-mail addresses
Execution account: You can configure an unattended account that report server can use for accessing various sources on scheduled operations, or when user credentials are not given for operations
Encryption keys: You can back up, restore, or change the symmetric key used to encrypt stored connections and credentials
Subscription settings: You can configure an account for subscriptions that need to access file shares
Scale-out Deployment: You can add multiple report servers to scale out your reporting solution
Power BI integration: You can use this section to register a Power BI account if you plan to integrate your reports with Power BI
In order to access either a local report server or remote report server using Configuration Manager, you need local system administrator permission of the server.
Let's see how we can open Configuration Manager and see the configuration settings. The steps are as follows:
When you open the Configuration Manager, it prompts you with a dialog box for Server Name and Report Server Instance:
Figure 1.07
Enter the server name and select the Report Server Instance for connecting with the required report server. If you have provided valid values and you have local administrator permission, it will open Configuration Manager for you:
Figure 1.08
You can navigate through the tabs given in the left pane. Note that the Apply button is grayed out until you make a change. Once the changes are made, you need to click on the Apply button to save and apply your changes.
The rsreportserver.config XML file holds most of the settings related to Configuration Manager. When you change any settings with Configuration Manager, the changes are applied to this file. You can see this file in the <installationpath>eport\Microsoft SQL Server\<instance name>\Reporting Services\ReportServer folder. You can open it and edit using any text editor or XML editor, but it is not recommended because your changes may prevent Report Server from functioning properly.
There are some settings that are not available through Configuration Manager. For example, if you need to add another Authentication Type, it has to be manually added to the rsreportserver.config file, as Configuration Manager does not provide an interface for adding Authentication Types.
Some of the settings should only change with Configuration Manager even though it is possible to change them through rsreportserver.config. For example, encrypted values that are related to some nodes should not be changed through the rsreportserver.config file manually.
In addition to the rsreportserver.config file, there are two more configuration files: rsreportserverservices.exe.config and rsreportdesigner.config. You can read more about them at https://msdn.microsoft.com/en-us/library/ms157273.aspx and
https://msdn.microsoft.com/en-us/library/bb630448.aspx
.
The service account for Reporting Services is assigned during the installation, it is configured to run the Reporting Services service within the context of a specific Windows account. Installation allows you to select a local account, domain user account, or built-in account for the service. Once set, the installation makes sure that the account has the required permissions to access resources and run processes related to Reporting Services by adding the account to relevant security groups.
If you need to view or reconfigure the assigned account, Microsoft recommends that you use Reporting Services Configuration Manager without using Services Desktop Application. You might change the assigned account to a new account, or you might change the password set for the account if it has been changed at the Windows level. If you use Services Desktop Application to manage the service account, the additional settings that are linked with the assigned account will not be automatically changed. Specifically, it will not be added to relevant security groups. But Reporting Services Configuration Manager makes sure that all required settings are updated accordingly when a change is applied to the assigned account.
You can see the current assigned account using Reporting Services Configuration Manager. Once connected to the instance, you can click on the Service Account page to see the current account set or to set a new account for the service:
Figure 1.09
As you can see, there are two options for setting it. You can either select a built-in account or a user-defined (local or domain) account. If you have accepted the default during account selection, you should see the selected account as Virtual Service Account.
When selecting an account, either during installation or later using Configuration Manager, it is important to remember that the account you select has no excessive privileges. Not only that, but also you must make sure that it is a dedicated account for Reporting Services.
There are different types of account that you can select for Reporting Services:
Local system account: This account is a highly privileged account that should not be selected (or required) for Reporting Services. Generally, we should avoid highly privileged accounts for SQL Server services.
Network service account: This account has fewer privileges compared to the Local System account but has network log-on permissions. Therefore, it is not a bad practice to select this account as the Reporting Services service account. However, since this account is used by many applications, it might introduce a security threat, so it is better to avoid this account type as well.
Virtual service account: Virtual Service account was introduced with Windows Server 2008 R2, and SQL Server started supporting it from SQL Server 2012. It is simply a virtualized account based on a Network Service account but with its unique identifier. It is a local account, and you do not need to create or configure it. For SQL Server services, virtual accounts are automatically created during installation. Since it is a managed account, you do not need to worry about policies or password expiry issues, and this account type is recommended if you do not have a domain user account configured.
Local service account: This is a local account that is similar to an authenticated Windows user account in a local users group. This account accesses resources as a null session or without credentials. Therefore, this will not work as expected when report server needs to access external resources such as a remote database.
Local user account: There is a local, least-privileged Windows account configured for Reporting Services. This is recommended when Reporting Services is installed in a non-domain environment.
Domain user account: A least-privileged Windows domain account configured for Reporting Services. This is recommended when Reporting Services is installed in a domain environment.
Managed service account: This account is similar to a Virtual Service account, but this is a type of domain account. It is more secure than a domain user account and, unlike the Virtual service account, it needs to be created and configured by a domain administrator before it can be used.
In order to manage the assigned account, follow these steps:
Start the Reporting Services Configuration Manager as shown in the preceding image.
Make the necessary changes to the Report Server service account and click on Apply. You may change the account from Virtual service to Network service account for testing.
The Apply button opens the SQL Server Connection Dialog window. It requires an account that has administrative privileges for applying grant rights script to a newly selected service account. If you have administrative permissions, select Current User - Integrated Security, or supply SQL Server credentials by selecting SQL Server Account:
Figure 1.10
Click on OK to save the changes. Once completed, verify the messages in the results shown in the next image and see whether everything is successfully completed:
Figure 1.11
Reporting Services Configuration Manager performs a few operations to apply your changes successfully:
A newly configured account is added to the report server group created on the local server.
The account is added to the necessary security roles in the SQL Server database engine instance that hosts the report server database, only if the connection configured to the report server database is set to service account. This account is not just a part of report server database; it gets added to the Master, Msdb, ReportServer, and ReportServerTempDB databases under different roles:
Master database: This account is added to RSExecRole
Msdb database: This account is added to RSExecRole, SQLAgentOperatorRole, SQLAgentReaderRole, and SQLAgentUserRole
ReportServer database: This account is added to db_owner and RSExecRole
ReportServerTempDB database: This account is added to db_owner and RSExecRole
The encryption key is updated, including the profile information of the account.
Note that if you add a new service account to Reporting Services, the old account will not be removed from the database instance. See the next screenshot. It shows both accounts under the Users node; NT AUTHORITY\NETWORK SERVICE is the new account and NT SERVICE\ReportServer$SQL2016_M is the old account. Therefore, it should be manually removed if it is not required by the database engine:
Figure 1.12
As you saw in the Installing Reporting Services with default configurations recipe in this chapter, once the installation is done, one way of verifying that the installation is successfully completed is by accessing one of the URLs that are configured automatically during the setup. If the installation is done with install only mode, then the URLs have to be configured using Reporting Services Configuration Manager, because only the install and configure option creates URLs for Reporting Services during the installation.
Installation with the default configuration reserves two URLs for Reporting Services, one for the Web Service application, and another for the Web Portal application. These URLs are defined as URL reservations in HTTP.SYS, which is an operating system component that listens to network requests and handles them. Even though it is possible to create and configure these URLs using different tools, it is recommended to use only Reporting Services Configuration Manager for creating and configuration. URL reservations are automatically created when you configure URLs using Reporting Services Configuration Manager.
URL reservations in HTTP.SYS define which URLs are configured for accessing Reporting Services applications. You can configure one or more URLs for accessing the Report Server Web Service and one or more URLs for accessing the report Web Portal. Although multiple URLs can be configured for web service and web portal, all URLs have to be connected to the web service virtual directory and the web portal virtual directory respectively. It is not possible to have multiple virtual directories.
Note
Note that Internet Information Services (IIS) is no longer used by Reporting Services to manage applications, and it is not required. All requests are completely handled by HTTP.SYS.
If you need to create or configure URLs using Reporting Services Configuration Manager, you need to understand how default URLs are created during the installation. This table explains how URLs are set by default:
|
Application |
Instance type |
Default URL |
Reservation in
|
|
Web service |
Default instance |
|
|
|
Web service |
Named instance |
|
|
|
Web Portal |
Default instance |
|
|
|
Web Portal |
Named instance |
|
|
You can see this in the rsreportserver.config file located in the <installationpath}\Microsoft SQL Server\<instancename}\Reporting Services\ReportServer folder:
Figure 1.13
As mentioned before, you should not use any other tool to create or configure URLs, including the rsreportserver.config file.
In order to create or configure them, you need to make sure that you are a member of local administrator group of the server. In addition to that, if IIS is running in the same server, make sure that there are no virtual directories already configured with the names you have planned for Reporting Services virtual directories for applications.
Reporting Services Configuration Manager has two pages for managing URLs, one for web service applications, and another for web portal applications. It is shown in the following screenshot:
Figure 1.14
If you need to configure the Web Service URL, select the page and make the necessary changes:
If you need to configure the Web Portal URL, you can select it to view and configure it. The preceding screenshot shows the settings related to the Web Service URL. As you see, the URL is formed by combining the machine name and virtual directory name.
If you need to change the name of the virtual directory, you can simply change the text in the Virtual Directory input box. Remember that the name of the virtual directory is case sensitive, and only one virtual directory is allowed for the web service application.
If you want to configure multiple URLs for the web service application, you can click on Advanced... and add more URLs.
When you click on Advanced..., it opens a window that can be used to add both HTTP and HTTPS URLs. For testing purposes, let's allow users to connect with the web service using another port. Follow these steps to configure a URL with another port:
Click on the Advanced... button to open the Advanced Multiple Web Site Configuration window.
Click on Add in the HTTP section to add a new report server HTTP URL.
Set the TCP Port as you want and click on OK to save it. Click on OK again to save the settings:
Figure 1.15
Once the settings are saved, you should see two URLs appearing: one with port 80, which is the default, and another with port
50000. When you set a port, make sure that it is not reserved by another application:
Figure 1.16
You can click on either URL to open the web service application. The configuration of Web Portal URL is the same as the Web Service URL. The Web portal URL page also has the Advanced... button for configuring additional URLs. Let's see how we can change the name of the virtual directory for web portal.
For the Web Portal URL, the default virtual directory name is formed as
Reports_<instancename>. As you can see in the following image, in our testing machine, it has been set asReports_SQL2016_Mand the URL has been formed ashttp://DINESH-LAP01:80/Reports_SQL2016_M:
Figure 1.17
Let's change the name of the virtual directory to
ProductionReports, as shown next. Once changed, the Apply button must be clicked on to save the changes. Note that you need to make sure that there is no virtual directory in IIS with the same name, if IIS is installed in the same server:
Figure 1.18
You can click on the new URL and see whether it opens web portal or not.
As we discussed before, Reporting Services Configuration Manager creates URL reservations in HTTP.SYS when changes are applied. If you open the rsreportserver.config file, you will see how changes are applied to the file.
As we set an additional port to the web service application, we can set additional IP addresses or hostnames for forming new URLs. One advantage with additional URLs is that you can provide different URLs for different audiences, as shown here:
For internal (or Intranet) users:
http://server/reportsFor external (or Internet) users:
http://www.company.com/reports
Figure 1.19
When accessing either web service or web portal in the local server, you can use localhost instead of an IP address or server name. You can simply call applications using
http://localhost/reportserver
and
http://localhost/reports
. The
http://localhost
URL is interpreted as
http://127.0.0.1
. However, if you have configured URLs to the computer name or a single IP address, this will not work unless you have added an additional reservation for 127.0.0.1.
Reporting Services stores and maintains different types of information such as settings, metadata, and objects related to its applications. Some of this information is stored in configuration files and some of it, specifically metadata and objects, is stored in a SQL Server database. Reporting Services native mode uses two databases named ReportServer and ReportServerTempDB by default. The ReportServer database is the primary storage for holding all metadata and objects. ReportServerTempDB is for maintaining temporary information.
Note
Reporting Services uses three databases if the mode is SharePoint integrated mode. In addition to the ReportServer and ReportServerTempDB databases, it uses a third one to hold altering metadata.
If the Reporting Services instance is the default instance, the names of these databases are set as Report Server and ReportServerTempDB. If the instance is a named instance, then the default names become ReportServer$<instancename> and ReportServerTempDB$<instancename>.
The Reporting Services database is used to maintain report definitions, report models, shared data sources, schedules, security information, and snapshots. You should treat this database as a normal database; hence, it should be regularly backed up and should be a part of High Availability and Disaster Recovery strategies.
ReportServerTempdDB stores temporary data, such as session and cache data. Since it maintains temporary data, you do not need to have a backup strategy for this database. However, if this database gets corrupted or accidently deleted, you can create it using the CatalogTempDB.sql query located in the <installationpath>\Microsoft SQL Server\<instancename>\Reporting Services\ReportServer folder.
Similar to Reporting Services URLs discussed in the previous recipe, if you selected Install and Configure during the installation, these two databases are created automatically as a part of the installation. If you selected Install only, then you need to use Reporting Services Configuration Manager to create databases. You can host these databases either in a local database engine instance or in a remote database engine instance. Reporting Services supports the following database engine versions for hosting its databases:
SQL Server 2016
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
However, you need to make sure that the Reporting Services database is hosted in the correct edition:
Enterprise edition: The database can be hosted in Enterprise or Standard edition
Standard edition: It can be hosted in Enterprise or Standard edition
Web edition: The database can be hosted in Web edition (local only)
Express edition: The database can be hosted in Express edition (local only)
Developer edition: It can be hosted in Enterprise, Standard, or developer edition
You can use Reporting Services Configuration Manager to create and configure the report server database. Or you can use it to view or modify settings related to the database that is already configured. Use the following steps to create or modify the report server database:
Open Reporting Services Configuration Manager and go to the Database page.
You should see two sections: Current Report Server Database and Current Report Server Database Credential. Click on the Change Database button:
Figure 1.20
Change Database starts a wizard. You need to select a task from first page; select Create a new report server database to create a new one or select Choose an existing report server database to add an existing one. Select the first option for standalone report server.
The next page allows you to select the hosting server and credentials to connect with it. Note that the credential required in this page is for creating the database only. It will not be used by Reporting Services later to connect with the database:
Figure 1.21
Select the hosting server and click on Next after setting the credentials. The next page allows you to set Database Name and Language.
There is no restriction on the database name. You can enter the name of the database as you want. However, it is always better to have a proper naming convention and name it accordingly. As you can see, the name of the temp database is automatically set using the database name. Click on Next to continue:
Figure 1.22
The next page is for setting credentials to connect to the database by Reporting Services. You have three options: Windows credentials, SQL Server credentials, or Service credentials. If the database hosting server is a remote server, you should select either Windows or SQL Server credentials; otherwise, you can select one of these as per your requirements:
Figure 1.23
Everything required is set. Click on Next to continue. You should see a summary page with all your input. Continue with the wizard to complete the database configuration.
If you go back to the second section of the Database page in Reporting Services Configuration Manager, you will see that there is a button named Change Credentials. This is for changing the credentials configured for Reporting Services to connect with the report server database. Although this gets configured either during the installation or while configuring the report database, you may use this option for changing credentials in the following situation:
The password set to the local or domain account is changed
The authentication type needs to be changed
The wizard creates both ReportServer and ReportServerTempDB in the selected SQL Server instance. It uses codes stored in the <installationpath>\Microsoft SQL Server\<instance name>\Reporting Services\ReportServer folder to create these databases. Once the report server databases are created, report server uses the credentials stored for accessing report databases. During the configuration, it creates the encryption keys required and adds the necessary permissions for accessing the database.
Note
Users who access reports do not require permissions for report database or login for report database server. Report server uses credentials stored for accessing the database when required.
Reporting Services stores the connection for report database in the rsreportserver.config file in an encrypted format. Depending on the authentication type selected, some of the nodes related to the connection may be empty. As an example, if the authentication type selected is Service Credentials, then LogonDomain, and LogonCred are empty:
Figure 1.24
In order to verify the report database configuration, log in to the SQL Server instance that hosts the databases. You should see two databases configured with the names you used:
Figure 1.25
Remember, Microsoft does not recommend using these databases directly to query or modify data. It is an internal database for report server and should not be accessed and modified.
As mentioned before, Reporting Services uses three databases when the mode is SharePoint integrated mode. Read more about SharePoint integrated mode in Chapter 9, SharePoint Integration.
Although the installation and configurations are done and everything works fine as you expected, it does not mean that the process of installation and configuration is completed. You have to do a few more tasks to complete it. These tasks make sure that your Reporting Services is protected and maintained properly.
There are a few key tasks you should perform after installation and configuration:
Make sure that report database is a part of your backup strategy
Back up the
rsreportserver.configfileMake sure you have backed up Encryption Keys
The startup type of the services related to Reporting Services and database engine are set as Automatic
The required tasks can be performed using standard techniques and available tools:
Taking a backup of the ReportServer database is easy with Management Studio. Or it can be a part of your scheduled backup job. The main thing to consider is that it should be treated as a normal database and backed up regularly. You may implement a strategy that has one backup type, such as Full Database backup, or based on the complexity in terms of number of reports it holds, frequency of changes, or heavy user interaction. There can also be a strategy that has a combination of various backup types, such as Full, Differential, and Log.
To back up the
rsreportserver.configfile, you can take a copy of it and keep it in a safe place. Since most settings related to report server are held with this, you need to make sure that you have a backup copy of it.You can use Reporting Services Configuration Manager to back up Encryption Keys. This should be done because this requires many instances when recovering after failure. We will discuss Encryption keys in more depth in Chapter 10, Administering and Managing Reporting Services.
In order to make the startup type of related services automatic, use Services Desktop Application. Note that you should not use this application to change any other settings.
The key thing to understand is how quickly you respond to an issue, troubleshoot an issue, or how you can recover your reporting environment in a disaster. There are a few standard recovery strategies for reporting environments, but these post installation tasks will definitely help you to recover in most situations. As an example, if you make a mistake while changing the rsreportserver.config file manually, you can simply restore it to the previous stage if you have a backup copy of it. If, for some reason, your ReportServer database becomes corrupted, you can get it recovered using the database backup. Therefore, post installation tasks should not be ignored and must be a part of the installation and configuration process.
