Will Transactional Replication Work?

Question

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.

Answer 1

Correct me if I'm wrong, but this is how I understand the process of copying widgets.

1. Copy widgets from the site servers (A and B) to the Central Server

2. If a widget needs additional processing, copy that widget from the central server to its site server A or B

3. At this point, site A has its own records plus X site B records

4. Transaction replication is sending widgets from A and B to the Central server

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

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

2. Select Launch Replication Monitor

3. Expand My Publishers

4. Expand the Publisher you want to Modify

5. Select the Publication.

6. In the subscriptions window to the right, right click on the Subscription and select Agent Profile.

7. Create a new profile and select skip_errors

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

/* From Central server, 
insert all records from SITEA except those that already 
exist on Central
*/
INSERT INTO widgets (widgetID, widgetName, widgetDate, widgetDesc)
SELECT widgetID, widgetName, widgetDate, widgetDesc
FROM openquery(SITEA_SERVER, '
 select widgetID, widgetName, widgetDate, widgetDesc 
 from widgets_database.dbo.widgets
')
EXCEPT 
SELECT widgetID, widgetName, widgetDate, widgetDesc
FROM widgets WITH (nolock)