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.
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.
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.