April 27, 2010 at 4:54 am
We have a principal server S01 and a mirror server S02. Mirroring model is high protection.
The principal also has transactional replication enabled.
If i take down the mirror ( lets say someone unplugs it), then my log will grow as the transactions cannot be saved at the mirror until it comes back online. Im aware of this, but i need to know if the Replication will be affected? can it continue as normal or is it dependant on the mirror being in sync?
Will pausing mirroring(clicking the pause button) allow replication to continue unaffected?
April 27, 2010 at 3:49 pm
It shouldn't matter - as long as the witness is available (if you are using one) then the principal will continue to create log entries.
April 27, 2010 at 4:18 pm
But im using high protection mode, so i dont have a witness.
April 27, 2010 at 4:29 pm
Going out a limb here, but I think it would continue working. The best thing to do is test this in a test environment and see what happens.
April 27, 2010 at 4:36 pm
not possible at present unfortunately as there are no UAT envrionments available. Il see about doing a mockup on my work pc..
April 28, 2010 at 10:42 am
Data is copied to the mirrored server in Synchronous fashion to avoid chances of data loss. In order the data to be committed on principal it should first be commited on mirror. It means your applications have to wait for that long till it receive confirmation.
However Transactional Replication only copies committed transactions from the transaction log file. Since the mirror is not available and the transaction on principal are not committed therefore transactions will not be replicated.
But when the Mirror comes back online, the transactions will be applied one by one to mirror and then committed on principal which will later be replicated also.
April 28, 2010 at 10:55 am
aqkhan_k (4/28/2010)
Since the mirror is not available and the transaction on principal are not committed therefore transactions will not be replicated.But when the Mirror comes back online, the transactions will be applied one by one to mirror and then committed on principal which will later be replicated also.
Are you sure that's true? I'm pretty sure (not 100% positive) that in synchronous mirroring without a witness, the mirroring session will "timeout" after not being able to connect to the mirror server for a certain period of time, after which it will go ahead and start committing transactions on the principal to allow it to function. Then it will catch up after the mirror comes back online.
Not sure about the question of replication breaking or not though.
The Redneck DBA
April 28, 2010 at 11:07 am
Jason Shadonix (4/28/2010)
aqkhan_k (4/28/2010)
Since the mirror is not available and the transaction on principal are not committed therefore transactions will not be replicated.But when the Mirror comes back online, the transactions will be applied one by one to mirror and then committed on principal which will later be replicated also.
Are you sure that's true? I'm pretty sure (not 100% positive) that in synchronous mirroring without a witness, the mirroring session will "timeout" after not being able to connect to the mirror server for a certain period of time, after which it will go ahead and start committing transactions on the principal to allow it to function. Then it will catch up after the mirror comes back online.
Not sure about the question of replication breaking or not though.
You are 100% correct - with no witness, mirroring is effectively suspended when the principal cannot connect to the mirror.
April 29, 2010 at 3:01 am
aqkhan_k (4/28/2010)
Data is copied to the mirrored server in Synchronous fashion to avoid chances of data loss. In order the data to be committed on principal it should first be commited on mirror. It means your applications have to wait for that long till it receive confirmation.However Transactional Replication only copies committed transactions from the transaction log file. Since the mirror is not available and the transaction on principal are not committed therefore transactions will not be replicated.
But when the Mirror comes back online, the transactions will be applied one by one to mirror and then committed on principal which will later be replicated also.
This is the information i required. thanks a mil. This causes an issue to us because we replicate a large amount of data to related systems, which allows our customers, products etc to be in sync with little latency.
As data is updated in system A, it is replicated to system B. If no transactions are being committed due to the mirror being down, there will be a huge build up of transactions, and when mirroring is finally brought back online, replication will be overwhelmed, and can take an hour or more to catch up. This means systems are out of sync for a period of time longer than we can allow. So although replication does not "break" so to speak, it is severly delayed and then overwhelmed, which is as good as downtime in our system.
Good to know this.
Thanks all.
April 29, 2010 at 4:24 am
winston Smith (4/29/2010)
This is the information i required. thanks a mil. This causes an issue to us because we replicate a large amount of data to related systems, which allows our customers, products etc to be in sync with little latency.
As data is updated in system A, it is replicated to system B. If no transactions are being committed due to the mirror being down, there will be a huge build up of transactions, and when mirroring is finally brought back online, replication will be overwhelmed, and can take an hour or more to catch up. This means systems are out of sync for a period of time longer than we can allow. So although replication does not "break" so to speak, it is severly delayed and then overwhelmed, which is as good as downtime in our system.
Good to know this.
Thanks all.
Not really... It's not true that the mirror being down will cause transactions to not be committed on the principal (System A). If replication is getting messed up by mirroring being down, it's for some other reason.
The Redneck DBA
April 29, 2010 at 6:41 am
Since you are not using a witness, if the mirror databaseis unavailable, the transactions will get committed on the principal database. This will start approximately 10 seconds after the principal stops talking to the mirror (the default timeout).
If you were using a witness server and that server were to go down after both lost contact with the mirror, then the principa database would shutdown until either the mirror database or the witness came back online.
April 29, 2010 at 8:26 am
Lynn Pettis (4/29/2010)
Since you are not using a witness, if the mirror databaseis unavailable, the transactions will get committed on the principal database. This will start approximately 10 seconds after the principal stops talking to the mirror (the default timeout).If you were using a witness server and that server were to go down after both lost contact with the mirror, then the principa database would shutdown until either the mirror database or the witness came back online.
this is what i understood, the principal becomes unavailable for use. This si all detailed in the MS white papaer available from the MS website (cant remember the link, sorry)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 29, 2010 at 8:43 am
Perry Whittle (4/29/2010)
Lynn Pettis (4/29/2010)
Since you are not using a witness, if the mirror databaseis unavailable, the transactions will get committed on the principal database. This will start approximately 10 seconds after the principal stops talking to the mirror (the default timeout).If you were using a witness server and that server were to go down after both lost contact with the mirror, then the principa database would shutdown until either the mirror database or the witness came back online.
this is what i understood, the principal becomes unavailable for use. This si all detailed in the MS white papaer available from the MS website (cant remember the link, sorry)
But since there isn't a witness involved here, the principal database should be just fine if the mirror server spontaneously self destructs for some reason. Which (I think) wouldn't affect replication any.
The Redneck DBA
April 29, 2010 at 10:31 am
When a publication database is mirrored, Log Reader Agent behavior is governed by the mirroring state of the database. By default, the Log Reader Agent can only replicate log records that have been hardened in the log on the mirror server (the process in which the mirror server writes a transaction log record to the transaction log file is termed hardening the log). This means it cannot replicate log records with a Log Sequence Number (LSN) that is higher than the LSN of the last log record that was hardened on the mirror.
It is possible for the principal to become exposed (the mirror is accessible but there are log records that have not yet been hardened on the mirror) or isolated (the mirror is inaccessible). In both cases, if the principal is still able to serve the database, any changes made to the database are not replicated until the corresponding log records are hardened on the mirror.
This behavior introduces latency in the replication stream, and is done so that if a mirroring failover occurs, it is guaranteed that replication Subscribers cannot be “ahead” of the new principal database.
When the Log Reader Agent is waiting for transaction log records to be hardened on the mirror, the following message is entered in the Log Reader Agent history:
Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.
April 29, 2010 at 10:58 am
I looked up that log message and found http://support.microsoft.com/kb/937041
So you can force replication to continue with the trace flag if you have that CU or are on SP3. I guess it makes sense to default it this way.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply