Upgrading Reporting Services from 2005 to 2008

I was given the task of upgrading our 2005 SSRS to 2008 SSRS. This was not an in-place upgrade; we moved SSRS to a Virtual Machine with 2008 OS-64 bit. We have been using CNAMEs for a while, so switching servers is not a problem. A CNAME is basically an alias for a server at the network level. Our CNAME for our SSRS server is DWReporting, so everyone connects to http://DWReporting/Reports. Just a note on CNAMEs: I've had trouble connecting to Replication in SQL Server Management Studio 2005 so I tend not to use it there.

Environment
Our Network/Operations Team showed me how to backup and restore the VM via snapshots. This worked out great as I was fumbling along setting up the VM and SQL Serer 2008. I do not suggest that you use this method to backup your SQL Server Databases.

The one thing that was a gotcha trying to install SQL Server 2008 was that the .Net Framework could not be upgraded. I had to add that role to the server. There is no IIS in SSRS 2008 (which is great, by the way) so that does not need to be enabled/loaded. I installed just SQL Server (64 bit) and SSRS. We use SharePoint and will eventually get to integration with SSRS, but not now.

Upgrading SSRS to 2008 and Testing

I took a backup of the 2005 SSRS and the encryption key. I restored the databases and the encryption key on the 2008 version.  Once SSRS was working, I created a backup job for all the databases and backed up the key to the backup directory. To eliminate confusion, I did not enter the SMTP information for emailing reports since our production was still running and I did not want reports to be delivered from both servers.

I ran into issues with both users who had IE8 and users who did not have IE8.  It was the annoying authentication prompt for a user name and password. I had all non-IE8 users upgrade to IE8, and the users on IE8 had to reset the setting to default (Tools\Internet Options\Advanced Reset button - make sure that close IE  afterward). I was surprise how long (days) it took us (the Net/Ops guys and  myself) to find the solution to this.

We had Dundas Gauges and charts so I went through and viewed all reports and modified the reports that needed it. We keep our reporting project in VSS.  I kept every thing checked in and made changes to local copy. I made sure all reports were checked in before we started. We have a group of four that can check out and work on reports, so I warned everyone early in the week to check reports in.

Moving Day- Go live
I did this during our maintenance window on a Sunday morning:

• I restored the latest 2005 SSRS backups. 
• Disabled IIS on the 2005 server and made the databases read only, just in case. 
• Contacted the Net/Ops guy on call to change the CNAME (I did coordinate with him before hand). 
• I updated those reports that I modified
• Added  SMTP information for the emailing reporting

The Day After

• I had to deal with the IE8 issue (or I guess you would call it the non IE8 issue). I underestimated the number of users that would have problems.
• I checked out our Reporting Project from VSS in BIDs 2008 and upgraded it 2008
• Moved in modified reports that I updated and checked them in

 Report Manager seems faster moving from folder to folder. I believe this is because there is no IIS. Other than the IE upgrade issue, we really have had no other problems.

Update

Users were getting a repeated NT login prompts and the solution was to reset the IE internet options.

We also noticed that when creating or editing a subscription if we edited the schedule the TO Email line was wiped out and left blank.

We recently upgraded to SQL Server 2008 SP2, this resolved email in the subscriptions and seems to elevate the NT login prompt as well