Tuesday, October 9, 2012

Migrating SSRS ReportsServices from SQL 2005 to 2008

In the process of consolidating two servers, a task emerged to move a 2005 SQL backend and its SSRS instance to a box running SQL Server 2008.

At first I considered installing SQL side by side to avoid any upgrade issues. But as it turned out, installing SSRS was quite straight forward but getting IIS to serve the virtual directory was not. After several attempts I kept getting a WMI error and errors regarding failure to load assemblies, this was due to a clash between the 32bit and new 64bit environment.

After a few hours it became obvious it would be much simpler to simply migrate the actual reports across to SSRS 2008. So to that end I performed the following steps. Fortunately it didn't turn out to be too much work thanks to Reporting Services Scripter.

But this tool was designed to migrate between two 2005 servers and a newer platform so that script didn't work straight away.
 I opened the generated ‘RS Scripter Load All Items.cmd’ and set the RS variable so it referenced the 100 hive and set the url of the local report server, as shown in the following lines:

SET REPORTSERVER=http://localhost/ReportServer
SET RS=”C:\Program Files (x86)\Microsoft SQL Server\100\Tools\binn\rs.exe”

I still had issues with data sources, I found the best way to resolve this was to reconfigure the data sources through the report manager.  Use the report manager, open each report manually and modify its datasource property and point to a new shared datasource. This allowed my reports to access the database.

No comments:

Post a Comment