How to set log-shipping on ReportServer (Reporting Service Database)

Question

There are two sql instances

1. SQLInst1 - working as Prod
2. SQLInst2 - working as DR server

On the both servers, we have reporting service databases - Reportserver and Reportservertempdb databases. We want to set-up log-shipping on ReportServer database so that data can be replicated from Prod ReportServer database to DR Reportserver. The reason we want to do like this; at time of DR testing, replicated ReportSErver database have same information as our prod ReportServer has. So below are my points where I am looking for your help;

• Can we set up log-shipping on ReportServer database?
• Do we need to set up log-shipping for both the server (Reportserver and Reportservertempdb)?
• What is purpose of Reportservertempdb database?

Answer 1

You can set up log shipping for the reportserver database like any other database, but there are a few other considerations.

• reportservertempdb

You asked about the purpose of this database. From Books Online:

Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server.

In terms of backing this up, since this only stores transient data you only need one full back up, i.e. log shipping is not required. In fact, Microsoft recommends keeping this in simple recovery mode, so in that case you couldn't have log shipping enabled.

Microsoft also recommends:

If you back up the temporary database and subsequently restore it, you should delete the contents. Generally, it is safe to delete the contents of the temporary database at any time. However, you must restart the Report Server Windows service after you delete the contents.

So in your case you should consider this as part of your failover.

• Encryption key

SSRS uses encryption for storing secure credentials and connection information. When this is enabled this will need to be backed up and applied to the new instance.

To backup the symmetric key, you can use either the Reporting Services Configuration tool or the rskeymgmt utility.

You restore the key in the same way.

See Back Up and Restore Reporting Services Encryption Keys.

• Server side DLLs

Any shared custom code modules, if deployed on the primary server, will need to be available on the failover server.

• Configuration files

Any non-standard settings need to be applied to both the primary and failover servers.

• Report Manager and Web Service URLs

From your question it seems like your SSRS servers have different names. This may cause issues for server-based URLs such as the above. If you restore the Report Server database and its configured URL still includes the old server name, obviously this will cause issues. You will need to consider manually updating this during a failover or configuring a DNS entry in your environment that will be able to resolve this differences - again, this needs to be part of the plan.

• Conclusion

I suggest you familiarise yourself with the information at Backup and Restore Operations for Reporting Services. You can see it's much more than just one database to consider. Even this all assumes everything is own the same domain - not always the case for DR.

As with any DR plan, the only way to know for sure is to test until everything is foolproof.

Answer 2

First of all I will like to tell you what is log shipping.(I assume you don't know the basics)

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances(Generally Hot Site- Fail Safe Servers)). The transaction log backups are applied to each of the secondary databases individually.

The status of backup and restore operations is important, if these operations fail to occur as scheduled may malfunction.

Log shipping consists of three operations:

• Back up the transaction log at the primary server instance.
• Copy the transaction log file to the secondary server instance.
• Restore the log backup on the secondary server instance.

Now, your purpose for the log shipping is to syn data for the testing purpose you can do that and this is the best way as this is not burden on the server as only transactions log is sent over server that saves a lot time than syncing whole Data Base over server.

Yes, you can do log shipping with Reporting server(also with database), if required.

A Typical Log Shipping Configuration

The following figure shows a log shipping configuration with the primary server instance, three secondary server instances, and a monitor server instance. The figure illustrates the steps performed by backup, copy, and restorejobs, as follows:

1. The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.
2. Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.
3. Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.

Now, Monitoring server is optional so you can ignore that, you have only one secondary server so, Primary server will have Log Generation(back up) and Log shipping.

Secondary server will have Restore of Log.

So you need to implement on both server but the work will be different at both ends.