Migrating SQL Server Reporting Services

Question

Migrating SQL Server Reporting Services

blakemcneill

SSC Enthusiast

Points: 162
More actions
March 17, 2019 at 9:54 pm

#371744

Comments posted to this topic are about the item Migrating SQL Server Reporting Services

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

terry.home Ten Centuries Points: 1004 More actions · Answer 1

Thank you for very useful post. I have previously had to deal with SSRS Migrations (which were migrated by others) where my biggest gripe was that they failed to migrate subscriptions to reports that had taken lots of time to setup.

Does this method also copy subscriptions or is a seperate article required to cover that?

blakemcneill SSC Enthusiast Points: 162 More actions · Answer 2

terry.home - Thursday, March 21, 2019 3:33 PM
Thank you for very useful post. I have previously had to deal with SSRS Migrations (which were migrated by others) where my biggest gripe was that they failed to migrate subscriptions to reports that had taken lots of time to setup.
Does this method also copy subscriptions or is a seperate article required to cover that?

According to the article from Microsoft (https://docs.microsoft.com/en-us/sql/reporting-services/report-server/moving-the-report-server-databases-to-another-computer-ssrs-native-mode?view=sql-server-2017), the subscriptions should be copied over.

Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.

JSB_89 Ten Centuries Points: 1081 More actions · Answer 3

@blakemcneill

Thanks for the awesome post , i followed the step by step you mentioned but i have a question ..let me explain my steps

Guide me to migrate reports from sql 2012 developer server to sql 2016 Standard edition. I have got the encryption key moved over and all the necessary setup for migration. Installed SSRS on the new server installed the report builder . When i try to backup reportserver DB from SQL 2012 version and restore on SQL 2016 the SSRS reports URL does not work. I have tried multiple ways but nothing worked . Some tables are missing from 2012 and 2016. Cannot do the backup and restore as we are missing some tables on 2012 so higher version of SQL at 2016 has them and 2012 does not so the application will not start without the tables .Tried ETL the tables too.

First can we migrate dev 2012 to std 2016 edition what are the necessary steps and precautions.

I have never done SSRS migration

This reply was modified 5 years, 2 months ago by JSB_89.

blakemcneill SSC Enthusiast Points: 162 More actions · Answer 4

I would check the SSRS logs on the new server and see what errors are showing. When you say the URL is not working, do you mean you cannot get to Reporting Services at all via the URL or do you see a Reporting Services error page when navigating to the URL?

Are you able to try to migrate from 2012 Developer to 2012 Standard? It is possible there could be difference in the server edition.

corey lawson Hall of Fame Points: 3773 More actions · Answer 5

this worked for me (ssrs 2008 to ssrs 2016). we do not have database encryption turned on. we do not use SharePoint integration either.

tl;Dr summary: backup current ssrs keys. copy the .snk file. install ssrs 2016, but don't create the databases. restore ssrs databases on the new server. configure ssrs on the new server to use the new ssrs databases. import the .snk file.

MS says the same thing, except for checking the records in the one table and deleting the extra record from the old server.

a nice bonus is so far our old ssrs 2008 reports continue to run fine.

for subscriptions, you might need to touch them if they drop Excel files. you'll need to re-select Excel. ssrs 2008 drops .xls ("excel.old" files). ssrs 2016 only drops .xlsx files. the touch sets things up for ssrs 2016.

I also scripted out the subscription jobs from msdb..sysjobs. easy, as they are the jobs with "guid" names. we don't use ssrs Enterprise, so wanted to control the jobid values to not break our data-driven reports workarounds.

you may need to check those subscription job owners and job schedules too.

wish ssis upgrades were so simple...

corey lawson Hall of Fame Points: 3773 More actions · Answer 6

oh and a shout out to the ReportingServicesTools and dba-tools Powershell modules! if you can't (won't?) do the backup-and-restore method, life is much better with both of those modules.