Today, most of us take for granted our access to high-speed internet. But there are still parts of the world where connections are slow and spotty. Recently, a customer came to us with a legacy system that they wanted to share with a remote location 100+ miles away. During the off-season, the office location solely edits the system, but when the lodge opens for the season, most of the data is entered from the lodge location. The office still needs to receive updates for reporting and input of new reservations.
The internet connection at the lodge is slow, and FileMaker Pro access to the remote server was poor. Faster service was available, but it would be significantly more expensive and require year-long contracts. StarLink internet was considered, but there were serious concerns about latency and connection stability for FileMaker Server.
Our solution was to use 360Works MirrorSync to allow both locations to use the system with extremely fast local area network performance..
What MirrorSync does
MirrorSync is a utility for FileMaker Pro databases that allows data to be synced between FileMaker Servers, a FileMaker Server and an offline client, or a FileMaker Server and other data sources, including SQL databases, Salesforce, WordPress, and Amazon DynamoDB. It also offers load balancing and server failover options. Syncs can be scheduled at intervals as short as every 60 seconds, with more frequent syncs reducing the amount of data that has to be transferred. If one of the locations becomes inaccessible, the sync will keep trying until a successful connection is established. If the sync fails mid-way through the process, MirrorSync ensures that no data is lost and rechecks consistency the next time a sync is triggered successfully.
For our client, we needed to sync their office FileMaker Server with the lodge’s server, where the internet can be slow and unreliable at times.
MirrorSync requirements
Before getting started, we needed to ensure the solution had all of the items MirrorSync requires. Every table in the system must have the following:
- A unique primary key (PK),
- A modification timestamp field set to auto-enter a modification timestamp,
- A creation timestamp field set to auto-enter the creation timestamp,
- No fields with a backslash (\) in the name, and
- Layouts for all of the tables you want to sync.
For our client’s solution, we had to add the required timestamp fields. Our first tip is to append “_Host” to the name of your timestamp fields. This allows MirrorSync to automatically identify these fields, saving significant time during the setup process if your solution has a lot of tables. Otherwise, you will have to manually map them during setup. If you need to create these fields in your solution as we did, there’s no need to prefill them with any data.
For the layouts, we needed to create new blank layouts for MirrorSync to use. MirrorSync can auto-detect these during the setup process if the layout names contain a prefix. “Sync_” is the default prefix, but the prefix can be anything. During the MirrorSync setup, you can enter a custom prefix to automate the layout identification process.
Unlike previous versions of MirrorSync, there’s no need to put any fields on these layouts unless you only want specific fields to sync. By default, calculations, globals, and summary fields are ignored. MirrorSync will also skip any database that doesn’t have a specified layout.
Mirrosync uses a “hub/spoke” architecture, whereby you must select one of your servers as the primary, or Hub, and all other locations as “Spokes.” For our client, we used the office server as the Hub since it has the most reliable internet connection and is used most frequently throughout the year.
Tips recap
- Name your timestamp fields with “_Host”, eg. mymodtimestamp_Host
- Name your layouts with “Sync_”, e.g., Sync_mylayout
Primary keys
While 360Works provides relatively good documentation, we found it rather verbose with virtually no screenshots or bulleted key points. Consequently, we had some difficulty wrapping our heads around some advanced setup steps, particularly the many ways to handle one of the most important pieces—primary keys—and how to ensure that keys are not only unique per table but also unique across the entire system.
The legacy FileMaker system utilized number-based serials in all of its tables. While MirrorSync can work with traditional serial number primary keys, UUID primary keys, or custom primary key strategies, our initial thought was to convert the fields to UUID to ensure uniqueness. But with 62 tables and more than 1,000 relationships, this would have been quite an undertaking to accomplish. Thankfully, MirrorySync does most of the heavy lifting for you.
MirrorSync also warns not to create new primary keys where they already exist. This is because MirrorSync evaluates tables based on their relationships to ensure parent and child tables are updated in the correct sequence. Of course, it’s OK if a table has no relationships. MirrorSync will still recognize and sync these tables independently.
The primary key should never be empty and never change once a record is created. Additionally, the “Prohibit modification of value during data entry” option must be unchecked. While not required, MirrorSync recommends these fields have the “not empty” and “unique” validation options enabled, validation set to “always,” and “Allow user to override during data entry” unchecked.
Primary key setup recap
- Not empty
- Never changed after record creation
- “Prohibit modification…” unchecked
- Validate always
- “Allow user to override…” unchecked
- Not empty
- Unique Value
Handling serial number-based primary keys
Our client’s system had two situations for which MirrorSync provides additional preparation instructions: Number-based serials and primary keys that are user-visible, i.e., appear on layouts a user sees. Why are these situations problematic? In the first case, maintaining unique numerical combinations across all tables can be challenging. In the second case, especially in situations where a Spoke is offline when a record is created, duplicate serial numbers might be generated. For example, when an offline Spoke creates a new invoice, the next sequential number is used unbeknownst to the Hub. So if the Hub also creates a new invoice, it will get the same serial number.
MirrorSync provides several ways to approach these situations.
MirrorSync-managed
If you don’t care if the user-visible numbers are different in different databases, choosing this option is easiest.
This approach works by allowing conflicting primary keys to exist on each separate database without writing those primary keys to other databases. For example, a record with PK serial number 100 is created in the first database. When synced, the record is given the next sequential number in the second database, e.g, 250. Mirrorsync can reconcile the different PKs by using an internal table to translate between the primary keys on all databases that are syncing. MirrorSync also rewrites foreign keys to maintain relationships between records.
However, on a layout where this PK is visible, a different PK number will be shown depending on which database the user is in. This can be problematic if the PK is used for an invoice. Using our example above, the Invoice Number will be 100 when viewed in the first database and 250 when viewed in the second. This could lead to confusion when trying to reference records.
One caveat: MirrorSync does not consider PKs based on UUIDs as MirrorSync-manageable. While UUIDs are generally considered to be random enough, making duplicates between tables unlikely, MirrorySync recommends adopting a strategy to ensure uniqueness. This could involve appending a unique identifier to the beginning or end of the UUID. It is the developer’s responsibility to pick a scheme that ensures that the same primary key is never used for different records on different databases.
Developer-managed: Option 1
If you need to assign user-friendly serial numbers to your records that appear consistent across all synced databases, you can use Mirrorsync’s Write-back method. However, the offline file will not get the assigned PK until the record is synced for the first time.
For this work, you need to have separate fields for your PK and Invoice number. In your Invoice number field, set an auto-enter calculation:
If( Get( MultiUserState ) = 2; [yourPKfield]; "" )Check the box that says “Do not replace existing value of field.”
In the primary key selection screen in MirrorSync, select your Invoice Number field as the write-back field. (See screenshot in earlier section.) This will cause MirrorSync to write the Invoice Number from the server back to the offline file when the record is first synced.
Caveat: If you don’t already have separate fields for PKs and other serial-based fields, it can be challenging to use this method. In our client’s case, there was no separate user-visible field. Adding a new field would have required not only replacing all layout occurrences of the PK but also updating scripts and sorts.
Developer-managed: Option 2
To use your PK as a user-visible number that remains consistent across databases, you can utilize MirrorSync’s global variable $$MIRRORSYNC_DATABASEID. However, this option is more complex to implement.
First, you need to create a startup script. If your solution already has one, you’ll need to call Mirrorsync’s setup script, which is included with its installation. The script sets the global variable, so you will need to manually run the script now to set it before proceeding.
Next, duplicate your PK field. Add one of the following auto-enter calcs to the duplicated field:
- For text-based PKs, use $$MIRRORSYNC_DATABASEID & “.” & [yourPKcopyfield]. This results in PKs that look like this: 1.1″, “1.2”, “1.3” from device 1, and “2.1”, “2.2”, “2.3” from device 2.
- For numeric-based PKs, use $$MIRRORSYNC_DATABASEID * [multiplier] + [yourPKcopyfield]. This results in PKs that look like this: “10001”, “10002”, “10003” from device 1, and “20001”, “20002”, “20003” from device 2.
- The multiplier can be any number, e.g., 10000. You will want to make this number large enough to support the maximum number of records that will ever be created in a table. If your record count ever reaches the limit, duplicate PK numbers will get created, jeopardizing syncing.
Advantages of this approach:
- Reasonably short user-visible numbers
- IDs are assigned immediately upon record creation, without needing to wait for syncing
Disadvantages:
- Offline users must do an initial sync before they can create records.
- Requires a startup script to run, which means it won’t work with custom web publishing applications.
- PKs will not be sequential, so you can’t assume that a PK with a higher value was created before or after another PK.
- Numeric-based PKs are limited to a fixed number of records before they start conflicting.
- For text-based PKs, all FKs must also be text-based.
- Setup is a bit more complex than write-back fields.
Caveat: If your solution has a lot of tables, this method can be daunting to implement. If you have a table where numerous records get created, such as a log table, estimating a multiplier for numeric-based serials can also be a challenge. In our client’s system, there were several tables with more than 10,000 records and two with more than 100K records. Also, all of the PKs were numeric. We considered changing the PKs and FKs to text, but decided this was too risky based on the complexity of the solution.
The method we chose
After careful consideration, we ultimately decided to stick with MirrorSync-managed serials and not worry about the user-visible primary keys. Our client was not using the PKs in any real sense, such as searching for guests by their ID. So, we felt it was safe if PK numbers varied when viewed in the office versus the lodge. Also, we set our syncing to occur every 60 seconds. Even with the lodge’s slow internet connection, we felt the likelihood of new records in the same table being created and assigned the same serial number was slim, especially in tables used in day-to-day operations, e.g., Guests, Reservations, and Invoices. Why? Records in these tables are generally created by the office. Records in other tables, such as POS transactions, are only created at the lodge.
Multifile systems
Our client’s system had one external file reference that contained two tables. While Mirrosync says it supports multi-file solutions, we encountered a permission error during the setup process, which we weren’t able to identify or fix. As a result, we opted to incorporate the external database into the primary system and eliminate the external file reference. Perhaps with more time and patience, we could have identified the issue, but the file was small enough that we decided it was best to merge it.
Installation
Now that our house was in order, it was time to install MirrorSync. Both the Hub and Spoke machines need the MirrorSync daemon installed. The instructions are very clear and straightforward, but do require a few further adjustments to your solution after installation.
MirrorSync provides a FileMaker Pro file from which to copy the following items:
- One table,
- One script folder, and
- Layout elements
This is a relatively simple cut/paste process. With these in place, only two remaining changes have to be made:
- Update the name of the primary MirrorSync script with the name created during the MirrorSync installation process.
- Update the URL in the script where the MirrorSync daemon is installed, i.e., the Hub server.
MirrorSync setup
The following are some additional screenshots of the MirrorSync setup process. We found very few screenshots elsewhere, which made preparing for the setup before purchasing a license challenging.
Rolling out updates with MirrorSync
Another handy feature MirrorSync offers is the ability to issue updates. If layout or schema changes are made on the Hub, updating the Database Version field in the MirrorSync table with any number, e.g., 1.1, will instruct MirroSync to spin off a new copy of the solution to Spoke servers. We found ourselves using this feature several times, and frankly, it was a lifesaver.
Additional notes
On our first attempt at a sync, MirrorSync alerted us to bad timestamps in one table and duplicate PKs in another. After fixing these, the initial sync took about an hour, even though no records had been altered on the Hub or Spoke. Because the Spoke copy didn’t contain any different data from the Hub when we started the process, we discovered that we didn’t need to install the database on both machines before initiating the setup process. Allowing Mirrorsync to install the database on the Spoke was the most efficient option, taking approximately 12 minutes to complete during testing. When we performed the actual installation, MirrorSync struggled to download and install the solution on the Spoke, even though both servers were in the same office. We think the client’s VPN was the issue. Thankfully, MirrorSync also provides a download link, which we used on the Spoke to install the database.
Once we had the system working on the client’s servers, we encountered an oddity where some records were not getting synced. We reached out to 360Works, who suggested the problem sounded like a time zone issue. Since both servers were literally in the same building, we didn’t believe this could be the issue. However, after a little internet sleuthing, we discovered that the Apple M-class computers can fail to honor the “set time zone automatically” setting. We deployed a workaround we found on Reddit, but to be safe, we followed 360Works’ advice to utilize MirrorSync’s ability to handle time zones. This involved two steps:
- Adding a new modification timestamp field to every table with the auto-enter calc:
Let ( x = [YourModificationTimestampField] ; Get(CurrentHostTimestamp ) - Changing the modification timestamp field on the MirrorSync Setup Mapping screen to refer to the new auto-calc fields.
Final thoughts
Our first time implementing MirrorSync was a bit challenging, not due to MirrorSync itself but rather our client’s legacy system. The hardest part was determining how to handle the primary keys. The MirrorSync documentation was difficult to read, and there were no case studies or other real-world reports to guide us. We contacted support a few times, and while consistently helpful, responses sometimes took days. We did find a YouTube presentation, which addressed some of the setup questions we had, but we felt pretty much on our own.
After tackling the somewhat confusing setup process, MirrorSync continues to operate without any further interaction. However, it will periodically issue warnings when it encounters syncing issues, one of which happens when a record is locked, in other words, a user is still editing a record. This type of warning will auto-resolve on subsequent syncs once the record is released/committed. There are other times when we’ve received “severe” warnings, but these have also self-resolved on subsequent syncs. Aside from these minor kinks, MirrorSync works pretty flawlessly.
We hope our tutorial helps fellow FileMaker developers gain better insight into what MirrorSync has to offer, illustrates the setup process in more detail, and provides a roadmap to implementing MirrorSync with your solutions.
If you have any questions about MirrorSync or other development issues, please feel free to contact us.