Post reply

Question of Replication, Transaction Publication

  • June 7, 2011 at 2:07 am

    #251303

    Hi guys,

    It is my first time to set up replication in SQL Server 2008 R2. The situation is I have a PRODUCTION database and would like to create a TEST database which has the live data instantly. I thought REPLICATION is a ideal solution, which means everytime PRODUCTION database inserted, updated, deleted. The live data would affect TEST database at the same time.

    Created REPLICATION and selected Transaction Publication. Everything is working until a test programmer tried to insert a new row in a table. The error was quite clear, the IDENTITY column had been replcated but not set to the right value. That means, when a new record created, a duplicated IDENTITY value occured and error raised.

    I did some study on REPLICATION and particularly concerned if I selected wrong Publication type.

    So, could you guys give me some advise on this senario and is there anyway to reset IDENTITY column to the right value in the subscriber database? ( Forgot to say that there are about 30 tables, so reset IDENTITY column one by one is quite a big job... )

    Or is there any other better way to create a TEST database which has its data updated simultaneously with LIVE database?

    (sorry for my english 🙂 ). Any help please ~

  • June 7, 2011 at 2:58 am

    #1334843

    welcome to the wonderful world of replication! if you read though BOL and/or a couple of books or there must be stuff on sql server central, you'll find out that you have a number of things you have to specifically deal with, identity columns are one. You normally have to modify the replication proc and make sure your replicated table doesn't set an identity value. (for the situation you ask )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • June 7, 2011 at 3:11 am

    #1334850

    Delete this duplicate record in subscriber database manually.

  • June 7, 2011 at 6:02 am

    #1334919

    Delete duplicated records would not be quite suitable for this situation. I am looking for a way to reset IDENTITY column to the right value ( but not reset them one by one ... )

    Any suggestion, please?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply