Big Edit: (Much better) Option 1:
(Additional note: @VladimirBaranov mentioned this in the comments, and I missed it, but here's a fleshing out of how to use a SEQUENCE in this situation anyway)
My original idea is further down this answer, and would still be potentially viable, but I think this newer option will fit the bill exactly for however many servers you need. It was bothering me that I knew there was a right way to do this in TSQL, and I could not remember what it was. My brain finally dredged it up today: SEQUENCE. SQL Server 2012 and 2014 allow you to define a sequence to generate a series of numbers for use in your tables:
CREATE SEQUENCE oddNums
START WITH 1
INCREMENT BY 2;
GO
CREATE SEQUENCE evenNums
START WITH 0
INCREMENT BY 2;
GO
Then instead of AUTO INCREMENTing your PK's, give them a DEFAULT value from the SEQUENCE (these are the tables from the fiddle linked below):
CREATE TABLE oddMirror (
[id] int PRIMARY KEY DEFAULT NEXT VALUE FOR oddNums,
[data] varchar(7)
);
CREATE TABLE evenMirror (
[id] int PRIMARY KEY DEFAULT NEXT VALUE FOR evenNums,
[data] varchar(7)
);
These sequences are totally unaffected by merges, and will continue to generate odd or even numbers forever no matter what the latest PK in the table is.
Here is a SQLFiddle of this in action.
Note that you can't define the column as IDENTITY if you do this (because of the DEFAULT clause), so you will have to be careful about inserting into your id column, but otherwise this should be about as straightforward as it gets.
This could be done with as many servers as you want, just adjust how much each SEQUENCE increments by and where it starts from, but you would have a hard (not impossible) time adding additional servers to the mix once your SEQUENCEs were defined.
Also, here is an MSDN blog that discusses alternative strategies for simulating a SEQUENCE on prior versions of SQL Server.
(Not as good) Option 2:
(Note: this is my original answer) I have been playing with this some this evening, and depending on how you have things set up, I think you could get away with reseeding the table on each server after the sync is done, based on the current highest id in the table. You would just have to do it slightly differently for each server to keep new ids odd on one and even on the other.
So you have:
CREATE TABLE oddMirror
(id INT NOT NULL IDENTITY(1,2),
data NVARCHAR(10))
GO
and
CREATE TABLE evenMirror
(id INT NOT NULL IDENTITY(2,2),
data NVARCHAR(10)
GO
After you sync the two tables, you don't know if the current identity seed is odd or even, so you need to reset it on each table to the correct "next" value for the server. So, on oddMirror:
DECLARE @maxId INT
DECLARE @newSeed INT
SET @maxId = (SELECT MAX(id) FROM oddMirror)
SET @newSeed = (SELECT CASE WHEN @maxId % 2 = 1 THEN @maxId ELSE @maxId -1 END)
DBCC CHECKIDENT('dbo.oddMirror', RESEED, @newSeed)
GO
And an almost identical process on evenMirror:
DECLARE @maxId INT
DECLARE @newSeed INT
SET @maxId = (SELECT MAX(id) FROM evenMirror)
SET @newSeed = (SELECT CASE WHEN @maxId % 2 = 0 THEN @maxId ELSE @maxId -1 END)
DBCC CHECKIDENT('dbo.evenMirror', RESEED, @newSeed)
GO
So basically, on oddMirror we are saying, "Get the current max id. if it's odd, don't change it, but if it's even, back it up by one."
Then do the same thing on 'evenMirror', except check whether the max id is even instead of odd.
So as an example, take this data:
oddMirror
1,"one"
3,"three"
5,"five"
and
evenMirror
2,"two"
4,"four"
6,"six"
8,"eight"
(notice that evenMirror has more rows)
After a sync, each table would look like this:
oddMirror
1,"one"
2,"two"
3,"three"
4,"four"
5,"five"
6,"six"
8,"eight"
--evenMirror looks the same as this now
Running things through the above queries:
MAX(id) on oddMirror is 8. 8 % 2 = 0, so set @newSeed = 8 - 1 = 7, meaning the next row in oddMirror will get id = 9.
MAX(id) on evenMirror is also 8, but the query is slightly different. 8 % x = 0 so set @newSeed = 8, meaning the next row in 'evenMirrorwill getid = 10`
id = 7 will be skipped in this scenario, but I'm guessing that's not a tremendous concern.
If you then queried:
INSERT INTO oddMirror (data) VALUE ("data")
GO
INSERT INTO evenMirror (data) VALUE ("otherData")
GO
Tables would look like this:
oddMirror
1,"one"
2,"two"
3,"three"
4,"four"
5,"five"
6,"six"
8,"eight"
9,"data"
and
evenMirror
1,"one"
2,"two"
3,"three"
4,"four"
5,"five"
6,"six"
8,"eight"
10,"otherData"
This could theoretically be expanded out to accommodate more number of servers by changing what modulo you take and adding additional WHENs to the CASE statement for each possibility, although that would certainly get cumbersome to maintain. But, we already knew that the right solution (GUIDs) is not available here, and if you're reading this far, the next best solution (SEQUENCEs) may not be available, so whatever we come up with is inevitably going to be cumbersome.
The biggest drawback to this approach is that the tables must be locked until the sync process is complete. If a write comes in before sync is complete and ids are reseeded, there will almost certainly be a collision. If the tables are not written to very often, or you are already locking them for syncing, or if you have a significant "dead" spot in your daily cycle (like 3-4 a.m. or something) where this could be done without serious disruption, it may not be that big of a deal, but only you will know how viable this is.
So, your setup may or may not make this possible, but I have been playing with this quite a bit tonight in my sandbox db, and it seems to work well to make sure that new id's are always odd in one db and always even in the other.
getDate(). – SiloumSEQUENCE, which gives finer control over generated numbers. – Intercolumniation