Chapter 2: Putting the Pieces in PlaceInstalling Reporting Services

Before you can begin to enjoy all the benefits of Reporting Services discussed in Chapter 1, you, of course, have to install the Reporting Services software. SQL Server 2000 Reporting Services required its own installation process. Things have gotten a bit simpler with SQL Server 2005 Reporting Services. Reporting Services now installs as part of the SQL Server 2005 installation. Nevertheless, it is important to understand the structure of Reporting Services before completing your installation.

In this chapter, you learn about the components that make up Reporting Services and the three licensed editions of Reporting Services offered by Microsoft. Next, you find out how the components are combined in different types of Reporting Services installations and see how to plan for each installation type. As part of that planning, you learn about the software that must be in place prior to installing Reporting Services. After considering these preliminaries, we walk you through the installation process.

Preparing for the Installation

The most important part of the Reporting Services installation is not what you do as you run the setup program, but what you do before you begin. In this section, we discuss the knowledge you need and the steps you should take to prepare for installation. With the proper plan in place, your Reporting Services installation should go smoothly and you can create reports in no time.

The Parts of the Whole

Reporting Services is not a single program that runs on a computer to produce reports. Instead, it is a series of services, web applications, and databases that work together to create a report management environment. As you plan your Reporting Services installation, it is important that you understand a little bit about each piece of the puzzle and how all these pieces work together to create a complete system.

Figure 2–1 shows all the parts that make up a complete Reporting Services installation. Each part has a specific role to play in the development, management, and delivery of reports or in the management of the Reporting Services environment itself. Not all these items are installed with Reporting Services. Some are prerequisites and must be installed before you can begin the Reporting Services installation process.

Figure 2–1: Reporting Services component parts

Let’s take a look at each part and see how it fits into the whole.

Note 

Not all Reporting Services installations include all the items shown in Figure 2–1. The following sections of this chapter discuss the various types of installations and which components they include.

The Report Server Service

The Report Server service is the heart of Reporting Services and is, of course, installed as part of the Reporting Services installation. As you saw in Figure 1–4 of Chapter 1, the Report Server is responsible for processing any report requests. This includes fetching the report definition, retrieving the data used in the report, and rendering the report in the desired format.

The Report Server is a Windows service, and it makes its functionality available as web services. This means the Report Server does not directly interact with the user. Instead, the Report Server Windows service runs in the background and handles requests made by other programs through the web services. Like other Windows services, such as SQL Server and Internet Information Services (IIS), the Report Server begins running when the computer starts up and continues running until the computer shuts down.

Because the Report Server service starts up on its own, it needs to have a valid user-name and password that it can use to log on to the server when it starts up. This login information, along with other information that determines how the Report Server will operate, is stored in the RSReportServer.config file. The content of this configuration file is determined by the choices you make during the setup and configuration process.

Note 

Most of the information in the RSReportServer.config file is stored as plain text and can be modified using Notepad or a similar text editor. The login information, however, is encrypted when it is stored in this file. It cannot be changed except through the administration utilities.

The Administration Utilities

The administration utilities are tools for managing the Report Server service and for making changes to its configuration. These utilities take care of tasks such as manually starting the Report Server service if it fails to start up automatically. The utilities can also be used to change the login information used by the service when it starts up.

Most of these utility programs are run in a command window. There isn’t a user interface with menus, buttons, and text boxes. Instead, the utilities use parameters specified as part of the command line that launches the program. These parameters determine what changes the utility program will make in the Reporting Services configuration.

The one administration utility that does have a Windows user interface is the Report Server Configuration Manager. This program provides a convenient method for examining and modifying the configuration settings of a Reporting Services installation. You learn about the Report Server Configuration Manager in more detail in the section “The Report Server Configuration Manager.”

The administration utilities can be run on the computer that is hosting the Report Server service to manage the configuration on that computer. Most of the administrative utilities can also be used to manage a Report Server service that is running on another computer. This is called remote administration.

The administration utilities are installed as part of the Reporting Services installation.

SQL Server 2000/SQL Server 2005

SQL Server 2000 or SQL Server 2005 is required to hold the database where Reporting Services stores its Report Catalog database. Reporting Services also uses the SQL Server Agent, which you learn about shortly. In addition, databases in SQL Server can be used as data sources for Reporting Services reports.

SQL Server Agent

SQL Server Agent is part of SQL Server and is created as part of the SQL Server installation process. It is used by SQL Server to execute jobs scheduled to run at a certain time. These jobs might back up a database or transfer information from one database to another. Jobs may be scheduled to run once or they may run on a regular basis, such as once a day or once a week.

Reporting Services also uses the SQL Server Agent to execute scheduled jobs. These jobs are used to run reports and distribute the results. In Chapter 1, you learned about users who subscribe to a report. When users subscribe to a report, they ask for it to be run and delivered to them on a regular basis. When a user creates a subscription, Reporting Services creates a SQL Server Agent job to handle that subscription.

For example, our Production Manager in Chapter 1 wanted an inventory report to be printed every four hours during the workday. He subscribes to the inventory report and creates a delivery schedule of 8:15 A.M., 12:15 P.M., and 4:15 P.M. When this subscription is created, Reporting Services creates a SQL Server Agent job scheduled to run at 8:15 A.M., 12:15 P.M., and 4:15 P.M. each day. The job takes care of running the report and e-mailing it to the Production Manager.

The Report Server and Report Server Temp DB Databases

During the Reporting Services installation process, two databases are created within SQL Server: the Report Server and Report Server Temp DB databases. The Report Server database is used to store the Report Catalog. (Recall from Chapter 1 that the Report Catalog holds the information about all the reports deployed to a Report Server.) The Report Server database also holds information about the Report Manager website. This includes such things as the folder structure of the website and the security settings for each folder and report.

As the name implies, the Report Server Temp DB database is used as temporary storage for Reporting Services operations. Information can be stored here to track the current users on the Report Manager website. Short-term copies of some of the most recently executed reports are also stored here in what is known as the execution cache.

Sample Reports and the AdventureWorks Database

As part of the Reporting Services installation process, you can choose to install several sample Reporting Services reports. These reports end up in the Report Catalog within the Report Server database. If you choose to install the sample reports, another database, called AdventureWorks, is installed in SQL Server. The AdventureWorks database serves as the data source for the sample reports.

Internet Information Services

Internet Information Services (IIS) is used to host Internet and intranet websites. IIS also serves as the host for web services. A website, of course, is used by a person to request information from a computer over the Internet or some other network. A web service is used by a computer to request information from another computer over the Internet or some other network.

When Reporting Services is installed, it creates a website and a web service hosted by IIS. Therefore, IIS must be installed before you begin the Reporting Services installation.

The Reports Website

The Reporting Services installation creates a website called Reports. The Reports website is what provides the Report Manager interface for Reporting Services. If Reporting Services is installed on a server named www.MyRSServer.com, then when you surf to www.MyRSServer.com/Reports, you will see the Report Manager home page.

The Reports website is built using ASP.NET. This means ASP.NET support has to be enabled on IIS for the website to function. You learn how to do this in the section “Installation Requirements.”

Report Server Web Service

A web service called Report Server is also created by the Reporting Services installation. The Report Server web service allows other programs to interact with and even administer Reporting Services. In addition, it allows other programs to request reports without having to go through the Report Manager interface. In short, the Report Server web service allows Reporting Services to be tightly integrated into other applications. Because web services work across an intranet or across the Internet, the web service interface allows Reporting Services to be integrated with applications running in the next room or in the next country.

The Report Server web service is also built using ASP.NET, so once again, ASP.NET support must be enabled on IIS for this feature to function.

Report Designer

As discussed in Chapter 1, Reporting Services reports are created using the Report Designer either in the Business Intelligence Development Studio or in Visual Studio 2005. The Report Designer will function exactly the same in either development tool. There is no difference between a report created in the Business Intelligence Development Studio and in Visual Studio 2005.

If you are going to use the Business Intelligence Development Studio for creating reports, you need to install the Business Intelligence Development Studio as part of the Reporting Services installation process. If you plan to create reports using Visual Studio 2005, you need to purchase it and install it separately. Visual Studio 2005 does not come with Reporting Services.

Documentation

The final piece of Reporting Services is the documentation. The bulk of this documentation is found in the SQL Server Books Online. After Reporting Services is installed, you can view the SQL Server Books Online through your Start menu. You’ll find it under Programs | Microsoft SQL Server 2005 | Documentation and Tutorials | SQL Server Books Online. In addition to this is a set of help screens for the Report Manager interface that can be accessed through the Reports website.

Editions of Reporting Services

Reporting Services can be licensed in three different editions: Standard Edition, Enterprise Edition, and Developer Edition. There is also an Evaluation Edition, which does not require a license, but can only be used for a limited time. We won’t be discussing the Evaluation Edition in this book, but you can think of it as essentially being a Developer Edition you get to try out for free.

Reporting Services is licensed as part of your SQL Server 2005 license. Therefore, in a production environment, the Reporting Services edition you are licensed to use is the same as the SQL Server 2005 edition you are licensed to use. For example, if you have a Standard Edition of SQL Server 2005, you are only licensed for the Standard Edition of Reporting Services.

The Standard Edition

All editions of Reporting Services provide a rich environment for report authoring, report management, and report delivery. Just a few of the more advanced features of Reporting Services are not included in the Standard Edition. These advanced features are listed in the following section, “The Enterprise Edition.”

The Enterprise Edition

The Enterprise Edition of Reporting Services includes the following advanced features:

• Security Extension API   Create your own custom security structure, rather than requiring Windows integrated security for the Report Manager web application and the web service. The Security Extension API is discussed in Chapter 12.

• Data-Driven Subscriptions   Send a report to a number of users from a predefined mailing list. Data-driven subscriptions are discussed in Chapter 11.

• Web Farm Configuration   Configure several IIS servers running the Report Manager web application and the web service to point to a single SQL Server 2000 or 2005 server hosting the report catalog. The web farm configuration is discussed in the section “Types of Reporting Services Installations.”

• Advanced Server Support   Utilize multiple symmetric multiprocessing to support more than four processors and additional memory support to handle more than 2GB of RAM.

• Report Builder   Develop basic reports based on a model of the underlying database without having a detailed knowledge of database querying or database structure. The Report Builder is discussed in detail in Appendix D.

The Developer Edition

The Developer Edition provides support for all the features of the Enterprise Edition. The Developer Edition does not, however, require that you have an Enterprise Edition license of SQL Server 2005. Of course, the Developer Edition is only for development and testing. It cannot be used in a production environment.

Types of Reporting Services Installations

Now that you are familiar with the components that make up Reporting Services and the ways that Microsoft licenses Reporting Services, you can give some thought to just what your Reporting Services installation will look like. The first decision you need to make is which of the components you want to install. Although you can choose to include or exclude items in any combination you like, in the end, only three combinations make sense: the full installation, the server installation, and the report author installation.

In addition to these are a couple of specialized installation types. These are the distributed installation and the web farm installation. These installations are for high-end, high-volume Reporting Services sites or installations where security concerns prevent IIS and SQL Server from running on the same computer. We will discuss these configurations briefly, so you are familiar with the variety of ways that Reporting Services can be configured.

The Full Installation

The full installation, as the name implies, is the “everything including the kitchen sink” installation. All the items shown in Figure 2–1 and discussed previously are included in this installation. Nothing is left out.

The full installation is most likely to be used in a development environment. This might be on a server used by a group of developers or on a power workstation used by a single developer. In either case, we want to have all the bells and whistles available to us as we figure out how to best use Reporting Services to suit our business needs.

The Server Installation

The server installation is most likely to be used when we’re setting up Reporting Services on a production server. On a production server, we only want those items that are going to be used to deliver reports or help us manage Reporting Services. We don’t want to include anything that will take up space unnecessarily. Figure 2–2 shows the items included in the server install.

Figure 2–2: The server installation

The server installation includes the Report Server service and the administration utilities used to manage it. This type of installation also includes the Reports website and the Report Server web service for managing and delivering reports. In addition, Reporting Services will need the SQL Server Agent, and the Report Server and Report Server Temp DB databases for its operations.

We won’t be doing any development work on the production server, so we will not need Visual Studio or the Business Intelligence Project Types. It is possible that you would want the documentation on the production server for questions on managing Reporting Services. Probably a better idea, though, is to have the documentation handy on a development computer and to keep the production installation as uncluttered as possible. The same can be said for the sample reports and the Adventure Works database. You may want these on your production server for demonstration purposes, but again, it is probably better to do this on a different computer and reserve your production server for reports and data required by your users.

The Report Author Installation

The report author installation is for individuals who are creating Reporting Services reports but not doing heavy-duty development. These report authors may even be creating ad hoc reports and require more capabilities than those available in the Report Builder. Report authors will not be creating full-blown applications that incorporate Reporting Services as part of a larger business system. The items included in the report author installation are shown here.

Report authors need the capability to create and preview reports. This capability is found in the Report Designer available in the Business Intelligence Development Studio or in Visual Studio 2005. Report authors may also want access to the Reporting Services Books Online to look up information as they create reports. (Although, in my humble opinion, this book would serve as a better resource.) When report authors have completed their reports and are ready to have others use them, they will deploy the reports to a production Reporting Services server.

The Distributed Installation

In a distributed installation, the Reporting Services items discussed are not installed on a single computer. Instead, they are split between two computers that work together to create a complete Reporting Services system. One computer runs SQL Server 2000 or SQL Server 2005 and hosts the Report Server database. This is the database server. The other computer runs the Report Server service and IIS. This is the report server.

Figure 2–3 shows a distributed installation. Note, Figure 2–3 shows the servers and the report designer workstations. It does not show computers used for viewing reports.

Figure 2–3: A distributed installation of Reporting Services

The distributed installation has advantages when it comes to scalability. Because the workload of the three server applications—IIS, SQL Server, and the Report Server—is divided between two servers, it can serve reports to a larger number of simultaneous users. The disadvantage of this type of installation is that it is more complex to install and administer. However, if you need a high-volume solution, it is certainly worth the effort to obtain a solution that will provide satisfactory response times under a heavy workload.

The Web Farm Installation

The web farm installation is a specialized form of the distributed installation, as shown in Figure 2–4. In a web farm, a single database server interacts with several report servers. Each of the Report Servers uses the same Report Server database for its information. By using additional report servers, we can handle even more simultaneous users with the web farm installation than we could with the distributed installation.

Figure 2–4: A web farm installation of Reporting Services

Again, note that Figure 2–4 shows only the servers and the report designer workstations. It does not show computers used for viewing reports.

When report designers create reports, they can deploy them to any of the report servers. No matter which server is used, the reports will end up in the single Report Server database. Once the reports are in the Report Server database, they can be delivered by any of the report servers. In addition, because all the information about the Report Manager is stored in the Report Server database, any changes to the Report Manager configuration made on one server will take effect on all the servers.

For example, suppose an administrator uses the Reports website to access the Report Manager through Report Server A. The administrator creates a new folder in Report Manager called Sales Forecasts 2006, sets the security so the sales staff can access this folder, and places the Sales Forecast report in the folder. Immediately after the administrator is finished, a salesperson brings up Report Manager through Report Server C. The salesperson can browse the contents of the Sales Forecasts 2006 folder and will be able to run the Sales Forecast report.

As with the distributed installation, the web farm installation provides a way to handle a large number of simultaneous requests for reports. Even though the web farm uses a number of servers to deliver reports, it allows the Report Manager interface to be administered without duplication of effort. The web farm installation may take additional effort to get up and running, but once it is ready to go, it provides an efficient means of serving a large number of users.

Installation Requirements

We briefly touched on some of the required software for many of the Reporting Services items. In this section, we itemize these requirements with respect to each of the three installation types just discussed. Before we get to that, however, let’s take a look at the hardware requirements for Reporting Services.

Hardware Requirements

The first thing to keep in mind when considering what computer hardware to use for Reporting Services is this: bigger and faster is better. With Reporting Services, we are dealing with a server application that will be handling requests from a number of users at the same time. In most installations, the Report Server service will be sharing processor time and computer memory with IIS and SQL Server 2005. We need to have enough server power, so all three of these systems can happily coexist.

Processor

Microsoft’s stated minimum processor is a 500 MHz Pentium II. You should install Reporting Services on this type of computer only if you are a patient person. A more realistic low end is probably a Pentium III at or near 1 GHz. This is true even for the report author installation. The Business Intelligence Development Studio and Visual Studio 2005 demand a fair amount of horsepower to keep them from being sluggish.

Computer Memory

Microsoft’s minimum requirement for computer memory is 512MB. This is, indeed, a bare minimum. If you are running Reporting Services on the same server with IIS and SQL Server, that minimum should probably go up to 1GB.

Disk Space

A server installation of Reporting Services requires a minimum of 50MB of disk space. This does not include the space required for SQL Server 2005 or IIS. Consult the Microsoft website for information on the disk space requirements for these items.

A report author installation requires a minimum of 30MB of disk space. Plan on using an additional 145MB if you are installing the sample reports. Taken all together, you are going to need a minimum of 225MB of disk space for a full installation of Reporting Services.

Remember, these requirements are minimums. Also, keep in mind that they do not include the space required for reports to be deployed to the server or project files created by the Report Designer. A Reporting Services installation is not useful if there is no room for reports.

Software Requirements

The following software must be installed and running properly on your computer before you can complete a server installation:

• The latest service pack for your Windows version. (For Windows 2000, this is SP4. For Windows XP, this is SP2.)

• SQL Server 2000 with Service Pack 3a or SQL Server 2005.

• Internet Information Services (IIS) 5.0 or higher.

• The .NET Framework version 2.0.

• ASP.NET support must be enabled in IIS. (See the following section “Other Installation Considerations” for more on this.)

• Microsoft Data Access Components (MDAC) 2.6 or higher.

Other Installation Considerations

You need to keep several other tidbits of information in mind as you plan your Reporting Services installation. Many of these items are listed here.

Distributed Installation and Web Farm Installation Considerations

If you create a distributed installation, the report server and the database server must be in the same domain or in domains that have a trust relationship. If you create a web farm installation, all the report servers and the database server must be in the same domain or in domains that have a trust relationship.

Database Server Considerations

The following are a couple of things to keep in mind as you are determining which server will host the Reporting Services databases:

• The Report Server and Report Server Temp DB databases must be hosted by SQL Server 2000 or SQL Server 2005. They cannot be hosted by an earlier version of SQL Server, SQL Server Personal Edition, or the Microsoft Data Engine (MSDE).

• If you do not want to use the default name for the Reporting Services database (ReportServer), you can specify a different name. The database name you specify must be 117 characters or fewer.

IIS Server Considerations

The following are two things to keep in mind as you are determining which server will host the Reporting Services website and web service:

• The Reporting Services installation creates two virtual directories under the default website or another website on the IIS Server. The Reports virtual directory hosts the ASP.NET application that provides the Report Manager interface. The ReportServer virtual directory hosts the Report Server web service.

• If you do not want to use the default names for these virtual directories (Reports and ReportServer), you may specify different names. The virtual directory names that you specify must be 50 characters or fewer.

E-mail (SMTP) Server

If you are going to allow users to subscribe to reports and have them e-mailed, you need to specify the address of a Simple Mail Transfer Protocol (SMTP) server during the Reporting Services installation. SMTP is the standard for exchanging e-mail across the Internet. You need to specify the address of an e-mail server that will accept e-mail messages from the Report Server service and send them to the appropriate recipients.

In many cases, the address of your e-mail server is the same as the portion of your e-mail address that comes after the @ sign, prefaced by www. For example, if your e-mail address is [e-mail], your e-mail server’s address is probably either www.Galactic.com or smtp.Galactic.com. Be sure to verify the address of your e-mail server with your e-mail administrator. Also, make sure this e-mail server supports the SMTP protocol and that it will accept and forward mail originating from other servers on your network.

Encrypting Reporting Services Information

One of the options you may select during the Reporting Services installation process is to require the use of a Secure Sockets Layer (SSL) connection when accessing the Reports website and the Report Server web service. When an SSL connection is used, all the data transmitted across the network is encrypted, so it cannot be intercepted and read by anyone else. This is important if your reports contain sensitive personal or financial information.

To use SSL on a server, the server must have a server certificate. Server certificates are purchased from a certificate authority and installed on your server. You can find information on certificate authorities on the Internet.

Each server certificate is associated with a specific URL. To use SSL with the Reports website and the Report Server web service, your server certificate must be associated with the URL that corresponds to the default website on the server. If www.MyRSServer.com takes you to the default website on your server, then the server certificate must be associated with www.MyRSServer.com. If you plan to require an SSL connection, you should obtain and install the appropriate server certificate prior to installing Reporting Services.

When you require the use of an SSL connection to access the Reports website and the Report Server web service, your users must specify a slightly different URL to access these locations. For instance, if the users would normally use http://www.MyRSServer.com/Reports to get to the Reports website, they will now have to use https://www.MyRSServer.com/Reports. The https in place of the http creates the SSL connection.

Microsoft Distributed Transaction Coordinator

The Reporting Services setup program uses the Microsoft Distributed Transaction Coordinator (MS DTC) to help control the setup process. You need to make sure the MS DTC will be available to the setup program. To do this, go to the Control Panel and select Administrative Tools, and then double-click Services. In the list of services, find the entry for Distributed Transaction Coordinator. The entry in the Startup Type column should be Manual or Automatic, as shown in Figure 2–5.

Figure 2–5: The services entry for the Distributed Transaction Coordinator

If the Startup Type is Disabled, use the following procedure:

1. Double-click the entry for Distributed Transaction Coordinator in the Services window.

2. Select Manual from the Startup Type drop-down box in the Distributed Transaction Coordinator Properties (Local Computer) dialog box (see Figure 2–6).

   
   Figure 2–6: The Distributed Transaction Coordinator Properties (Local Computer) dialog box

3. Click OK.

4. Close the Services window.

Login Accounts

The login account you are logged in as when you run the setup program must have administrative rights on the computer where the installation is being done. If you are doing a distributed or a web farm installation, the login account must have administrative rights on both the computer that will be the report server and the computer that will be the database server.

The login account you are logged in as must also have system administration rights in the SQL Server installation that will contain the Report Catalog. The setup program uses this login to access SQL Server and create the items necessary for the Report Catalog. You may specify a different login, either a SQL login or a Windows login, for the Report Server to use when accessing the Report Catalog after the installation is complete.

You will be asked to specify several login accounts during the Reporting Services installation. Make your choices ahead of time and track down any passwords you may need before you begin the installation process.

The Report Server Service Login Account for Windows

First, you will be asked to specify the login account used by the Report Server service. You can choose from the following types of accounts:

• The built-in account NT AUTHORITY\SYSTEM (also called the local system account)   The local system account has access to almost all resources on the local computer and may or may not have access to resources on other computers in the network.

• The built-in local service account   This account exists on Windows 2003 servers for running services. This account cannot access other servers on the network. This choice will only be available if you are installing Reporting Services on Windows 2003.

• The built-in network service account   This account exists on Windows 2003 servers for running services. This account can access other servers on the network. This choice will only be available if you are installing Reporting Services on Windows 2003.

• A domain user account   This is a regular user account that exists in the domain in which this server resides.

Microsoft recommends the local system account be used as the login for the Report Server service. Using the local system account ensures the Report Server service has all the rights it needs on the local server.

The Report Server Web Service Login Account for Windows

The second login account required by the Reporting Services installation is used by the Report Server web service. If you are installing on any Windows platform other than Windows 2003, you do not have a choice here. The Report Server web service will be required to use the login account configured for ASP.NET on this computer.

If you are installing Reporting Services on Windows 2003, you can choose between the network service account and the local system account for this login. The network service account is used by services to log on to the local machine. It has the added advantage of being able to log on to other computers in the network. When you’re installing on Windows 2003, the network service account is the default for the Report Server web service login.

The Report Server Service Login Account for the Database Server

The third login account required by the Reporting Services installation is used by the Report Server service to log in to SQL Server, and to access the Report Server and Report Server Temp DB databases. As noted earlier, this login account is used after the installation is complete. It is not used to access SQL Server during the installation process.

You have four options:

• The login account used by the Report Server service to log in to Windows

• The built-in local system account

• A domain user account

• A SQL Server login

You need to work with the database administrator of your SQL Server to determine which of these options to use.

Note 

If a SQL Server login other than sa is used, the SQL Server login must be added to the RSExecRole role in the ReportServer, ReportServerTempDB, master, and msdb databases.

Running the SQL Server Installation Program

When you run the SQL Server installation program, you need to run it under a login that is a member of the local system administrators group. In addition, your login needs to have administrator permissions in SQL Server, so you can perform the following tasks:

• Create SQL logins

• Create SQL roles

• Create databases

• Assign roles to logins