SQL Server Performance Forum – Threads Archive

Transaction Replication from Prod to Test

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?

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

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.

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

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?

Agent profile name is "Continue on data consistency errors." MohammedU.
Moderator
SQL-Server-Performance.com

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.

Right click the distribution agent/Agent Profiles/ MohammedU.
Moderator
SQL-Server-Performance.com

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).

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

Thanks, that was far from intuitive.

]]>