identity_insert and synchronization issue

I'm using SQL Server 2008, visual studio 2008, linq, ado.net in my current project. this project is implemented in web and desktop.

now my problem is with synchronization of data between web db and desktop db.

here synchronization is as follow:

1. new data inserted in client is synced to server.
2. updated data in client is synced to server.

here let me show you my table structure.

let us suppose my table name is "customer" with fields

customer

................开发者_JS百科...

customerno, bigint, primary key, not null, identity true (starting with 1)

firstname, ...

lastname, ...

now i'll have the same table in client but the primary key identity will start with 20000. this primary key identity depends on the number of client systems. let consider the following scenario

server = customerno starting with 1, with 1 record in table

client1 = customerno starting with 20000, with 2 records in table

client2 = customerno starting with 30000, with 3 records in table

now when i press sync in client1, using the following code

        cmd.CommandText = "set identity_insert " + tableName + " on";
        cmd.ExecuteNonQuery();

        foreach (string query in this.queries)
        {
            cmd.CommandText = query; // this is my insert query
            cmd.ExecuteNonQuery();
        }

        cmd.CommandText = "set identity_insert " + tableName + " off";
        cmd.ExecuteNonQuery();

as linq doesnt support identity_insert, im using ado.net

in my code, first im setting the identity_insert on, inserting the data and finally setting identity_insert off.

now here my problem, after data is synced from client1, and two records are inserted in server with customerno as 20001,20002.

as per http://msdn.microsoft.com/en-us/library/ms188059.aspx

the identity of customerno column in server is set to the max value (in this case it will be 20002).

so now when i insert data in server, it inserts with customerno as 20003.

here i dont want the server identity column to take the max value.

considering the above case, before sync, we have 1 record in server, and its id is 1, so after sync (with one or more clients), i want the next id(customerno) in server to be 2,3,4..... for newly inserted data in server.

here any client can sync data with server at any point of time.

You are using one field for two things. Uniquely identifying a customer and to figure out the source of the information.

You can use a uniqueidentifier as the primary key and use another field to determine the source of the information.

Use newid() as a default constraint for your primary key. When you are replicating you specify the GUID to use and that will be used instead of newid().

Edit 1

If you have trouble replacing your primary key you can add the GUID column as a candidate key. The problem then will be that the logic for replicating data between servers will be a lot more complicated if you need to update tables that have a relation to your Customers table. You have to first insert all Customers and then query the Customers table to figure out what FK CustomerID you should use in related tables.

You are in a very bad place. If you want to assign ranges of surrogate key you can't use an identity column. Abandoning this and going with the built-in replication technology is probably The Right Thing© to do.

If you can't do the right thing you'll can drop the identity column and make it an int. Then you can create your own auto-incrementing algorithm but watch out for race conditions (i.e. don't use MAX)

What you want to do is possible I think. I'm not suggesting that it is the best idea ever but as far as I can see the following would work at the expense of blocking all data modifications during the synch. Obviously there are inherent problems in the approach itself though in terms of needing to accurately predict maximum possible numbers of client and the maximum number of inserts from each source.

create table dbo.t (id int identity(1,1) primary key)

INSERT INTO dbo.t DEFAULT VALUES
INSERT INTO dbo.t DEFAULT VALUES
INSERT INTO dbo.t DEFAULT VALUES




--Do the Synch
BEGIN TRAN

DECLARE @MaxId int = (SELECT MAX(id) FROM t WITH (TABLOCK, REPEATABLEREAD) WHERE id<20000)
                      --Take a shared table lock to prevent any concurrent modifications

SET IDENTITY_INSERT t ON
INSERT INTO t(id) VALUES (20000)
SET IDENTITY_INSERT t OFF

DBCC CHECKIDENT ("dbo.t", RESEED, @MaxId)

COMMIT 
--Synch Finished

INSERT INTO dbo.t  DEFAULT VALUES /*Will have the value 4*/
SELECT * FROM dbo.t


DROP table dbo.t