I have multiple site servers and a central server. Each has a Widget
table with identical schema. There is currently a custom process that sends new Widget
records from the site server that generated it to the central server. If at any point a Widget
needs to be sent from one site to another for further processing, there is a process that copies the Widget
record from the central server to the necessary site server.
At this point Site A will have all of its own Widget
records, plus x number of Widget
records from Site B. Site B will have only its own Widget
records.
If I were to set up Site A and Site B to replicate the Widget
table to the central server, will adding these new records to Site A (and that already exist in the central server) have any adverse effects on replication? We are using SQL Server 2008 R2 SP2.
Best Answer
Correct me if I'm wrong, but this is how I understand the process of copying widgets.
Copy widgets from the site servers (A and B) to the Central Server
If a widget needs additional processing, copy that widget from the central server to its site server A or B
At this point, site A has its own records plus X site B records
Transaction replication is sending widgets from A and B to the Central server
The Site B records on Site A may already exist on the Central server because Site B may have already sent them to the Central server via transactional replication.
As published tables must have a primary key, the replication would fail when Site A attempts to replicate Site B's widgets to the central server.
You could skip those errors via the Agent Profile, if you had the specific error number.
From SSMS 1. Right Click on Replication
Select Launch Replication Monitor
Expand My Publishers
Expand the Publisher you want to Modify
Select the Publication.
In the subscriptions window to the right, right click on the Subscription and select Agent Profile.
Create a new profile and select skip_errors
In the SkipErrors row, add in the error numbers seperated by : that you wish to skip.
That is probably not what you are trying to accomplish, as the replication would crash every time you copied widgets from Site B to Site A.
Question, are you trying to partially or fully replace the existing process of copying widgets between sites with transactional replication?
Note
There may be a more elegant way to do this, such as using TSQL to copy records in batches from Site A and Site B to Central, if they don't yet exist. You could accomplish that using OPENQUERY or SSIS.
Here is a crude example using OPENQUERY