We have a big database (100 GB) in our production environment and we have two mirrors. we use replication to sync our mirrored database.(Transactional Replication on SQL Server 2012)
when we created replication, our DBA first download full backup from our servers and restore it on Mirror server ,then configured the replication between Publisher (Production-DB) and Subscriber (Mirror-1). Everything works fine and mirror and production are synced perfectly.
before each release, we should test our system on our UAT server with REAL! data. so we create a back up from one of our Mirrored Servers (onsite mirror) and Restore it on our UAT Database.
All table in our database have one PK like :
[ID] [int] IDENTITY(1,1) NOT NULL
In our UAT Database the last record of OrderTransaction table ID = 160.000.000, but when our application try to insert into this table, system encounter a problem and exception raised.
Violation of PRIMARY KEY constraint ‘ID’. Cannot insert duplicate key in object ...
system try to insert an integer value smaller than 160.000.000 (ID = 145.695.000) as i queried the database this id is the last ID when the backup of production db restored to mirrored db and after that transactional replication started. all information on production and mirrored are synced correctly and ID of the Tables on both servers are the same and identical.
When I run this command on the UAT or Mirrored Database it returns 145.695.000
SELECT IDENT_CURRENT( 'OrderTransaction' )
as I read MSDN, blogs and related SO question, this problem related to replication and identity columns.
But, how can i force our Mirror database to grow its Identity with Production.(and accept this value as local identity value). Or may be we should write a script that reseed every table for testing in our UAT environment!?
thanks.
If you are only having issues when restoring a prod mirror into UAT your simplest solution, as you suggest, is likely to just script a reseed for each table.
For more general problems replication and identity problem you may find this excellent article from Hilary Cotter on Simple-Talk useful.
In this article we looked at ways to implement identity management solutions in replication to avoid primary key conflicts. We looked at partitioning and manual and automatic identity range management solutions. Solutions using automatic identity range management can work well with careful planning.
EDIT: This should reset all the identities to the current max value:
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED)'
Which is a slight modification to Pinal Dave's work.