SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Upgrading Reporting Services 2005 to 2008


Upgrading Reporting Services 2005 to 2008

Author
Message
Andy Hogg
Andy Hogg
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 1016
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.
denglishbi
denglishbi
Right there with Babe
Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)Right there with Babe (789 reputation)

Group: General Forum Members
Points: 789 Visits: 1362
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
Andy Hogg
Andy Hogg
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 1016
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.
Andy Hogg
Andy Hogg
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 1016
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.
Nikola paunovski
Nikola paunovski
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
Andy Hogg
Andy Hogg
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 1016
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.
darkos
darkos
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 56
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.
Nikola paunovski
Nikola paunovski
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.


ilyas.info786
ilyas.info786
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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 :-)
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5934 Visits: 3866
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search