Valid DSN Error and connection/compatibility error due to ReportServer database when attempting in-place upgrade from sql Server 2014 to sql Server 2016
The following error details are in the upgrade log:
The report server database is not a supported compatibility level or a connection cannot be established. Use Reporting Services Configuration Manager to verify the report server configuration and SQL Server management tools to verify the compatibility level.
The report server configuration is not complete or is invalid. Use Reporting Services Configuration Manager to verify the report server configuration.
The report server has already been upgraded to 2016. The report config app on the report server looks normal. REPORTS in this database continue to WORK properly before and after the report server upgrade to 2016.
We've deleted the account on the database and report server and re-added it. We've re-selected the "ReportServer" database from the report config app and ran "Test Connection" with success. We're using the same domain account that was in use for 2014.
The report server has 2 instances. We've applied the above attempts to both.
The last step will be to take out the existing ReportServer database and use the report server config app to "Create New" ReportServer database. However, we would like to avoid this as we would have to manually re-deploy hundreds of reports to the server. ( And it might not solve the problem. )
Any ideas would be appreciated. Thanks.
ASKER
The versions of the ReportServer databases match the master and are all 2014.
The link is a data migration assistant. We looked at that at the beginning of our process. When you run it, it prompts you for server A, server B, etc. It doesn't seem to be geared toward an in-place upgrade. Do you have experience using it in that scenario?
The link is a data migration assistant. We looked at that at the beginning of our process. When you run it, it prompts you for server A, server B, etc. It doesn't seem to be geared toward an in-place upgrade. Do you have experience using it in that scenario?
"The versions of the ReportServer databases match the master and are all 2014." - sorry if I wasn't clear enough however I though the screen shots from the link above about "understanding-sql-server-
The link refers to the DMA tool indeed but that will be used on the Server you need to upgrade from and did you installed it and to actually run it? Of course you can use it to asses an upgrade and you will select your existing as source and new version as destination but you don't need to actually have bot versions/instances on the same or separate servers. Just select the "Assessment" under the new project not a actual migration or upgrade.
https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15
"Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server."
The link refers to the DMA tool indeed but that will be used on the Server you need to upgrade from and did you installed it and to actually run it? Of course you can use it to asses an upgrade and you will select your existing as source and new version as destination but you don't need to actually have bot versions/instances on the same or separate servers. Just select the "Assessment" under the new project not a actual migration or upgrade.
https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15
"Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server."
ASKER
Thanks for your attention, by the way.
Yes, sorry for the imprecise term, the compatibility levels are all 2014 120.
We're going to give the DMA a try to see what it reveals. Thanks for the "assessment" tip, we probably missed that the first time.
Yes, sorry for the imprecise term, the compatibility levels are all 2014 120.
We're going to give the DMA a try to see what it reveals. Thanks for the "assessment" tip, we probably missed that the first time.
Hm....so if the ReportServer and ReportServerTempDB databases are already upgraded and compatibility mode is set to 120 as you mentioned is likely the DMA would not help much after the upgrade was already done. My advice was for the future upgrades - whether in place or migration/upgrade to use the DMA prior to the upgrades to reveal any potential issues. Let's try think something else prior to recreating the ReportServer database from scratch.
Do you have n older - prior to the upgrade backup of both ReportServer and ReportServerTempDB databases?
Do you have a backup of the original source encryption key?
If you do, I would stop SSRS, rename exisitng ReportServer database just in case!! to have it on the server as you should be still able to use it to extract all the reports, data sources, ETC from it.
And try see if the steps under "Conclusion" from link below can help you instead of creating a brand new empty SSRS ReportServer db.
https://www.sqlshack.com/migrating-ssrs-content-powershell/
Other useful resources that you most likely have already but if not..I would save some of those scripts as who knows when they'll be gone:
https://gallery.technet.microsoft.com/scriptcenter/Export-of-all-SSRS-reports-57910227
https://github.com/Microsoft/ReportingServicesTools
https://www.mssqltips.com/sqlservertip/4738/powershell-commands-for-sql-server-reporting-services/
Do you have n older - prior to the upgrade backup of both ReportServer and ReportServerTempDB databases?
Do you have a backup of the original source encryption key?
If you do, I would stop SSRS, rename exisitng ReportServer database just in case!! to have it on the server as you should be still able to use it to extract all the reports, data sources, ETC from it.
And try see if the steps under "Conclusion" from link below can help you instead of creating a brand new empty SSRS ReportServer db.
https://www.sqlshack.com/migrating-ssrs-content-powershell/
Other useful resources that you most likely have already but if not..I would save some of those scripts as who knows when they'll be gone:
https://gallery.technet.microsoft.com/scriptcenter/Export-of-all-SSRS-reports-57910227
https://github.com/Microsoft/ReportingServicesTools
https://www.mssqltips.com/sqlservertip/4738/powershell-commands-for-sql-server-reporting-services/
ASKER
The DMA returned a bunch of stuff to clean up for the future regarding stored proc stuff like don't use ntext, etc. but only information and warnings, no show stoppers.
We'll look at your additional suggestions next. Thanks.
We'll look at your additional suggestions next. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please see this link https://www.spiria.com/en/blog/web-applications/understanding-sql-server-compatibility-levels/ for different compat levels and I suggest you set your ReportServer to the same compat level as the master DB on each SQL instance.
For future upgrades my advice is to always run the upgrade adviser/assistant as this will show you prior to upgrading all issues from critical to trivial https://docs.microsoft.com/en-us/sql/database-engine/install-windows/prepare-for-upgrade-by-running-data-migration-assistant?view=sql-server-2016