August 22, 2019 at 3:36 pm
I've tried several times to successfully migrate a SQL Server 2008R2 ReportServer and ReportServerTempDB to SQL Server 2017. Every time it fails and one of the log files says that the database upgrade failed.
I install SSRS 2017 but don't configure. I backup the 2008R2 SSRS databases and the encryption key. This database is joined to a scale-out deployment. I restore the SSRS databases to SQL Server 2017.I then start the SSRS Config Manager and step through the config. I restore the encryption key as part of this process. The roles and permissions for RSExec are created.
When I load the Report Manager URL I get the message "The service is not available.
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database."
I look in one of the log files and see the below:
use [ReportServer]
------------
| RequestID = s_74ca2bc9-5b8f-4741-b61a-38af5cc4415e System.Data.SqlClient.SqlException (0x80131904): Column names in each table must be unique. Column name 'EditSessionID' in table 'SessionData' is specified more than once.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection.ExecuteBatchScript(String script, TimeSpan individualCommandTimeout)
ClientConnectionId:c52a9d0d-f464-411f-82ad-1057404a8b6f
Error Number:2705,State:4,Class:16
2019-08-22 10:00:02.2162|FATAL|6|Database upgrade failed!! The database may now be in an inconsistent state.| RequestID = s_74ca2bc9-5b8f-4741-b61a-38af5cc4415e System.Data.SqlClient.SqlException (0x80131904): Column names in each table must be unique. Column name 'EditSessionID' in table 'SessionData' is specified more than once.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection.ExecuteBatchScript(String script, TimeSpan individualCommandTimeout)
at Microsoft.BIServer.Management.WebApi.DatabaseUpgrade.DatabaseUpgrader.GetStatusAndUpdateIfRequired(ISqlAccess sqlAccess)
ClientConnectionId:c52a9d0d-f464-411f-82ad-1057404a8b6f
Error Number:2705,State:4,Class:16
2019-08-22 10:00:12.2452|INFO|11|Could not find ProductInfo table. Will do first-time upgrade.| RequestID = s_0a4ce8b5-ced4-4325-b43a-395c6d7cb9ff
2019-08-22 10:00:12.2452|INFO|4|Upgrading ReportServer database to version 14.0.600.1109 hash B114DCF686D456AD82E265B6E3C78CE6652923210651185B82C532429D01A214| RequestID = s_0a4ce8b5-ced4-4325-b43a-395c6d7cb9ff
2019-08-22 10:00:12.2921|ERROR|4|Aborting SQL batch script! Error executing SQL batch script at entry
------------
--- T.0.9.45 to 100 ---
August 22, 2019 at 5:41 pm
I don't think it's all of the problem but with a scale out deployments, you need to delete all of the keys in the keys table (after backing up the encryption key) before doing the configuration and all of the nodes in the scale out should be offline when you do the migration. The Configure the Report Server section in the following documentation goes into this and how to manage the migration on a scale out:
Migrate a Reporting Services Installation
Sue
August 23, 2019 at 1:19 pm
There is a database upgrade process that is failing. At least that's what another part of the SSRS logs indicate.
Last night I tried to restore the SSRS databases to a SQL Serve 2014 SSRS instance and that also failed at the point where I tried to restore the encryption key.
--Error after trying to restore encryption key for the ReportServer database from SRV-SQL-03
"Microsoft.ReportingServices.WmiProvider.WMIProviderException: 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 '147'. The expected version is '163'. (rsInvalidReportServerDatabase)
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, SecureString password)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, SecureString password)"
when I finished the URL config and tried the encryption key part again on the 2014 SSRS instance I now get the below. Reporting Service is running.
Microsoft.ReportingServices.WmiProvider.WMIProviderException: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.
---> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, SecureString password)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, SecureString password)
August 23, 2019 at 1:21 pm
Now seeing this in the ReportServerservice log file in the 2014 SSRS instance. Part of this is the same error I'm seeing on the 2017 instance.
library!WindowsService_5!1a64!08/23/2019-03:44:41:: i INFO: Current DB Version 147, Instance Version 163.
library!WindowsService_5!1a64!08/23/2019-03:44:41:: i INFO: Starting upgrade DB version from 147 to 163.
library!WindowsService_5!1a64!08/23/2019-03:44:41:: 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_5!1a64!08/23/2019-03:44:41:: 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: Column names in each table must be unique. Column name 'EditSessionID' in table 'SessionData' is specified more than once.
library!WindowsService_5!1a64!08/23/2019-03:44:41:: e ERROR: ServiceStartThread: Attempting to start service again...
library!DefaultDomain!1e4c!08/23/2019-08:13:19:: e ERROR: Throwing Microsoft.ReportingServices.Library.InvalidReportServerDatabaseException: , Microsoft.ReportingServices.Library.InvalidReportServerDatabaseException: 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 '147'. The expected version is '163'.;
August 23, 2019 at 6:30 pm
In this last error, you were attempting an upgrade on the 2014 instance, not sure how that comes into play with migrating a 2008R2 instance to 2017. But in terms of the error, it's just the first one you'd need to pay attention to as the rest is from the upgrade issues. So it starts with this:
library!WindowsService_5!1a64!08/23/2019-03:44:41:: i INFO: Starting upgrade DB version from 147 to 163.
library!WindowsService_5!1a64!08/23/2019-03:44:41:: 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.;
Check the disk space, make sure you are doing the upgrade locally, run the upgrade as administrator. If this instance is involved in the scale out somehow it can be related to upgrading the other nodes. If you upgrade once instance of a scale out deployment without offlining the other nodes, you will usually end up with a mess. You would also want to check the windows event logs for the servers of those instances.
Sue
August 23, 2019 at 7:34 pm
Sue, We were simply trying to see if this migration would work on a lower version. We actually ended up updating the versionnumber column in the DBUpgradehistory table to 163 and after that the Report Manager started loading. The idea was if we could get it working here, then I'd try backing up this database and restoring it to the intended SQL Server 2017 destination.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply