Peer to Peer replication Issue : not a single failure error is there

Question

Peer to Peer replication Issue : not a single failure error is there

Bhuvnesh

SSC Guru

Points: 59351
More actions
January 13, 2012 at 12:41 am

#249073

I got stuck very badly as i have implemented the P2P transactional replication but its not working as i have tested it with a deleted data
i have deleted some data from server A but its not relfecting at server B , i also tried the vice versa too.
all the jobs are running successfully.
NO issues or error captured at replication monitor.
NO issue at "snapshot agent " or "log reader agent"
can anybody tell me how to troubleshoot.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

Use the error logs and DMVs to check the LSNs on the primary and mirror, make sure they match - it could be that you have a delay specified somewhere. I have a setup where a 24-hour delay is introduced with a log-shipping configuration so that there's an option to recover if bad data or troublesome DROPs/DELETEs are fed into the primary.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Error logs doesnt show any thing.can you tell me DMvs , i can use ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 3

ys.dm_db_mirroring_connections for mirroring, this will give you a lot of useful stats about active/passive/history of connections and parameter settings.

Check the logs on the secondary, you should see some entry there that log entry with LSN xxx was applied on ddd

Use the log shipping monitor if this is the method you are using.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

derek.colley (1/13/2012)
ys.dm_db_mirroring_connections for mirroring

we are using transactional replication, no data in this(your) query 🙁

derek.colley (1/13/2012)
Use the log shipping monitor if this is the method you are using.

we are not using log shipping

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 5

My apologies, I misunderstood your original question.

According to BOL, propagation from B to A is rarely possible...

"By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber. For more information, see How Transactional Replication Works."

The link is here -> http://msdn.microsoft.com/en-us/library/ms151176.aspx and here -> http://msdn.microsoft.com/en-us/library/ms151706.aspx

I won't pretend I know too much about TR not having implemented it myself so I'll leave it to more experienced heads to guide you...

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

derek.colley (1/13/2012)
My apologies, I misunderstood your original question.
According to BOL, propagation from B to A is rarely possible...

Here i am talking about Peer to peer replication 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

i got succeeds to implement it but following error now i got

while inserting from server A to B

Error messages:
A conflict of type 'Insert-Insert' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000002add and peer 1 (on disk), transaction id 0x000000000000280d (Source: MSSQLServer, Error number: 22815)
Get help: http://help/22815
A conflict of type 'Insert-Insert' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000002add and peer 1 (on disk), transaction id 0x000000000000280d (Source: MSSQLServer, Error number: 22815)
Get help: http://help/22815

and while inserting from server B to A

Error messages:

Cannot insert duplicate key row in object 'dbo.MSpeer_lsns' with unique index 'uci_MSpeer_lsns'. (Source: MSSQLServer, Error number: 2601)

Get help: http://help/2601

Cannot insert duplicate key row in object 'dbo.MSpeer_lsns' with unique index 'uci_MSpeer_lsns'. (Source: MSSQLServer, Error number: 2601)

Get help: http://help/2601

The procedure sys.sp_MSupdatepeerlsn failed to UPDATE the resource MSpeer_lsns.. Server error = 2601. (Source: MSSQLServer, Error number: 21499)

Get help: http://help/21499%5B/quote%5D

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Divine Flame SSCoach Points: 15943 More actions · Answer 8

Bhuvnesh (1/16/2012)

i got succeeds to implement it but following error now i got

while inserting from server A to B

Error messages:
A conflict of type 'Insert-Insert' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000002add and peer 1 (on disk), transaction id 0x000000000000280d (Source: MSSQLServer, Error number: 22815)
Get help: http://help/22815
A conflict of type 'Insert-Insert' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000002add and peer 1 (on disk), transaction id 0x000000000000280d (Source: MSSQLServer, Error number: 22815)
Get help: http://help/22815

and while inserting from server B to A

Error messages:
Cannot insert duplicate key row in object 'dbo.MSpeer_lsns' with unique index 'uci_MSpeer_lsns'. (Source: MSSQLServer, Error number: 2601)
Get help: http://help/2601
Cannot insert duplicate key row in object 'dbo.MSpeer_lsns' with unique index 'uci_MSpeer_lsns'. (Source: MSSQLServer, Error number: 2601)
Get help: http://help/2601
The procedure sys.sp_MSupdatepeerlsn failed to UPDATE the resource MSpeer_lsns.. Server error = 2601. (Source: MSSQLServer, Error number: 21499)
Get help: http://help/21499%5B/quote%5D

Ensure you have set following two properties to TRUE in Subscription Options tab(You can found this tab in Publication Properties) :

1. Allow peer-to-peer conflict detection

2. Continue replication after conflict detection

Sujeet Singh

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Thanks divine but seconds error is still there .

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Divine Flame SSCoach Points: 15943 More actions · Answer 10

Bhuvnesh (1/16/2012)
Thanks divine but seconds error is still there .

May be you have mistakenly tried to insert the same row on both servers. i.e. you have inserted the row on node A & it has been replicated to node B. Now, when you will you try to insert the same row on B, you will get this error as this is already there.

Sujeet Singh

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 11

Can you please explain the table "Select * from MSpeer_lsns"

data from above query on server A

idlast_updatedoriginatororiginator_dboriginator_publicationoriginator_publication_idoriginator_db_versionoriginator_lsnoriginator_versionoriginator_id

12012-01-16 13:30:33.523INBKUMAR02\SQL2008R2CVENT_DM70DM701-10249085220x000000EF00000D9200011710505601

32012-01-16 14:16:52.083INKMEHTANI02\SQL2008R2CVENT_DM70CVENT_DM701-4374076480x001710505602

22012-01-16 13:40:37.847INKMEHTANI02\SQL2008R2CVENT_DM70DM7019609388030x000000EF0000068900041710505602

data from above query on server B

idlast_updatedoriginatororiginator_dboriginator_publicationoriginator_publication_idoriginator_db_versionoriginator_lsnoriginator_versionoriginator_id

12012-01-16 19:06:13.110INBKUMAR02\SQL2008R2CVENT_DM70CVENT_DM701-10249085220x001710505601

22012-01-16 19:21:33.477INKMEHTANI02\SQL2008R2CVENT_DM70CVENT_DM701-4374076480x000000F20000013000161710505602

Please give advice from above results why i am getting issues.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ryan13in SSC Enthusiast Points: 145 More actions · Answer 12

Hi had this same issue , where it kept complaining that the the record could not be inserted into the table because of a unique index violation. If you are trying to reestablish P2P replication then delete all the records from table dbo.MSpeer_lsns for this publication ( This table exists on the Publication database ) . Records are inserted back into this table once the publication and subcription are reestablished.

The records appear as orphaned in the table when the replication is broken , and the replication is not dropped properly . Replication tries to insert the same record when you try to reestablish replication and it results in a unique index violation. Hope this works for you

Regards

Ryan