setting up Log shipping between 2 sql server 2016 servers I want my databases read-only standby on the secondary server.
there is a tick box saying Disconnect users in the database when restoring backups.
what if I don't tick this box? The restore of the log would not come trough because it could not get exclusive lock on the database?
But then, what if someone is logged in for hours? Hours without restoring the logs?
From this page in the MS documentation:
There are two options for configuration when you place the secondary database in standby mode:
You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.
You can choose not to disconnect users. In this case, 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.
So yes, it could be hours, days, weeks, or years without a restore if users are still connected to the secondary.
what if I don't tick this box? The restore of the log would not come trough because it could not get exclusive lock on the database?
Another reason that I can think of is -
The .tuf file (transaction undo file) that gets generated when you configure logshipping in stand-by mode will keep increasing as no restore is happening due to users being connected to the stand-by server and eventually you will run out of disk space.
Also, having configured logshipping in standby mode, you should be aware that the logs get restored slower when logshipping runs in standby mode --> Think about your RTO !
