Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-11-29 : 05:08:24
|
| Larry writes "We have offices in serval different locations throughout the US & Canada and the Phillipines. We have databases setup as follows:Eagan, MN SQL Server 2000 SP 3a, Windows Server 2000St. Louis, MI. SQL Server 2000 SP 3a, Windows Server 2003Montreal, Canada MSDE 2000 SP 3a, Windows NT 4Moncton, Canada MSDE 2000 SP 3a, Windows NT 4Manilla, Phillipines MSDE 2000 SP 3a, Windows Server 2003Eagan, MN is our headquarters so we consider that server our main server and that is where we have the publication setup with push subscriptions to the other four sites. We replicate every 3 minutes at 30 second intervals.So first the Eagan site replicates with the St. Louis site. Then 30 seconds later the Eagan site replicates with the Montreal site, etc. The replication generally takes about 5-10 seconds per site with the the Phillipines replicating in about 20 seconds so the replications do not overlap.The problem we are having is that we get replication conflicts quite often that say an entry was deleted at one site but updated at another site. For example, the data was deleted at Eagan but updated at St. Louis. When this does happen I do know that the data originated at St. Louis so it was inserted first at St. Louis and it was updated at St. Louis. The insert and update happen fairly close together in time but may or may not happen within the 3 minute replication cycle. I am confident that no one at a different site is deleting the information. So it appears that when the update occurs at St. Louis, then the replication occurs that the orginal insert did not occur at Eagan. This causes the system to think the row was updated at St. Louis but deleted at Eagan and we get the conflict. We have the default conflict resolution, so what happens is that the delete wins and the row is deleted off of the St. Louis database which is not what we want to happen. When the row is reinserted at St. Louis the replication generally picks it up and things are normal again. And it does not happen all the time, just occasionally but often enough to be a real annoyance. I have been trying to track down the cause of this problem and I am just guessing that the insert is not being picked up so it is just a theory.Have you heard of this before? Can the replication miss an insert occasionally and cause this problem? Do you know of a way to prevent this from happening?Thanks for you help,Larry" |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-01 : 03:01:58
|
| assuming what you're replicating in those five sites are the same, will it be easier to keep track of the performance and if ever errors if you include a distributor that will manage the merge transactions?--------------------keeping it simple... |
 |
|
|
|
|
|
|
|
Data missing on a Merge Replication