Search This Blog

Friday, November 8, 2013

Set up the report subscriptions on another server in the Disaster Recovery location

It depends if you can take your current Report server off line or not. If you can take your reporting server off line, you can use DETACH/ATTACH metod.
This approach preserves permissions in the databases. If you are using a SQL Server 2008 database, you must move it to another SQL Server 2008 instance. After you move the databases, you must reconfigure the report server connection to the report server database. If you are running a scale-out deployment, you must reconfigure the report server database connection for each report server in the deployment.
Use the following steps to move the databases:
1.     Backup the encryption keys for the report server database you want to move. You can use the Reporting Services Configuration tool backup the keys.
2.     Stop the Report Server service. You can use the Reporting Services Configuration tool to stop the service.
3.     Start SQL Server Management Studio and open a connection to the SQL Server instance that hosts the report server databases.
4.     Right-click the report server database, point to Tasks, and click Detach. Repeat this step for the report server temporary database.
5.     Copy or move the .mdf and .ldf files to the Data folder of the SQL Server instance you want to use. Because you are moving two databases, make sure that you move or copy all four files.
6.     In Management Studio, open a connection to the new SQL Server instance that will host the report server databases.
7.     Right-click the Databases node, and then click Attach.
8.     Click Add to select the report server database .mdf and .ldf files that you want to attach. Repeat this step for the report server temporary database.
9.     After the databases are attached, verify that the RSExecRole is a database role in the report server database and temporary database. RSExecRole must have select, insert, update, delete, and reference permissions on the report server database tables, and execute permissions on the stored procedures. For more information, see How to: Create the RSExecRole.
10.  Start the Reporting Services Configuration tool and open a connection to the report server.
11.  On the Database page, select the new SQL Server instance, and then click Connect.
12.  Select the report server database that you just moved, and then click Apply.
13.  On the Encryption Keys page, click Restore. Specify the file that contains the backup copy of the keys and the password to unlock the file.
14.  Restart the Report Server service.


No comments:

Post a Comment