Upgrading to Reporting Services 2008 R2

Well now SQL Server 2008 R2 has been released, now what? I want to take advantage all of cool stuff in Reporting Services 2008 R2. The obvious thing is I have to upgrade the existing report. There are couple of upgrade path supported:

  • From SSRS 2005 to SSRS 2008 R2
  • From SSRS 2008 to SSRS 2008 R2

In either path, there are couple of methods that we can use:

  1. In-place upgrade, easy but I strongly not recommend this.
  2. Side by side by attach and detach the database. This will be my main focus in this post.
  3. Obtain report source code, convert with VS2008 and deploy to new server with R2 enabled.

First method was definitely not my choice because does not provide roll back scenario. The 3rd method is nice but it means I have to reconfigure all security settings I have done. I did the second method which is pretty easy as long as we plan carefully, and retain all report permission intact.

Preparation
I had SSRS 2005 when decided to upgrade. Make sure that it already upgraded to service pack 2. The reason is SSRS 2008 will upgrade the database schema automatically as long as it’s already SP2.

Step 1
Backup ReportServer and ReportServerTempDb from old server. Actually the first one is the most important, but just take both backup just in case :).

backupdb

 

Step 2
Backup encryption key from old machine. It’s needed later when we restoring the configuration on R2 machine. The key is important to prove that we are the legitimate person to perform restoration.

backupkey

Step 3
Install SSRS 2008, this can be in the same machine with different instance or separate machine. Take note that we can actually run it as a service only and still use SQL Server 2005 as a storage for ReportServerDB. I won’t explain about the installation steps because it’s not the interest of this post. I assume that most DBAs know how to do it, otherwise do your homework in search engine :). Make sure to not configure RS during the installation because we will do it manually later.

Step 4
Restore both databases to a new machine. Actually it’s not necessary to restore it to SQL 2008 R2 storage because RS 2008 R2 supports backward compatibility. There are many cases that company just want to have a new RS R2 but still want to utilize old SQL 2005 storage, which is fine. In my case, I restore them to the same instance of SQL 2005 with SP2 because I could not get SQL 2008 one because of licensing issues. Take note that I restore them as different name for example: ReportServer2008R2 and ReportServer2008R2TempDB. This to avoid overwrite of existing RS 2005 DB. If you have separate machine you do not need to pay attention of those naming anyway.

Step 5
Configure Reporting Service 2008 R2. Open Reporting Service Configuration manager to configure virtual directory, service account, and the most important thing: connect to existing ReportServer2008R2. I won’t explain in detail about the basic configuration here, since most DBA should be familiar with it.

The one that needed attention is to connect to existing database rather than creating a new database. In my case, I connect to ReportServer2008R2 which is the one I restored earlier. And here is the magic: RS 2008 R2 will convert and upgrade all RS 2005 schema to R2 automatically. All reports and security configuration are safe inside.

r2db

r2db-2

The last thing is to restore the encryption key. We won’t able to start the service without supplying a valid key.

restoreky

Step 6
Test your server and report. Navigate to http://yourservername/reportserver to check that the service is alive. Then open http://yourservername/reports and see that all reports has been migrated to R2 :)

reportserver

reportmanager

  2 comments for “Upgrading to Reporting Services 2008 R2

  1. Nisar
    October 15, 2010 at 9:49 AM

    Thanks you very very much for this documents.
    This has really help me with the upgrading of reporting service 2005 to reporting service 2008 R2.

  2. mca
    November 8, 2010 at 4:04 PM

    test

Leave a Reply to Nisar Cancel reply