000014446 - How to reseed identity columns in SQL Server?

Document created by RSA Customer Support Employee on Jun 16, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000014446
Applies ToRSA Key Manager Server 2.1.3
Microsoft Windows Server 2003 SP2
RSA Key Manager Server 2.2
Microsoft SQL Server 2005
RSA Key Manager Server 2.5.0.x
IssueSQL server identity columns not replicated
How to reseed identity columns?
27 Feb 2009 12:00:01,694 1235764801537 Client 1 (6) ERROR TP-Processor2 - Query error
edge.java.sql.SqlException: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK_STATE_AUDIT'. Cannot insert duplicate key in object 'dbo.STATE_AUDIT'.
 at edge.java.sql.DefaultPreparedStatement.executeUpdate(KeyManager:80)
 at com.rsa.keymanager.core.database.sql.execute.DefaultSqlExecutor.update(KeyManager:40)
 at com.rsa.keymanager.core.database.sql.execute.DefaultSqlExecutor.insert(KeyManager:51)
 at com.rsa.keymanager.core.database.sql.cud.DefaultStateAuditCud.create(KeyManager:32)
 at com.rsa.keymanager.core.database.sql.cud.DefaultStateTimelineCud.d(KeyManager:64)
 at com.rsa.keymanager.core.database.sql.cud.DefaultStateTimelineCud.create(KeyManager:35)
CauseIdentity columns on secondary SQL Server has not been reseeded.
Resolution

This fix below is good only for RKM Server 2.1.3.x and 2.2.x

If you have one primary and one secondary SQL Server

Verify that the identity columns have values lower than 1000000000. Run the following on both the primary and secondary SQL Servers:

use RKM
SELECT OBJECT_NAME(OBJECT_ID) AS TABLENAME,
            NAME AS COLUMNNAME,
           
SEED_VALUE,
            INCREMENT_VALUE
,
            LAST_VALUE
,
            IS_NOT_FOR_REPLICATION
  FROM SYS.IDENTITY_COLUMNS
WHERE OBJECT_NAME(OBJECT_ID) = 'CRYPTO_POLICY'
      
OR OBJECT_NAME(OBJECT_ID) = 'IDENT'
      
OR OBJECT_NAME(OBJECT_ID) = 'IDENTITY_GROUP'
      
OR OBJECT_NAME(OBJECT_ID) = 'STATE_AUDIT'

Once it is determined that the identity columns have not been reseeded on the secondary, run the following:

USE
RKM;
GO
DBCCCHECKIDENT('CRYPTO_POLICY', RESEED, 1000000000);
GO
DBCCCHECKIDENT('IDENT', RESEED, 1000000000);
GO
DBCCCHECKIDENT('IDENTITY_GROUP', RESEED, 1000000000);
GO
DBCCCHECKIDENT('STATE_AUDIT', RESEED, 1000000000);
GO
 


Use the following for RKM Server 2.5.x.x

SELECTOBJECT_NAME(OBJECT_ID) AS TABLENAME, 
       NAME AS COLUMNNAME, 
       LAST_VALUE
, 
       SEED_VALUE
, 
       INCREMENT_VALUE
, 
       IS_NOT_FOR_REPLICATION 
  FROM SYS.IDENTITY_COLUMNS
WHERE OBJECT_NAME(OBJECT_ID) = 'CRYPTO_POLICY'
   OR OBJECT_NAME(OBJECT_ID) = 'IDENT'
   OR OBJECT_NAME(OBJECT_ID) = 'IDENTITY_GROUP'
   OR OBJECT_NAME(OBJECT_ID) = 'STATE_AUDIT'
   OR OBJECT_NAME(OBJECT_ID) = 'POLICY'
   OR OBJECT_NAME(OBJECT_ID) = 'JOB_DEFINITION'
   OR OBJECT_NAME(OBJECT_ID) = 'JOB'
   OR OBJECT_NAME(OBJECT_ID) = 'AUTOREG_PROFILE'
   OR OBJECT_NAME(OBJECT_ID) = 'AUTOREG_KEYSTORE'

DBCCCHECKIDENT('CRYPTO_POLICY', RESEED, 1000000000);
DBCC CHECKIDENT('IDENT', RESEED, 1000000000);
DBCC CHECKIDENT('IDENTITY_GROUP', RESEED, 1000000000);
DBCC CHECKIDENT('STATE_AUDIT', RESEED, 1000000000);
DBCC CHECKIDENT('POLICY', RESEED, 1000000000);
DBCC CHECKIDENT('JOB_DEFINITION', RESEED, 1000000000);
DBCC CHECKIDENT('AUTOREG_PROFILE', RESEED, 1000000000);
DBCC CHECKIDENT('AUTOREG_KEYSTORE', RESEED, 1000000000);
GO

NotesView other solution a34944  - Does RSA Key Manager support replicated Microsoft SQL Server 2005?
Legacy Article IDa44804

Attachments

    Outcomes