none
SQL Server PK when there is Replication RRS feed

  • Question

  • I had worked on product that use SQL server with replication once in the year 2000, now I am working on another one.

    in that project, we put a setting in the application telling the software from which number it should start, that numbering system exist in table (which is not replicated) so that each server or location can has it is own starting numbering system for each, location #1 will start invoicing from 1, location #2 will start invoicing from 1000000, the same goes for customer number, payment, receipt , etc...

    What is best method used/recommended (table structure) when creating a project that has sql server replication?

    I am doing the project in vb.net 2008, using sql 2005.

    If this forum is wrong for my question, please move it to the correct one and thanks.

    TIA

    Samir Ibrahim
    Saturday, March 6, 2010 1:09 PM

Answers

  • Use an identity column and add a location field to your tables.

    Then use automatic identity range management and filter what goes to each server by the location field.

    If you are using merge replication you can use dynamic join filters to only send the appropriate data to each subscriber.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by Samir Ibrahim Sunday, March 7, 2010 6:21 PM
    Saturday, March 6, 2010 4:48 PM
    Moderator

All replies

  • Use an identity column and add a location field to your tables.

    Then use automatic identity range management and filter what goes to each server by the location field.

    If you are using merge replication you can use dynamic join filters to only send the appropriate data to each subscriber.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by Samir Ibrahim Sunday, March 7, 2010 6:21 PM
    Saturday, March 6, 2010 4:48 PM
    Moderator
  • Your answer was more than enough.

    Thank you.
    Sunday, March 7, 2010 6:22 PM