May 2, 2009 at 3:36 pm
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.
May 3, 2009 at 4:18 am
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
May 4, 2009 at 10:56 am
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.
May 6, 2009 at 8:25 am
It would appear that the book is incorrect.
I am attempting to upgrade to 2008 now so I will confirm this for myself.
May 15, 2009 at 1:04 pm
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?
May 17, 2009 at 4:26 am
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.
June 2, 2009 at 3:14 pm
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.
June 5, 2009 at 3:13 pm
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.
May 4, 2011 at 12:37 am
Hi,
Kindly suggest me for upgrading MSSQL 2005 server Reporting services to 2010 ie .Net framework 4.0,
Thanks in advance 🙂
May 9, 2011 at 3:57 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
May 9, 2011 at 4:01 am
ilyas.info786, there is no upgrade possible for the .Net level used by SQL2005 reporting services. You may be able to use custom code in a SQL 2005 report to access .Net 4.0 classes, but anything you do with .Net 4.0 has to be done using your own code.
If you want a version of Reporting Services that uses .Net 4.0 natively, then you need to upgrade your SQL environment to SQL Server Denali.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
September 10, 2011 at 7:48 pm
I have two report servers that I want to share the same database. When I set them up things seem to go fine until I add the second server to the scaled out deployment. Then it seems to kill all of the connectivity to the first server. I am not on a domain, but instead have two independent servers. Is there any way I can set up the login to work from both servers? or do is Active Directory required here?
September 12, 2011 at 2:47 am
What you want to do is only supported using Enterprise Edition and above and needs to use domain accounts.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply