SQL Server Performance

Transaction Replication from Prod to Test

Discussion in 'SQL Server 2005 Replication' started by we7313, Mar 14, 2007.

  1. we7313 New Member

    Currently we are in need of copying prod data to a test environment.
    This works fine with Transactional replication. The problem is when someone changes a data row in the test environment & then the same row is prod gets updated.

    Example:
    Prod Table has 3 columns
    ABC 123 ABC

    Test has the same
    ABC 123 ABC

    The test data now changes to:
    AAA 123 ABC

    The prod data now changes to:
    ABC 222 ABC

    It seems to break the replication with the following error:
    Error executing a batch of commands. Retrying individual commands.

    I would expect the data from prod to just do an insert if it cant find the row to update.
    It also stops all other transactions from being replicated.

    Any ideas?

  2. MohammedU New Member

    Transactional replication requires PK for each table involved in it...
    There will not be a problem as long you don't modify the PK...But replication will not insert the modified row as new row but it will be updated on subscriber...

    You will end up....
    The test data now changes to:
    ABC 222 ABC


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. we7313 New Member

    I should have specified that the first column was the primary key. I assumed that an insert would be sent down in their were no rows to update on the same constraint. Instead I recieve the error messsage.

  4. MohammedU New Member

    Yes, it is correct but you can over come that issue by changing the agent profile...

    I don't remember the agent profile name at this time...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  5. we7313 New Member

    I've also been looking in the agent profile. I am unable to find anything that would resolve this issue? Do you know where abouts I should be focusing in the agent profile?
  6. MohammedU New Member

    Agent profile name is "Continue on data consistency errors."



    MohammedU.
    Moderator
    SQL-Server-Performance.com
  7. we7313 New Member

    Actually the area I'm looking in is the Article properties (in the publisher). Is this the area you are refereeing to as the Agent profile? If so I do not see this attribute.
  8. MohammedU New Member

    Right click the distribution agent/Agent Profiles/

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  9. we7313 New Member

    In the Microsot Sql server management studio/ replication I only have a folder for local publications & local subscriptions. Is this the area where I should see the distribution agent? Also, I do have the Distributor running on the same server as the publisher and subscriber (for this test scenario).
  10. MohammedU New Member

    Expand the local publication and click the publication/click launch replication monitor/Expand server list/click on publication/
    On the ride hand side of the window you see the subscriptions...
    Right click the subscription/Click Agent Profile...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  11. we7313 New Member

    Thanks, that was far from intuitive.

Share This Page