Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to run a sql server logging db with 2 main tables in 2 seperate datacentres writing to both at the same time.

I had the idea of restoring the db to the new datacentre and then reseeding the identity column to -1 and setting the increment to -1, that way there would never be any chance of duplicate id's when the data needs to be combined. DATACENTRE1 would be positive integers, DATACENTRE2 negative integers

would an increment of -1 cause any issues?

share|improve this question
2  
    
Could you elaborate on writing to both at the same time? –  hot2use Nov 22 '12 at 13:59
add comment

5 Answers

up vote 7 down vote accepted

Going backwards just feels wrong to me.

With only two data centers you could also implement identity ranges. Unless you cycle through identity values at an alarming rate, there is no reason you can't have:

-- Data center 1
CREATE TABLE dbo.Table
(
  ID INT IDENTITY(1,1) PRIMARY KEY
  -- , ...
);

-- Data center 2
CREATE TABLE dbo.Table
(
  ID INT IDENTITY(1000000000,1) PRIMARY KEY
  -- , ...
);

This would allow to generate 1 billion (well, 999,999,999) values in data center 1 before there is any danger in collision with data center 2. And you could add a CHECK constraint in data center 1 to prevent overlapping values, depending on how you prioritize errors vs. duplicates. You could also implement a recurring job that periodically checks how close you are to the lower bound of the other data center, if you're concerned that you'll really generate over a billion values in either data center in the app's lifetime (never mind yours).

If ~1 billion is not enough, there are two other alternatives to give more room to breathe:

-- Data center 1
  ID INT IDENTITY(-2147483648,1)

-- Data center 2
  ID INT IDENTITY(1,1)

This would give each data center over 2 billion non-overlapping values, with no danger of collision until data center 1 approached 0. And if that's still not enough:

-- Data center 1
  ID BIGINT IDENTITY(-9223372036854775808,1)

-- Data center 2
  ID BIGINT IDENTITY(1,1)

Or if you want to keep all values > 0, you could split the positive range roughly in half (you can be a little more precise than this if you're more pedantic than me).

-- Data center 1
  ID BIGINT IDENTITY(1,1)

-- Data center 2
  ID BIGINT IDENTITY(4611600000000000000,1)

I don't even know how to say that number, but it's ginormous. And in reality you'd have to work extremely hard on a very fast computer to use up all those values by the time your great great great great great grandchild graduates from college. And if you are on Enterprise Edition you can use data compression so that you don't pay the hit for all 8 bytes, at least in data center 1, until you exceed 2 billion there.


In a system I managed I did this a slightly different way - we had multiple web servers with Express instances that were responsible for generating ID numbers that needed to be unique in the enterprise. So we just set up a sequence generator on each machine (they didn't need to actually store the value) using a BIGINT IDENTITY column. We had < 9 servers, so they were all seeded like this:

-- WEBAPP0001
ID BIGINT IDENTITY(10000000000,1)

-- WEBAPP0002
ID BIGINT IDENTITY(20000000000,1)

-- WEBAPP0003
ID BIGINT IDENTITY(30000000000,1)

...

When the values were used and then merged in the central system, not only were we guaranteed to not have any duplicates, but it was also easy to instantly recognize which web server they came from (which was sometimes useful in debugging) without introducing any composite key requirements. And we had no concerns that any one web server would ever generate more than 10 billion values.

Many people would go with a GUID in this scenario, but I think there are several strong arguments against that approach.

share|improve this answer
    
I'd love your thought on this topic! –  Ruud van de Beeten Mar 28 '13 at 14:22
    
@Ruud not sure what you mean, do you mean something in addition to what I said in my answer? Can you elaborate? –  Aaron Bertrand Mar 28 '13 at 14:25
    
I meant your idea of using a BIGINT with a gigantic number as offset is nice. :) –  Ruud van de Beeten Mar 28 '13 at 15:02
add comment

It won't cause problems in that SQL Server lets you do it:


create table decrement(
id integer identity(0,-1),
test int
)

insert into decrement (test) select number from numbers

select top 10 id, test from decrement order by id asc
go
id  test
------------
-5103   5110
-5102   5109
-5101   5108
-5100   5107
-5099   5106
-5098   5105
-5097   5104
-5096   5103
-5095   5102
-5094   5101

But a good idea in the long term might be a different problem. Others may end up confused (ie; I had to think about the order on the query above as it's upside down to normal). Or what happens when someone else restores the database and reseeds the IDENTITY the 'normal' way and you've got overlapping IDs?

Is it possible to modify your schema so you have a 'site' column? Then use the site and ID as a composite key?

share|improve this answer
add comment

Some problems that may rise with with this setting:

Following the link in @Martin Smith's comment, negative values in an identity column may cause issues with some applications: Why database designers do not make IDENTITY columns start from the min value rather than 1?

Another issue is not related to the values being negative but being decreasing, and if the identity is also the clustered key of the table. B-tree structures are more efficient when traversed from left to right (lower to higher values) and when the inserts are done on the right (higher) side, e.g. when the key is ever-increasing. This property matters ever more for the clustered key of the table. See this blog post by Kimberly Tripp about the best properties of clustered keys, especially about being ever-increasing.

With the decreasing key, you will be inserting data always on the wrong (left) side of the index, causing fragmentation of the index. The effects may not be critical to your case, but I think you should have this in mind if the identity is also chosen as the clustered key.

Or as @Martin suggests, have the clustered index also defined as DESC for the (-1) datacenter. This will avoid any of the above problems with fragmentation.


Other options to achieve same functionality keeping increasing IDs is to have even values in one datacenter and odd in the other (so both with +2 increment) or a siteID added to the primary key as @Stuart suggested.

share|improve this answer
    
Ever decreasing would be all right if the index was also defined as DESC. Otherwise you could end up with fragmentation –  Martin Smith Nov 22 '12 at 15:26
    
@Martin: Right, didn't think of that. Corrected.! –  ypercube Nov 22 '12 at 15:42
add comment

Just try it! (and then report back here :-) ).

No, I don't expect any issues - after all, INT's data range is from -2 billion through +2 billion - I don't see why negative numbers should behave any differently from positive IDENTITY numbers....

share|improve this answer
add comment

The approaches I have used are:

  • start a sequential values for each server and increment by a value greater than the anticipated number of servers.
  • Use different ranges for each server.
  • Use a common sequence for both servers.

Each solution has pros and cons.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.