Backup Files and Data
Backup the encryption key for the report server database. The report
server encryption key is backed up using the Reporting Services
Configuration Manager. For SQL 2005 navigate to Start/All
Programs/Microsoft SQL Server 2005/Configuration Tools/ and click
Reporting Services Configuration. On the Instance Selection dialog enter
Machine Name and Instance Name and click Connect.
In the navigation panel on the left select Encryption Keys, then
click Backup. Enter a Password in the Encryption Key Information dialog
and click the button next to Key File to specify a location and name for
the key backup file.
Click Exit to exit the Reporting Services Configuration Manager.
Microsoft recommends backing up the report server configuration files.
As I went through the process I did not need these files later in the
process, but backed them up anyway. Here is a list of the files and
their locations in a SQL 2008 installation on the E: drive.
- Rsreportserver.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- Rssvrpolicy.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- Report Server web.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- Rswebapplication.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- RsmgrPolicy.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- Report Manger web.config - E:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
- ReportingServicesservice.exe.config - E:\Program
Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting
Services\ReportServer\bin
- Machine.config for ASP.NET if you modified it
Backup the report server database using any supported method for
backing up SQL Server databases. I choose to backup the databases using
SQL Management Studio. Microsoft states that both the ReportServer and
ReportServerTempDB databases should be backed up and restored to the new
server. In SQL Management Studio expand Databases and right click the
ReportServer database, select Tasks and select Back Up....
Make sure the Backup Type is set to Full and specify a location and filename for the backup.
Do the same tasks for the ReportServerTempDB database.
Note: If the database is using the Full recovery model, use the
following script to back it up to avoid breaking the log chain, 'With
Copy_only' will make a copy only backup and will not break the log
chain:
Backup Database ReportServer To Disk='E:\Temp\ReportServer_04192012.bak' With Copy_only
Move Report Server Database and Configure Report Server
If you are moving the databases to a server that has never hosted SQL
Reporting services you will need to create the RSExecRole, click
this link for instructions.
When ready, restore the ReportServer and ReportServerTempDB databases
to the new server. In my case I am replacing the existing Report server
databases. In SQL Management Studio expand databases and right click
the ReportServer database, from the pop up menu select
Tasks/Restore/Database.
Make the entries in the Restore Database dialog appropriate for the
backup you took. In my case it is a file stored on the local disk. Note:
To gain exclusive access to the database for the restore I had to stop
the SQL Server Reporting Services (MSSQLSERVER) service.
In the Options page of the Restore Database dialog check 'Overwrite
the existing database (WITH REPLACE)', leave the database ready for use
and use the buttons under 'Restore As' to locate the physical database
files (existing .mdf and .ldf files).
Once the ReportServer database has been restored repeat the process
for the ReportServerTempDB database. If your reporting installation
includes custom items you must re-deploy those items, refer to the 'Move
Custom Assemblies or Extensions' section of the migration document. My
installation does not include custom items so I will not cover this
section. Now that the databases and custom items (if any) have been
moved you must use the Report Services Configuration tool to configure
Reporting Services. Navigate to Start/All Programs/Microsoft SQL Server
2008 R2/Configuration Tools and click Reporting Services Configuration
Manager. Enter the Server Name and Report Server Instance in the
Reporting Services Configuration Connection dialog and click Connect.
If you stopped the Reporting Services service during the restore
process click the Start button to start the service. If needed configure
the URLs for the Report Server and Report Manager, instructions can be
found in the Migration document under the Configure the Report Server
section, I did not need to do this. For Reporting Services to recognize
there is a different database you must go through the Configure Database
process. In the Navigation panel click Database then click the Change
Database button.
The following images show the screens and settings when configuring the database.
Now restore the Encryption Key from the database you restored. In the
Navigation panel of the Reporting Services Configuration Manager select
Encryption Keys and click the Restore Button. In the Restore Encryption
Key dialog click the ... button to locate the file you created when
backing up the Encryption Key from the server you are migrating, enter
the password you used and click the OK button.
Now this is where I had trouble. According to the Migration document,
once the Encryption Key has been restored things should be working.
When I tried to access my Report Server I got an error stating 'The
feature: "Scale-out deployment" is not supported in this edition of
Reporting Services. (rsOperationNotSupported)'. I opened Reporting
Services configuration Manager and clicked Scale-out Deployment in the
navigation panel and saw that there were two servers listed, the old one
and the new one.
I did not find any mention of this in the migration document. After
searching the internet I found some articles describing this issue and
the fix. There are a couple of ways to fix this but the simplest is to
delete the record corresponding to the old server in the Keys table of
the ReportServer database. Once I deleted the record for the old server
Reporting Services started working as expected.
Next Steps
- Review the Migration document. Click this link.
- As always test on a test server before using in production.
- Be sure to inspect and test the new reporting server.
- For more information check these references.
No comments:
Post a Comment