Looking for guidance: Integrated SharePoint SSRS migration and upgrade from 2007 to 2010
Our existing prod farm is MOSS 07 with an integrated SSRS instance using SQL Server 2008, both on Windows Server 2003 64bit boxes. SharePoint databases are on a different SQL Server 2008 server. I have built a new SharePoint 2010 farm on Windows Server 2008 R2 boxes, using a single SQL Server 2008 R2 Enterprise server as the backend for both SharePoint databases and Reporting Services (installed in Integrated mode). We are going to be using the database-attach method of upgrading our SharePoint farm. Is there any specific guidance out there on exactly how to migrate/upgrade Reporting Services in this scenario? I've read through some of the MSDN content (How To: Migrate a Reporting Services Installation) but nothing seems to deal specifically with doing this when in Integrated mode with SharePoint. Our business heavily relies on SSRS reports in SharePoint and also shared and custom report subscriptions via email. We do have a dev SharePoint 2010 farm in a similar configuration that I can test migration/upgrade steps. Thanks.
September 25th, 2010 4:03pm

Does referring to the following help: http://blogs.msdn.com/b/prash/archive/2010/05/26/steps-to-consider-when-upgrading-from-sharepoint-2007-to-2010-with-reporting-services-in-sharepoint-mode.aspx ? thanx, Prash This posting is provided "AS IS" with no warranties, and confers no rights.
Free Windows Admin Tool Kit Click here and download it now
September 29th, 2010 8:39pm

Prash - it helps a bit, but as Laura Rogers mentions in the comments, what are the steps when going with the database-attach method? Question - Since I already installed Reporting Services on our new SQL Server 2008 R2 server during initial install and in integrated mode, ReportServer and ReportServerTempDB already exist. When I copy over the .bak files from my existing 2007 farm SQL Server 2008 and restore, should I create new databases or overwrite the old ones? Should we NOT have done the initial install in integrated mode and then switched it over later in Reporting Services Config Manager?
September 30th, 2010 7:48pm

Prash - it helps a bit, but as Laura Rogers mentions in the comments, what are the steps when going with the database-attach method? Question - Since I already installed Reporting Services on our new SQL Server 2008 R2 server during initial install and in integrated mode, ReportServer and ReportServerTempDB already exist. When I copy over the .bak files from my existing 2007 farm SQL Server 2008 and restore, should I create new databases or overwrite the old ones? Should we NOT have done the initial install in integrated mode and then switched it over later in Reporting Services Config Manager? Any thoughts Prash? (or anyone else?)
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2010 12:01am

Assuming you have already installed RS 2008 R2 with SharePoint integrated mode, you have done a backup of the RS ReportServer and ReportServerTempDB databases from your RS 2008/SP 2007 environment, and you also took a backup of the RS encryption keys. Then, follow these steps: * Restore the RS database backup on a SQL Server database * Start the Reporting Services Configuration Tool (http://msdn.microsoft.com/en-us/library/ms159644.aspx) * Connect to the RS 2008 R2 instance configured in SharePoint integrated mode * Configure the database, and point it to the restored copies of the RS databases from your original RS 2008 SP-integrated deployment At that point the RS 2008 R2 service should attach, and automatically upgrade the database structures. Finally, don't forget to restore the encryption keys via the RS configuration tool. HTH, Robert Robert Bruckner http://blogs.msdn.com/robertbruckner This posting is provided "AS IS" with no warranties, and confers no rights.
October 5th, 2010 9:14pm

Thanks very much for the info Robert. One clarification needed - since SSRS was already installed when SQL Server was installed, "empty" ReportServer and ReportServerTempDB databases already exist. I assume when doing the restore that Overwrite must be selected in options so that the existing are replaced with the migrated databases. Correct?
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2010 10:39pm

You could overwrite them, or drop them. Robert Bruckner http://blogs.msdn.com/robertbruckner This posting is provided "AS IS" with no warranties, and confers no rights.
October 5th, 2010 11:40pm

I followed all the steps as mentioned above: restored ReportServer and ReportServerTempDB on the new SSRS 2008 R2 instance opened Reporting Services Configuration Manager and connected to the SSRS instance Clicked on "Database" Clicked on "Change Database" Selected "Choose an existing report server database" Verified Database Server settings and had a successful "Test Connection" Selected "ReportServer" database in the Report Server Database dropdown Left "Service Credentials" as-is, clicked Next, Next Successful change database operation Clicked "Encryption Keys", "Restore" Browsed to backed up key from source SSRS instance (SQL Server 2008) and entered password, clicked OK It appears that restoration of key was successful, as there was no error message. However, there was no "completed" message either. SQL Agent jobs are now listed in SSMS However - when I browse to our migrated Reports site collection in SharePoint and go to Site Settings, Manage Shared Schedules, there is nothing listed. In the source MOSS farm there are approx 50 shared schedules, and many custom schedules created by users. Question: how do we migrate shared and custom schedules successfully? Is the order of operation important here - i.e. should we attach our content DB containing the Reports site collection to the new SP2010 farm only after we've done the SSRS migration?
Free Windows Admin Tool Kit Click here and download it now
October 6th, 2010 7:10pm

Other than shared schedules are you able to view / operate on all other reporting functionality? In general, a large (if not most) portion of data stored in the Report Server catalog DB in SharePoint mode is relative to / dependent on SharePoint content/info. Master copies of SSRS Content types items like Reports (.rdl), data sources (.rsds), semantic models (.smdl) etc. are stored in the SharePoint content DB and cached on-demand by SSRS in the RS catalog. Report / data source metadata like subscriptions, schedule, stored credentials etc. are exclusively stored in the RS catalog, having said that they are associated / linked with appropriate references, that include SharePoint site GUID and / or relative paths from root site; to their respective content item like .rdl, .rsds files, which in turn are stored within doc libs in SharePoint Sites. Hence, you should consider recovering this site's content DB (and those for other sites that require reporting) alongwith the RS catalog. HTH PrashThis posting is provided "AS IS" with no warranties, and confers no rights.
October 6th, 2010 10:17pm

UPDATE - We were unsuccessful in our SSRS migration - sql agent jobs were created for each subscription, but several things were not correct in the SSRS environment: our 50+ shared schedules did not get migrated values in the dbo.Catalog table were all messed up - NULL or erroneous GUID/ID values transaction log quickly filled up with seemingly upgrade-related items: In 5 minutes’ time, this pattern ran 115 times: SELECT DISTINCT SUBSTRING([Path], 1, LEN([Path])-LEN([Name]) - 1) as Prefix, LEN([Path])-LEN([Name]) as PrefixLen FROM [Catalog] WHERE LEN([Path]) > 0 AND [Path] NOT LIKE '/{%' ORDER BY PrefixLen DESC ---- returns 30 rows SELECT [Item], [Status] FROM [UpgradeInfo] ---- returns 3 rows UPDATE [UpgradeInfo] SET [Status] = @Status WHERE [Item] = @ItemName ---- 1 row (each iteration) We are now going to uninstall our SSRS instance, reinstall, and attempt to redo the integration config and migration.
Free Windows Admin Tool Kit Click here and download it now
October 11th, 2010 7:08pm

UPDATE 2 - Reinstalled SSRS, chose integrated mode right off the bat restored the two RS databases from production server, overwriting the existing new ones ran the Reporting Services Config Mgr, Database settings, existing instance, pointing to ReportServer database, completed successfully Restored the production encryption key successfully Checked SQL Agent and saw jobs created for each subscription Checked for Shared Schedules in Site Settings for our Reports site collection - nothing Looked in dbo.Catalog and dbo.Schedule - instead of a valid path in the Path columns, we have a bunch of "/{00000000-0000-0000-0000-000000000000}/..." entries Now, this is a migration to our dev 2010 farm that is using a different load-balanced host header than the MOSS 2007 prod farm that is the source of the RS databases. Question: is this a supported upgrade scenario? Also, Prash's reply above was marked as an answer, but I don't think it really is for what I'm trying to resolve.
October 11th, 2010 11:41pm

Wrt " migration to our dev 2010 farm that is using a different load-balanced host header than the MOSS 2007 prod farm " - it looks like you are moving reporting data across 2 different SharePoint farms of different versions. SSRS DB in SharePoint mode is tightly coupled with the SharePoint Farm it is integrated with hence cross-farm SSRS DB migration is not supported. Have you considered reporting data migration via tools like rs.exe scripting utility, which is supported for SharePoint mode in R2? HTH, PrashThis posting is provided "AS IS" with no warranties, and confers no rights.
Free Windows Admin Tool Kit Click here and download it now
October 15th, 2010 5:57am

Prash, please reference a Microsoft article that say that is not supported, as I am not sure you are correct about that statement. As long as they are also migrating the content databases, I believe this is a supported model and Robert's steps should work, but sometimes you have problems with schedules and catalogs that have to be worked out. Linda Chapman | SharePoint Consultant | My Blog: http://LindaChapman.BlogSpot.com | http://www.linkedin.com/in/LindaChapman
July 1st, 2011 10:55am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics