When log shipping between a primary database and a secondary database, does the secondary database have to be set to read-only?

By read-only, I am not talking about the idea of only reading from the database. I am talking about the database having a specific status of Read-Only or Standby/Read-Only.

I cannot find anything that states that this is required in the Log Shipping Overview and other related pages. This is causing an issue as I am unable to add any indices to a read-only database.


You can't change the secondary database in any way.
This is normal - the secondary database is supposed to be a backup of the main database, so it's not possible to add stuff that's not in the main database. That's why it's in read-only mode.

It's fine to use the secondary database for reporting, to reduce the load on the primary server (see Using Secondary Servers for Query Processing).
But if you need any special indices for that, you have to add them in the primary database and wait until they are log-shipped to the secondary database.

As jchelad already said in his answer, one disadvantage of using the secondary database for reporting is that users are disconnected whenever a log backup is restored.
It's possible to change this, so that restoring is paused until nobody is connected to the database.

Quote from the above link:

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.

But I wouldn't recommend using the second option.
We are using logshipping at work, and we are using the secondary database for reporting.

We decided to choose the first option (users are disconnected when a backup is restored) because having a backup is more important than being able to run queries all the time.

We didn't like the second option (only restore if no one is connected to the database) because in case the main server dies, we don't want to find out that the secondary database wasn't restored in the last few hours because the salespeople have been running queries all day.

We "educated" our reporting users that there is a short time every 15 minutes where they can't run queries, and they accept that.
(the alternative would be to run the queries on a copy of the main database from yesterday evening, but they prefer data from today, so they have to live with the "15 minute break")

  • Thanks for the info. I guess what this boils down to is the fact that log shipping is designed for backup purposes - not reporting. So (although it can be used to reduce the reporting load on the primary database) it is not truly designed for this. One of the big issues we are running into is the fact that the transaction indices and the reporting indices we require are very different and we do not want to add the reporting indices to the primary database (due to size,performance,etc).
    – jzacharuk
    Sep 4 '12 at 16:49
  • Well, it is possible to use it for reporting, if you can live with the downside that querying and restoring isn't possible at the same time. As I said, our reporting users at work accept it as well. And if they would start to complain about it, then it wouldn't be much work to set up a second secondary database - one in "users are disconnected while restoring" mode for the sake of having a backup, and the other in "only restore if no one is connected" mode for reporting. I've already thought about this, but the need isn't there (yet?). It's just a matter of disk space... Sep 4 '12 at 19:02

The secondary database holds the warm standby copy of the primary database. The secondary database may be in either the RECOVERING state or the STANDBY state, which leaves the database available for limited read-only access and that too during the interval between restore jobs. Hope this is what you were looking for

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.