Out of the three deployment solutions for which you can use log shipping, using it as a report database solution is the least effective. However, it does have its advantages. Log shipping is a low cost solution, leverages inexpensive hardware and it is a simple solution to implement and manage. Therefore, in certain scenarios, it may be feasible to use the secondary server’s database for production reporting, provided that the database recovery mode is STANDBY. However, there are several inherent disadvantages to using this server for reporting.
The restore process needs exclusive access to the database while restoring; if users run reports, the restore process fails to restore and the job waits for the next restore interval to try again. Log shipping alerts may trigger, sending an alert that the secondary server has fallen behind. Moreover, at the time of role-switching, there may be transaction logs that have not been applied because reporting prevented it, which increases the role-switching time because these transaction logs are applied. However, log shipping can be configured to disconnect users who are in the database to restore the transaction logs, but longer-running reports may be kept from completing in that case. As an example, if you have the restore run every 10 minutes, but you have a report that takes 30 minutes to complete, the report would never run to completion because log shipping would kill the connection every 10 minutes. To improve the chances that the report will run to completion, the restore job interval would have to be longer, which makes the secondary server fall further behind. Additionally, the data for the reports will not be current; and the secondary server’s database schema cannot be optimized for reporting because it is read-only. For example, if particular indices are beneficial to the report database, the indices need to be created in the primary server’s database, which may suffer from having the additional indices.
For these reasons, using log shipping for reporting has several challenges and does not make a good reporting solution for some environments. For occasional reporting, provided the organization can live with these challenges, it is possible to use log shipping for reporting; however, a better report solution may be transactional replication, which provides concurrency, granularity, and near real-time synchronization, with the added flexibility to allow modification of the database schema.
NOTE
The fact that log shipping must have exclusive access to the database can be a big disadvantage. If you apply logs every hour, then any current running report must be killed before the log can be applied. This kind of partial access inconveniences users and might cause them to bypass the use of log shipping for reporting purposes many times. However, if you can live with this issue, then log shipping is a viable solution.
When it comes to log shipping from a primary which cannot see the target server, transactional replication would not work either.
I have implemented a log shipped reporting process as follows:
1. Log ship log files to network share.
2. sftp log files to local share.
3. log ship restore with standby to local server.
4. copy out data since last restore to local report database from standby/ read only database.
5. users can now query local report database without worrying about getting kicked off of standby database as not using it.
Not an ideal solution I know, but it got around the issue of the primary server not being able to see the reporting server. (two different companies, two different domains, one dataset to work off of).