sql server
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.
Since the secondary in a log-shipping configuration is either READ ONLY or RESTORING, you cannot make changes to the database (i.e. create a USER) until a ROLE CHANGE happens (i.e. when you failover from primary to secondary). You can create a LOGIN on the secondary server.
You can use this script - How to transfer logins and passwords between instances of SQL Server to script logins - both SQL and Windows.
If you are using SQL Server 2014, then you can just create a ROLE and then grant that role connect any database and select all user securables. This works on readonly databases in logshipping configuration as well.
Note that above is not that elegant if you keep security in mind, but still is an alternative that can be considered.
FULL backups do not break log shipping. FULL backups simply reset your differential base for all subsequent DIFF backups until the next FULL occurs.
Log shipping occurs in 3 phases:
1- Log backup on the primary
2- Copy log file to destination server (or network share)
3- Restore Log to secondary
If using default settings, these each occur in 15 minute intervals. This means that it can take up to 45 minutes for a log file taken on the primary to be successfully applied to the secondary.
An easy way to verify that things are in sync is use the 'Transaction Log Shipping Status Report' on the secondary server by right clicking the server name in SSMS and navigating to the following:
EDIT: Here's where to look:
Best Answer
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:
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")