SQL Server Performance

Set up replication from a restored copy

Discussion in 'Performance Tuning for SQL Server Replication' started by ndinakar, Jun 23, 2006.

  1. ndinakar Member

    I need to make some big time changes to the publisher DB (which is the PROD DB). So I want to disable replication, make all the changes, back up the db and restore it on the reporting server and setup replication between the PROD and REPORTing db's. How do we resolve the IDENTITY column issues? All the (few) articles I have see on google talk about creating blank tables and set the IDENTITY NOT FOR REPLICATION option but there is not a single article that talks about setting up REP from a restored copy. I cannot create a blank db and set up snapshot as the db is 2.7 TB and it will take forever for the log agents to catch up.

    Anyone has any ideas?

    Thanks,
    Dinakar

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  2. satya Moderator

    As you say they are 'big' changes then why don't you refresh the replication after performing the changes on main database and then redefine the replication.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  3. ndinakar Member

    hi Satya

    Can you rephrase what you mean by refresh the replication?
    The changes are more in number ( a few hot fixes to data and some scheme changes) and the mgmt wants to re-setup replication. So the only option left for me is to disable replication, make all the changes on the PROD, back up the copy and restore on the reporting db and set up a fresh trans-replication. I know I need to change identity columns. But I was having some issues with Identity columns. The replication set up went fine but when I updated a record on the publisher and checked to see if the change got propagated to the subscriber, the rep failed with an error "Cannot update IDENTITY column". So I was wondering if there are any pre-replication setup changes I need to make to the tables having IDENTITY columns.

    Based on my understanding, (1) I can restore the prod copy, then manually go into each table that has IDENTITY column and turn it off on the restored copy (2) or I need to manually go into design view for each table on the PROD and change the IDENTITY property to "Yes, Not for Replication". And we have around 350 tables that have IDENTITY columns.


    any thoughts on how to resolve the issues?

    Thanks.
    Dinakar

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  4. ndinakar Member

    I have figured this out.

    thanks for anyone who looked into.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************

Share This Page