1

We have to do database replication with SQL Server; the two database are on different servers.

We achieve replication, but our scenario is when live is down so we use replicated server as our production server.

Here is where the problem occurred: when we insert anything on replicated server from application it fails because of violation of a primary key constraint.

And yes, it violates b/c on replicated server primary key starts from 1

How to overcome this problem? Or it is correct way to convert replicated server to production server?

migrated from stackoverflow.com Aug 3 '18 at 19:01

This question came from our site for professional and enthusiast programmers.

  • 1
    Are you asking about a table with identity column, which happen to be primary key? – SqlWorldWide Aug 3 '18 at 19:07
1

In case of aligning identity columns, run this on the source:

 SELECT 
        IDENT_SEED(TABLE_SCHEMA+'.'+TABLE_NAME) AS Seed,
        IDENT_INCR(TABLE_SCHEMA+'.'+TABLE_NAME) AS Increment,
        IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) AS Current_Identity,
        TABLE_SCHEMA+'.'+TABLE_NAME as table_name,
        'DBCC CHECKIDENT('''+TABLE_SCHEMA+'.'+TABLE_NAME+''', RESEED,'+CAST(IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) +1  AS VARCHAR(10))+')' as Command
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),'TableHasIdentity') = 1
    AND TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME not like 'Mspeer%'
    AND TABLE_NAME not in ('sysarticles','syspublications')
    ORDER BY TABLE_SCHEMA, TABLE_NAME

The code to run on the replica is in a column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.