0

Using MS SQL Server to replicate data between 2 servers for security purposes. Is it possible to perform an operation on the data sent from the publisher database before it is inserted into the subscriber table?

I have a table (lets call it DeviceData) that has a column DeviceName on the publisher. DeviceData is being subscribed to on the subscriber database, however I need DeviceData to have a foreign key on it referencing a table Device. Is there anyway to check what data is not in table Device in column DeviceName and insert it into Device before it is inserted into DeviceData?

Here is a visual example I drew: enter image description here

JudasMoses
  • 113
  • 4

1 Answers1

1

Alternatively to MBuschi's great idea, you can also look into possibly using an INSTEAD OF INSERT trigger on the DeviceData table. This trigger would first verify the data you need is in the Device table, and then would insert the replicated data into your DeviceData table. (Here's Microsoft's Books Online for Triggers.)

One pro of this solution is it doesn't muck about in your replication setup (should you ever need to script it and redeploy it). While Microsoft offers docs on how to accomplish MBuschi's solution, in general I find the more customizations to a replication topology, the more of a pain it can turn into to manage in the long term.

But an even better pro of using a trigger is that it decouples the data synchronization feature you use from the relational integrity logic you're trying to support. So if one day you decided to switch from replication to another synchronization technology such as SSIS (or even another type of replication), you wouldn't have to re-implement a solution to account for the Device table. Using a trigger ensures that your relational integrity logic is saved at the table level, and is applied no matter where the insert into your DeviceData table comes from.

J.D.
  • 40,776
  • 12
  • 62
  • 141