Suppress deletions of certain records in the spoke database

[Markus Selbach]

We want to use MirrorSync to synchronise a very large FileMaker database to a SQL Database.

The Hub (FileMaker) Database controlls our production and should contain only data from the last 2 years.

The Spoke (SQL) Database is used by BI-Department to analyse actual and historical data and must contain alltime data

The direction of the sync is one-way from hub to spoke.

On a certain date all data older than 2 years is deleted (really deleted - not just marked as deleted!!!) from the hub. I'm wondering how can I prevent MirrorSync from syncing these deletetions to the spoke? 

One solution would be to clear or extend the primary keys in the spoke. This would work, because the primarykeys were created only for MirrorSync. But it's not a nice solution. I would prefer a seperate field "locked" in all tables that marks these records as locked. Is there any customization option that let me exclude records where locked=1 from being deleted by MirrorSync?

 

Thanks in advance for any ideas on this topic.

Best,
Markus

[nick360Works]

Hi Markus,

Thanks for explaining your setup clearly. This is something that can be easily accomplished using the MirrorSync Customization Script, which is now a part of the MirrorSync Script in MS 6.

In the "WillDelete" / A RECORD WILL BE DELETED ON THE HUB section of the script, you can constrain your found set to omit and filter out which records you want to be deleted. Therefore, you could limit your deletions only to records that are marked in a certain way (ie, existing records only) that you would want to be deleted.

Unfortunately, specific MirrorSync customization falls outside the scope of our free support. If you would like one of our developers to help you write this customization we can do so at our hourly rate of $185.

[nick360Works]

Markus,

Apologies, that information is only pertinent in cases where the deletions occur in FileMaker. Since your setup is a server to server, with your SQL database as the spoke, there is no way to selectively disable records from hub to spoke.

One alternative is to disable deletions entirely in the MirrorSync.xml file. That way, when hub records are deleted, the records would remain in SQL.

You can locate that file at the following location:

/Library/360Works/Applications/webapps/MirrorSync/META-INF

You would then modify the following parameter to a value of "never":

<Parameter name="deletionScanning" value="auto" />

[Markus Selbach]

Thanks Nick for your ideas. 

Unfortunatly disabling deletion completly is not an option because only the historical data should be frozen. 

Currently I'm investigating if our spoke SQL DB supports record-level-security, so that we can make delete requests from mirrorSync user fail (silently?) if fieldvalue locked=1. I will post results here if this works.

[Markus Selbach]

As our MySQL database doesn't support record-level-security I can't tell if this approach would work in general 😕.

I tried adding a before delete trigger 

BEGIN
IF OLD.Locked= 1 THEN
  SIGNAL SQLSTATE '02234'
  SET MESSAGE_TEXT = 'not found';
END IF;
END 

which works. But as an error is thrown MirrorSync will retry to delete on every sync, which might drop performance when databases become big.

Therefore I'm giving up on this.