locked
Database Upgrade Problem (migration from SSRS 2005 E to SSRS 2008R2 E) RRS feed

  • Question

  • I am in the process of performing a migration upgrade to our TFS server (2008 to 2010).  When migrating the SSRS database I'm having issues.  It is currently running on SQL Server 2005 Enterprise and is moving to SQL Server 2008 R2 Enterprise  (both 64 bit).  The SQL DB server is a new Windows 2008 R2 E server.

    I've tried this several different ways, but here is the jest of it.  I backed up the database on the 2005 server and restored it on the 2008R2 server.  On my report server (a new Win 2008 R2 E server w/ SSRS 2008R2E), I select the database and establish the virtual directories and all.  I try to restore my key, but that fails.  If I try to browse to either of the websites, I get the following error:

    The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'C.0.8.40'. The expected version is '147'. (rsInvalidReportServerDatabase)

    If I check the SSRS log, I see this.

    appdomainmanager!DefaultDomain!152c!03/31/2011-14:44:09:: i INFO: Appdomain:2 WindowsService_0 started.
    resourceutilities!WindowsService_0!152c!03/31/2011-14:44:09:: i INFO: Reporting Services starting SKU: Enterprise
    library!WindowsService_0!1440!03/31/2011-14:44:09:: i INFO: Catalog SQL Server Edition = Enterprise
    library!WindowsService_0!1440!03/31/2011-14:44:09:: i INFO: Current DB Version C.0.8.40, Instance Version 147.
    library!WindowsService_0!1440!03/31/2011-14:44:09:: i INFO: Starting upgrade DB version from C.0.8.40 to 147.
    library!WindowsService_0!1440!03/31/2011-14:44:10:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.;
    library!WindowsService_0!1440!03/31/2011-14:44:10:: e ERROR: ServiceStartThread: Exception caught while starting service. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Cannot find the object "SessionData" because it does not exist or you do not have permissions.
    Changed database context to '<Edited:databasenameTempDB>'.
    library!WindowsService_0!1440!03/31/2011-14:44:10:: e ERROR: ServiceStartThread: Attempting to start service again...

    So it sees that the database is not the right version, but it is unable to upgrade it apparently.  I know RSExecRole is all correct.  I've even tried creating a new database within the config manager and that works fine.  I would like to have the other database though.

    Is there some way to force the upgrade or to see what is actually going on here?  I would appreciate any advice.

    Thursday, March 31, 2011 7:49 PM

Answers

  • Changing the version number didn't work.  I just ran into more errors, most likely because the scheme wasn't updated.

    I apparently have corrected this a different way.  I restored the database again and I changed the service account to be one that has sysadmin rights on the SQL Server.  That seems to have brought everything up to date and functional.  Once running, I changed the account back to one with less rights.  I guess I'm not sure why this would have kept it from functioning.  I was running Reporting Services Configuration Manager from an account with sysadmin rights and I thought it modified the database using impersonation of that account.  Apparently I was wrong.  Jerry, if you can tell me why this worked, I would be thankful.

    • Marked as answer by David_Stanley Monday, April 4, 2011 5:22 PM
    Monday, April 4, 2011 2:34 PM

All replies

  • Friday, April 1, 2011 11:24 AM
  • Yep, I've done that.  Creating a new database works, but I'm trying to upgrade an existing database.  Is there some way of moving all the data to the new database so I don't have to manually move all reports, permissions, etc?  If not, I still need to upgrade the database and building a new one isn't a solid option, but one thats on the table.

    Friday, April 1, 2011 12:28 PM
  • Hi David,

    From the error message, the 2005 db version is not matched with the current 2008 R2 reporting services. To get it around, try the following alternative way to match SSRS 2008 instance:

    Open the SQL Server 2008 R2 management Studio (SSMS 2008 R2), launch the query box and run the following T-SQL command to see if it helps:

    Method1:
    Use 2008R2ReportserverDB
    select top(1) [ServerVersion] from [dbo].[ServerUpgradeHistory] ORDER BY [UpgradeID] DESC
    --the above query is to get the 2008 R2 Report Server DB version number. For example, it is 147
    Use 2005ReportServerDB
    ALTER PROCEDURE [dbo].[GetDBVersion]
    @DBVersion nvarchar(32) OUTPUT
    AS
    set @DBVersion = '147' --to change the 2005 db version to 147 to match the SSRS 2008 R2 instance

    Method2:
    Use 2005ReportServerDB
    ALTER PROCEDURE [dbo].[GetDBVersion]
    @DBVersion nvarchar(32) OUTPUT
     AS
    SET @DBVersion = (select top(1) [ServerVersion] from 2008R2ReportserverDB.[dbo].[ServerUpgradeHistory] ORDER BY [UpgradeID] DESC)
    --to change the GetDBVersion procedure to retrieve db version from SSRS 2008 R2 instance DB to match the SSRS 2008 R2 instance.

    please let me know the result.

    thanks,
    Jerry

    Monday, April 4, 2011 1:22 AM
  • Changing the version number didn't work.  I just ran into more errors, most likely because the scheme wasn't updated.

    I apparently have corrected this a different way.  I restored the database again and I changed the service account to be one that has sysadmin rights on the SQL Server.  That seems to have brought everything up to date and functional.  Once running, I changed the account back to one with less rights.  I guess I'm not sure why this would have kept it from functioning.  I was running Reporting Services Configuration Manager from an account with sysadmin rights and I thought it modified the database using impersonation of that account.  Apparently I was wrong.  Jerry, if you can tell me why this worked, I would be thankful.

    • Marked as answer by David_Stanley Monday, April 4, 2011 5:22 PM
    Monday, April 4, 2011 2:34 PM
  • The Configuration manager doesn't update the database , its the RS service , double check that your RS service account is member of the RSExecRole, that role is designed to have all the required permissions to perform the Reporting database operations

    • Marked as answer by David_Stanley Monday, April 4, 2011 5:17 PM
    • Unmarked as answer by David_Stanley Monday, April 4, 2011 5:17 PM
    Monday, April 4, 2011 4:37 PM
  • Sorry about the "Answer" check.  I clicked the wrong button.

    I had verified that RSExecRole was set up correctly per MS documentation. But that didn't change anything.  I was able to create a new RS database, but not upgrade.  I don't know if our DBAs that set up the server altered a default setting or what, but from what I could tell RSExecRole and the account had the correct permissions.

    Since the database has been upgraded, it seems to function perfectly with the non sysadmin account.  So your guess is as good as mine.  Hopefully this thread helps others.  I will go ahead and close it for now.

    Monday, April 4, 2011 5:22 PM
  • In this extreme rare cases you can also generate the script and apply it by yourself to overcome any permission issues, this could be accomplished using the WMI method GenerateDatabaseUpgradeScript from the Reporting Services WMI Provider http://msdn.microsoft.com/en-us/library/ms154641.aspx

    Monday, April 4, 2011 5:34 PM