Sql-server – How to manage primary key value while multiple secondary server sync with primary server

availability-groupsdata synchronizationprimary-keyreplicationsql server

we have three secondary database server in different cities and one primary server.
my question is i want asynchronous transaction commit to all three database but primary key data conflict occur when commit to primary database…because same id can generate on all three server.
then which technology is available for this kind of scenario for maintain primary key conflict issue…

Note-all three secondary server work as a 'replication publisher' and primary server work as a subscriber(primary replica of alwayson).

solution from my side– i commit the records on secondary server and i use some tricks on all secondary server like..server 1 (seed value = 1 increment by = 3),server 2 (seed value = 2 increment by = 3), server 3 (seed value = 3 increment by = 3)…according to this setting my primary key value never conflict….so please advice am i going right way or not ???

Best Answer

There are 3 possible solutions that I can think of to what you are asking.

  1. First solution is exactly how you have it, set each server to increment an identity column by the same increment but start them offset by one. (Server 1: start at 1 increment by 2, Server 2: start at 2 increment by 2) This solution works well because it keeps your key small but allows you to generate them without running into conflicts. On the other hand you have to configure each identity column on each server so you have the maintenance overhead.
  2. Another solution is to set your key to a guid, at 16 bytes the chances of running into a conflict are small, but it's a rather wide key and comes with it's own set of troubles. See here for more info: Kimberly Tripp GUIDs as Primary Key Or here: GUID vs INT Debate
  3. Use a composite key where one column represents the server that created the record and the other column is an identity that increments by 1. This also has a disadvantage of having to configure the column on each server to represent the server it was created on.

In the end there is no right or wrong way to do it, it's all a matter of what trade offs you want to put up with.

Related Question