You are hereSQL Server 2005 DBA / How-to move reporting services to a new server

How-to move reporting services to a new server


By sqlguychuck - Posted on 11 December 2007

Your rating: None Average: 4.3 (16 votes)
  1. Backup your ReportServer database.
  2. Backup your encryption key on existing installation with the Reporting Services Configuration Manager tool found in the SQL Server 2005 | Configuration Tools folder under Windows Start Menu. Place it in a specific location, otherwise you will have to search for default save location. 
  3. Install reporting services on a new server and restore the ReportServer database. You don’t need to restore the tempdb.
  4. Run the Reporting Services Configuration Manager tool on this new server.
  5. Solve any issues you see, configure to run under a domain account with a strong password, and then on Encryption Keys step, restore the key you saved in above step. 

New document on SharePoint/Reporting Services installation steps.
If you didn’t follow these steps, you will see these errors: IIS Reportserver webpage displays this error: Report Server Windows Service (MSSQLSERVER) has not been granted access to the catalog content. Also, in applications event viewer you see "Report Server (MSSQLSERVER) cannot connect to the report server database." And "Report Server Windows Service (MSSQLSERVER) has not been granted access to the catalog content." Restore the encryption key to fix this error.

 
You may get this error if you are downgrading your SQL edition below Enterprise edition:
The feature: "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported) 
This is because SQL Standard only supports one instance id. You will need to remove the old instance id from a command line tool.
Open folder in cmd window: C:\Program Files\Microsoft SQL Server\90\Tools\Binn
Run RSKeyMgmt.exe -l It should list 2 or more servers, e.g.
DB2\MSSQLSERVER - 04452517-6757-4172-9450-2e7c0f5afbe9
DBMONITOR2\MSSQLSERVER - d0fdea1f-0573-4488-bb0c-41c1695cc06a 
Remove all but your current server with –r option:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn>RSKeyMgmt.exe -r 04452517-6757-4172-9450-2e7c0f5afbe9
Are you sure you want to delete this key? Yes (y)/ No (n): y 
The command completed successfully.
If you have issues restoring the encryption key, complaining that the password is invalid, you need to exit the RS Configuration Manager and start again.
All should be good now.
If you upgrade to SQL 2008 Reporting Services, you could also see this unrelated issue: "The underlying connection was closed. Could not establish trust relationship for the SSL/TLS secure channel."
I have read and used in our environments that default install wants to use SSL, so you turn that requirement off by altering config file key to turn off SSL security:
C:\Program Files\Microsof SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\reportingserver.config
<Add Key="SecureConnectionLevel" Value = "0"/>
Values 0-3 are valid. Default seems to be 2.
-Chuck

When trying to configure the SQL Server Reporting Services (SSRS) Database Setup from the Reporting Service Configuration Manager you may receive the following error:
The database version (C.0.8.40) does not match your reporting services installation.  You must upgrade your reporting services database.
Couldn't generate the upgrade script.  There is no upgrade script available for this version.
Resolution:
This error occurs when the database version does not match the version of the SSRS installation. This error will occur when you install the SQL Server you components in following order.

  1. Install SQL Server 2005 Database Services.
  2. Install SQL Server 2005 Service Pack 2.
  3. Install SQL Server Reporting Services.

Because of this, SQL Server Report Services and Database Services versions are mismatching. Therefore, you need to install SQL Server Service Pack 2 again so that both versions are equal. 

Rate This

Your rating: None Average: 4.3 (16 votes)