I have an Organization table and a Site table in my database.
The application is installed at various Geographical Sites and each Site has its own Local Database.
I am using Sql Azure Synchronization Group for synchronizing all the Sites data once every week. It is fine for all other data to get synchronized in a week, but this one table Site - Whenever a new Site is inserted, I need a unique identifier for this site - I know I can use GUID which I have used at other places, but there is a requirement for using INT only.
If I use INT, its possible that same INT key is inserted at different locations, which upon synchronization, will result in overwritten records and lost Sites.
What can I do for having a unique INT primary key for each record inserted in this table ?
Please tell me any possible solutions.
These are separate databases, so the only way SQL Azure will provide a unique Site ID is via Guid. Maybe you could stage a simple WCF service for issuing sequential INTs for you, which you'd call before creating a site (and maybe it keeps its "next id" in a Table row, or something like that. You'd have to make your WCF service either Singleton or capable of issuing multiple IDs without reusing IDs. You may have the occasional unused site INT, since it's possible something goes wrong with the code calling the WCF service, but if you're ok with gaps in your IDs, this shouldn't matter.