SQL Server Performance

Implementing replication w/internal id fields...

Discussion in 'SQL Server 2005 Replication' started by gcetti, May 5, 2008.

  1. gcetti New Member

    Hello,

    I'm new to replication - we are trying to see if we can implement it on our database application.
    I'm having a problem understanding how to deal with internal key fields (programattically generated integers) in our database that we use to link the tables together. The key fields must be unique, so at merge time any new record that was added the key must be adjusted (for primary and all one to many tables) to be offset so it is unique when pushed to the publisher.
    With multiple subscribers, the internal id field will be incrementing at each site - and it is not a conflict when they run into each other because the surrogate key is unique, but the internal id needs adjusted so it is on the server.
    I'm working with writing a custom logic DLL in C# overriding the BusinessLogicModule - but I dont understand how I can change the data before it is inserted. From what I see the insertedDataSet and updatedDataSet are passed in by value and cannot be changed - is this correct? How can I adjust my internal id fields before it actually gets saved at the publisher end?
    What is the best way of handling this situation? The replication adds the rowid field for the unique id of the record, but I still want to use our internal Id field that we use to link the tables together.

    Thanks,
    Greg
  2. satya Moderator

    See this Replicating Identity Columns article that talks about handling the identity columsn within the merge replication, there are some issues associated with managing identity columns with replication in your SQL Server database. As with previous releases of software, SQL Server 2005 requires to use special care when replicating tables with identity columns.
    If you must use updateable subscriptions, you need to define identity ranges on publisher and subscriber servers to avoid the creation of duplicate primary keys. further within the books online it refers:
    ...that replication will copy check constraints from publisher to subscriber(s) by default. If you're managing identity seeds manually, be sure to change this default behavior (using article properties' dialog) so that these check constraints aren't replicated.

Share This Page