Peer-to-Peer – the “hidden” column

Sometimes it’s useful to know how peer-to-peer really works under the bonnet. This is for troubleshooting. Or just out of interest if you’re feeling geeky :). To see how rows are tracked we’ll need to make sure the dedicated admin connection is working:

exec sp_configure ‘show advanced options’, 1
exec sp_configure ‘remote admin connections’, 1

Connect up to your SQL instance using the DAC eg Admin:Servername

Now you’ll have access to a special hidden column: $sys_p2p_cd_id. In the case of my table tRegion I run:

select *, $sys_p2p_cd_id as HiddenColumn from [TestP2Pdb]..tRegion

Lo and behold I can see some funky numbers in this column….

To find out what it means, and how this data is used, we run the following query:

select originator_node, res.* from
select *, $sys_p2p_cd_id as HiddenColumn
,sys.fn_replvarbintoint($sys_p2p_cd_id) as OriginatorID
,sys.fn_replp2pversiontotranid($sys_p2p_cd_id) as TranID
from tregion
) res
inner join dbo.MSpeer_originatorid_history poh
on res.OriginatorID = poh.originator_id

The query above reflects the fact that the hidden column $sys_p2p_cd_id actually holds 2 separate bits of info: the node which made the change and the id of the transaction itself.

The internal function sys.fn_replvarbintoint is particularly useful here as this returns the Originator_ID. In the query above I have joined the inner results to the MSpeer_originatorid_history table in order to get the actual peer hostname (originator_node) so you can see what is going on, but the numerical Originator_ID is still important itself. This is because it is used in the conflict resolution if p2p_continue_onconflict is set to true – the Originator_ID with the highest value wins in a conflict.

Looking a bit deeper…

At this stage you might ask how can this possibly work? If the TranID changes when a row changes, the value in $sys_p2p_cd_id must change, so the value of $sys_p2p_cd_id between one changed node and another (not changed) will naturally be different. If we look for differences in $sys_p2p_cd_id as an indication that there is a conflict, then all data changes should produce such a conflict!!!

Actually, the log reader here does something quite clever. When the data change is first made, the old and the new values of the $sys_p2p_cd_id column are stored in the MSRepl_Commands table. We can use sp_browsereplcmds to see that these values are added to the arguments for the internal stored procedure sp_MSupd_dbotRegion. This way the old value of $sys_p2p_cd_id can be checked against the value at the destination, because this should still be identical. If it isn’t then there is a conflict and we then go on to take a look at the 2 Originator_IDs and check for the highest value which will predominate.

Peer-to-Peer – interesting insert-insert conflicts

Understanding conflict resolution is important in peer-to-peer if you intend to enable it. There is an argument that you should enable conflict detection and not the resolution. Knowing that a conflict has happened allows you to investigate and then to initialize one of the nodes after plugging the hole in the application logic. Even so, some people have conflict resolution enabled. It certainly helps with update-update conflicts (provided you assign the OriginatorIDs correctly – see related post on this site). For insert-insert conflicts you’d expect a failure which can’t be resolved. That’s what used to happen.

Consider a simple table tRegion. We have a PK on the RegionID column and one site inserts “Rome” and the other node “Paris”. Both have a RegionID value of 2 so there will be a conflict when we synchronize.



Synchronization at each site looks pretty innocuous:

The nodes are defined as below – the publisher has an OriginatorID of 100 and the subscriber a value of 1 so the publisher will win any conflicts.

The conflict viewers show us an interesting story:

The crucial bit is that “The incoming insert was applied to peer 1”!!! In other words we now have data in sync and both regions are “Rome”. How is this possible?

Well the magic is in the insert stored procedure -“sp_MSins_dbotRegion1335813640” in this case. It will convert the insert into an update for the lower OriginatorID where there is an insert-insert conflict :).

Note that this could have unintended consequences! Foreign key records continue to point at this PK record. We could have eg the case that all the Paris streets added at the subscriber now belong to the Rome region. So – be very careful if you are going to use conflict resolution!

Managing Peer to Peer Identity Ranges

If you’re replicating using the peer-to-peer transactional setup, at some stage you’ll want to replicate a table which has identity columns. This is not prohibited but it comes with a warning from Microsoft: “We do not recommend the use of identity columns. When using identities, you must manually manage the ranges assigned to the tables at each participating database.”. This means that out of the box there is no support for identity columns so you’ll have to be careful to avoid any issues……

(1) Reseed the identity column at each peer

We can reseed the identity range manually using DBCC CHECKIDENT. With a little imagination you can assign ranges using the seeds which will never overlap, or use more sophisticated methods based on the seed and increment like e.g. odd and even allocations or even positive and negative allocations. While this works well, there are some obvious issues:

  1. the solution requires a lot of thought if there are many subscribers, in order to assign mutually-exclusive ranges
  2. there is a lack of transparency. Which peers have which ranges? Where is it all being managed from – a spreadsheet somewhere?
  3. Setting up new peers always involves manually reseeding the range.
(2) Use a composite key

The PK could be composed of 2 columns: the identity column and additionally a LocationID column. This means that the identity column is allowed to have overlapping values from different peers and there is no manual reseeding required – the default seed and increment of (1,1) is fine. The combination of the Identity column and the LocationID is always unique across peers, because each peer has a different LocationID. The added benefit is that the record is immediately identifiable in terms of the originator as we simply refer to the value of the LocationID.

(3) Use a persisted computed column

This is similar to the solution above and I came across this from the excellent RBI Dev DBA Group. In this case we now have three columns: the Identity column, the LocationID column and a third persisted computed column that combines the two:

LocationID ID PK_ID
10000 1 10000+1=10001
10000 2 10000+2=10002

The PK_ID is the persisted computed column and is set as the primary key. Now we have a narrow primary key and also a guarantee of uniqueness across peers. To be honest I haven’t used persisted computed columns much and at first I thought that it wouldn’t work because the replication-generated insert stored procedure wouldn’t cater for the automatically-computed PK value, or that the updates to LocationID would be “confused” on synchronization with the PK itself having changed. Anyway, I tested it and it all works fine. The persisted column formula was simply the LocationID + Identity – with a large LocationID as a seed to ensure no overlap.

Conceptually this is very similar to (1) with the LocationID performing a similar function to the seed of the DBCC CHECKIDENT. However IMO it is far more preferable as it is completely transparent. If you chose to implement this solution you’ll just need to allocate a different default LocationID to each peer and ensure they don’t overlap. This can be done via an additional mapping table maintained in the publication which maps HostName to LocationID, and using a scalar function as the default for the LocationID column which performs the lookup. This means that a backup from one server can be used as a starting point for another peer with no intervention at all assuming the mapping table is already populated.

Peer-to-Peer Update Conflicts – Be careful with the OriginatorID!!!

In Peer-to-Peer Transactional Replication we can allow for conflict detection and also to continue after a conflict.

Really changes to the data at different nodes should be partitioned so conflicts are not possible but not everyone sets it up this way so there is a rudimentary conflict resolution mechanism in place for us to use.

For an update-update conflict we’ll see mention of it in the conflict viewer in a format like the one below. One node skips the conflict ie preserves its value, while the other applies the update and so overwrites its value.


In the case above, both records end up being “Madrid” and we have data in sync. But how was this decided? The point to notice is the numbers above “peer 100” and “peer 1”.

We can see which node has which value when looking at the topology:

..and the number 1 was allocated when we set up the subscriptions :

In the conflict, the row that originated at the node with the highest ID wins. The value of 100 is assigned to the Publisher by default, and as above we left the default of 1 at the first subscriber.

Now – here’s the point – the publisher in this scenario will always beat the 1st subscriber. The second subscriber always wins against the first, the third always beats the second and so on. This is not something we can change afterwards.

So, we need to decide which nodes are the most important before we set this up!