This weekend I’ll be upgrading a copy of our 2005 instance to 2008.  Upgrading the copy will let us fall back to the old version if needed.  (update: and yes, we had to, see notes below).

I opted to create a new instance on another server – apart from our clustered SQL engine. Running IIS on a SQL server is not recommended and you can run into some memory issues with IIS and SQL 2005 reports. It’s also a security issue, serving pages from your database server.

My plan was to install some 2008 components on the server – analysis, database, integration. Then I installed 2005 reporting and created a copy of the ReportServer database on this report server.

When it came time to upgrade the 2005 instance, the upgrade would not proceed. There were no components detected by the wizard to upgrade. So I uninstalled the 2008 database, moved the database to our new 2008 DB cluster and the upgrade worked. Then I cleared everything off and reinstalled 2005 with all the services. I patched it up and once more, restored the ReportServer (and of course ReportServerTempDB) to my 2008 cluster.

A quick note – if you’re planning on hosting more than one SSRS database on a server and need to change one of the database set names, you’ll need to update all the stored procedures in the ReportServer database to point to the properly named TempDB. You can script out all the procedures in ALTER form in SSMS and then run a find/replace on ReportServerTempDB. Perhaps it should be a best practice when installing a report server not to give the database the default name – use some kind of numbering scheme, so we don’t have to resort to hacky solutions when things go bad and you may have to consolidate some apps onto one server.

Another note – I also tried to attach the 2005 report server database to a 2008 report instance – it didn’t automatically upgrade it, just got an error.

Another challenge is managing the subscription jobs when setting up a new server. I wrote a script which would find all the jobs with a category of reportserver and generate some scripts to enable, disable and delete the jobs. This let me set up the new production server and then disable all the jobs until the cutover – otherwise we’re going to get duplicate reports emailed to people.  The script is below – set your output to text and run the output.

–disable
USE msdb
SELECT ‘EXEC msdb.dbo.SP_UPDATE_JOB @job_id = ”’ + CONVERT(VARCHAR(100),j.job_id) + ”’  , @enabled = 0′ FROM dbo.sysjobs_view j INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
WHERE c.NAME = ‘Report Server’
–enable
USE msdb
SELECT ‘EXEC msdb.dbo.SP_UPDATE_JOB @job_id = ”’ + CONVERT(VARCHAR(100),j.job_id) + ”’  , @enabled = 1′ FROM dbo.sysjobs_view j INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
WHERE c.NAME = ‘Report Server’
–delete
USE msdb
SELECT ‘EXEC msdb.dbo.SP_DELETE_JOB @job_id = ”’ + CONVERT(VARCHAR(100),j.job_id) + ”’ ‘ FROM dbo.sysjobs_view j INNER JOIN dbo.syscategories c
ON j.category_id = c.category_id
WHERE c.NAME = ‘Report Server’

Important notes from this install

If you want to upgrade 2005, don’t put 2008 on the box first. I’ve had it work once and fail twice.

Stopping and starting the report service in the report configuration manager will sometimes fix things.

Make sure the ssl certificate friendly name is the same as the url.  Getting 2005 set up on server 2008 needed this.

Installing reporting services 2005 on IIS 7 takes a little additional config.  You’ll need to enable some features in IIS7 and make sure to run SSRS in a classic .net app pool.  http://support.microsoft.com/kb/934164

Be sure to assign the ssl certificate to the web site, just as you would in IIS6.  If the check mark to require SSL is grayed out in SSRS configuration manager, you probably haven’t done this.

Make sure your sever can ping the url you are planning on using and that you have IIS running if you are planning on using ssl in 2008 – it is a dependency.  If you are adding ssl bindings in the 2008 SSRS config tool and they are successful, but not showing up, then something has been hosed.  This can be fixed by using the HttpSysConfig.exe tool to delete the http.sys bindings and then deleting the entries for those URLs from the report server config file.  I had this issue during our first attempt and couldn’t figure it out during our allotted down time.

The new reporting server is on a VMWare virtual 2008 server – love it!

Phew!!  Hopefully this weekend everything works out this weekend – I’m sick of Reporting Services.