Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Upgrading Reporting Services 2005 to 2008 Expand / Collapse
Author
Message
Posted Saturday, May 2, 2009 3:36 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:34 AM
Points: 494, Visits: 857
I wonder, does anyone have any practical experience in upgrading a Reporting Services 2005 installation to 2008?

I've read various documents on this, and am about to try a few practice runs on a VM, but I'd appreciate any insights from anyone who has already been through it.

In particular I'm interested in:-

1) Best route to take? In place upgrade versus a fresh 2008 installation and migrating the existing reportserver database over. How does 2008 RS react to an existing IIS installation? Is it smart enough to pull the settings e.g. certificate etc.

2) What percentage of reports functioned and formatted correctly post upgrade?

3) Any particular caveats or recommendations, particularly with regard to a scaled out deployment (RS and DB on separate servers), with load balancing (2 RS servers accessed from a virtual URL on a load balancing switch.

Grateful for any pain-saving insights.
Post #708977
Posted Sunday, May 3, 2009 4:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
I would go with a side-by-side install if you are able to do this. You can backup and restore your databases to the new server to reference for your catalog and then you will also need to backup and apply your encryption key. Only major issue I faced was with the conversion of the Dundas items that were being used in SSRS 2005. The custom .NET code that was added to the Dundas items does not convert over and the custom colors for charts did not either since Dundas uses a RGB type value reference.

Other than that it was a very smooth process. You can still publish out your SSRS 2005 reports to 2008 server to if needed.

I would recommend taking a look at this information:

SQL Server 2008 Upgrade Technical Reference Guide - This 490-page document covers the essential phases and steps to upgrade existing instances of SQL Server 2000 and 2005 to SQL Server 2008 by using best practices. These include preparation tasks, upgrade tasks, and post-upgrade tasks. It is intended to be a supplement to SQL Server 2008 Books Online.


----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Post #709030
Posted Monday, May 4, 2009 10:56 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:34 AM
Points: 494, Visits: 857
Thanks for the tip Dan.


I've just finished reading Michael Otey's book "Microsoft SQL Server 2008 new Features", and on page 58 he states:-

"Some of the clustering-specific improvements in SQL Server 2008 includes support for an unattended cluster setup. In addition, all of the different services within SQL Server 2008 are fully cluster aware including the

*Relational database engine.
*Analysis Services.
*Integration Services.
*Reporting Services.
*SQL Server Agent.
*Full Text Search.
*Service Broker."


Is this accurate? If so it is the first I've heard of this with regard to SSIS & SSRS.

If SSRS is now cluster aware then I will not need to worry about balancing my two SSRS servers with a load balancing switch.

Post #709518
Posted Wednesday, May 6, 2009 8:25 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:34 AM
Points: 494, Visits: 857
It would appear that the book is incorrect.

http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/fa723b21-ef90-4109-bb53-b48cc000a821/

I am attempting to upgrade to 2008 now so I will confirm this for myself.
Post #711127
Posted Friday, May 15, 2009 1:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 7:29 AM
Points: 2, Visits: 12
Hi Andy
Did you manage to upgrade the RS2005 DB to RS2008?
I'm in the process of doing the same
The only thing is that mine is in Share-point Integrated Mode
I copied and attached the RS2005 Database (Integrated mode) in the SQL 2008 database instance
I can connect to it successfully from the RS2008 instance but it is showing that the instance and database is in Native mode,
anyone has idea why is this so?
Post #718211
Posted Sunday, May 17, 2009 4:26 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:34 AM
Points: 494, Visits: 857
Hi Nikola,

Yes actually the DB upgrade has gone very smoothly and it appears at first glance that all the reports are working.

I'm a little concerned with the speed of the thing, but then I am running this as a proof of concept on a small VM, so things may speed up when it comes time to actually perform the upgrade on what are physical servers.

My SSRS is in Native mode, and I haven't tried to run in integrated mode so I haven't hit that particular problem I am afraid.

If your SSRS thinks that it is running in Native mode, do you perhaps have the option to just convert it to integrated mode (again) ?

Let us know how you get on.
Post #718621
Posted Tuesday, June 2, 2009 3:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 1:06 PM
Points: 193, Visits: 54
From my previous experiences of installation of RS 2005, you have to recreate the database for RS to work in Sharepoint Integrated mode.

I am in the process of upgrading my RS from 2005 to 2008, which i will begin to work on tomorrow.

The only reason i can think of for the DB to to flip to Native mode in Nikolas case is the copy process, but that shouldn't change the schema, so, it baffles me.

I will update you all, how the process went.
Post #727753
Posted Friday, June 5, 2009 3:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 7:29 AM
Points: 2, Visits: 12
After thinking more about this and retrying the same I managed to successfully upgrade from 2003 to 2008

I had to do some manual steps along the way but pretty much everything was smooth

Since I had the servers installed side by side on different machines
I stopped the sql 2003 and copied over the database and temp database to the new server
I attached the new database (renaming it... I had make and change a trigger set on the Schedules table to reflect this change)
I connected to the database from the RS Integrated instance
Again it showed that it is in integrated mode
I stopped and started the instance as explained in the article bellow and the Database got upgraded and switched to integrated mode

Now both the old server and the new server showed in the Scale-out Deployment section of teh RS Configuration.

I tried removing the old server here without luck...
I also could not backup the keys

I stoped the service and in SQL manager found teh table that stores this settings and simply removed it

use ReportServerIntegrated
delete from dbo.Keys where MachineName = 'OldServerName'

After this i started the instance and managed to backup the keys

I also had to change the links to the reports as I had to change the domain since I was doing this whole thing side by side and did not want downtime for the users

so i did thos for each link

use ReportServerIntegrated

update dbo.Schedule
set [Path] ='http://neurl/reports'
where [Path] ='http://oldurl/reports'

here I realized there was a trigger pointing to the temporary database which now had a different name
after updating the trigger with the new temp DB name I was ok.

after this I had to redeploy each report, as it could not find it... but that was not a big deal since I had to anyways...


teh following article helped clear out things
http://technet.microsoft.com/en-us/library/ms403392.aspx


especially the part

To upgrade the database manually
If Setup did not upgrade the report server database automatically, your report server installation is only partially upgraded. To complete the upgrade, you must upgrade the report server database manually by running the Reporting Services Configuration tool after Setup is finished.

1.Run the Reporting Services Configuration tool and connect to the report server that you just upgraded. For more information about how to start the tool and connect to a server, see How to: Start Reporting Services Configuration.

2.Open the Database Setup page, and then click Connect to connect to the SQL Server instance that hosts the report server database.

3.Select the report server database that you want to upgrade.

4.Click Apply to update the database schema.



Post #730065
Posted Wednesday, May 4, 2011 12:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 12:34 AM
Points: 1, Visits: 0
Hi,
Kindly suggest me for upgrading MSSQL 2005 server Reporting services to 2010 ie .Net framework 4.0,
Thanks in advance
Post #1102886
Posted Monday, May 9, 2011 3:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:14 AM
Points: 2,868, Visits: 3,215
I would definitely recommend installing to a new server.

It is safest to assume that any new SQL version has changes to the RS Catalog database, so do not plan on restoring a backup of your old RS DBs to your new server (also don't to a detatch/attach of the old DB). Instead, get hold of RSScripter from sqldbatips and use this to migrate all your RS objects to the new database.

When you have migrated all your objects, test that everything works as expected. You can then swap your new server into live and take the old server out of live use.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1105299
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse