Auto increment even and odd for two databases for synchronization without affecting auto increment property

Question

Need quick help. I am having my database with bigint auto increment property. I have database on two location's which need to synchronized. As bigint is not good choice for synchronization because of possibility primary key replica on different sites. I can not move on with GUID because for that i need to change my code as well as database which is not possible for me.

Right now i have two location only for database, so i think if can make my primary key auto increment to be always even at one location and to be odd at other location. It can solve my issue in quick manner.

How can i do it using computed column specification or by any other way. For synchronization i am using Microsoft sycn framework.

If i use identity(1,2) A server or identity(2,2) B server after synchronization it disturbs next increment value. For example if at A server max id 3 and at B server current id is 4. After sync max id on A server will be now 4. I want new id on A server should be 5 only but in reality it inserts 6. How can i solve this issue

Answer 1

Here is a very simple solution, but it will work only for two servers. It can't be easily extended for more servers.

The good thing about it is that it doesn't use CHECKIDENT to reseed the tables and you don't need to worry about simultaneously running transactions to get the accurate MAX ID to feed into CHECKIDENT.

Also, MSDN warns that identity property on a column does not guarantee the following:

Consecutive values after server restart or other failures – SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

If you choose a solution that is based on reseeding identity using CHECKIDENT you'd better double check that it works correctly in such cases.

Also, to run CHECKIDENT you may need specific permissions:

Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Solution

My main idea is that on the first server you use IDENTITY(1,1) and on the second server you use IDENTITY(-1,-1). Rather than trying to make IDs odd and even they will be positive and negative.

Here is a script that proves that it works as intended without any extra work.

-- Sample data
CREATE TABLE #T1 (ID bigint IDENTITY(1,1), V1 int);
CREATE TABLE #T2 (ID bigint IDENTITY(-1,-1), V2 int);

INSERT INTO #T1 VALUES (11);
INSERT INTO #T1 VALUES (12);
INSERT INTO #T1 VALUES (13);
INSERT INTO #T1 VALUES (14);

INSERT INTO #T2 VALUES (21);
INSERT INTO #T2 VALUES (22);
INSERT INTO #T2 VALUES (23);

SELECT * FROM #T1;
SELECT * FROM #T2;

We start with this sample data in our tables:

#T1
ID  V1
1   11
2   12
3   13
4   14

#T2 
ID  V2
-1  21
-2  22
-3  23

Perform the sync

-- Insert into T1 new values from T2
SET IDENTITY_INSERT #T1 ON;

MERGE INTO #T1 AS Dst
USING
(
    SELECT ID, V2
    FROM #T2
) AS Src
ON Dst.ID = Src.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID, V1)
VALUES (Src.ID, Src.V2);

SET IDENTITY_INSERT #T1 OFF;

-- Insert into T2 new values from T1
SET IDENTITY_INSERT #T2 ON;

MERGE INTO #T2 AS Dst
USING
(
    SELECT ID, V1
    FROM #T1
) AS Src
ON Dst.ID = Src.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID, V2)
VALUES (Src.ID, Src.V1);

SET IDENTITY_INSERT #T2 OFF;

SELECT * FROM #T1;
SELECT * FROM #T2;

Result of the sync - two identical tables

#T1
ID  V1
1   11
2   12
3   13
4   14
-1  21
-2  22
-3  23


#T2
ID  V2
-1  21
-2  22
-3  23
1   11
2   12
3   13
4   14

Insert more data to check how identity works after the sync

-- Insert more data into T1 and T2
INSERT INTO #T1 VALUES (15);
INSERT INTO #T1 VALUES (16);

INSERT INTO #T2 VALUES (24);
INSERT INTO #T2 VALUES (25);
INSERT INTO #T2 VALUES (26);

SELECT * FROM #T1;
SELECT * FROM #T2;

-- Clean up
DROP TABLE #T1;
DROP TABLE #T2;

Generated identities after the sync

#T1
ID  V1
1   11
2   12
3   13
4   14
-1  21
-2  22
-3  23
5   15
6   16

#T2
ID  V2
-1  21
-2  22
-3  23
1   11
2   12
3   13
4   14
-4  24
-5  25
-6  26

You can see that new identities in T1 continue to be positive and new identities in T2 continue to be negative.