August 9, 2010 at 10:41 am
Hi,
I have a strange question.
As per MSDN to configure Database Mirroring the DB should be in Full recovery mode.
In Mirroring the log buffer is transferred from principal to mirror and my question is, as it is transferring only the log buffer why can't we use the simple recovery mode.
Can any one throw some light regarding the logic behind it.
Regards,
Sandhya.
August 9, 2010 at 12:18 pm
Good question,
my best guess is since a checkpoint flushes the logs this could pose problems....if a log is empty wont be able to send the log info to the mirror.
since the mirror sever still uses logs this could be an issue....
http://msdn.microsoft.com/en-us/library/ms189901.aspx
Database Mirroring Sessions
The mirror server immediately writes the incoming log to disk, where it is held until it is applied to the mirror database. The log waiting on the mirror's disk is known as the redo queue. The amount of unrestored log waiting in the redo queue is an indicator of the time required to fail over to the mirror database. For more information, see Estimating the Interruption of Service During Role Switching.
August 17, 2010 at 3:56 pm
Database mirroring can be configured in warm-stand-by or hot-stand-by mode; in either case, for database mirroring to grantee that no transactions have been missed it requires FULL recovery mode.
In database mirroring, in Synchronous mode, SQL Server sends transaction to Mirrored Partner, waits for confirmations and then confirms it on principal partner.
In Asynchronous mode, SQL Server sends transaction to Mirrored Partner, but does not wait for confirmation and writes it.
So in both cases it needs to have the full transaction information, in case network lag, the principal might be behind transactions which have not been yet sent/committed on mirrored partner. If they allowed database in simple recovery mode it is possible in asynchronous mode that some transactions might be missed there by making the mirrored partner not a true-mirror and cause data inconsistencies.
Why do you want to set the database to simple recovery? You can still do t-log backup so your t-log file should not grow monstrously big.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 17, 2010 at 10:30 pm
This is just my opinion, but I believe it would have been possible for them to do database mirroring with a database in simple recovery mode. It would have been more difficult, but I believe it could have been done.
However it was not done. Mirroring was coded to rely on the full transaction logging mechanism and even though they coded replication to work with simple recovery by fully logging transactions, they didn't with mirroring. For whatever reason it was, we must use Full recovery for now. Who knows what changes the next major version of SQL will bring?
August 17, 2010 at 11:03 pm
sandhyarao49 (8/9/2010)
as it is transferring only the log buffer why can't we use the simple recovery mode.
BUt why do you require this ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 17, 2010 at 11:17 pm
Robert Davis (8/17/2010)
This is just my opinion, but I believe it would have been possible for them to do database mirroring with a database in simple recovery mode.
Think it could have had something to do with minimally logged operations?
Seeing that in bulk-logged and simple the tran log does not contain full info on changes for bulk-operations, to mirror such operations in anything other than full recovery would require more than just the log records to be sent across. Not impossible, but more work than current.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2010 at 7:07 am
Yes, of course. My thinking is simply that it could have been written to work with simple. When you have transactional replication running on a database that is in simple recovery model, it fully logs the transactions just like it does in full recovery model (it just doesn't require a log backup to clear the log). The same could have conceivably been done with mirroring, but it wasn't.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply