Home > sql server > Setup MSSQL replication with peer to peer topology: problem setting up Conflict Detection

Setup MSSQL replication with peer to peer topology: problem setting up Conflict Detection

February 11Hits:1

I'm setting up a SQL Replication strategy, using MSSQL2008 with peer-to-peer publications (2 servers, each one subscribes to the other).
I followed this HOWTO from MSDN, and the setup seems to be working fine: add a record to one table on server A, query on server B shows the new record. So far, so good.
So far I only have one table 'Templates':

Id PK (calculated field)
NodeId int default 1/2 (Server A = 1, Server B = 2)
LocalId int autoid
Name nvarchar(100)

Now, I would like to enable 'Conflict detection', which should be enabled by default. But every time I try to save the 'Conflict Detection' feature in the Publication Properties I get the following error:

Cannot save Peer conflict detection properties.

An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo)

Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)
at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn)
at Microsoft.SqlServer.Replication.TransPublication.SetPeerConflictDetection(Boolean enablePeerConflictDetection, Int32 peerOriginatorID)
at Microsoft.SqlServer.Management.UI.PubPropSubscriptionOptions.SaveP2PConflictDetection()
at Microsoft.SqlServer.Management.UI.PubPropSubscriptionOptions.SaveProperties(ExecutionMode& executionResult)

Column name 'Id' does not exist in the target table or view.
Changed database context to 'TestDB'. (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=1911&LinkId=20476

Server Name: SERVER_A
Error Number: 1911
Severity: 16
State: 1
Line Number: 2

Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Now, I googled the hell out of this error, and nothing shows up. I also can't seem to find out what the exact target table of the error "Column name 'Id' does not exist..." is.
Has anyone every done this successfully? Am I missing something? Having this setup without conflict detection feels pretty useless...

OK, so after some more research and setting up with different databases etc, I found out that the calculated 'Id' column of the Templates table is the culprit. I don't know why, but the replication doesn't seem to allow calculated columns (which are also primary key). It works now too, without the 'Id' column, and using the NodeId and LocalId as a combined PK.
So now the question is, why isn't it allowed to have a calculated column as PK for replication with conflict detection?

Related Articles

  • Setup MSSQL replication with peer to peer topology: problem setting up Conflict DetectionFebruary 11

    I'm setting up a SQL Replication strategy, using MSSQL2008 with peer-to-peer publications (2 servers, each one subscribes to the other). I followed this HOWTO from MSDN, and the setup seems to be working fine: add a record to one table on server A, q

  • Massive peer-to-peer replication topology?June 19

    We have a client that's geographically distributed in remote communities, with somewhat unreliable network/internet connectivity between each of the physical locations. There is a single central location, and 52 satellite locations. Normally, our app

  • peer-to-peer replication in SQL server 2008 R2August 1

    http://www.databasejournal.com/features/mssql/article.php/3814591/Peer-to-Peer-Replication-in-SQL-Server-2008--Configure-a-two-node-topology.htm here i can see peer-to-peer replication option but this options are not in my SQL 2008 r2 standard editio

  • sql server peer to peer replication - have the agents running as NT AUTHORITY\NETWORK SERVICE?February 14

    On a test rig, I'm trying to get SQL Server 2005 peer to peer (transactional) replication working, with the replication agents running under the NT AUTHORITY\NETWORK SERVICE account. Before this I setup a peer to peer replication with the agents runn

  • Is Peer-to-Peer replication a replacement for a merge-replication?October 27

    please help me in evaluating new possibilities with MS SQL Server 2008. For the last 3 years I have had an MS SQL 2000 Server Merge Replication with a Publisher and two Subscribers on duty, the backup of the database on one instance is as big as 19 G

  • SQL 2008 R2 3rd Party Peer-to-Peer Replication, Global Site DistributionMarch 17

    We are looking at hosting 3 globally distributed SQL Server installations at different data centers. The intent is that Site A will serve web traffic and data for a specific region, same with Site B and C. In the case that Site A data center goes dow

  • How to implement Peer to peer database replication between two sites?June 1

    I am looking for SQL Server database solution which can replicate data between two sites vice versa (not just one way) I've looked into SQL Server failover cluster (MSCS) but then since I have two different sites (Production and DR) it would be to ha

  • What is the difference between 'two way transactional replication' and 'peer to peer replication'?September 16

    Can someone explain in detail the exact differences between 2 way transactional replication and peer to peer replication? --------------Solutions------------- generally transactional has a publisher and a subscriber, with some updating from the subsc

  • Peer-to-Peer Transactional Replication Load Balancing in MS SQL Server

    Peer-to-Peer Transactional Replication Load Balancing in MS SQL ServerFebruary 27

    I'm looking at Peer-to-Peer Transactional Replication and it looks like a perfect fit for my requirements, but there's one unclear part - load balancing (see picture below). How it is implemented in SQL Server? Is it programmable? Or it's something t

  • peer to peer RTP call setup in asteriskFebruary 17

    I want to set up call between to peers in asterisk in which RTP flow is between two peers when internal calls.I don't want to go RTP flow from peer-asterisk-peer.I want to setup RTP flow like peer-peer.My sip.conf setup is like this.` [1001] type=fri

  • Conflict handling, SQL Server 2008 R2 Peer to PeerMarch 15

    I have a Peer to Peer replication topology set up between two SQL Server 2008 R2 servers. I was doing some testing on fault tolerance and conflict handling. I have conflict detection enabled, and continue relpication after conflict detected enabled.

  • OpenVPN to connect remote networks, peer to peer instead of client/server possible?January 14

    I would like to connect two remote networks using OpenVPN. I am very new to this we are very small and don't have a sys/network admin. What are the possible setups to have both networks both live on a common LAN via OpenVPN? I would prefer to use the

  • Limit to Windows peer-to-peer network size?October 12

    We have a small office with 8 computers. They are mostly running Windows 7, with 2 running XP and one on Vista. They are all connected via a 16 port gigabit ethernet switch, which is in turn connected to an ADSL router. Also connected to the router a

  • Verifying 10gbe peer to peer settingsDecember 20

    I attempted to get some help with this question a few days back: Peer to peer connection over SFP+ Passive 10Gbe cable It was however closed as a duplicate. Now I have setup the physical connections between the two servers and want to see if I can ge

  • pfsense peer-to-peer OpenVPN not connectingApril 6

    I'm trying to setup a peer-to-peer OpenVPN between two pfsense servers running 2.0.1-RELEASE, but the client keeps getting the connection dropped, with a status of "reconnecting; ping-restart" and nothing appears to be routing between them. Both

  • Best peer-to-peer game architectureJune 21

    Consider a setup where game clients: have quite small computing resources (mobile devices, smartphones) are all connected to a common router (LAN, hotspot etc) The users want to play a multiplayer game, without an external server. One solution is to

  • Is there a pure peer to peer screen sharing app? June 19

    I run a meetup, and we would like to share the presenter's screen for the people in the back. However, wireless in the room doesn't always cooperate, and internet may not be available. Is there a pure peer-to-peer screen sharing application that coul

  • How to configure Unison for real peer-to-peer?April 18

    I have a handful of devices, and am considering keeping them in sync with Unison. The devices are not running all the time, they are switched off a few hours per day, not necessarily at the same time. None of the devices is running 24 hours a day. So

  • When to go peer to peer to an actual server for small business? January 27

    I've been asked by a small business to help them with their network setup when they moving to a new location. Currently everything is peer to peer with a tcp/ip printer. At what point is it worth migrating to something like linux or windows server? T

  • Peer to peer communication in complicated multi-subnets LAN

    Peer to peer communication in complicated multi-subnets LANSeptember 5

    We are developing a P2P application and stucked at the part of making communication between 2 peers which are in same local LAN but 2 different subnets. We know that there are a lot of cases that 2 certain PCs in same LAN are definitely "un-connectab

Copyright (C) 2018 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 1.251 s.