SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Migrate SSRS 2017 to Power BI Report Server

UPDATE: The blog post doesn’t make this exactly clear, but fiddling around with the report database to get the upgrade working isn’t supported by Microsoft. However, someone from the SSRS team told me the March 2018 release of Power BI Report Server has fixed some bugs, so you should try to upgrade the normal way.

Recently I had the pleasure of migrating a SSRS 2017 instance to an instance of Power BI Report Server. I was really looking forward to it, since it means we would have everything in one single portal: paginated reports, Excel workbooks and of course Power BI Desktop reports. We had a new virtual machine installed with a brand new installation of the PBI RS server, October 2017 edition. I followed the simple steps of the official documentation: Migrate a report server installation. Seems easy right?

Alas, it was not the case. When launching the portal, we were greeted with an error message saying the service was unavailable (it was though) and that the server couldn’t connect to the database (it could).

Okay, time to dive into the logs! There we found the following error: “ERROR: Database downgrade detected. The database schema version is newer than service schema version”. Wut?

The kicker is, we had an old SSRS 2012 instance still running (the one we actually migrated to SSRS 2017, but I digress) and migrating that ReportServer database went without a single issue. Looking around on the web, it seems migrating SSRS 2012/2014/2016 is not an issue, but PBI RS server seemingly has a problem with SSRS 2017. Also, the SSRS web service didn’t seem to have a problem: you could browse the web service and render reports. Just not the portal. Both SQL Server 2017 and PBI RS Server were updated to the latest versions.

After quite some searching, I finally ran Profiler to capture all the queries to the ReportServer database. One stored procedure stood out: GetCurrentProductInfo. Hmmm, and the error message complains about schema versions. The stored procedure basically checks for the most recent row in the ProductInfoHistory table. The contents of the SSRS 2017 ReportServer table:

And on a brand new PBI Report server database freshly installed by the SSRS configuration manager:

Aha, we’re on to something: the Sku and the BuildNumber are different. So I would do what anyone would do in production: manually change the values in the table. Lo and behold, the portal works! I could browse the reports and render them. Happy dance! But then I tried to upload a Power BI Desktop report. No dice, the upload failed with the superb error message: “there was an error”.

My mental state:

Digging into the logs (again), I found the following error message:

Apparently the ProductInfoHistory table wasn’t the only thing that went wrong. The SSRS configuration manager forgot to run a script that updates the SSRS 2017 ReportServer database so that it can actually work with the Power BI objects. So I did what any sane person would do in production: open up SQL Compare, compare the schema of the SSRS 2017 database (which should have been migrated) with a brand new ReportServer database created by PBI RS server, and push the missing objects into the SSRS database.

Lotsa changes…

I restarted the service and tried uploading the PBI Desktop report, and finally, it worked!

UPDATE2:

Recently I’ve bummed into another issue when trying to upgrade the instance. There’s a table called DBUpgradeHistory that keeps track of all the installed updates on your instance. When you follow the steps in this blog post, you have to update this table as well, otherwise when you try to upgrade to a newer version, the update script will try to implement updates that are already there and it will crash. For example, columns will be added to a table, but since they are already there the ALTER TABLE statement will fail.

Conclusion

Migrating SSRS 2017 to Power BI Report Server ain’t easy folks. Somebody at Redmond forgot to test if they were compatible or not. You have two options:

  • Think before you act. Don’t install SSRS 2017 in the first place, but install PBI Report server right away. No migration necessary.
  • You can follow the steps outlined in this blog post. Make sure you have backups of your ReportServer databases and of the encryption key.

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at element61, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Comments

Leave a comment on the original post [sqlkover.com, opens in a new window]

Loading comments...