Is it possible to have secondary server available read-only in a log shipping scenario?

C

6

5

I am looking into using log shipping in a SQL Server 2005 environment. The idea was to set up frequent log shipping to a secondary server. The intent: Use the secondary server to serve report queries, thereby offloading the primary db server.

I came across this on a sqlservercentral forum thread:

When you create the log shipping you have 2 choices. You can configure restore log operation to be done with norecovery or with standby option. If you use the norecovery option, you can not issue select statements on it. If instead of norecovery you use the standby option, you can run select queries on the database. Bear in mind with the standby option when log file restores occur users will be kicked out without warning by the restore process. Acutely when you configure the log shipping with standby option, you can also select between 2 choices – kill all processes in the secondary database and perform log restore or don’t perform log restore if the database is being used. Of course if you select the second option, the restore operation might never run if someone opens a connection to the database and doesn’t close it, so it is better to use the first option.

So my questions are:

Is the above true? Can you really not use log shipping in the way I intend?
If it is true, could someone explain why you cannot execute SELECT statements to a database while the transaction log is being restored?

EDIT:

First question is duplicate of this serverfault question. But I still would like the second question answered: Why is it not possible to execute SELECT statements while the transaction log is being restored?

Cornwell answered 2/12, 2009 at 13:18 Comment(0)

M

7

could someone explain why you cannot execute SELECT statements to a database while the transaction log is being restored?

Short answer is that RESTORE statement takes an exclusive lock on the database being restored.

For writes, I hope there is no need for me to explain why they are incompatible with a restore. Why does it not allow reads either? First of all, there is no way to know if a session that has a lock on a database is going to do a read or a write. But even if it would be possible, restore (log or backup) is an operation that updates directly the data pages in the database. Since these updates go straight to the physical location (the page) and do not follow the logical hierarchy (metadata-partition-page-row), they would not honor possible intent locks from other data readers, and thus have the possibility to change structures as they are read. A SELECT table scan following the page next-prev pointers would be thrown into disarray, resulting in a corrupted read.

Matteson answered 2/12, 2009 at 16:43 Comment(0)

R

7

Well yes and no.

You can do exactly what you wish to do, in that you may offload reporting workloads to a secondary server by configuring Log Shipping to a read only copy of a database. I have set this type of architecture up on a number of occasions previously and it works very well indeed.

The caveat is that in order to perform a restore of a Transaction Log Backup file there must be no other connections to the database in question. Hence the two choices being, when the restore process runs it will either fail, thereby prioritising user connections, or it will succeed by disconnecting all user connection in order to perform the restore.

Dependent on your restore frequency this is not necessarily a problem. You simply educate your users to the fact that, say every hour at 10 past the hour, there is a possibility that your report may fail. If this happens simply re-run the report.

EDIT: You may also want to evaluate alternative architeciture solutions to your business need. For example, Transactional Replication or Database Mirroring with a Database Snapshot

Rattling answered 2/12, 2009 at 13:24 Comment(0)

M

7

could someone explain why you cannot execute SELECT statements to a database while the transaction log is being restored?

Short answer is that RESTORE statement takes an exclusive lock on the database being restored.

For writes, I hope there is no need for me to explain why they are incompatible with a restore. Why does it not allow reads either? First of all, there is no way to know if a session that has a lock on a database is going to do a read or a write. But even if it would be possible, restore (log or backup) is an operation that updates directly the data pages in the database. Since these updates go straight to the physical location (the page) and do not follow the logical hierarchy (metadata-partition-page-row), they would not honor possible intent locks from other data readers, and thus have the possibility to change structures as they are read. A SELECT table scan following the page next-prev pointers would be thrown into disarray, resulting in a corrupted read.

Matteson answered 2/12, 2009 at 16:43 Comment(0)

J

3

If you have enterprise version, you can use database mirroring + snapshot to create read-only copy of the database, available for reporting, etc. Mirroring uses "continuous" log shipping "under the hood". It is frequently used in scenario you have described.

Jehoash answered 2/12, 2009 at 14:6 Comment(0)

C

2

Yes it's true.

I think the following happens:
While the transaction log is being restored, the database is locked, as large portions of it are being updated.
This is for performance reasons more then anything else.

I can see two options:

Use database mirroring.
Schedule the log shipping to only occur when the reporting system is not in use.

Corinthian answered 2/12, 2009 at 13:24 Comment(6)

The reason the database is locked is in order to enforce consistency of the database data. – Rattling 2/12, 2009 at 13:25

Surely a DB lock is overkill? if consistency was the issue? Normal locking could apply? or does the locking escalate as the restore occurs, until the entire db is locked? – Corinthian 2/12, 2009 at 13:30

@John Sansom: But when the secondary db is read-only (only SELECTs, no INSERT or UPDATE). Then the consistency reason goes away, doesn't it? – Cornwell 2/12, 2009 at 13:33

@Bravax: I totally agree. There should be an option where you specify that you don't really care about the performance of the transaction log restore, you don't need to lock the entire database, you just want the INSERTs and UPDATEs to be done in the sequence they were done on the primary server. – Cornwell 2/12, 2009 at 13:38

A “very” simplified explanation of the process is that when a Transaction Log restore is being performed, each and every transaction recorded within the log file is replayed on the database. This process means that in order to guarantee the integrity of the database data, access is not granted until the restore has completed. – Rattling 2/12, 2009 at 14:19

Of course. Sorry, I wasn't thinking it through. A DB lock makes sense now. – Corinthian 2/12, 2009 at 14:28

T

0

Slight confusion in that, the norecovery flag on the restore means your database is not going to be brought out of a recovery state and into an online state - that is why the select statements will not work - the database is offline. The no-recovery flag is there to allow you to restore multiple log files in a row (in a DR type scenario) without bringing the database back online.

If you did not want to log ship / have the disadvantages you could swap to a one way transactional replication, but the overhead / set-up will be more complex overall.

Transpontine answered 2/12, 2009 at 13:27 Comment(0)

M

0

Would peer-to-peer replication work. Then you can run queries on one instance and so save the load on the original instance.

Mordent answered 2/12, 2009 at 14:8 Comment(0)

Recommended topics

Hot tags