Bidirectional replication update record problem

Question

Hi, I would like to present you my problem related to SQL Server 2005 bidirectional replication. What do I need? My teamleader wants to solve one of our problems using bidirectional replication between two databases, each used by different application. One application creates records in table A, changes should replicate to second database into a copy of table A. When data on second server are changed, then those changes have to be propagated back to the first server. I am trying to achieve bidirectional transactional replication between two databases on one server, which is running SQL Server 2005. I have manage to set this up using scripts, established 2 publications and 2 read only subscriptions with loopback detection. Distributtion database is created, publishment on both databases is enabled. Distributor and publisher are up. We are using some rules to control, which records will be replicated, so we need to call our custom stored procedures during replication. So, articles are set to use update, insert and delete custom stored procedures.

So far so good, but?

Everything works fine, changes are replicating, until updates are done on both tables simultaneously or before changes are replicated (and that takes about 3-6 seconds). Both records then end up with different values.

UPDATE db1.dbo.TestTable SET Col = 4 WHERE ID = 1
UPDATE db2.dbo.TestTable SET Col = 5 WHERE ID = 1

results to:

db1.dbo.TestTable COL = 5

db2.dbo.TestTable COL = 4

But we want to have last change winning replication. Please, is there a way to solve my problem? How can I ensure same values in both records? Or is there easier solution than this kind of replication?

I can provide sample replication script which I am using.
I am looking forward for you ideas,
Mirek

Answer 1

I think that adding dateUpdated field on both tables could help. This way in your replication code a record would be updated only if dateUpdated is greater then the one already stored.

That dateUpdated field would obviously store the datetime when the original record was updated, not when the replication was performed