April 21, 2009 at 10:14 am
Hi,
We have two servers with databases using Peer to Peer replication. We are steering the Development teams to use Composite Keys in order to avoid Replication Conflicts and the extra overhead of managing Identity ranges. I know there are a number of ways to avoid confilcts i.e setting Odd\Even ranges or having Composite keys based on a Composite of an Identity value and Location Value as mentioned above.
However, the Dev team have proceeded without thinking about Composite Keys (we did tell them!) and now we are making them redevelop their code. This is a lot of work as they have to redesign all their Stored Procs and schema. So, they have come up with another solution to reduce their redevelopment efforts. Looks really good at face value.
- Create two new columns at the end of the table. One is an Identity Column and the other is a Location ID column (populated by a seperate table)
- Make the Primary Key a 'Persisted Computed Column' based on a simple formula on the two new columns e.g (SampleIdentity*10) + SampleLocationID
The replicated row will be based on this computed column and in thoery there should never be any conflicts.
My question(s)
- Has anyone ever used this type of solution?
- Are there any know issues with using Replication and Persisted Computed Columns?
- Does the above formula need to be more complex?
Regards
S
April 21, 2009 at 2:33 pm
It should not be a problem theoretically but Identity columns are still identity even if they are being used indirectly. If you happen to change your inserts to run on the replica you will have to reseed first no matter what.
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply