Right now we use autogenerated primary keys in various tables of our SQL server 2005 database. Now we have a need to put separate database servers at various remote locations and data will need to sent back to the central server. Some data will be sent back from central server to the remote sites. Data also gets added at the central location.
How can I handle replication when using autogen keys? Can I add some additional column like a SiteID and make the primary key a combination of Autogenerated key and Site ID? Would that work? The autogenerated primary keys are also foreign keys in other tables.
I have read a little about having separate ranges (of autogenerated keys) for various sites but don't think that will work as we already got lots of data in our central server.
Any help is much appreciated.
Anyone?
I am trying to find info on the web on what I can change in my existing database to make it replication friendly and so far coming up with nothing. I have read about using GUIDs vs Autogenerated keys but that would be useful when creating new databases.
I would assume these kind of situations should be common when companies expand so looking for the various solutions out there, if possible.
|||Both those methods work.
Either uniqueidentifier with the primary key set to default of newid()
Or a composite key like Site, ID so you get records like:
LONDON, 123
LONDON, 124
LONDON, 125
NEWYORK, 321
NEWYORK, 221
The only problem with a composite key is if you want to make it a foriegn key in another table then you are stuck with having to populate the composite key instead of just one column. uniqueidentifiers are handy because the table needs one anyways but it gets really mundain if you are doing work with tables as it is not very human friendly.
Martin
No comments:
Post a Comment