identity columns and transactional replication

The use of Identity columns in a transactional replication topology is not supported. However, identity columns are commonly used in database design as keys. This blog will consider what can happen if a database with identity columns is replicated.

To test what can go wrong, I created a table (T1) with an identity column (C1). I then created a publication and a subscription. Using the below SQL,

Insert R1 (c2,c3,c4) values (25′,’Test insert’,’13 Nov 2012′)

I inserted rows on the subscriber. The identity column is not neded in this statement. After running this SQL, the subscriber has several rows – as below.

Finally, I began to insert rows to the publisher using the same TSQL. This appeared to work – there were no errors, and the data was present on the publication database.

However, the insert was not replicated. The error:

Violation of PRIMARY KEY constraint ‘PK_R1’. Cannot insert duplicate key in object ‘dbo.R1’. The duplicate key value is (1).(Source: MSSQLServer, Error number: 2627)

was recorded in the “Distributor to Subscriber” tab of the “Subscription Details” window (below).

Subscriber – errors

This post has identifies a serious issue with identity columns in a replication topology. This can be controlled through several techniques.

If the Subscriber is read-only, there should not  be any issues. Changes to the identity column are replicated as they happen. Assuming that the seed value remains consistent between the publisher and all subscribers, all should be well.

It is possible to assign identity ranges to each subscriber when the replication is established. There are a number of management issues here – for example, how to maintain ientity ranges after a backup.

In conclusion, identity columns should – if possible – be avoided in a replication topology. However, this may not be possible as the database may not have been designed with replication in mind. In this case, care should be taken when implementing replication.