Managing the Solution

So far, we have been using the Web-based Report Manager tool both for viewing reports and for management purposes such as setting up report properties and security. Report Manager is a convenient management tool if you are managing one report server at a time over a Web connection, but otherwise you can use SQL Server Management Studio to connect to report servers and manage all aspects of your BI solutions (including database, Analysis Services, Integration Services, and Reporting Services) from within one tool.

Deployment

As described in the "High-Level Architecture" section, we will be using separate report and database servers. Deploying Reporting Services is similar to deploying many ASP.NET applications. You need to configure the server as an application server and enable IIS and ASP.NET.

The database server has no special requirements; the ReportServer and ReportServerTempDB databases are created when you set up Reporting Services. To deploy the reports and data sources that you have created, you can use the BI Development Studio as we have already described. If you want to create a repeatable, easy-to-deploy package containing all the reports and data sources to be deployed to production, however, unfortunately it is not going to be as easy as the Analysis Services or Integration Services deployment wizards.

Currently, no tool enables you to package up a report solution for deployment. If you can't use BI Development Studio because you want something that an operations team can use, you will have to get them to either deploy the report files one at a time using Report Manager or SQL Server Management Studio. Alternatively, you can write a custom script to deploy the solution following the techniques in the PublishSampleReports.rss script that ships as a sample with Reporting Services. You can execute scripts using the rs command-line utility, for example:

rs -i PublishSampleReports.rss -s http://myserver/reportserver -v parentFolder="Professional Services Reports" 

Alternative Deployment: Database and Reporting Services on the Same Server

One option frequently used is to install both the SQL Server database and Reporting Services on the same server. The best reason to do this is the traditional one: cost. Because Reporting Services is a component of SQL Server just like Analysis Services or Integration Services, that means that every server running Reporting Services needs a valid SQL Server license, even if the report definitions are being stored in a separate, licensed SQL Server catalog database.

Because there will probably be moderate use of this particular application and the database is not very large, you might be able to get away with using a single server for both database and reporting servers in this case. If the application starts to suffer from performance problems, it is easy to add an additional reporting server, or even a Web farm to handle large numbers of concurrent users.

One area to be careful of is corporate server standards. Large enterprise companies often specify that database servers cannot run additional Windows services that are typically not required for databases for security reasons, and IIS is usually not enabled on database servers. Because Reporting Services requires IIS, they will either need to make an exception in this case or run Reporting Services where it belongs (on a Web server).

Maintenance

After the initial euphoria from a user community accustomed to struggling to get information has worn off, it is likely that they will quickly come up with requests for changes in the form of new reports that present the information in different ways, or request new measures to understand different areas of the business.

Adding New Reports

Using the Visual Studio report designer, you can easily add new reports or modify existing ones, and then publish them to the server. Because the operations staff may have modified the data source connection information, the OverwriteDataSources project setting is useful as it allows developers to redeploy report projects without overwriting data sources.

In a production system, however, the usual approach is for the developers to publish their changes to a test server, which are then deployed to the production reporting server, as described in the "Deployment" section.

The Long-Term Outlook for Standard Reporting

Publishing a set of standard reports usually has the most benefit to an organization when the solution is initially deployed. In the long run, requests for minor changes, which will inevitably accumulate from the user community, can quickly drive the cost of the solution up beyond the point where any real return on investment is possible. The most practical answer to this problem is to introduce some form of "self-service" reporting and analysis after the initial standardizing of reports. This could take the form of building Analysis Services cubes to enable users to use tools such as Excel to analyze their information or using the end-user reporting features of Report Builder.

Operations

Mission critical is a term usually associated with line-of-business systems such as payroll and ERP. When users become accustomed to easy access to information through a reporting solution, however, there will be a lot of pressure on operations teams to ensure that the system is reliable. In addition to the usual operations tasks associated with servers and databases, a few additional areas are required for reporting solutions.

Long-Running Report Jobs

This is a common issue that operations staff must address. Reports could start taking a long time to complete for various reasons, such as an increased load on the source database server or an increase in the amount of data for a report. The best approach is to set sensible timeouts on the server or specific reports so that Reporting Services can automatically cancel any reports that are taking too long to complete.

Alternatively, administrators can see which jobs are running and cancel them if necessary using the Manage Jobs section under Site Settings in Report Manager, or the Jobs folder in SQL Server Management Studio. However, reports are usually waiting for the data source to return data, so a common approach is to cancel the long-running query on the database server.

Backing Up Information

Backing up a Reporting Services solution covers several areas. First, all the catalog information such as reports, security settings, and snapshots are stored in the ReportServer SQL Server database. This database needs to be backed up using standard SQL Server backups, along with the ReportServerTempDB database. Second, because all configuration information such as database connection strings is stored in an encrypted format in the ReportServer database, you need to back up the encryption keys for your server using the rskeymgmt utility.

Finally, the report projects and solution files that you used to develop the reports also need to be backed up. The best approach for this is to use a source control solution such as Visual Studio Team System's version control or SourceSafe.