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.

Say we have two Servers: Server A and Server B. Data is being replicated (vanilla Transactional Replication) from A to B.

For one of the Tables, we have data as follows:

Server A   - Server B
=========    =========

ID|Content   ID|Content
----------   ----------
1 | ...      1 | ...
2 | ...      2 | ...
  ...         ...
98| ...      98| ...
99| ...      99| ...

After a while, we delete old data:

Server A   - Server B
=========    =========

ID|Content   ID|Content
----------   ----------
50| ...      50| ...
51| ...      51| ...
  ...         ...
98| ...      98| ...
99| ...      99| ...

Now, we changed the order of replication, so now Server B is Replicating to server A.

The issue is:

I Would have expected that new data that is added to Server B (and then replicated to Server A) would continue using the ID column (It would go to 100, 101, 102, ...). Instead, I noticed it starts back at 1.

The question is:

How will the DB handle it when the ID finally hits 50? Will the DB just start throwing Primary Key violations, or will it realize what is going on, and jump from ID 49 to ID 100?

If it would start throwing Primary Key violations, is there a command we can use to tell Server B that it should start at ID 100? And if so, can this command be done while it is replicating data?

share|improve this question
    
Out of curiosity, why have you flipped the replication so B is the master? –  Max Vernon Dec 5 '13 at 19:32
    
We use Server A as our Main Server, and Server B as a Backup. We've had issues when we try to fail-over to B, so we periodically make B the Main and A the Backup (and then vice versa). This is just our way of testing our Backup Server. –  Onion-Knight Dec 5 '13 at 19:44
    
I thought you were possibly attempting to do High Availability. Have you considered using Database Mirroring which is designed for HA instead of replication? –  Max Vernon Dec 5 '13 at 20:24
add comment

1 Answer

up vote 2 down vote accepted

After switching to a different master you must reseed the identity value, otherwise you will get duplicates in the id column. Use the DBCC CHECKIDENT statement.

DBCC CHECKIDENT ( 'dbo.ITest', reseed, 1000 ) ;

Choosing the number could be automated by using a SELECT MAX( TID ) query and placing the returned number into a dynamic SQL string. This must be placed in a serializable transaction to make sure no other sessions insert new rows while this session is reseeding the value (thanks David). Also, the master must not be changed while reseeding is taking place.

One way to deal with this might be to create non-overlapping identity domains. On ServerA, create the identity column as identity ( 1, 2 ) and on ServerB as identity (2, 2 ). That way ServerA's inserts will be 1, 3, 5… and ServerB's inserts will be 2, 4, 6… Of course, if you ever want to create a third server then you are hosed. :-)

Better still, don't use identity in a multi-master situation. It provides no prevention of duplicates. Use a uniqueidentifier or an application-generated identifier instead.

share|improve this answer
    
Be careful when picking the new seed by checking take contents: make sure you are in single user mode or otherwise arrange for no other activity to be going on or make sure you explicitly take out appropriate locks. –  David Spillett Dec 5 '13 at 20:24
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.