I am looking for some advice on how best to implement a near real-time data sync into a MDS entity.
The source systems are on a SQL Server 2008R2 instance
MDS is installed on SQL Server 2012 Instance
A linked server has been created on the 2008R2 instance to the 2012 Instance
I need to publish the details of a customer into the MDM entity as soon as it is changes or is created in the source system. My initial approach was to create a Insert, Update, Delete trigger on the source table which was responsible for populating
the MDS staging table and then calling the MDS load and validate sprocs via the linked server, however the trigger fails with the following error:
Cannot use SAVE TRANSACTION within a distributed transaction.
I thought this error may have been due to calling the MDS sprocs from the source table trigger over a linked server, so I changed the source table trigger to only stage the data into MDS staging table and then created another trigger on the MDS staging table
that called the load and validate MDS sprocs. Unfortunately this also resulted in the same error.
Any advice on how best to implement a real time (is it even possible) data sync in MDS would be greatly appreciated.