How to move a SQL Server Reporting Services installation to new server?

Question

We just built a new cluster to replace to stand alone BI servers. Its running Windows 2003 x64 and SQL 2005. One instance is already running in production and we're getting ready to move another server's data onto a named instance of this cluster.

This legacy server is SQL 2000 SP4 running SQL 2000 SSRS as well. There are around 10 databases that will be moved along with something like 1000 reports.

what is the easiest way to migrate the reports to the new hardware? no one wants to manually export and import the rdl's. The cluster will host the reportserver db and the databases. we have 2 new servers running SSRS in scale out mode that will host the reports.

Answer 1

SSRS exports a web service that allows reports to be downloaded and uploaded programatically (amongst other things). It comes with utility called rs.exe that lets you script this type of process using vb.net. All this really does is top and tail the script and then compile and execute it. IronPython or any .net language can also be used to do this type of scripting. IIRC RS2000 uses a different mechanism but it is not complex to programatically extract a rdl.

Although doing this programatically is quite simple there is a tool called Reporting Services Scripter that does this sort of thing with a GUI and might be all you need.

Note, however, that the RS2000 reports will not necessarily convert cleanly to RS2005. You may still have to do some manual conversion. This means that (like it or not) you are probably up for regression testing all 1000 of the reports, which will be very time consuming. Now would be a good time to be somewhat ruthless about requiring a business case to retain each report.

Answer 2

you could try this, not sure between 2000 and 2005 if it will work, but I have had luck with it in the past http://www.sqldbatips.com/showarticle.asp?ID=62