Handling Autogenerated PK/FK Conflicts In Merge Replication
Aug 14, 2007
We use autogenerated primary keys in most of our tables. Some of these keys are also foreign keys in other tables. Right now there is only 1 database sever at a central location. But now there is a need to have multiple database servers at different locations. Data from these remote sites needs to be replicated to the central server. Some data would also distribute from central server to selected remote sites.
If I could resdesign, I would have choosen something like GUIDs for the primary keys or combination of something like ServerName and AutoGenerated number as a combined key. But that's not possible right now. How do I handle merge replication conflicts in this case?
I am looking for some pointers as to how to handle this case. If it were just simple table with 1 primary key, that would be easy as I can throw the primary key on remote server and let the central server create a new key when data is inserted. But in my case, a single table can be related to 5 or more other tables through these autogenerated keys. Any help is much appreciated.
I have SQL CE clients replicating against a SQL Server 2005 db using merge replication. The DB has a table A and a table B, which has a foreign key to table A. It is common in my application for records in table A to be deleted on the server. I'm running into issues when a table A record has been deleted, but table B records were created on the clients which point to that record. When I sync I get a conflict because the table B records cannot be applied at the server, and the table A delete cannot be applied at the client.
What I would like to happen is to have the table B records on the client be deleted by the merge process, and to create a log of the event. I've looked into creating a business logic handler to do this, but I'm not sure what type of conflict this is (UpdateDeleteConflict or otherwise), and I'm not sure that deleting the table B records is something I can do in the business logic handler.
This seems like it would be a common problem in merge replication. I'm not locked into using a custom business logic handler at all. Any suggestions are welcome.
I have set up merge replication between 1 publisher and 9 subscribers (all push subscriptions). Distributor and publisher are located on the same machine.
Although everything seems to work fine from the outside (most of the time), there are a lot of conflicts in the conflict table for the replication. They appear all the time. There are a lot of "download insert failed" conflicts. They always look like in the following three screenshots: http://www.tronk.be/conflicts/conflict1.JPG http://www.tronk.be/conflicts/conflict2.JPG http://www.tronk.be/conflicts/conflict3.JPG
In the same way, there are also many "upload insert failed" conflicts.
In addition to this, there are some "update conflict"s (but a lot less than the other conflicts). Some of them show the same row at both sides: http://www.tronk.be/conflicts/conflict4.JPG Others show a different row at the both sides: http://www.tronk.be/conflicts/conflict5.JPG
The only thing that causes a real problem is the last screenshot, although I don't understand why the other conflicts are there (the inserst statements actually seem to happen anyway, even though there are conflicts). In case of the last screenshot, I can't find any place where an UPDATE actually happens at APP-STB, while I can clearly pinpoint the UPDATE at the other side (which is what actually comes from our program).
One more thing, the system is running on its limits, but all replication seems to be working fine.
I am using SQL Server 2000 Merge Replication. Sometimes when the data is replicated there are conflicts which when examined show it is due to voliation of foreign key constraint. But the data (keys) in already present in the master tables. Is there a way to give an order to the way the tables are replicated. This is so because i think the data in the details table is relpicated first instead of the master table. The conflicts are resolved properly when done using the conflict viewer.
I got a problem with merge replication. I got a central sql server 2005 database on which i got a publication. Also there are 2 sql servers CE which are subscribers to that publication. I need to add some records on both mobile servers indepently but i'm using primary key as a user id in one table. So when i add a user on one PDA i use next available number in column ID. In the same time I add a user on other PDA with the same ID, because I don't know that there is such user with the same ID. Ok then I do synchronization. First PDA synchronizes with server but second tells me that there is a record with the same PK. And my question is. Can these be resolved writing a custom resolver or maybe you know others resolutions, because I think that is a typicall problem but couldn't find any solution other than using ie. HOST_NAME() function.
There is an error in one of my merge publication. The error is,
The change for the row with article nickname 2336003 (test), rowguidcol {436456F0-F5AD-E411-80CF-5CF3FC1D2D76} could not be applied at the destination. Further information about the failure reason can be found in the conflict logging tables.
When i checked my tables I got following values in rowguid column
We are using a mix of SQL 2005 and 2000 servers and our "main" database server is running SQL 2005 x64 (SP2 ver. 3042).
Our system has run perfectly for months, then subsequent to an SP2 update we are seeing several instances where the data record counts are different for several tables among all the servers.
We are using Merge Replication, with no filters and published every 2 minutes.
Publisher is 2005 x64, subscribers SS2000 (SP3) and SS2005 x64. Pull agents, no filters on subscriptions. We are seeing many seemingly random conflicts on between SS2000 subscriber and publisher. It happens on several different tables.
One table is never editted, only inserts happening everywhere and deletes happening on the SS2000 subscriber. Deletes will sometimes generate conflict. Reason is '"he row was deleted at 'CTS11.CTS' but could not be deleted at 'cts4a.cts'. Unable to synchronize the row because the row was updated by a different process outside of replication." CTS11 is SS2000 subscriber, CTS4A is publisher.
Probably unrelated bug but when looking at conflicts on this same table in SS2005 conflict viewer, get error "ID is neither a DataColumn nor a DataRelation for table summary (System.dATA)" and then "Column ID does not belong to table summary (System.Data)". ID column is rowguid, only unusual thing about table is that it has varchar(8000) field plus some other fields.
Other tables generate conflicts with this reason "The row was updated at 'CTS11.CTS' but could not be updated at 'cts4a.cts'. The merge process was unable to synchronize the row." I enabled verbose logging in the merge agent but the log file didn't contain any further explanation.
This same topology and schema worked fine when all publishers and subscribers were SS2000.
Any insight into how to fix this would be appreciated.
Hi, I am using SQL Server 2000 and the clients would like to archive and remove some of the old records from certain tables in the database. I would like to know the methods of setting ID as autogenerated and if the ID will be initialized to 0 again if records are removed, and if autogenerated IDs reuse missing IDs (e.g. I have ID 1-10, remove ID 2, will the next new record be ID 2?)
We need the ID to keep incrementing and not reuse missing IDs, nor reinitialized once we've archive the old records, and I was thinking of storing all the IDs in a table. Just asking for the possibility of SQL Server autogenerated ID does this.
I was looking to modify how the INSERT happens with regards to replication only to find my solution in the proc itself. When I edit the proc this is what I am displayed in SQL QA or EM:
create procedure "sp_MSins_dboMEETING";2 @c1 int,@c2 int,@c3 int,@c4 varchar(250),@c5 datetime,@c6 datetime,@c7 bit,@c8 char(1),@c9 datetime,@c10 bit,@c11 bit,@c12 bit,@c13 datetime,@c14 smallint,@c15 datetime,@c16 smallint,@c17 binary(8),@c18 bit,@c19 bit,@c20 bit,@c21 varchar(1000) as if exists ( select * from "dbo"."MEETING" where "MEETING_ID" = @c1 ) begin update "dbo"."MEETING" set "MEETING_TYPE_ID" = @c2,"MEETING_STATUS_ID" = @c3,"TITLE" = @c4,"START_DATE" = @c5,"END_DATE" = @c6,"PUBLISH_IND" = @c7,"GROUP_IND" = @c8,"PUBLISH_DATE" = @c9,"MY_ADVISORS" = @c10,"SUBMITTED_IND" = @c11,"ACTIVE_IND" = @c12,"CREATE_DATE" = @c13,"CREATED_BY" = @c14,"LAST_UPDATE_DATE" = @c15,"LAST_UPDATED_BY" = @c16,"DATE_INDEXED" = @c17,"ON_DEMAND_IND" = @c18,"NOT_REPORTED_IND" = @c19,"MAJOR_PROJECT_IND" = @c20,"MAJOR_PROJECT_COMMENT" = @c21 where "MEETING_ID" = @c1 end else begin insert into "dbo"."MEETING" ( "MEETING_ID","MEETING_TYPE_ID","MEETING_STATUS_ID","TITLE","START_DATE","END_DATE","PUBLISH_IND","GROUP_IND","PUBLISH_DATE","MY_ADVISORS","SUBMITTED_IND","ACTIVE_IND","CREATE_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","DATE_INDEXED","ON_DEMAND_IND","NOT_REPORTED_IND","MAJOR_PROJECT_IND","MAJOR_PROJECT_COMMENT" ) values ( @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12 ,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21 ) end
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Is the second version listed at the bottom like a comment or can it actually get called? I am going to script out all of these procs and save them, and then remove the first version (listed at the top) and the use the second version since it does what I need. I just thought it was interesting to see two stored procedures in a single definition, never seen the "PROC_NAME";2 notation, have you? If so please tell me what it does, is it just a way to create a second version of the procedure in a comment type fashion or is it used another way?
- Transactional (5 articles - tables only) Users receive data based on their territory #, therefore they receive their customers sets of data. It happens that customer change from one territory to another but not frequently. When it happens, so far so good, the data is redirected to the new salesrep using the model we configured (Territory table with SUSER_NAME() to filter the data).
Ok, here's my problem. Since a while, I can see in the replication monitor that some users seems to log the same conflict again and again (Merge process). I mean, checking the history for many subscribers, there is always the same number in the "Conflict" colums. As an example: - Merge completed after processing 18 data change(s) (4 insert(s), 14 update(s), 0 delete(s), 31 conflict(s)) - Merge completed after processing 27 data change(s) (10 insert(s), 17 update(s), 0 delete(s), 31 conflict(s)) - Merge completed after processing 20 data change(s) (5 insert(s), 15 update(s), 0 delete(s), 31 conflict(s))
and so on...(Those are only 3 historical entries for a single subscriptions but there are many like that, always with the same count of conflict - vary per user). It appears to me that the same conflicts come over and over.
The thing is that if I decide to reinitialize a subscription, conflicts will disappear, therefore I know that it is not a process on the server that keeps changing the data; anyway, even if it was, changes would be applied on the subscription because the server always win in my setup.
Any idea what should I do with this? Any help would be greatly appreciated. Thanks.
I was hoping a sharp eye may be able to pick up on what I am doing wrong here, I am i must be making a comon mistake. If necessary I am happy to create and post a sample if necessary.
Problem: The publication parent table has a filter (defined below) and also has a join filter(defined below) to a child table. When changes are made to the parent and child table and synchronised a conflict is raised. For the child table for some reason the merge agent is saying an explicit update occured at the publisher but the values at the publisher have not changed at all and no sql has been executed to update the publisher rows. This in effect is stopping the changes to the child table being applied until i manually apply them in the conflict resolver. The strange thing is the conflict should not occur in the first place.
Perhaps my SQL server 2005 version? 9.00.2050.00 SP1 Standard Edition
Environment:
SQL Server 2005 - 9.00.2050.00 SP1 Standard Edition SQL Server Mobile Detail:
FKs are
FK: Job.JobScheduleID --> JobSchedule.ID
FK: JobDetail.JobID --> Job.ID
All three tables have int based IDs that are auto increment.
Publication Articles:
JobSchedule is download only
Job is Bidirectional, identity range management is MANUAL (only updates occur on this table)
JobDetail is Bidirectional, identity range management is MANUAL (only updates occur on this table)
Filters are of the following form:
Filter Job:
SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL AND
( [JobScheduleID] in (SELECT distinct ID from JobSchedule where GETDATE() BETWEEN [JobSchedule].[start] AND [JobSchedule].[end]) )
Join Filter Job --> JobDetail
SELECT <published_columns> FROM [dbo].[Job] INNER JOIN [dbo].[JobDetail] ON [Job].[ID] = [JobDetail].[JobID] Now the first thing to note with respect to using GetDate() i have read http://msdn2.microsoft.com/en-us/library/ms365153.aspx so i thought that i would remove that portion of the Filter on the Job table just to see what happens.
SELECT <published_columns> FROM [dbo].[Job] WHERE convert(nchar,[companyID])=Host_Name() AND [JobCompletedDate] IS NULL
This still did not resolve the issue. I then Remove the AND [JobCompletedDate] IS NULL and it then started working fine (cool) but of course no longer satisfys the rule i want to create (seriously uncool ).
I have a custom conflict resolver - which fires on update conflicts (using row level tracking)
I have had a couple of occasions when the resolver has failed with the following error:
"The schema of the custom Dataset object implemented in the business logic handler does not match the schema of the source Dataset object. Verify that the custom Dataset object has been correctly defined"
In both cases I found that the row for which a conflict was being handled was not a conflict at all. One was a straightforward non conflicting update at the publisher and the other was a similar update at the subscriber.
I got round the problem by temporarily using a fix version of the conflict resolver dll that either set the custom Dataset to the publisher dataset or the subscriber dataset - depending on where the update had occurred.
When the first error (publisher update) occurred - the resolver code was basing the custom dataset on the publisher dataset - which was presumably empty - so I changed the code to base the custom dataset on the subscriber dataset. The second error therefore occurred when the custom dataset was based on the subscriber dataset - which again was presumably empty
Note that the tables involved in each occasion were different and neither table is filtered.
Is there a known bug in this area?
I am considering trying to change the resolver code to identify false conflicts in order to workround the problem - but this would be difficult to test as I can't reproduce the problem
Can someone who has had direct experience with this tell me exactly what happens when a conflict (updating same record on two nodes at the same time) occurs in a P2P replication topology? Does the Dist. Agent throw an error? More importantly does the replication set continue to replicate the articles after any error occurs?
Hi, I need to set an alert when a replication-conflict occurs rather that to check for conflicts manually. How can i accomplish this? I couldn't find the particular error-message to trap in an alert.
Sadly the hostnames for the boxes I am trying to replicate are similar:
- db1.nyc.mydomain.tld - db1.sac.mydomain.tld
The servers can talk to each other over all necessary ports however when I generate a push subscription from the publisher, I am asked to add the SQL Server subscriber and it already shows DB1 listed since itself is DB1.nyc. How can I attach the remote subscriber when setting up the subscription from the publisher if the hostname conflicts in my unique scenario?I created a entry on the publishers host file to use db2sac for the SAC IP and entering that alias in as the remote subscriber but no dice.
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example... Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
Hi everbody, I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database. I added filelds many times but this time i got errors.
I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.
I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher. "Transaction cannot start while in firehose mode"
Hi, I read some where that replication has two types conflict resolution, 1. row based and 2. Column based... If I am right... Can any one point me how to find out this option and how to set it up....
I have a merge replication going between 4 servers. The problem is when ever I do some BCP transfer to one of the tables in one of the servers. It puts the data in that table. But that Data does not get replicated to any other server like it should.
Please Advice on what to do. Is there any option I am forgetting to set or something.
I have successfully tried merge replication on single server with 2 databases. now i want to do the same with different servers, when i create pull subcription on server 2 which user account should I use? it is giving log in failure i tried using windows admin account and also the 'sa' account.
I have posted this earlier and I am re-posting it simplifying what I had said.
The scenario is:
I have two sql server database instances with the same database schema and all. However, both of them have different data. I have not set them for replication at all. Now, I want to do merge replication between them such that the data between them could be syncronized.
When I do pull merge subscription I have two choices - 1.Bringing schema and data to subscriber from publisher
2.Not bringing the schema and data from publisher to subscriber.
Obviously, I chose the second choice. But upon syncronizing I dont see any data from publisher coming to subscriber and vice-versa. If I add new data to publisher and do syncronization, I can see ONLY the new data created after the replication setup in subscriber. If I add new data to subscriber and do syncronization then the new data is removed from subscriber and not propagated to publisher.
I have just set up Merge replication, I have two servers, server A and server B, the merge replication worked successfully but I don't quite sure which databases should or should not replicated? If not, what other methods should I use?
I would really appreciated any comments or advice out there!
I had set up merge replication. I got these error messages where replications starts "Column names in each table must be unique. Column name 'PubID' in table 'bonflict_DBName_PHP_Data_Publications' is specified more than once "
PHP_Data_Publications table defind as:
CREATE TABLE [dbo].[PHP_Data_Publications] ( [PHP_Pub_ID] [uniqueidentifier] NOT NULL , [PHP_Data_ID] [uniqueidentifier] NOT NULL , [PubID] [uniqueidentifier] NOT NULL , [UserID] [uniqueidentifier] NOT NULL , [Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Publication] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Pub_Year] [datetime] NULL , [Pub_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[PHP_Data_Publications] WITH NOCHECK ADD CONSTRAINT [DF__PHP_Data___rowgu__01E91FA0] DEFAULT (newid()) FOR [rowguid] GO
CREATE UNIQUE INDEX [index_1466488303] ON [dbo].[PHP_Data_Publications]([rowguid]) ON [PRIMARY] GO
Is it possible to modify or add new fields in sql 2000 when the instance became publisher ? I tried it several times but it failed. I access microsoft website but i did not help.
