Chapter 21. Deploying and Configuring SSRS

SSRS can be deployed in a number of ways from a single-server deployment to a scale-out deployment in a web farm. This, in many ways, makes SSRS an ideal solution for companies that want to start out small, but be able to grow into a much larger solution.

Just like anything else, there are limits to what you can do with what edition (see Chapter 4 “Reporting Services Deployment Scenarios,” specifically the section “Key Features of SSRS by SQL Server 2005 Editions”), but it is not a technical issue—rather it is a matter of licensing and the related cost. This chapter covers the various deployment scenarios, and explains how to configure individual settings on the Report Server.

Overview of Deployment Scenarios

SSRS has two main deployment scenarios. The first is possibly the simplest—the single-server deployment. In this scenario, a single machine is responsible for hosting both major components of SSRS—the database and the Report Server.

The second major scenario is the scale-out deployment, in which the database is on one machine, possibly a clustered virtual machine, and the Report Server is on another machine or on a web farm.

Advantages/Disadvantages of the Standard Model

The standard model, or single-server deployment model, might sound simple and easy to do at first, and it is certainly the way to do it for a development workstation, or a simple trial or proof of concept. However, you should consider a couple of things when debating whether to use this model in a production environment.

Performance Impact of the Standard Model

The primary consideration for most administrators after cost is performance. Having both the database and the Report Server on the same machine might sound tempting on the financial front because SSRS is included with the SQL Server Relational Engine; however, both the relational engine and Report Server love RAM and CPU cycles. SSRS is going to use all the RAM it can get or whatever it needs (the lower of the two numbers) to render a report. Rendering reports, and especially rendering large reports, also chews up lots of CPU cycles. Adding this overhead to an older machine that is already struggling with the database server is not advisable.

Disk Space Requirements for SSRS

Anyone who has known a DBA, or who has been one, knows there is one thing all DBAs love—storage. They just can’t seem to get enough of it, even in today’s environments with large Storage Area Networks (SANs) and hundreds of spindles—the DBA always wants more. This is for good reason.

SSRS, like most databases, installs with a very small footprint. It’s almost, and possibly is, negligible. However, depending on how SSRS is used, the disk space requirements can grow pretty large. To understand how space is used inside the SSRS database, an overview of the different types of objects and how they are stored is required.

By now, it should be understood that the SSRS database holds the RDL files, data sources, models, and all metadata, such as folders and access control lists (ACLs). This might seem like a lot to store, but, in reality, this is rather small, and only in the most extreme cases should this cause issues. Session state information for SSRS is stored in the Report Server temporary database. Because only one row is generated per user session, this should not get very large, and grows at a predictable rate.

Other things stored in the database can, however, grow to be very large. Resources for reports are stored in the catalog as a binary large object (BLOB). It’s a sure bet that your friendly neighborhood DBA hates BLOBs. When a BLOB is stored initially with the report RDL, it might not be such a big deal; however, if a resource is stored as part of a report in an archive solution, this can get very large very quickly. Cached reports or temporary snapshots are stored in the Report Server temporary database as a BLOB in intermediate format. Because cached reports include raw query results, the BLOB can get pretty large. Another disk space consideration when using cached reports with parameterized reports is that a separate copy of the cached report is generated for each combination of report parameters. The bottom line is that if you are using temporary snapshots, prepare to use disk space. On the same token as temporary snapshots are report history snapshots. The only difference between them is that report history is saved inside the Report Server database and not the Report Server temporary database.

Availability Impact of Standalone Deployment

If the performance impact of the single-server deployment can be shrugged off, the availability impact of it can’t be. Having one machine be the central data store and Report Server creates a single point of failure in an enterprise environment. This makes having a backup essential to save the system from some unforeseen calamity. Not much more can be said about it. It is up to the administrator to decide how critical the functionality SSRS provides is. If it can be down for as much time as needed to restore from tape, or if SSRS is not yet important enough to be deployed in a redundant manner, then a standalone deployment should suffice.

Advantages/Disadvantages of the Scale-Out Model

The scale-out model of deployment has two main advantages over the standalone model—performance and availability. However, it has one major downside—cost. Because in the scale-out model, the database server is separate from the web server, the performance penalty of combining the database engine with the Report Server’s rendering engine gets nullified. In addition, the database can be clustered in a virtual server to provide a highly availability.

With modern SAN technologies, the database can even be replicated to a remote site. SSRS’s application server lives on a separate server. The server is simply the first node in what could become a network load balanced (NLB) cluster. The cluster gives the ability to scale out for performance/availability or both. Scaling out also helps with dispersing the workload generated by scheduled subscriptions because each machine on the cluster looks for events that trigger a subscription to process. The cluster also allows one node to be removed for upgrades/maintenance and then be placed back online when the maintenance is complete.

All of this flexibility comes at a price—literally. The only editions to support a scale-out deployment are Developer and Enterprise Editions. Microsoft does not offer support for Developer Edition, and does not license it for use in a production environment. Second, every machine in a scale-out deployment has to be licensed separately for Enterprise Edition. More than anything, the cost of a scale out is what keeps most shops from adopting it.

Requirements for a Standard Deployment

In a standard deployment, the web server/application server and the database server are installed on the same machine. For this reason, it is important that the minimum hardware requirements be met or exceeded. It is also helpful to have the NETBIOS name or IP address of the Simple Mail Transfer Protocol (SMTP) server handy as well as the service account used to execute the reports in unattended mode and the credentials with which to log in to the database.

After collecting all the necessary information, you just need to run setup and configure the Report Server. Sounds easy, doesn’t it? While running, the installation program offers two main options. The first option is the default installation. This is the option used for running the standard deployment. This option sets up the database server and the Report Server on the same machine. The second option is called the Files Only option. This option is used primarily in scale-out deployments. For the brave or simply curious, this option can be used to set up SSRS locally; however, the administrator must run the Report Services Configuration Tool after the install completes and configure the options herself.

Requirements for a Scale-Out Deployment

As you have seen earlier in this chapter, SSRS can be deployed in a scale-out fashion on a web farm. Each machine in the web farm runs both the correct web server or website to host the report server and the SQL Server Reporting Services. As anyone who has managed a web farm knows, in theory any machine on the farm should be easily replaceable with another in the same configuration, and state cannot be stored on any box on the farm. SSRS accomplishes this task by using data source configuration information and reports inside the Report Server database. The application servers just need to register themselves with the database server. This might sound simple, but it is not trivial. Luckily, SSRS 2005 has given administrators much better tools to aid in this configuration process.

Overview of Report Server Initialization

Because SSRS uses potentially sensitive information, it is important to secure it appropriately. This is compounded by the fact that in a scale-out situation multiple Report Servers need to encrypt and unencrypt the data stored in the database. To understand how SSRS accomplishes this, you need a bit of knowledge about encryption and encryption techniques.

In general, there are two kinds of encryption—symmetric and asymmetric. Symmetric is very fast because it uses only one possible key to encrypt and unencrypt the data. However, this form of encryption has its drawbacks. How can you share information that has been encrypted with the symmetric key without compromising the key? The answer is to use asymmetric encryption. Asymmetric encryption uses a combination of keys, one public and one private. The public key can be shared with another host and can be used to decrypt messages encrypted with the private key. The same can be said for the private key. Asymmetric encryption is relatively very slow, so it should not be used to encrypt/unencrypt frequently.

SSRS uses both types of encryption in a simple, yet intelligent way. For every Report Server database, SSRS generates a unique symmetric key that can then be used to encrypt the data. At this point, every Report Server that needs access to the data must publish its public asymmetric key along with its unique installation ID and client ID to the Report Server database. The Report Server database then uses the public asymmetric key to encrypt the internal symmetric key and share it with the client. After being encrypted with the client’s public asymmetric key, the symmetric key cannot be decrypted by anyone else without the private key. Administrators can actually watch this process unfold by watching the changes in the Keys table during the activation process. The process of exchanging public keys and symmetric keys is called activation.

Activation is a two-phase process. The first phase is the Announce Self phase, and the second phase is the Activated phase. The Announce Self phase covers the reading of the keys from the Keys tables and, if needed, the writing of the client’s public key to the Keys table. The Activated phase is the time the Report Server gets the symmetric key in encrypted form.

The process of adding and removing machines in the scale-out deployment model is simply the process of running activation over again. The same is true for taking an SSRS installation and pointing it to a different database.

Steps to Set Up SSRS in a Scale-Out Configuration

SSRS requires an existing NLB or server cluster. It does not create or set one up for you. The basic steps to set up a scale-out configuration include installing SSRS on the node, configuring the node, and finishing the configuration by editing the configuration files.

The following steps start the install of SSRS on a node of a new server cluster:

1. Run SSRS setup on the node that will become part of the cluster. The critical step here is to do a file only installation. When using the SQL Server Installation Wizard, this option is labeled Install But Do Not Configure the Server.

2. Run the SQL Server Surface Area Configuration Tool to configure the Report Server. The Report Server service must be up and running for this to work because it is what registers the installation with the Report Server database.

3. Open the Reporting Services Configuration Manager.

4. Configure the virtual directories for use by the Report Server and Report Manager. In most scale-out deployments, this should be identical on all machines.

5. Continue to the Database Setup page and enter or select the name of the SQL Server database instance to connect to. Click Connect to connect to the instance and retrieve the list of running databases. To create a new database, click New. The new database is created with the ID of whoever is running the configuration tool.

6. Enter the credentials for SSRS to use to log in to the database.

7. 7. Click Apply for SSRS to connect to the database and complete the activation process.

8. Click the Encryption Keys tab and make a backup of the SymMetric key.

9. Click the Initialization tab, and make sure the Initialized check box is checked. If it is not, click the Initialize button.

10. Continue to enter the email settings and unattended execution account. At this point, you can verify that the Report Server is operational by requesting a page from the Report Server virtual directory (/ReportServer). Repeat steps 1-10 for every server in a web farm.

11. Open the rsreportsserver.config file and change the <UrlRoot> tag to use the cluster name instead of the machine’s physical name.

12. Open the rswebapplication.config file and change the <ReportServerUrl> tag to use the cluster name instead of the machine’s physical name.

To add nodes to an existing cluster, follow these steps:

1. Run SSRS setup on the node that will become part of the cluster. The critical step here is to do a file only installation. When using the SQL Server Installation Wizard, this option is labeled Install But Do Not Configure the Server.

2. Run the SQL Server Surface Area Configuration Tool to configure the Report Server. The Report Server service must be up and running for this to work because it is what registers the installation with the Report Server database.

3. Open the Reporting Services Configuration Manager.

4. Configure the virtual directories for use by the Report Server and Report Manager. In most scale-out deployments, this should be identical on all machines.

5. Continue to the Database Setup page and enter the name of the SQL Server database instance to connect to. Click Connect to connect to the instance and retrieve the list of running databases. To connect to an existing database, click Upgrade. All nodes on the same scale-out deployment must point to the same database.

6. Enter the credentials for SSRS to use to log in to the database.

7. On the Initialization tab, the entry for the new node should exist, but the Initialized check box should not be checked.

8. Launch the Report Server Configuration Tool on a machine that has already been initialized.

9. On the Initialization tab, select the machine that is to be added to the scale-out configuration and select Join. At this point, the Initialized check box should appear for both machines in the configuration.

10. 10. Open the rswebapplication.config and the rsreportserver.config files and change the <ReportServerUrl> and the <UrlRoot> tags, respectively, to state the cluster name instead of the network name.

To remove a server, simply uninitialize it by opening the Reporting Services Configuration Tool from any node on the cluster, selecting the node to be removed, and clicking the Remove button. To move a node, remove the node from its existing setup and follow the steps to add it to the new cluster.

Configuring SSRS

It is rare that administrators ever have to install something without having to change it later. Luckily, SSRS 2005 comes with a slick, new configuration tool to help you do just that.

As you have already seen, the Reporting Services Configuration Manager puts a pretty face on most of the options you would want to configure. To be sure, there are still other options out there; however, they require manual editing of the configuration files.

Configurable Components

Each tab in the Reporting Services Configuration Manager shown in Figure 21.1 is for a different component of SSRS. Modifying the component’s different options is very straightforward, and the built-in Help does an excellent job of walking you through the configuration.

Figure 21.1. Reporting Services Configuration Manager.

Table 21.1 lists some of the configurable components.

Table 21.1. Report Items and Effects of Security

For most shops, the defaults should more than suffice. If any component’s options need to change, the value should come from the administrator in charge of the environment.

Configuration Files

All configuration options for SSRS are stored in XML-based configuration files. Although the sensitive data, such as database connection strings, is encrypted, most of the options can be configured with a simple text editor such as Notepad. If a value is encrypted, it must be edited via a tool such as rsconfig.exe utility, which is a command-line tool to edit the encrypted database information or rskeymgmt.exe, which is an encryption key management tool.

As with editing any text-based configuration file, there is some inherent risk. Because the files are XML, all of the configurable elements are going to be in either an element or an attribute. A number of things can happen when editing one or more of these files. If the file is changed successfully, the configuration change is applied with little to no effect on end users.

Things do get interesting if a mistake happens. Should the XML become malformed, the server ignores the bad file until the server is restarted. At that point, the server fails to start. If the configuration is a valid XML document and the configuration value is simply invalid, the server uses the default value if one exists. Otherwise, the server fails to start.

Table 21.2 summarizes the configuration files.

Table 21.2. Summary of the Configuration Files

Key Management

By now, you have seen what it takes to activate the Report Server(s). It is not trivial, and it does not take long for very important encrypted data to start filling the Report Server’s catalog.

This poses the question of what to do about the encryption keys. The symmetric key is never exposed in an unencrypted fashion, and the public keys are tied to the account running the Report Server Windows service and web service. The database knows nothing. Should a hardware failure happen, it is relatively easy to restore the database and hook a new Report Server to it. However, the new machine will not be able to decrypt the data stored in the catalog. What’s one to do?

Thankfully, the developers who wrote SSRS gave you a tool called rskeymgmt.exe. The Reporting Services Configuration Manager (shown in Figure 21.2) also contains similar functionality. Both tools allow you to back up and restore the symmetric key. Effectively, these two tools allow end users to perform the following tasks:

• Perform a backup of the symmetric key. This provides safeguards against disaster recovery, and provides a helpful tool to perform a server migration.

• Restore a symmetric key from another Report Server instance over to the current installation.

• Change the symmetric key and reencrypt all data in a Report Server database. This is helpful should the key ever get compromised, a key individual leave the organization, or as a proactive practice to safeguard the data.

Figure 21.2. Encryption tab in the Reporting Services Configuration Manager.

Backing Up the Symmetric Key

Taking a backup of the symmetric key should be performed immediately after installing SSRS. Because there is only one symmetric key for every Report Server database, the backup only needs to be performed once unless the key is changed or the backup is lost. Always have a backup of the symmetric key handy. The following list describes some situations in which the backup will become useful:

• Changing the service account under which the Report Server Windows service runs, or changing its password

• Renaming the machine or changing the instance name of the SQL Server relational engine that hosts the Report Server database

• Migrating or changing the Report Server database of an existing installation

• Restoring the Report Server installation due to hardware failure

To back up the symmetric key, you must have a password to give to the utility. The password is used as an encryption key to encrypt the symmetric key before saving it. This ensures that the symmetric key is never seen unencrypted. Don’t forget the password or let it be compromised.

You can complete the following steps to back up the symmetric key with the Reporting Service Configuration Manager:

1. Open the Reporting Services Configuration Manager and click on the Encryption Keys tab.

2. Click Backup.

3. Enter a strong password, and enter the location in which to store the resulting file.

4. Click OK.

In a similar fashion, this can be done from the command line with the rskeymgmt.exe utility:

rskeymgmt -e -f rsdbkey.snk -p<password>

Restoring the Symmetric Key

Should disaster ever strike, and the key needs to be restored, you must have both the files with the key and the password for that file. Should the restored backup not contain a valid symmetric key for the Report Server database, the Report Server will not be able to unencrypt the data. In the absolute worst case, an administrator might have to delete all the encrypted data, and then reenter it.

To restore the symmetric key with the Reporting Service Configuration Manager:

1. Open the Reporting Services Configuration Manager and click on the Encryption Keys tab.

2. Click Restore.

3. Select the location of the file (in most cases this is the *.snk file), which contains the symmetric key. Type the password that unlocks the file.

4. Click OK.

To do the same thing from the command line, run the following command:

rskeymgmt -a -f rsdbkey.snk -p<

Changing the Symmetric Key

Changing the symmetric key involves generating a new key, and reencrypting all encrypted data that was stored using the old key. It is certainly not something that needs to happen every day, although it is a good idea to do it from time to time as a best practice. Think of it as changing the administrator or sa password. The processes should also be done when the key has been compromised.

To change the symmetric key, the web service for the SSRS needs to be disabled. In a scale-out situation, all machines running the web service must be disabled. When the key has been successfully changed, the administrator can reenable the web service on the Report Server(s). To disable the web access to SSRS, use the SQL Server Surface Area Configuration Tool:

1. Open the Surface Area Configuration Tool and select Surface Area Configuration for Features.

2. Select Reporting Services from the navigation menu on the left.

3. Select Web Service and HTTP Access.

4. Uncheck the Enable Web Service and HTTP Access check box.

5. Click Apply.

Remember to do this for every machine in a scale-out situation. After the web service has been disabled, changing the symmetric encryption keys is fairly straightforward. To change the symmetric key with the Reporting Service Configuration Manager, complete the following steps:

1. Open the Reporting Services Configuration Manager and click the Encryption Keys tab.

2. Click Change.

3. Click OK

to acknowledge the computer(s), instance number, and installation ID.

The command to do this via the command line is also fairly simple:

rskeymgmt -s

Before changing the encryption key for a Report Server installation via the command line, you need to stop the web service and HTTP access. After the change is complete, you need to restart the windows service and reenable the web service. For a scale-out deployment, this needs to be done on all of the Report Servers. After the key has been updated, the administrator can reenable web access.

Deleting the Symmetric Key

By deleting the symmetric key, you give up any hope of ever retrieving the encrypted data. All of it will have to be reentered from the ground up. In a scale-out situation, all of the Report Servers deployed will have to be reinitialized. Proceed with extreme caution. After the keys have been deleted, the following items will definitely be affected:

• Data source connection strings

• Credentials stored in the catalog

• Reports that are based on Report Builder models (the models use shared data sources)

• Subscriptions

To delete the symmetric key with the Reporting Service Configuration Manager, complete the following steps:

1. Open the Reporting Services Configuration Manager and click the Encryption Keys tab.

2. Click Delete.

Click. OK.

The command to do this via the command line is also deceptively simple:

rskeymgmt -d

After deleting the encryption keys, you need to restart the Report Server Windows service. For a scale-out deployment, you need to restart the Report Server Windows service on all Report Server instances.

Summary

Deploying SSRS in its simplest form is very straightforward. A single server, although simple, cheap, and easy to maintain, does have its penalty when it comes to performance and reliability. A scale-out deployment that might make more sense from a reliability and performance perspective will cost in terms of licensing. It is up to the administrator to decide at what point the buck stops and what is more important.

Either way, after installing, various configuration options are possible. The Report Server Configuration Manager is the Swiss army knife for configuring SSRS. Its graphical interface makes it a cinch to change configuration options in SSRS.

After SSRS is up and running, back up the symmetric key and keep it in a safe place. From time to time, it might be wise to rotate it. Should you lose it, the only option is to delete the keys and reenter all encrypted data.