Upgrading SSRS From SQL 2005 to SQL 2012

Upgrading SSRS From SQL 2005 to SQL 2012

Upgrading SSRS From SQL 2005 to SQL 2012 150 150 Carlos L Chacon

These instructions should also work with upgrading SQL 2008 and SQL 2008R2 as well; however, my experience was upgrading from SQL 2005.  I was pleasantly surprised with the process; however, I ran into one little bump which I detail below.  I also upgraded from SQL Enterprise 2005 to Standard 2012.

On the New Server

SSRSConfigStep1I installed Reporting Services.  SQL Server happened to be on the same server as the reporting services service, but this is not required.  The 2005 installation was separate from the database.  I installed with no configuration and then used the Reporting Services Configuration Manager to set the Service Account, the Email Settings, set the Web Service URL, and the Report Manager URL.  I did not set the database just yet.

I restored the Reporting Service databases ReportServer and ReportServerTempDB on the new server.  While I believe you could modify the compatibility level at this point, I did not.  I should note my service account is the same AD account on both instances.  You may have to grant some permissions if you use a different account.

On the Old Server

I took a backup of the encryption keys.  I then copied that file to the new server.

The Big Finale

On the new server, I then connected to the 2012 database and Reporting Services took care of the rest.  It liked the new databases and I received no warnings or errors.

Because I had not updated the compatibility level, I stopped reporting services, updated the compatibility level and then restarted reporting services.  Up to this point, I was all good.

The last step was to restore the encryption keys on the new server using the file from the old server.  As this is something I don’t test often enough, I was glad this went without a hitch.  🙂

Tripping Over the Finish Line

I then opened up the browser and when to http://localhost/reports where I was greeted with–a big error!  The feature: “Scale-out deployment” is not supported in this edition of Reporting Services.  It appears the restoration of the encryption keys added the original server to the configuration and now Reporting Services was unhappy.  To remove the old server from the configuration, I tool the following steps.

I opened a command prompt and when to “C:Program Files (x86)Microsoft SQL Server110ToolsBinn”.  I then used the RSKeyMgmt utility with the -l flag to show the list of servers in my configuration.

RSKeyMgmt –l

I Removed the instance of the old server using the following command:

RSKeyMgmt -r GUIDofOldServer

I restarted SSRS for good measure and I was able to access all the reports on the new server.  I haven’t finished all my testing, but so far it looks good.

Imagine what’s possible with a dedicated SQL specialist on your team.

Leave a Reply

Back to top