Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Location
    Dublin, Ireland
    Posts
    2

    Unanswered: replicating 3 databases causing problems?!

    We are trying to deal with replication in a legacy design involving 2 SQL servers each taking INSERTS from about 100 call centre client PCs. In each case a client logs into either SQL Server and upon each INSERT is handed a unique Call_ID to use when inserting additional information in relation to that specific call.

    Each of the two databases are subsequently being replicated into a third database where reports are being pulled.

    The problem is that to prevent each database giving the same Call_ID to a client we have setup SQL 1 to use a Call_ID starting with 1 and incremented by 2 (i.e odd numbers!). SQL 2 starts with 0 and increments by 2, (even numbers). These ‘increment’ rules are built into the table schema and seem to be causing a problem when we try to replicate into the third database as the two initial schemas are not considered identical.

    The first database to be replicated will work and the second will fail. We get messages saying it is due to unique values.

    I thought we may be able to have identical schemas by changing the ID field to a fixed 12 digit number and prefix it with a 10xxxxxxxxxx on one server and 11xxxxxxxxxx on the other. The 10 and 11 would be held in a table with the value being pulled based upon server name.

    Server Value
    SQL1 10
    SQL2 11

    Hence we would be able to extract the value and prefix the ID with it.

    Has anyone come across the reason as to why the first replication will work but the second will always fail? And would this mod solve the problem?

    Moreover I suspect that our design is fundamentally flawed and that we need to have two servers handling a single database? This single database would then more easily be replicated to the reports database.

    Thanks for any input!

    Paul

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem is that you are using two transactional publications... The existing databases should work nicely using a merge publication, but beware that this implies that any changes made in the reporting server would then replicate back to the entry servers.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •