Tag Archive for: Reporting Services

Sometimes, you may need to swap the RS catalog database. For example, you may need to clone the catalog database from a production machine to troubleshoot an issue. One way to do so is to replicate the report items (report definitions, data sources, images, etc.). Another, and perhaps faster, way is to copy the report catalog database and redirect the Report Server temporarily to the new catalog by using the handy Reporting Services Configuration Manager (new with RS 2005). The following steps worked for me to redirect my local SSRS instance (SP1+hotfix) to a different catalog (from SSRS 2005 RTM).

1. Use the SQL Server Management Studio to create a new database, e.g. ReportServerTest.
2. Create a new database ReportServerTestTemp to serve as the Report Server Temp database.
3. Back up the production database you need to troubleshoot.
4. Restore the production database to ReportServerTest. Make sure to rename the database files by flipping to the Options tab (e.g. rename the files to ReportServerTempDB.mdf and ReportServerTempDB_log.mdf)
5. Back up the production instance of the ReportServerTemp database and restore it to ReportServerTestTemp (again rename the database files).

Note: As its name suggests, the ReportServerTemp database is just a temporary database used by the Report Server for session caching. Since you don't need its data, you can restore (copy) it from any RS temp database.

6. Open the Reporting Services Configuration Manager (Microsoft SQL Server 2005 program group -> Configuration Tools -> Reporting Services Configuration) and switch to the Database Setup tab (see attached screenshot). 
7. Enter the name of the new Report Server database (ReportServerTest) and hit Apply. If your SSRS instance is newer version of SSRS 2005 (e.g. SP1), you will be prompted to upgrade. If all is well and you don't see error and warning messages, the Report Server will be re-configured to use the new database. If at this point you try to connect to the Report Server (e.g. /reportserver/) you will get the rsReportServerNotActivated error. The reason for this is that the Report Server cannot decrypt the content of the new catalog with its encryption key.
8. In the Reporting Services Configuration Manager, flip to the Initialization tab. You should have two report two rows (see attached file) in the grid corresponding to your local computer name (initialized) and the computer which hosted the report catalog you restored. Let's assume you don't have connectivity to the remote machine and you cannot connect to it.
9. Open the command prompt and execute:

     "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\RsKeyMgmt.exe" -d

This will effectively remove any encrypted content, including connection and subscription credentials.

Note: If you have a backup of the encryption keys from the source server, to prevent losing the encrypted content you can restore the encryption keys on the target server by executing the following commands in the order shown: rskeymgmt –e, rskeymgmt –r, and then rskeymgmt –a.   

10. At this point the Report Server should be initialized. You may need to go back to the Initialization tab and click Initialize (you should have only row with your computer name).
11. You can now open the Report Manager and change the connection strings for the data sources .
12. Once you done testing, go back to Reporting Services Configuration Manager and flip the catalog database to the original ReportServer database.

One of the most exciting (but not well known and documented) extensibility areas of SSRS 2005 is custom report items (CRI). As its name suggests, custom report items allow you to implement your own reporting controls when the standard SSRS report items (textbox, chart, table, matrix, etc.) are not enough. For example, the attached screenshot shows a report that uses a CRI I wrote to show a KPI value graphically.

If you have experience in developing Windows Forms .NET controls, you will feel immediately at home with CRI. In fact, I dare say that you can take the GDI+ source code of any Windows Forms.NET control and convert it easily to an SSRS CRI. As you can imagine, it won’t be long before third-party vendors come up with all sorts of cool SSRS widgets.

If you want to learn more about CRI, check out the PolygonsCRI sample which Chris Hays wrote. It is included with the latest refresh of the SQL Server 2005 samples. I have an article in the works for MSDN Magazine that explains the implementation details of a progress tracker CRI.. I will post an update here when the article is available (most likely July-August issue of MSDN Magazine).

Euan Garden (Product Unit Manager for SQL Server) tells us that SQL Server Express Advanced Services (which includes Reporting Services Express) has reached a beta stage. I emphasized the importance of choosing the right RS edition in one of my previous blogs.