Merge Replication - Foreign Keys

[scotthutchison]

I am currently new to Merge Replication and have not been able to find one really simple underlying answer to the fundamentals of Merge Replciation....

Foreign Keys.... An example - I have two DBs in separate locations both adding new records. Assume that adding these new records create foreign key links to other tables. Of course when you come to synchronise - a foreign key in a table perhaps now relates to a different record.. How do you deal with this?

Eg: two DBs, the DBs both started out the same with 10 records in the CARS table for example. The two dbs also had 20 records in the MODEL table which states the model name and a foreign key relationship to the cars table. As the two DBs get updated separately and the result is now one DB has 2 new records in the car table for example and the other has 3 new records in its cars table... Of course these have corresponding entries in their MODEL table referencing primary keys. So how do I, using merge replication keep all these new records and update their foreign key references to suit as they synchronise?

It seems fundamental to merge replication and I cannot find advice anywhere on how to do this. Do I have to change both tables to use GUIDs as primary keys rather that incrementing numbers perhaps??

advice would be greatly appreciated

[stummala]

You need to partition your identity's on publisher and subscriber. Have even and odd series(eg have identity of (1,2) on publisher and identity(2,2) on subscriber) or you can use identity ranges (eg 1-1000000 on publisher and 1000001-2000000 on subscriber) etc

And use the identity = "Yes, Not for Replication" property.
Dont have to add the GUID column, the merge agent will automatically add the rowcolguid column.
Basic steps to follow for replication :
Publisher :
1) Alter all the tables that have identity columns to have partition. Eg: Say you are to implement even/odd identity partitions
On publisher (d/b1) lets have "Odd" ranges :
Go to design table and change the identity to yes, not for replication
and seed = 1. increment to "2"
2)Configure the merge replication. The process will create d/b(d/b2) at
Subscriber
3)Now, drop the subscription and alter the tables to have identity(2,2) nad "not for replcation" ,so that it will have even identities.
$)readd the subscription , now with an option that the "subscriber has the data and schema"

If you have some existing data, and the odd table is not always generating odd numbers and vice versa. frop subscription, try to use a dbcc check_ident property and reseed it to have the latest odd/even identity and readd the subscription.

[Rawhide]

I suggest using identity ranges instead of an odd/even scheme. Odd/even only works if you have only 2 servers in the merge. If you need to add a third server later, you would have to redo the whole merge plan.

Plan ahead is my best advice.

[scotthutchison]

I have continued to research this further and discovered a few more hurdles. I set one DB to auto number from 100000 and above and the other to remain the way it was counting from 1. When you synchronise - merge replication changes the setting on the table in the subscriber database and overwrites all the records of 100000 and above, resets your counter to be what the distributer is and loses all your data.

Planning ahead would have been nice if the client had thought they wanted this 3 years ago when we sent this website live..... and I also find it amazing that all the documentation out there simply does not address how to design and manage a good database using merge replication...

It seems the only anser is to use GUIDs as primary keys.. no other way to do it perhaps? surely this technology is better than that??

Thanks for your input so far!!

[Rawhide]

Make sure you have it configured to let the merge process handle the identity ranges!!!

[stummala]

Try reading this article available on internet:
Architecting Replication with Identity Columns - By Michael R. Hotek
The article covers most of the design issues with identities.

When you are merging after the redesign of both the databases, merge with 'no sync' option. this way the changes you have made will be preserved. Choose, the subscriber has data and schema option during the merge.

[krystalchimera]

heya,

im pretty new to merge reps as well and i'm having heaps of trouble trying to get the data from two dbs on seperate machines to synchronize..any recommendations for online / offline resources much appreciated.

thx,
priyanka.

[rmiao]

Should be able to find details in books online.

[krystalchimera]

heya rmiao,

i found some great stuff yesterday afternoon..thx heaps

pri.