September 20, 2011 at 8:46 pm
Comments posted to this topic are about the item Database Mirroring State
September 20, 2011 at 8:48 pm
Nice question. Thanks for putting it together.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 20, 2011 at 10:38 pm
Your question is quite unclear.
1. What do you mean by pausing?
2. Read from this section from BOL:
If database mirroring is removed completely, the mirror database is in a recovery state and must be restored in order to become functional. The behavior of the recovered database with respect to replication depends on whether the KEEP_REPLICATION option is specified. This option forces the restore operation to preserve replication settings when restoring a published database to a server other than that on which the backup was created. Use the KEEP_REPLICATION option only when the other publication database is unavailable. The option is not supported if the other publication database is still intact and replicating. For more information about KEEP_REPLICATION.
Please clarify!
September 20, 2011 at 11:29 pm
There is a "slight" difference between "pausing database mirroring" and "If the mirror is unavailable".
Better link ?
http://msdn.microsoft.com/en-us/library/ms190664.aspx
/T
September 21, 2011 at 12:05 am
Chris, The question could have been more elaborate. As there are different operating modes in database mirroring, it is quite tough to choose the right answer. Anyways, got to know something regarding the mirroring\replication combination. Thanks.
M&M
September 21, 2011 at 12:15 am
I did know that transactional replication depends on the state of the mirror.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
September 21, 2011 at 2:15 am
Good stuff Chris, nice question.
September 21, 2011 at 2:27 am
Sudhir Dwivedi (9/20/2011)
Your question is quite unclear.1. What do you mean by pausing?
2. Read from this section from BOL:
If database mirroring is removed completely, the mirror database is in a recovery state and must be restored in order to become functional. The behavior of the recovered database with respect to replication depends on whether the KEEP_REPLICATION option is specified. This option forces the restore operation to preserve replication settings when restoring a published database to a server other than that on which the backup was created. Use the KEEP_REPLICATION option only when the other publication database is unavailable. The option is not supported if the other publication database is still intact and replicating. For more information about KEEP_REPLICATION.
Please clarify!
What I mean by pausing is if you run the below on against a mirrored database.
ALTER DATABASE DatabaseName SET PARTNER SUSPEND;
Granted I should have used the word suspend instead of pause.
As the mirror partnership is not synchronised and running in High-safety mode without automatic failover - See the below from BOL from the link i provided with the answer.
All committed transactions are guaranteed to be hardened to disk on the mirror. The Log Reader Agent replicates only those transactions that are hardened on the mirror. If the mirror is unavailable, the principal disallows further activity in the database; therefore the Log Reader Agent has no transactions to replicate.
It is this that the question is refering to. This is however assuming that you haven't enabled trace flag 1448 😉
http://support.microsoft.com/kb/937041
Thanks
Chris
September 21, 2011 at 2:27 am
SQLRNNR (9/20/2011)
Nice question. Thanks for putting it together.
Thanks
September 21, 2011 at 2:30 am
tommyh (9/20/2011)
There is a "slight" difference between "pausing database mirroring" and "If the mirror is unavailable".Better link ?
http://msdn.microsoft.com/en-us/library/ms190664.aspx
/T
I agree, but the effects with regard to transactional replication are the same if it is "paused" or "Unavailable". Good link though added to my favourites.
Thanks
Chris
September 21, 2011 at 2:53 am
Thank you for the question
I have got the answer from msdn which says:
"Some of these factors, such as a very long-running transaction or a paused database mirroring session, can cause the transaction log to fill. "
here: http://msdn.microsoft.com/en-us/library/ms345414.aspx
Regards,
Iulian
September 21, 2011 at 3:47 am
Good question Chris
Ant
September 21, 2011 at 4:10 am
chris.mcgowan (9/21/2011)
What I mean by pausing is if you run the below on against a mirrored database.ALTER DATABASE DatabaseName SET PARTNER SUSPEND;
Granted I should have used the word suspend instead of pause.
As the mirror partnership is not synchronised and running in High-safety mode without automatic failover - See the below from BOL from the link i provided with the answer.
All committed transactions are guaranteed to be hardened to disk on the mirror. The Log Reader Agent replicates only those transactions that are hardened on the mirror. If the mirror is unavailable, the principal disallows further activity in the database; therefore the Log Reader Agent has no transactions to replicate.
Since the whole thing of QotD is about learning, I'm still not clear with the explanation given:
1. Where in the QotD is stated that the database is running in High-safety mode without automatic failover?
The link provided with the explanation lists this option as the last one.
2. If the principal disallows further activity in the database, it is my understanding that there aren't any transactions. Why is it then that the transaction replication is affected? How is it affected?
Thanks in advance for your clarification.
Regards,
Michael
September 21, 2011 at 5:30 am
good question!
September 21, 2011 at 7:01 am
Good question.
The explanation would have been better if it said that replication would be delayed if mirroring was paused in High Performance Mode or in High Safety Mode without automatic failover (since transactions will not be hardened on the mirror while mirroring is paused), and stated also what happens if mirroring is paused in High Performance mode with automatic failover (which I believe is that replication continues as normal but with reduced replication latency - but I can't find a reference to back that up).
Tom
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply