One thing to remember is that an
ID does not have to be generated by the
IDENTITY has challenges, since as soon as rows are moved from the standby server to the current server (using
IDENTITY_INSERT of course) with an
ID value higher than the current identity value it becomes the new current identity value. Which can complicate matters between the two servers.
For other issues see: http://msdn.microsoft.com/en-us/library/ms186775(v=sql.110).aspx
You might find a sequence generator with NOCACHE or some other mechanism to generate
IDs on each machine gives you more control. For example, you can set ID ranges that can last for a long time for each server. For example:
- Server1 ID's from 1000000 to 1999999
- Server2 ID's from 2000000 to 2999999
Or you might suffix the numbers so that all
IDs from Server1 end with a 1 and all
IDs from Server2 end with a 2.
Or anything else that helps you avoid or limit the need for juggling values constantly or keeping the two machines aware of each other's state.