13.07.2017

SSRS Upgrade from SQL Server 2014 to SQL Server 2016 – Script when database renamed

Technical Value

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“.

  1. USE [master]
  2. RESTORE DATABASE [ReportServerNative2014_NewName2]
  3. FROM  DISK = N'M:\SQL2016_BACKUP\ssrs2014.bak' WITH  FILE = 1,  
  4. MOVE N'ReportServerNative2014' TO N'M:\SQL2016_DATA\ReportServerNative2014_NewName2.mdf',  
  5. MOVE N'ReportServerNative2014_log' TO N'L:\SQL2016_LOG\ReportServerNative2014_NewName2_log.ldf',  
  6. NOUNLOAD,  STATS = 5
  7. GO
  8. USE [master]
  9. RESTORE DATABASE [ReportServerNative2014_NewName2TempDB]
  10. FROM  DISK = N'M:\SQL2016_BACKUP\ssrs2014tempdb.bak' WITH  FILE = 1,  
  11. MOVE N'ReportServerNative2014TempDB' TO N'M:\SQL2016_DATA\ReportServerNative2014TempDB_NewName2.mdf',  
  12. MOVE N'ReportServerNative2014TempDB_log' TO N'L:\SQL2016_LOG\ReportServerNative2014TempDB_NewName2_log.ldf',  
  13. NOUNLOAD,  STATS = 5
  14. GO

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:

  1. SELECT TOP (1000) [ID]
  2.       ,[LinkID]
  3.       ,[Name]
  4.       ,[ItemID]
  5.   FROM [ReportServerNative2014_NewName2].[dbo].[ExtendedDataSets]

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:

  1. USE [ReportServerNative2014_NewName2]
  2. GO
  3. CREATE VIEW [dbo].[ExtendedDataSets]
  4. AS
  5. SELECT
  6.     ID, LinkID, [Name], ItemID
  7. FROM DataSets
  8. UNION ALL
  9. SELECT
  10.     ID, LinkID, [Name], ItemID
  11. FROM [ReportServerNative2014TempDB].dbo.TempDataSets
  12. GO

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“

  1. /**************************************************************/
  2. /* Copyright (c) Microsoft.  All rights reserved. */
  3. /**************************************************************/
  4. -- !!! This assumes the database is created and the user is either a dbo or is added to the RSExecRole
  5. -- !!! Please run setup to create the database, users, role !!!
  6. --------------------------------------------------
  7. ------------- Upgrade RS Catalog
  8. --------------------------------------------------
  9. --- Starting in Kilimanjaro, the catalog version numbers were changed to be just an integer (starting with 100)
  10. --- The old system (C.0.9.45) was last used in Katmai.
  11. use [ReportServerNative2014_NewName2]
  12. /*
  13. ...
  14.  */
  15. --- RenderMobileReport
  16. if not exists (select * from [dbo].[DBUpgradeHistory] where [DbVersion] = '175')
  17. BEGIN
  18.     INSERT INTO [dbo].[DBUpgradeHistory] ([DbVersion]) VALUES ('175');
  19. END
  20. /**************************************************************/
  21. /* Copyright (c) Microsoft.  All rights reserved. */
  22. /**************************************************************/
  23. --------------------------------------------------
  24. ------------- Upgrade RS Temp DB
  25. --------------------------------------------------

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

  1. use [ReportServerNative2014_NewName2TempDB]
  2. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExtendedDataSets]'))
  3. drop view [dbo].[ExtendedDataSets]
  4. CREATE VIEW [dbo].ExtendedDataSets
  5. AS
  6. SELECT
  7.     ID, LinkID, [Name], ItemID
  8. FROM DataSets
  9. UNION ALL
  10. SELECT
  11.     ID, LinkID, [Name], ItemID
  12. FROM [ReportServerNative2014_NewName2TempDB].dbo.TempDataSets
  13. GRANT SELECT, REFERENCES ON [dbo].[ExtendedDataSets] TO RSExecRole

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:

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

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.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden