none
Looking for guidance: Integrated SharePoint SSRS migration and upgrade from 2007 to 2010 RRS feed

  • Question

  • 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.

    Saturday, September 25, 2010 1:03 PM

All replies

  • 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.
    Wednesday, September 29, 2010 5:39 PM
    Answerer
  • 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?

    • Edited by Eric Riehl Thursday, September 30, 2010 4:49 PM spelling
    Thursday, September 30, 2010 4:48 PM
  • 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?)
    Monday, October 4, 2010 9:01 PM
  • 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.
    Tuesday, October 5, 2010 6:14 PM
  • 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?

    Tuesday, October 5, 2010 7:39 PM
  • 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.
    Tuesday, October 5, 2010 8:40 PM
  • I followed all the steps as mentioned above:

    1. restored ReportServer and ReportServerTempDB on the new SSRS 2008 R2 instance
    2. opened Reporting Services Configuration Manager and connected to the SSRS instance
    3. Clicked on "Database"
    4. Clicked on "Change Database"
    5. Selected "Choose an existing report server database"
    6. Verified Database Server settings and had a successful "Test Connection"
    7. Selected "ReportServer" database in the Report Server Database dropdown
    8. Left "Service Credentials" as-is, clicked Next, Next
    9. Successful change database operation
    10. Clicked "Encryption Keys", "Restore"
    11. Browsed to backed up key from source SSRS instance (SQL Server 2008) and entered password, clicked OK
    12. It appears that restoration of key was successful, as there was no error message. However, there was no "completed" message either.
    13. 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?

    Wednesday, October 6, 2010 4:10 PM
  • 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
    Prash


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, October 6, 2010 7:17 PM
    Answerer
  • 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:

    1. our 50+ shared schedules did not get migrated
    2. values in the dbo.Catalog table were all messed up - NULL or erroneous GUID/ID values
    3. 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.

    Monday, October 11, 2010 4:08 PM
  • UPDATE 2 -

    Reinstalled SSRS, chose integrated mode right off the bat

    1. restored the two RS databases from production server, overwriting the existing new ones
    2. ran the Reporting Services Config Mgr, Database settings, existing instance, pointing to ReportServer database, completed successfully
    3. Restored the production encryption key successfully
    4. Checked SQL Agent and saw jobs created for each subscription
    5. Checked for Shared Schedules in Site Settings for our Reports site collection - nothing
    6. 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.

    Monday, October 11, 2010 8:41 PM
  • 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,
    Prash


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by singhguru2001 Thursday, October 28, 2010 11:53 PM
    Friday, October 15, 2010 2:57 AM
    Answerer
  • 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

    Friday, July 1, 2011 2:52 PM
  • For other people struggling through a SSRS 2005 and MOSS 2007 migration to SSRS 2008 and SharePoint 2010 the steps are as follows:

    1. Configure new SharePoint 2010 farm.
    2. Configure SSRS 2008 R2 following technet articles installing it on a SharePoint application server that is in the farm. Do NOT point to the RS database at this time, just create a new RS database.
    3. Follow the steps in the SP2010 Kerberos Guide.docx in the SSRS section exactly, do not skip anything.
    4. Migrate your SharePoint content database that holds the root of your web application first and get it working.
    5. Backup your ReportServer database and its ReportServerTempDB and its Encryption Key that you need to migrate and restore it to the Report Server using a different name than the current DB.
    6. Then modify SSRS to point to the restored DB and apply the Encryption Key through RS.

    Using the steps above in that order works even if your farm URLs are different.

    Hope this helps.


    Linda Chapman | SharePoint Consultant | My Blog: http://LindaChapman.BlogSpot.com | http://www.linkedin.com/in/LindaChapman
    Thursday, July 21, 2011 8:04 AM
  • Thanx for verifying the steps Linda.

    Additionally, to clarify when to consider SSRS catalog DB migration from one SharePoint Farm to another and when to consider SSRS data migration and why you may want to consider referring to: http://blogs.msdn.com/b/prash/archive/2011/06/11/migrating-ssrs-2008-r2-sharepoint-mode-from-one-sharepoint-farm-to-another.aspx

    HTH
    Prash


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, July 21, 2011 6:25 PM
    Answerer
  • Thanks for the information Linda and Prash. I guess I would add, based on my experience and actually being told by Microsoft support, that performing a database attach migration from MOSS/SQL Server 2008 SSRS pre-R2 to SP2010/SQL Server 2008 R2 SSRS *AND ALSO* changing the farm URL/host header is not supported.

    Microsoft support told me that the only supported upgrade path in this scenario is an in-place upgrade of the farm and the SQL Server 2008 SSRS instance.

    If something about that has changed since last fall, I'd love to hear about it!


    EDIT - sorry, logged in under another Live ID - I'm the same person as the OP.

    EDIT 2 - I'll add that when we did our initial migration of the SSRS databases to the new SP2010/SQL R2 farm, everything worked EXCEPT Shared Schedules and Subscriptions, both user-created and data-driven, were missing.

    • Edited by ERiehl Friday, July 22, 2011 8:46 PM added content
    Friday, July 22, 2011 4:40 PM
  • Eric,

    To the best of my knowledge, SSRS DB migration of an SSRS instance in SharePoint mode along with corresponding SharePoint content DBs and following the documented SSRS backup and restore steps is a supported Migration scenario.

    Having said that, there is likely to be some miscommunication / misunderstanding/ gap in the case you opened with Microsoft Support. Hence, can you please provide us with more specifics on it such as your Microsoft Support case / SR number? I would like to get more clarity and context on it, in order to get to the bottom of this.

    thanx,
    Prash


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, August 2, 2011 4:11 PM
    Answerer
  • Prash, thanks for the reply and offer to help.

     

    The case was #110101176010727 - "Unable to successfully migrate integrated SSRS into dev SharePoint 2010 farm". Interestingly, the support engineer, Gurpreet Singh, at one point spoke with you about our case.

     

    Here's our current situation - maybe there is a supported upgrade scenario now. This particular farm exists solely to support our BI team site that contains our published SSRS reports, schedules, and subscriptions via SharePoint integration with Reporting Services. All other team sites are on a different farm.

     

    Current farm and configuration:

    - SharePoint 2007 enterprise 64bit, SP2 and Oct 2009 CU applied, two WFEs:

       1. WFE server "MOSS" - central admin and team site hosting SSRS reports - this is a Windows Server 2003 R2 SP2 64bit server

       2. WFE server "MOSS3" - SQL Server 2008 SP1 CU3 backend providing integrated SSRS instance (SQL Server 2008 SP1 CU3) - this is a Windows Server 2003 R2 SP2 64bit server

    - backend for SharePoint content dbs is SQL Server 2008 SP1 with CU3, using SQL alias TCP/IP connection

     

    Goal for upgrade/migration:

    - SharePoint 2010 enterprise

    - content db backend can remain on the same SQL Server 2008 SP1 CU3 instance unless there is some very good reason to go to a 2008 R2 instance

    - SSRS integration server using a SQL Server 2008 R2 instance

    - Requirement: no loss of shared/custom schedules, subscriptions (either user-created or data-driven),

     

    Options:

    1. In place upgrade: this would require performing in-place upgrades of the Windows Server OS, MSSQL SSRS instance, and SharePoint on MOSS and MOSS3

    2. Database attach: build new farm with two servers: one WFE and one SSRS integration instance with SP 2010 bits installed

     

    #2 above is what we tried before and failed. After a lot of back-and-forth with Gurpreet trying to troubleshoot, the final word from him was that an upgrade/migration into a new SharePoint farm using a different host header was not supported for integrated SSRS via database attach method because the subscription and schedules information stored in the RS tables were hard-coded to the existing URL and not using GUIDs. Apparently if our SSRS backend was R2 we could have used RS.exe to overcome some of the limitations, but RS.exe isn’t supported for SharePoint integrated mode for Reporting Services 2008 (non-R2) and we were not in a position at that time to upgrade the SSRS instance to R2.

     

    At this time we no longer require the URL to be different – I assume this will greatly simplify our upgrade scenario.

     

    Questions for you:

    - Given our current scenario, which option for upgrade would you recommend, i.e. database attach or in-place? Which carries the least risk and least downtime?

    - Based on your answer to the above, what steps are required for the upgrade process?

    - Is there any compelling reason to go to SQL Server 2008 R2 for the SharePoint backend other than the SSRS integration? If we don’t need to do that at this time that will simplify our upgrade as well.

     

     


    Thanks very much for any help!
    Tuesday, August 2, 2011 5:11 PM
  • After discussion with CSS folks on your case 110101176010727, the right next step seems to be to have your case re-opened and take a closer look at your multi-machine scenario and ensure we are not missing anything when recommending the right migration steps / approach.

    Hence can you please re-engage with / call CSS and have your case re-opened?

    HTH
    Prash


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, August 9, 2011 5:15 PM
    Answerer