asked
Alternate to MERGE Replication
Hi, As we are doing phased rollout of our applications, we want some of the tables in the new and old databases are synchronized with each other. We have a table called ‘Person’ in the old database Contains Database: OLD Table Name : Person Columns: PersonCode int PK, PersonName varchar(100), DateofBirth DateTime, MobileNo varchar(10) Database: NEW Table Name : Person Columns: PersonCode int identity(1,1) PK, PersonName varchar(100), DateofBirth DateTime TableName: PersonContact Columns: ContactId int identity(1,1) PK, PersonCode int FK, MobileNo varchar(10) As an initial step we have migrated the data from OLD database to the NEW database. Now users should be able to add or edit Person & PersonContact in both databases and it should be synchronized. How can we do this? If I choose Merge replication, how it can resolve the PK conflicts such as adding Person at the same time in both places since PersonCode is not identity in the OLD database. What I have given above is just a sample structure and data, do not suggest us to create a view and use it either of these places. We need both tables to be exist in both places. Please advise.
Merge replication is the best way to achieve your goals, Also merge replication has a conflict resolution to deal with Insert\Update\Delete conflicts, When a conflict occurs, the Merge Agent launches the conflict resolver the resolver to determine the conflict winner and the winning row is applied at publisher and the subscribers, and the losing row is written to the conflict table. These conflicts are solved automatically by the merge agent, This behaviour can be changed. So in your case, when a conflict occurs due to PK violation, the winning row is written to the publisher or subscribers (By default its publisher wins) and the losing rows can be found in the conflict table. You can see the the conflict rows in conflict manager, the same can be viewed in replication table MSmerge_conflicts_info, I recommend you to check [How Merge Replication Detects and Resolves Conflicts][1] MSDN article. [1]:
http://msdn.microsoft.com/en-us/library/ms151749(v=sql.105).aspx
9 People are following this question.
