Comment by Seth Spearman on How do you sync databases using ms sync framework when tables are using identity column and you need to match on an arbitrary key
How do you sync databases using ms sync framework when tables are using identity column and you need to match on an arbitrary key
Sorry for the long and confused title.
I have a customer that wants to sync three database. (Later it could be more.) All data in all three databases must sync to the all others. This does not at all need to be real time but would probably run nightly or something like that.
This database is using identity columns as primary/foreign keys.
The issue is that a given record could be added independently into two (or even more) of the databases but be the same data. So, for example, I could have a customer name John Smith of 123 Main Street created at both of the site databases. In this case I would want to FirstName LastName and the first 8 characters of the address (or maybe a phone number) to form the "match key" for merging records instead of adding new ones every time. In other words, after all of the syncronization is complete, I would want all 3 database to have just ONE record for John SMith at 123 Main Street.
By the way, the way that I was thinking of doing this is having one "master" database hosted in the cloud. All three databases would do a 2-way sync to this master database.
My main question is how would you get this to work? How do you define the "match key" for tables in the database. Is it as simple as creating unique constraints on the match keys? Is there a way to do it without changing the DB schema? (I am wiling to consider this but I the database is 3rd party and subject to change.)
I don’t know how often this would be helpful but today I needed it to solve a problem. If you ever need to create an Windows Event Log entry you can do it from an ELEVATED Command Prompt as follows:
eventcreate /ID 1 /L APPLICATION /T INFORMATION /SO “My Event Source” /D “Actual Log Entry”