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")
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
