Upgrading SSRS From SQL 2005 to SQL 2012

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 Server\110\Tools\Binn\”.  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.

Formatting Date Parameter In SSRS

DefaultValuesParameterMy report runs a stored procedure that accepts a date as a parameter. I want this date to have a default value of the current date–or the date when the report is run. I can do this by creating a parameter to put into my dataset. I then click on the Default Values tab and choose the Specify values option.

DefaultValuesParameterExpression

 

In the value drop down, I click the Fx button and enter the function of =Today(). This will give me a default date with no time. If I wanted the time included, I can use the =Now() function.