This is the fifth installment in the 12 day series for SQL tidbits during this holiday season.
Previous articles in this mini-series on quick tidbits:
In the digital age it seems we are constantly flooded with articles about identity crises. From identity theft to mistaken identity. SQL server is not immune to these types of problems and stories. Whether SQL Server was housing the data that was stolen ,leading to identity theft, or if SQL Server is having an identity management issue of its own – SQL Server is definitely susceptible to the identity issues.
The beauty of SQL Server is that these identity issues seem to be most prevalent when trying to replicate data. Better yet is when the replication multiple peers setup in a Peer-to-Peer topology.
When these Identity problems start to crop up there are a number of things that can be done to try and resolve them. One can try to manually manage the identity ranges or one can flip the “Not for Replication” attribute on the table as two possible solutions.
The identity crisis in replication gets more fun when there are triggers involved. The triggers can insert into a table that is not replicated or can insert into a table that is replicated. Or even better is when the trigger inserts back into the same table it was created on. I also particularly like the case when the identity range is manually managed but the application decides to reseed the identity values (yeah that is fun).
In one particular peer-to-peer topology I had to resort to a multitude of fixes depending on the article involved. In one case we flipped the “Not for Replication” flag because the tables acted on via trigger were not involved in replication. In another we disabled a trigger because we determined the logic it was performing was best handled in the application (it was inserting a record back into the table the trigger was built on). And there was that case were the table was being reseeded by the application.
In the case of the table being reseeded we threw out a few possible solutions but in the end we felt the best practice for us would be to extend the schema and extend the primary key. Looking back on it, this is something that I would suggest as a first option in most cases because it makes a lot of sense.
In our case, extending the schema and PK meant adding a new field to the PK and assigning a default value to that field. We chose for the default value to be @@ServerName. This gave us a quick location identifier for each location and offered us a quick replication check to ensure records were getting between all of the sites (besides relying on replication monitor).
When SQL Server starts throwing a tantrum about identities, keep in mind you have options. It’s all about finding a few possible solutions or mix of solutions and proposing those solutions and then testing and implementing them.
One of the possible errors you will see during one of these tantrums is as follows.
Explicit value must be specified for identity column in table ‘blah’ either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
