Sql-server – Database Replication SQL Server

replicationsql serversql-server-2008-r2transactional-replication

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?

Best Answer

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

Related Question