Stefan Grigat

We have to migrate/upgrade the Reporting Services installed and used in SQL 2014 to our new server with SQL 2016. I had a look in the upgrade script and will show you some details.
As we want to keep the folder structure and permissions and these things of the Report Server, we just backup the database in version 2014, transfer it to the new Server and restore it in the SQL Server 2016 instance.
Because we did not want to reuse the name of the database as we had a version name in it, we renamed the database during the restore and we will use the database with the new name in the Reporting Services configuration wizard.
This renaming can be done, as long as the Reporting Services TempDb has the same name + TempDb suffix. So you can name the database pair „MyReportDatabase“ and „MyReportDatabaseTempDb“.

In this example I added the string „_NewName2“ to both databases and kept the versionname 2014 to show you what I mean.
Querying immediately after restoring it without attaching it to the Reporting Services Service a view that uses both databases will have a binding error:

Msg 208, Level 16, State 1, Procedure ExtendedDataSets, Line 10 [Batch Start Line 0]
Invalid object name ‚ReportServerNative2014TempDB.dbo.TempDataSets‘.
Msg 4413, Level 16, State 1, Line 6
Could not use view or function ‚ReportServerNative2014_NewName2.dbo.ExtendedDataSets‘ because of binding errors.
We go to the definition of the view and see the issue:

As you can see the view links the old name of the TempDb Database.

Now we configure the Reporting Services to use the existing database and then the service checks the version of the database an runs the upgrade script which is defined in the „C:\Program Files\Microsoft SQL Server\MSRS13.SQL2016\Reporting Services\RSWebApp\Microsoft.ReportingServices.UpgradeScripts.dll“

One part of this script, seen in the profiler while the database upgrade is done, is the following:

So this upgrade not only shifts the database to the new version but is also able to change the object bindings to the new database names.

Now we can query the view successfully:

ID LinkID Name ItemID
65B50E8A-AD1B-4198-B41B-4B9E4F734DD0 5E13FFDA-0A56-4735-A953-9B59DBBF4926 ds1 74E856F5-6902-4236-9D21-01A6A6D0D121
57BFF010-4E01-4AB5-B15A-4BEC2EA4C63B 67284741-E655-4EF8-9FB9-A606154A0B0C ds2 74E856F5-6902-4236-9D21-01A6A6D0D121
C9C2F7F2-0979-4201-B9EB-373B5ECA8973 E9F824F1-2B6E-4008-A9A8-3DDE87DC7B62 ds3 74E856F5-6902-4236-9D21-01A6A6D0D121
416A8D77-405F-435B-871D-41AD1ABA35A2 18618775-01BC-4944-84FB-434C4F80818D ds4 74E856F5-6902-4236-9D21-01A6A6D0D121
1426B31A-4116-43BA-918C-B22DA646618B E8111930-D20D-480D-8779-27F1A1C0D8D5 ds4 74E856F5-6902-4236-9D21-01A6A6D0D121

 

So when you upgrade the Reporting Services Database from SQL Server 2014 to SQL Server 2016 the upgrade script does not only do the neccessary updates to the new version but is also able to redefine the object definitions to the new database names.