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

Improve this question
3
  • 2
    you can change the way how changes are propagated with a custom stored procedure that does an insert in your device table and then an insert in your device data table. take a look here: learn.microsoft.com/en-us/sql/relational-databases/replication/…
    – MBuschi
    Commented Oct 7, 2021 at 8:14
  • @MBuschi that comment really does deserve to be an answer
    – Michael Green
    Commented Oct 7, 2021 at 10:33
  • I was responding from the car.... :-)
    – MBuschi
    Commented Oct 11, 2021 at 6:35

1 Answer 1

Reset to default
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.

Improve this answer
5
  • This sounds like a good method but will SQL Server not remove that trigger once the schema is reset through a snapshot restore?
    – JudasMoses
    Commented Oct 11, 2021 at 4:25
  • @JudasMoses You mean a backup restore?...if the backup was taken before the Trigger was created, then yes the Trigger would be gone, but that's true for pretty much any solution you could implement. As long as you take regular backups though, and end up restoring one after when the Trigger was created, then the Trigger will remain.
    – J.D.
    Commented Oct 11, 2021 at 11:29
  • No I'm talking about when a subscriber is reinitialised. All replicated tables on the subscriber are dropped and recreated.
    – JudasMoses
    Commented Oct 12, 2021 at 3:44
  • @JudasMoses It depends on the kind of Trigger you create, but yes if it's a Table Trigger and you have replication setup to drop and recreate the tables, then it would drop it. In that case, you can either change your Publication Articles' properties and set the "Action when name is in use" to TRUNCATE instead of the default of DROP or create a Post-Snapshot script that re-creates the Trigger. Both are loosely discussed here. I've used both options before.
    – J.D.
    Commented Oct 12, 2021 at 3:49
  • I've discovered problems with this setup. It seems the triggers are not applied when a subscriber is reinitialised. I have opened a seperate question on it here
    – JudasMoses
    Commented Oct 13, 2021 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.