SQL Reporting Server Issue

Faust Romero
Faust Romero used Ask the Experts™
on
I have 3 servers for this scenario. webserver1, newwebserver, sqlserver.
webserver1 uses SQLserver 2008 R2 for SQL Reporting Services
newwebserver uses SQLserver 2012 R2 for SQL Reporting Services
sqlserver uses SQLserver 2008R2.
webserver1 have been connected for SQL Reporting services to sqlserver.
Today, we tried to add newwebserver for SQL Reporting service to sqlserver, and for some reason it screw our current webserver1 SQL REporting services.
I need to be able to reconnect my webserver1 via SQL reporting services with my sqlserver. I have the "SWL REport key.snk" and password that was originale used to create this service. i have been trying to restore and error below.
encryption keyserrori need to be able to reconnect back. I already disconnect newwebserver, but i believe it is still connected for some reason. what i can do to reconnect back to ReportServer database.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I am not an SQL experts. Please be specific.

Commented:
It looks like connecting the new SSRS instance has upgraded your reportserver databases. Which server are the reportserver databases on? Do you have backups of them?

Author

Commented:
the sqlserver is on SQL2008R2. I have backup for the maindatabase, but we do not have backup for reportserver database. is there is a way to reconnect?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
You must have taken the back up reporting services with the password...Do you have it?

Author

Commented:
LVL37 i have the password and key file shown above in the picture. But i can not restore. I am not sure why.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:

Commented:
What is the result of this query against your reportserver database?

SELECT TOP (1) [UpgradeID]
      ,[DbVersion]
      ,[User]
      ,[DateTime]
  FROM [ReportServer].[dbo].[DBUpgradeHistory]
  ORDER BY [DateTime] DESC
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Database version mismatches with SSRS. This should fix..hopefully

All Programs -> Microsoft SQL Server (yoursql version) -> Configuration Tools -> Reporting Services Configuration,
select Database Setup tab and click Upgrade button.

Author

Commented:
see below.
dwh3.JPG
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You may also want to refer this -

http://www.msqlserver.net/2015/09/the-version-of-report-server-database.html

You need make both DB and report server identical.
Commented:
The ideal solution would be a backup of the reportserver database from before you pointed the new web server at it.

Failing that I would:

- Upgrade your existing SSRS service on the current webserver.

Failing that I would:

- Take a backup of your current reportserver database
- DELETE [ReportServer].[dbo].[DBUpgradeHistory] WHERE DbVersion > 147
- DELETE [ReportServer].[dbo].[DBUpgradeHistory] WHERE ServerVersion > 147
- Restart SSRS

I can't guarantee this will work - but as long as you have a backup it should be safe to try.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Before deleting, check that the entries exist.

You already ran the query to find top 1 - so simply take out the top 1 and run the query.

You should find your version 147

Do that for both :

select * from [dbo].[DBUpgradeHistory] WHERE DbVersion > 146

select * from [dbo].[ServerUpgradeHistory] WHERE ServerVersion > 146

Then (after backup) you can then try the deletes as Matt Bowler said above.

The 'gotcha' is if there have been any actual upgrade attempts and activity on the newer version.  Just connecting newwebserver sounds like what Matt said - connecting assumed the new version.

Again, backup first....

Commented:
"I need to be able to reconnect my webserver1 via SQL reporting services with my sqlserver'
you need to restore reportserver  reportservertemp  DBS from "pre" upgrade steps implementation
---
for upgrade:
try this solution it is a very similar to your problem
You cannot use SQL Server 2005 to host ReportServer 2012 databases
https://support.microsoft.com/en-sg/help/2796721/you-cannot-use-sql-server-2005-to-host-reportserver-2012-databases




 you may like more "upgrade in place "  idea ( just make sure you have a good  SRRS-sql backups)
see the Upgrade and Migrate Reporting Services
https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/upgrade-and-migrate-reporting-services

Author

Commented:
I was able to restore from a "ReportServer.bak" backup i had on a different sqlserver which I use for test. This different sqlserver had running SQL2008R2.  After that i restore the "Encryption keys" and complete information to point the right "Data Source" on "Reporting Services". Thank you all. Important to make backup before a change.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial