What will happen to our nice NextID setup? In what ways do I need to change our approach in order to have the same basic functionality. I fear that a person in the remote location will create a new record and be handed an ID that is the same as the ID just handed out to the user in the main office. Impending crash detected.
Well, this is a pretty loaded question as there are a lot of different options here and can be handled a number of different ways. So let's just talk a couple of basics. I will give you two options that may spark some ideas:
Option 1 - Web Server Handing Out IDsRecently I ran into a similar issue in a pre-existing database setup that we had that required a unique ID to be created across every user that we have (thousands of sites) for each individual record. This was a stressful though, but ultimately a simple solution. We created a single entry point via a Web Service (very simple to write) that controlled the next ID that would be handed out. This is hosted on an always up web server off-site in the event we have power outages here, it would not affect us. You can probably skip that step since this looks to be internal. At that point, we have a web handler that accepts inbound requests for any number of record types and returns the appropriate next ID. This way there will never be two alike across all of our users. The performance was actually surprisingly fast and did not have any degradation. If you host this on an internal network, then it would not be an issue at all in regards to performance. This is a great solution to allow you to keep your existing next ID increments and logic.
Option 2 - GUID Primary KeysThe next option would be to use GUID primary keys. My personal preference on this option is one of avoidance when given the chance. Not because of the complexity of implementation, but of growth, increased bandwidth, and difficulty of managing foreign keys as it is harder to trace, debug, etc. Also, if you try to implement this on existing structures, it will not be fun at all. Now that I have gotten my negative two cents out of the way, here are all of the pros. It is actually very easy to implement on new table structures and ties nicely into SQL Server Replication (if that is what you are trying to accomplish). This is a proven method of creating disparate primary keys and can also be done without a web service since the odds of a duplicate GUID (depending on the mechanism you use to pull them) is virtually impossible...though not totally impossible, statistically practically impossible. I have ran tests more than once to see if a duplicate GUID was reproduced using the simple SYstem.Guid.NewGuid method in .NET. After creating tens of millions of records, no dupes. Depending on the data in question and number of records, you want to still have a central place to pull this value to avoid any possible statical anomaly.
After all of that, if it were me, I would go with Option 1. But trust me, there are other opinions and experiences out there that have done this with option 2 as well. The nice thing about Option 1 in your case is that you could make your existing structures work and move forward whereas Option 2 will require some extensive reconstructive surgery to your existing structures.