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

Migrate SSRS 2017 to Power BI Report Server

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!


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.


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

Loading comments...