We've been doing a one-way sync from FileMaker Server to a data warehouse on SQL Server for years. MirrorSync runs every 15 minutes to pump the data from FileMaker tables to SQL Server tables.

We'd like to archive records in the FileMaker system but retain them in the data warehouse. This is primarily for performance and usability reasons. The FileMaker users have no need to refer to the records that are more than 5 years old, of which there are a few hundred thousand. Hence, we'd like to archive them, but the finance team doesn't want to lose them out of their data warehouse. 

Is there a way to remove the records from FileMaker while retaining them in the data warehouse? Ideally we'd have a sync that didn't replicate the deletions, but then return to business as usual after that sync was done. 

ETA: Sorry i'd not read the prior post, which sounds quite pertinent. I'll look into the selection criteria through the script as that may be able to accommodate our needs.

Edited November 9, 20205 yr by _ian

Hi Ian - a few questions.

1) Is FileMaker configured as the hub or the spoke in this sync?

2) Ordinarily, if a record is deleted in FileMaker, do you also want it to be deleted in SQL Server? Or should deletions never be synced?

--Jesse Barnum

Hi Jesse

FileMaker is configured as the hub.

 

Ordinarily we do want to sync record deletions. However, that is probably only true in some tables. Sales invoice lines, for example, require deletions to be synced because project managers will add and delete lines while they're in a draft state. Is that something we can set on a table by table basis? We will not be archiving any invoice related data.

There are two ways that you can prevent records from being deleted on the SQL database when they are deleted in FMS.

1) You can set MirrorSync to simply ignore all deletions. This is easy to change, but it applies to all tables and all configurations. If you want to do this, modify the file at C:\Program Files\360Works\Applications\conf\Catalina\localhost\MirrorSync.xml. Find the 'deletionScanning' parameter and change it from 'auto' to 'never'.

2) You can set up SQL as the hub, and FMS as the spoke. If you do that, then tables set for bidirectional sync will sync deletions, but tables that are one-way from spoke to hub will ignore deletions (MS never deletes records on the hub, if they are deleted from the spoke and the sync direction is one-way from spoke to hub). This is not really an ideal approach; you may not want to do a bidirectional sync, and getting the initial sync to work will be complicated. I'd recommend option #1.

--Jesse Barnum

thanks Jesse. My current thinking is to set it to ignore all deletions. I'll also revise my selection criteria to only replicate sales invoices that have been finalised. Those are never edited, so that avoids the need to replicate any deletions.