Hi All,
I was working on a customer requirement where they want to enable Database Mirroring and Replication on the same database. I thoughts lets discuss it with you guys:
ISSUE:
========
We want to setup replication and mirroring on the Principal server.
We also like to understand what would be the behaviour when the principal/mirror goes into disconnected state, Principal server becomes unavailable etc.
What will be the effect on Replication?
Research:
============
--Was able to setup the replication and mirroring by following the MSDN article
{
http://msdn.microsoft.com/en-us/library/ms151799.aspx
Replication and Database Mirroring
--Please make sure that the we are going thru this section in order to perform this setup.
--Configuring replication and database mirroring involves five steps.
1.>Configure the Publisher.
2.>Configure database mirroring.
3.>Configure the mirror to use the same Distributor as the principal.
4.>Configure replication agents for failover.
5.>Add the principal and mirror to Replication Monitor.
--Refer to the above article for more details on these steps
}
Setup:
----------
Server1: Publisher/Initial Principal: SQL 2005 (9.00.3310)
Server2: Mirror :SQL 2005 (9.00.3310)
Server3: Distributor and Subscriber: SQL 2005 (9.00.3310)
--We set the PublisherFailoverPartner switch only for as per the article Snapshot Agent (for all publications) Log Reader Agent (for all transactional publications)
--The initial setup looked good. Server1 was mirroring the data to Server2. Also replicating the data to Serevr3
Initiating the failures:
--------------------------
Scenario 1:
=================
Initial Principal goes down and you make the Initial mirror server the new Principal
-----------------------------------------------------------------------------------------------
Taking the Principal Server down:
---------------------------------
1.>Stopped Server1
2.>We see that both Server1 and Server2 go into disconnected state.
3.>We did not have a witness and hence no automatic failover occurred.
4.>The Initial principal is down so has to transfer the role of to using
alter database database_name set partner FORCE_SERVICE_ALLOW_DATA_LOSS
5.>Now the Status of the New Principal is
At this stage even if you get the Initial principal up, we will still see them in the disconnected state.
At this stage, the replication will not work (The data is not pushed to the subscriber) And you would see the following in the verbose logs
{
2009-04-14 23:08:37.328 Connecting to OLE DB T-GURSETHI\GURSETHI2 at datasource: 'T-GURSETHI\GURSETHI2', location: '', catalog: 'sqlnexus', providerstring: '' using provider 'SQLNCLI'
2009-04-14 23:08:37.328 Connection is using failover partner name 't-gursethi\gursethi1' to connect to database'sqlnexus'
2009-04-14 23:08:39.677 OLE DB T-GURSETHI\GURSETHI2: T-GURSETHI\GURSETHI2
...
...
...
2009-04-14 23:09:31.492 Publisher: {call sp_replcmds (500, 0, 0, , 5023, 500000)}
2009-04-14 23:09:36.515 Publisher: {call sp_replcmds (500, 0, 0, , 5024, 500000)}
2009-04-14 23:09:41.530 Publisher: {call sp_replcmds (500, 0, 0, , 5007, 500000)}
2009-04-14 23:09:46.554 Publisher: {call sp_replcmds (500, 0, 0, , 5039, 500000)}
2009-04-14 23:09:46.570 OLE DB DISTOLE GURSETHI2008': sp_MSget_last_transaction @publisher_id = 3, @publisher_db = N'sqlnexus', @for_truncate = 0x1
2009-04-14 23:09:46.571 Publisher: exec sp_replcounters N'sqlnexus'
2009-04-14 23:09:46.572 OLE DB Publisher 'T-GURSETHI\GURSETHI2': exec sp_replcounters N'sqlnexus'
2009-04-14 23:09:46.573 Status: 16384, code: 22522, text: 'Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.'.
2009-04-14 23:09:51.580 Publisher: {call sp_replcmds (500, 0, 0, , 5023, 500000)}
2009-04-14 23:09:56.607 Publisher: {call sp_replcmds (500, 0, 0, , 5024, 500000)}
2009-04-14 23:10:01.622 Publisher: {call sp_replcmds (500, 0, 0, , 5007, 500000)}
}
The Error with which the log reader agent would fail with is:
Indicating that the Principal and Mirror are not in sync and hence the data is not replicated to the subscriber.
In fact the data is not even pushed to the distributor as the agent that’s failing is log reader agent
If we execute on the publisher during this time, we can see the count of increasing
Also ran on the distribution database and confirmed that the new statements/transactions are not yet in there
==>This is as per the expected behaviour. Here are the behaviours observed.
Operating Mode
------------------------
High-safety mode with automatic failover
Transaction Safety is FULL
Mirroring and Replication Behaviour
-------------------------------------------
All the committed transactions are guaranteed to be hardened to disk on the mirror server.
Log reader will only replicate transactions which are hardened on the mirror.
Loss of a partner has the following effect:
1.>If the mirror is unavailable, the Principal continues to serve the database. The Log Reader Agent propagates commands to the distribution database.
But the commands are not yet pushed to the subscriber until the mirror server comes in sync with the principal. Also the principal cannot failover
to the mirror until the mirror is back online and has all transactions from the principal.
2.>If the principal is unavailable, automatic failover occurs. The mirror server switches to the role of principal, and it offers its database as the
principal database. The same points mentioned above in case of still apply here for replication
Operating Mode
------------------------
High-performance mode
Transaction Safety is OFF
Mirroring and Replication Behaviour
--------------------------------------------
Log reader will only replicate transactions which are hardened on the mirror.
Loss of a partner has the following effect:
1.>If the mirror is unavailable, the principal database is running exposed, however replication always runs in SAFE mode i.e., only replicates those
transactions that are mirrored. The Log reader agent doesn’t even push the commands to the distribution database. Be aware that replication latency
will increase if the mirror falls behind the principal.
2.>When the principal fails, the database owner has several choices, as follows:
a.>Leave the database unavailable until the principal becomes available again.
If the principal database and its transaction log are intact, this choice preserves all of the committed transactions at the expense of availability.
b.>Stop the database mirroring session, and then begin a new database mirroring session.
If the principal database is lost but the principal server is still running, immediately attempt to back up the tail of the log on the principal database.
If the tail-log backup succeeds, stopping the session may be your best alternative. After stopping the mirroring session, you can restore the log onto
the former mirror database, which preserves all of the data.
c.>Force service (with possible data loss) on the mirror server.
Forcing service and causes the mirror server to assume the role of principal and serve its copy of the database for clients.
When service is forced, whatever transaction logs the principal has not yet sent to the mirror server are lost.
Therefore, you should limit forced service to situations where possible data loss is acceptable and immediate database availability is critical.
When service is forced and the mirror server assumes the role of the principal, the log reader will work against the mirror and start picking up the new transactions.
But they are not propagated to the subscribers until the previous principal/Current mirror gets in sync with the new principal.
Hence the replication latency will increase if the mirror falls behind the principal.
Operating Mode
------------------------
High-safety mode without automatic failover
Transaction Safety is FULL
Mirroring and Replication Behaviour
-------------------------------------------
All the committed transactions are guaranteed to be hardened to disk on the mirror server.
Log reader will only replicate transactions which are hardened on the mirror.
Loss of a partner has the following effect:
If the mirror is unavailable, the principal disallows further activity in the database; therefore the Log Reader Agent has no transactions to replicate.
Regards
Gursethi
I was working on a customer requirement where they want to enable Database Mirroring and Replication on the same database. I thoughts lets discuss it with you guys:
ISSUE:
========
We want to setup replication and mirroring on the Principal server.
We also like to understand what would be the behaviour when the principal/mirror goes into disconnected state, Principal server becomes unavailable etc.
What will be the effect on Replication?
Research:
============
--Was able to setup the replication and mirroring by following the MSDN article
{
http://msdn.microsoft.com/en-us/library/ms151799.aspx
Replication and Database Mirroring
--Please make sure that the we are going thru this section in order to perform this setup.
--Configuring replication and database mirroring involves five steps.
1.>Configure the Publisher.
2.>Configure database mirroring.
3.>Configure the mirror to use the same Distributor as the principal.
4.>Configure replication agents for failover.
5.>Add the principal and mirror to Replication Monitor.
--Refer to the above article for more details on these steps
}
Setup:
----------
Server1: Publisher/Initial Principal: SQL 2005 (9.00.3310)
Server2: Mirror :SQL 2005 (9.00.3310)
Server3: Distributor and Subscriber: SQL 2005 (9.00.3310)
--We set the PublisherFailoverPartner switch only for as per the article Snapshot Agent (for all publications) Log Reader Agent (for all transactional publications)
--The initial setup looked good. Server1 was mirroring the data to Server2. Also replicating the data to Serevr3
Initiating the failures:
--------------------------
Scenario 1:
=================
Initial Principal goes down and you make the Initial mirror server the new Principal
-----------------------------------------------------------------------------------------------
Taking the Principal Server down:
---------------------------------
1.>Stopped Server1
2.>We see that both Server1
3.>We did not have a witness and hence no automatic failover occurred.
4.>The Initial principal is down so has to transfer the role of
alter database database_name set partner FORCE_SERVICE_ALLOW_DATA_LOSS
5.>Now the Status of the New Principal is
At this stage even if you get the Initial principal up, we will still see them in the disconnected state.
At this stage, the replication will not work (The data is not pushed to the subscriber) And you would see the following in the verbose logs
{
2009-04-14 23:08:37.328 Connecting to OLE DB T-GURSETHI\GURSETHI2 at datasource: 'T-GURSETHI\GURSETHI2', location: '', catalog: 'sqlnexus', providerstring: '' using provider 'SQLNCLI'
2009-04-14 23:08:37.328 Connection is using failover partner name 't-gursethi\gursethi1' to connect to database'sqlnexus'
2009-04-14 23:08:39.677 OLE DB T-GURSETHI\GURSETHI2: T-GURSETHI\GURSETHI2
...
...
...
2009-04-14 23:09:31.492 Publisher: {call sp_replcmds (500, 0, 0, , 5023, 500000)}
2009-04-14 23:09:36.515 Publisher: {call sp_replcmds (500, 0, 0, , 5024, 500000)}
2009-04-14 23:09:41.530 Publisher: {call sp_replcmds (500, 0, 0, , 5007, 500000)}
2009-04-14 23:09:46.554 Publisher: {call sp_replcmds (500, 0, 0, , 5039, 500000)}
2009-04-14 23:09:46.570 OLE DB DISTOLE GURSETHI2008': sp_MSget_last_transaction @publisher_id = 3, @publisher_db = N'sqlnexus', @for_truncate = 0x1
2009-04-14 23:09:46.571 Publisher: exec sp_replcounters N'sqlnexus'
2009-04-14 23:09:46.572 OLE DB Publisher 'T-GURSETHI\GURSETHI2': exec sp_replcounters N'sqlnexus'
2009-04-14 23:09:46.573 Status: 16384, code: 22522, text: 'Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.'.
2009-04-14 23:09:51.580 Publisher: {call sp_replcmds (500, 0, 0, , 5023, 500000)}
2009-04-14 23:09:56.607 Publisher: {call sp_replcmds (500, 0, 0, , 5024, 500000)}
2009-04-14 23:10:01.622 Publisher: {call sp_replcmds (500, 0, 0, , 5007, 500000)}
}
The Error with which the log reader agent would fail with is:
Indicating that the Principal and Mirror are not in sync and hence the data is not replicated to the subscriber.
In fact the data is not even pushed to the distributor as the agent that’s failing is log reader agent
If we execute
Also ran
==>This is as per the expected behaviour. Here are the behaviours observed.
Operating Mode
------------------------
High-safety mode with automatic failover
Transaction Safety is FULL
Mirroring and Replication Behaviour
-------------------------------------------
All the committed transactions are guaranteed to be hardened to disk on the mirror server.
Log reader will only replicate transactions which are hardened on the mirror.
Loss of a partner has the following effect:
1.>If the mirror is unavailable, the Principal continues to serve the database. The Log Reader Agent propagates commands to the distribution database.
But the commands are not yet pushed to the subscriber until the mirror server comes in sync with the principal. Also the principal cannot failover
to the mirror until the mirror is back online and has all transactions from the principal.
2.>If the principal is unavailable, automatic failover occurs. The mirror server switches to the role of principal, and it offers its database as the
principal database. The same points mentioned above in case of
Operating Mode
------------------------
High-performance mode
Transaction Safety is OFF
Mirroring and Replication Behaviour
--------------------------------------------
Log reader will only replicate transactions which are hardened on the mirror.
Loss of a partner has the following effect:
1.>If the mirror is unavailable, the principal database is running exposed, however replication always runs in SAFE mode i.e., only replicates those
transactions that are mirrored. The Log reader agent doesn’t even push the commands to the distribution database. Be aware that replication latency
will increase if the mirror falls behind the principal.
2.>When the principal fails, the database owner has several choices, as follows:
a.>Leave the database unavailable until the principal becomes available again.
If the principal database and its transaction log are intact, this choice preserves all of the committed transactions at the expense of availability.
b.>Stop the database mirroring session, and then begin a new database mirroring session.
If the principal database is lost but the principal server is still running, immediately attempt to back up the tail of the log on the principal database.
If the tail-log backup succeeds, stopping the session may be your best alternative. After stopping the mirroring session, you can restore the log onto
the former mirror database, which preserves all of the data.
c.>Force service (with possible data loss) on the mirror server.
Forcing service and causes the mirror server to assume the role of principal and serve its copy of the database for clients.
When service is forced, whatever transaction logs the principal has not yet sent to the mirror server are lost.
Therefore, you should limit forced service to situations where possible data loss is acceptable and immediate database availability is critical.
When service is forced and the mirror server assumes the role of the principal, the log reader will work against the mirror and start picking up the new transactions.
But they are not propagated to the subscribers until the previous principal/Current mirror gets in sync with the new principal.
Hence the replication latency will increase if the mirror falls behind the principal.
Operating Mode
------------------------
High-safety mode without automatic failover
Transaction Safety is FULL
Mirroring and Replication Behaviour
-------------------------------------------
All the committed transactions are guaranteed to be hardened to disk on the mirror server.
Log reader will only replicate transactions which are hardened on the mirror.
Loss of a partner has the following effect:
If the mirror is unavailable, the principal disallows further activity in the database; therefore the Log Reader Agent has no transactions to replicate.
Regards
Gursethi
2 comments:
Hey Guru,
This article is very good. We had same situation in my project and when i was searching in google i found your article.
This is Sreeni, Your EX-Team member in IBM (pool-9) :-)
Thanks a lot.
-Sreeni
Excellent explanation. Saved me hours tonight. I have this scenario for one of our clients and i was not sure what is causing the delay. I could see the subscriber waiting but was not sure why mirrroring would come in picture for this as these were two differnt setups but thanks for the article. it explained well.
Post a Comment