What is a good auto-generated key for a federated database?

Question

I am about to split a large database into a bunch of federated instances. Currently, most of the primary keys are auto-generated identity ints. Obviously, that's not going to work in a federated setup.

I've read people using auto-generated GUIDs to replace the ints, but that data type has well-known performance sapping problems on its own.

What are some of the strategies I can use for having unique values for primary keys across federated members?

Answer 1

One alternative is to perform a hash on some existing value or combination of values, for instance the user's login name and autogenerated identity (int):

SELECT 
    CONVERT(NVARCHAR(40), 
        HashBytes('SHA1', UserName + CAST(UserId AS VARCHAR)) ,2)
    AS FederatedIdentity
FROM Users

This way the value would be consistent across databases. You could persist the hash in a table; I usually create a view to work with generated columns like this.