August 30, 2010 at 9:17 am
To All,
We currently have a 2005 SSRS instance that we would like to "upgrade" to a 2008 instance. I use the quotes becuase these are two completely seperate boxes. so to say we are going to upgrade is something of a mis conception it seems. While I can find a lot of very concise documentation for doing an in place upgrade It seems that very inconsistant information exists on migrating report from one server to the other. so here are my questions.
I have seen posted several places and even in this forumn that you can simply resotre the ReportServerDB and the keys and that basically all will work fine. there are some additional steps but that it is a very simple process. But I have also seen several articles saying this is a bad idea. this seems like it would be a bad idea since there are differences between the 2005 schema and the 2008 schema for the ReportServerDB. I have seen several people suggest using the RSSScripter tool to migrate but when I have tried that I get a message saying a script created to 2005 can only be run on 2005. so I have had no luck there. I have not tried actually restoring the DB but based on my googling this seems like it could create many problems. So has anyone else done a migration from one physical machine to another? If so what has your expierence been? did you do a restore or some other method?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
August 30, 2010 at 9:35 am
Dan,
The SQL 2008 Upgrade Technical Reference Guide covers this in section 14.3.2.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 9:55 am
yeah I had read that previously but I was hoping for something more helpful than there advice to manually move everything on the server.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
August 30, 2010 at 10:26 am
I have a script (at home - can get it tonight if you want it) that will script out all of the reports/data sources / data models / folders off of one reporting server, and you can then use the resultant script to add all these objects to the new server. Let me know if you want it, and I'll hunt it down and post it.
It doesn't handle subscriptions. I wonder if those can be scripted out from the ReportServer db tables, and then inserted into the db on the new server?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 10:28 am
Wayne,
That would be fantastic!
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
August 30, 2010 at 11:02 am
Dan,
These links might also be helpful:
How to: Migrate a Reporting Services Installation
Moving the Report Server Databases to Another Computer
Seems like you can just restore your databases on the new server.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2010 at 1:47 pm
Wayne,
Thanks for your help and the information.
So for anyone reading this here is the procedure I settled on which I was able to successfully use on my test server. Some of these steps are airring on the side of retentive but that is a character flaw you have to endure.
A. In this scenario doing a restore seemed bothersome since I really had no idea if this would work. true I could create a backup but again I am a bit over cautious so I created to new databases. ReportServer2008 and ReportServer2008TempDB.
B. I restored the 2005 databases to these two database files. Now I was concerned that there where 5 tables that did not exist in the 2005 DB that existed in the 2008 version so I scripted them and created them in the 2008 database after restore. these tables are ChunkSegmentMapping,ExecutionLogStorage,Segment,SegmentChunk, and SubscriptionsBeingDeleted.
In a repeated test however I discovered that these tables will get recreated automatically.
C.In SQL Server Reporting Services Configuration mnager change the database by clicking the Database tab and selecting change database. select the two database created earlier.
D.Restore the Encryption Key
E. In your restored database there is a record in the Key table that refers to the old server by name or install date. Remove any entries that refer to the old server.
After completing these steps I was able to get my test system up and running as a mirror image of my production report server.
Hope this helps anyone with similiar concerns or questions.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
August 31, 2010 at 9:28 am
Try the URL given at the end.
When you try to transfer first time, it will say file(s) absent.
The rss and rdl , cmd, xml files etc will be created in the output directory. You would need to copy them over to c:\ and re run the transfer.
Also, give the SS2005 url as http://Sourceserver/ReportServer etc.
For the SS2008 give http://DestServer/ReportServer etc; From the Source Server first try the SS2008 url in http and connect atleast once giving (preferably) windows authentication and checking the check box to 'Remember' it.
This tool worked like a charm for me. But this does not transfer the DataSources. Which you would need to do @ SS2008 Report Service.
Just create the (new) SS2008 Report databases and transfer.
http://sqldbatips.com/showarticle.asp?ID=62
HTH
gk
August 31, 2010 at 5:31 pm
Dan.Humphries (8/30/2010)
Wayne,That would be fantastic!
Well, I found out that my script does not script out the existing code. As I was looking at it, I remember that I would have to script out each report manually, and paste it into this script.
I'm interested enough that I think I will attempt to figure out how to script out this information.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply