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:
I recently read the book Agile Data Warehouse Design - Collaborative Dimensional Modeling, from Whiteboard…
You can find the slides for the session Building the €100 data warehouse with the…
I was asked to do a review of the book Microsoft Power BI Performance Best…
This is a quick blog post, mainly so I have the code available if I…
Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…
This book was making its rounds on social media, and the concept seems interesting enough…
View Comments
Trash Panda
Its worse, its much worse
I tried to upgrade SSRS 2016 to SSRS 2017...all the subscriptions and folder permissions get trashed.
the Dweeb over on the MS forum Ricardo Multi insulted me and said "it works fine"
mmm no it doesnt
That dweeb is the Program Manager for SSRS, so usually he knows what he's talking about ;)
If the subscriptions and permissions got trashed, maybe there was something wrong with the restore of the encryption key?
he doesnt but its ok
yes key was imported
Thanks for the post Ken. We were thinking of upgrading to 2017 but now I think we'll just skip the upgrade and stay on 2016 until we move to Power BI Report Server. Great info!
I don't have SQL Compare but I was able to do a quick object query from a fresh install of the October 2017 PBI RS server compared to the SSRS 2016 RS database which was 'upgraded' to PBI and found around 80 missing objects.
My thought is while the SSRS2016 RS to PBI RS upgrade appears to complete as it normally would with an SSRS migration, it's very likely it will fail at some point due to these missing objects.
Point being, I think the same issues you found with the SSRS 2017 RS -> PBI RS will be the same with earlier versions of SSRS also.