Safe way to store primary key generator outside of database?

We have an application which stores its data in SQL Server. Each table has a bigint primary key. We used to generate these exclusively on demand, i.e. when you go to insert a new row, you first make a call to generate the next ID, and the开发者_Go百科n you do the insert.

We added support to run in offline mode: if your connection is down (or SQL Server is down), it saves the data to a local file until you go back online, and then syncs everything you've done since then.

This required being able to generate IDs on the client side. Instead of asking SQL for the next 1 ID, it now asks for the next hundred or thousand or 10,000 IDs, and then stores the range locally, so it doesn't have to ask for more until those 10,000 run out. It would actually get them in smaller chunks, so when 5000 run out, it still has a buffer of 5000, and it can ask for 5000 more.

The problem is, as soon as this went live, we started getting reports of primary key violations. We stored the data in the Windows registry, in HKEY_CURRENT_USER (the only place in the registry a user is guaranteed to be able to write to). So after some research, we discovered that HKEY_CURRENT_USER is part of the roaming profile, so it's possible the IDs could get overwritten with an old version. Especially if the user logs into multiple computers on the network simultaneously.

So we re-wrote the part that generates IDs to read/write a file from the user's "Local Settings" directory. Surely that shouldn't get overwritten by an old version. But even now, I still see occasional primary key violations. The only thing we can do in that case is delete any keys in the file, kick the user out of the application, and don't let them back in until they get new ID ranges.

But if "Local Settings" isn't safe, what would be? Is there anywhere you can store a persistent value on a computer which is guaranteed not to be rolled back to an old version? Can anyone explain why "Local Settings" does not meet this criteria?

I've done some consideration of a GUID like solution, but that has problems on its own.

in distributed environment as yours, your best bet is using GUID

Do you have to use the same key when you persist the data locally that you use when you sync with the database?

I would be sorely tempted to use a GUID when you persist the data locally and then generate the real key when you're actually writing the data to the database. Or persist the data locally starting with a value of 1 and then generate real keys when you actually write the data to the database.

Setup an IDENTITY (http://www.simple-talk.com/sql/t-sql-programming/identity-columns/) on the bigint primary key so that SQL Server generates the values automatically.

When your application is offline, you keep the pending changes local. When it comes back online, you send your updates (including new records) and SQL Server would INSERT them and automatically assign a primary key since you have the IDENTITY setup.

If you need to know what key value was generated/used after an insert you can utilize the @@IDENTITY property (http://msdn.microsoft.com/en-us/library/aa933167%28v=sql.80%29.aspx)