You can reduce the load on your primary/production server by using a Log Shipping secondary
server for read-only query. To do this, the secondary
database must be in STANDBY mode. You will not be able to run queries if
the database is in NORECOVERY mode. When you configure log shipping secondary server using SSMS, on the "Restore Transaction Log" tab, choose the "
Standby mode" option. You can also decide whether to disconnect users from the secondary database while the restore operation is underway.
After the log shipping secondary server setup is completed, you can check that the secondary database is in standby/readonly mode:
Now you are able to run readonly query on the secondary database:
If you try modifying data on the secondary database, it will be fail:
If you did NOT enabled the "Disconnect users in the database when restoring backups" option, then the Restore Job cannot restore transaction log backups to the secondary
database if there are users connected to that database. Transaction log
backups will accumulate until there are no user connections to the
database.
After all the connections to the secondary database are disconnected, the restore job will be resumed.
Any data modifications in the primary database will be transferred to the secondary database.
Trick: in order to prevent the readonly query blocks the restore job, you can specify the connection to USE another database, e.g. master, and query the secondary database tables using three-part-name.
