Post reply

Identity Range Management

  • August 7, 2006 at 10:42 am

    #191779

    Hi,

    I'm trying to set up a bi-directional transactional replicaton between two servers. The primary key for "tableA" is an identity field. I've done extensive research on the topic and decided to implement manual identity range management using different seeds. I would like to use the following scenario: DB1--seed 1, increment 2 DB2--seed 0, increment 2.

    There is already data stored in the DB, so first, I create an identity field on tempDB--seed 1, increment 1 to populate the column. I turn the "Is Identity" property off, then copy the tempDB (DB1) to the second server and name DB2. I then manually configure the identity property as stated above and ensure that the "Not For Replication" is yes. I then create a publication on each server. DB1pub and DB2pub. I set up a subscription for each server, and do NOT initialize. However, when I add new data into a table, both Databases use odd numbers incremented by two. I've checked the properties of each DB to ensure that the seeds and increments have not been modified, but they look OK. Does anyone know what I might be doing wrong? Any help would be greatly appreciated.

    Brent

  • August 9, 2006 at 11:01 am

    #654137

    Before answering your question, let me ask another question. What are you going to do if you need to add a third SQL Server into the mix? In short, your solution is not scalable.

    Now, to answer your question, after populating the table, reset the seed value to what you want the first next ID assigned by each value to be. So if the Max ID is, for example, 100, you would set one table to have a seed value of 101 and the other to have a seed value of 102.

    Now, getting back to scalability, I suggest allowing SQL Server to manage identity ranges unless you know for sure that you will never have more than 2 SQL Servers.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • August 10, 2006 at 5:54 am

    #654265

    Thanks Robert.  I'll give that a try.  I know that scalability is an issue although I am quite sure we we only need two servers for this task.  Thanks again.

    Brent

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply