MirrorSync advanced topics

Welcome to the advanced section of the MirrorSync documentation! This guide will help you customize MirrorSync to suit your development needs, and provide detailed instructions for various situations. If you would like a good general overview of the software, please read through MirrorSync basic setup first!

Deployment questions

How do I uninstall MirrorSync?

To uninstall on Mac, run the 'Mac Uninstaller.pkg' that comes with the MirrorSync. On Windows, run 'C:\Program Files\360Works\Uninstall.

On either platform, you can also run the '360Works Admin.jar' application (/Applications/360Works Admin.jar or C:\Program Files\360Works Admin.jar) and remove the MirrorSync instance. However, this does not remove the 360Works service, which is shared by other 360Works applications such as Zulu. You must use the uninstaller to completely remove the 360Works service.

Installation for hosting providers

To install multiple instances of MirrorSync, choose the Hosting provider option in the installer. This is exactly the same as the regular installation process, except that it will allow you to rename the instance of MirrorSync. You can continue running the installer as many times as you like, once per client, renaming each instance to something unique. These copies can then be managed via the 360Admin utility, which is found either in your Program Files or Applications folder. When installing additional instances of the application, only a single Tomcat process will be installed which is shared by all of the MirrorSync instances.

Hosting providers should read the next item about using sub-admin accounts with MirrorSync.

If MirrorSync is being installed on a different computer than your FileMaker Server, read the 'split server deployments' section below.

If you are installing MirrorSync on a Linux box, read the section below titled 'How do I install on Linux?'

For installation on Mac or Windows, we recommend you use the installer. We at 360Works use the installer for our hosting clients as well, and find it easy to update and manage. If you are curious as to what the installer actually does, it modifies and adds the following:

• Creates a folder at '/Library/360Works' on Mac or 'C:\Program Files\360Works' on Windows that is readable and writeable.
• Downloads and installs an instance of Tomcat 7 into that folder. (Only one copy of Tomcat is installed, regardless of how many copies of MirrorSync are running)
• Copies and renames the installer's MirrorSync.war, which deploys the web app, as well as the other supporting material into the folder.
• Adds a launch daemon in Library/LaunchDaemons in OS X, or creates a Windows Service to automatically start Tomcat.
• Modifies the http.conf file for Apache to allow for URL redirection. If using Windows with IIS, we create an ISAPI filter or a URL Rewrite rule for the URL redirect.
• Copies a lightweight Admin Utility JAR file to manage Tomcat to either C:\Program Files\360Works or /Applications.

If upgrading from an older version of MirrorSync, we'll remove the URL redirects for the old MirrorSync.

If you prefer to skip the installer and manually deploy using your own instance of Tomcat that you've set up, please note and follow these instructions:

1. Create a folder at '/Library/360Works' on Mac or 'C:\Program Files\360Works' on Windows. Make sure that it is readable and writeable to the process that Tomcat is running as. This is where MirrorSync stores its private data including the internal sync database and the stored configurations. If you have a strong preference for locating this someplace else, because of disk space for example, configure a '360directory' system property in Tomcat to point to some other path.
2. Rename the MirrorSync.war file to whatever name you'd like the instance to run as for your hosting customer, ie. 'MyCustomerSync.war' If you decide to run multiple instances of MirrorSync with the same name (using multiple instances of Tomcat), you'll need to set '360directory' separately for each Tomcat instance, otherwise those multiple instances will overwrite each other's private data.
3. Drop that .war file into the webapps directory in your Tomcat instance.
4. Modify the the MirrorSync.xml context descriptor and set the administrative username and password for MirrorSync. In Tomcat 6, this file is automatically written to the Tomcat/conf/Catalina/localhost. If you are running Tomcat 7, the file is located in the webapps/MirrorSync/META-INF/context.xml file, or you can follow the instructions at http://tomcat.apache.org/tomcat-7.0-doc/config/host.html regarding copyXML to get the same behavior as Tomcat 6 (we recommend doing this, so that you don't lose the context.xml file every time the application is redeployed).
5. If necessary for your configuration, set up URL forwarding from IIS / Apache to your Tomcat connectors. See tomcat documentation on how to do this.

Regardless of whether you use the installer or not, if you install enough instances of MirrorSync, you may start to run out of memory. If the memory error messages you get talk about PermGenSpace, then the memory problem is related to loading the MirrorSync classes, not the memory used during actual sync operations. In this case, modify the file at C:\Program Files\360Works\Applications\bin\setenv.bat (Windows) or /Library/360Works/Applications/bin/setenv.sh (Mac) and add this to the list of launch arguments:

-XX:PermSize=64m -XX:MaxPermSize=256m

Using a FileMaker sub-admin account

Unfortunately, the fmsadmin command line tool, which MirrorSync uses for servicing download link requests, does not work with sub-admin accounts. We have reported this as a bug to FileMaker. In the meantime, there is another solution for this problem: In the 360Works/Applications/conf/Catalina/localhost/MirrorSync.xml file, customize the fmsAdminUsername and fmsAdminPassword properties. Insert the full administrative user account information here, and MirrorSync will use that when downloading files or generating download links without prompting the users. In this way, your MirrorSync users can download files without ever needing full FileMaker Server admin credentials.

Split server deployments

If your FileMaker deployment is split onto multiple machines, you can use any of these three configurations:

1. Run MirrorSync on just the FileMaker Server, instead of the Web Publishing Engine
2. Run MirrorSync on the Web Publishing Engine. If you choose this option, then it is also recommended that you install MirrorSync on the FileMaker Server. This will enable download links to work. You do not need to enter a license key for the extra copy of MirrorSync running on FMS.
3. Run MirrorSync on some computer other than the Web Publishing Engine or FileMaker Server. This is a good option if you prefer to deploy MirrorSync on Linux. If you choose this option, then it is also recommended that you install MirrorSync on the FileMaker Server. This will enable download links to work. You do not need to enter a license key for the extra copy of MirrorSync running on FMS.

Regardless of which computer you install on, be sure that when you are configuring MirrorSync, you specify that the Web Publishing and Database Servers are on different computers. This option is on the first screen in the configuration process.

How do I install MirrorSync on Linux?

MirrorSync runs very well in Linux - it's our preferred deployment platform, and it's how we deploy our live demo file. If you are syncing with FileMaker Server, keep in mind that you'll obviously need a separate Mac or Windows computer running FileMaker Server.

We assume that you are familiar with Linux, and have already installed Java and Tomcat (version 7.0.52 or later). Here is the setup process:

• Create directory at /var/lib/360works - make it readable and writeable to Tomcat user (you can put the directory anywhere you want on the filesystem - if you choose a different location, set the '360directory' system property to the location you select).
• Copy the MirrorSync.war file into your webapps directory.
• Wait a few seconds for this to deploy, and then modify the file at ${TOMCAT_HOME}/conf/Catalina/localhost/MirrorSync.xml. Set the adminUsername property, and either the adminPassword or preferably the adminPasswordHash. While you're in this file, skim over the other properties to see if you want to customize any of them.

If desired, configure Apache to forward requests on port 80 to Tomcat, using a ProxyPass directive. Otherwise, just access MirrorSync on the Tomcat port (typically 8080).

Recommended: Either comment out or increase the Timeout setting in the Apache configuration, if it's set to 60 seconds. It is not uncommon for some operations in MirrorSync to take 5-20 minutes, depending on the amount of data involved.

Does MirrorSync work with runtime versions of FileMaker Pro?

MirrorSync is untested and unsupported for use in this configuration. In addition, the legal licensing agreement for creating runtime versions of FileMaker specifically disallows any automated transfer of data between the runtime version and FileMaker Server. This restriction means that no sync process can legally be used to transfer data between the runtime edition and FileMaker Server, whether that is from a 3rd party or a home-grown automation process. Contact your FileMaker Business Account Manager for volume pricing on FileMaker Pro licenses.

What if I need to redeploy FileMaker Server?

If using a Mac, no additional steps should be required. However, if you are running Windows, you'll want to re-run the installer for MirrorSync after redeploying FileMaker Server.

I am running MirrorSync 2. What do I need to do to upgrade to 3?

Read the documentation on this at Updating from MirrorSync 2 to 3

Installing without a network connection

The MirrorSync installer normally needs a network connection during the installation process. It uses this to download a copy of Apache Tomcat 7 from the Apache web site, as well as doing a network license check to validate the license key. If you are in an environment that has no outbound network capabilities, you can still install MirrorSync, but you'll need to follow these extra steps:

1. Contact support@360works.com and ask for a customized build of MirrorSync that does not need a network connection for license checking. Be sure to include your license key and registration information.
2. Manually create a new folder at /Library/360Works (on Mac) or C:\Program Files\360Works (on Windows).
3. Download Apache Tomcat 7 from http://archive.apache.org/dist/tomcat/tomcat-7/v7.0.55/bin/apache-tomcat-7.0.55.zip and extract it into the 360Works directory.

Now you should be able to run the installer without any network connection.

How do I migrate MirrorSync from one server to another?

If the hostname or IP address of your FileMaker Server that is embedded in the sync configuration has changed, then either plan on redistributing new offline files to your users, or contact 360Works support for help with this.

If the hostname or IP address does not change, and all that is changing is which server MirrorSync is running on, then follow these instructions:

1. Stop Tomcat on the old server, by using the utility at C:\Program Files\360Works\360Works Admin.jar or /Applications/360Work Admin.jar
2. Uninstall MirrorSync on the old computer (because a license key can only run on one server at a time).
3. Copy the folder at C:\Program Files\360Works\SyncData2_MirrorSync or /Library/360Works/SyncData2_MirrorSync to the new computer. Make sure you have matched the file permissions from the previous computer.
4. Install MirrorSync on the new computer.
5. Run the MirrorSync admin utility. Edit the configuration and modify the addresses of the MirrorSync server if necessary. In the last step of the process where you copy and paste the script steps into the MirrorSync script, be sure to first delete the 'MirrorSync_internal' and 'MirrorSync_external' items from the list of external data sources. They will be recreated with updated values when you paste the script steps.

Networking questions

Can MirrorSync send encrypted data? What about SSL enabled FMS?

Yes, it can. There are two network channels for encryption: The connection from the syncing client to MirrorSync, and the connection from MirrorSync to FileMaker Server. SSL encryption can be enabled or disabled for each channel separately. In most cases, the connection from the syncing client to MirrorSync is more important for encrypting, since the connection between MirrorSync and FileMaker Server is usually on the same computer or the same LAN.

To encrypt data between the syncing client and MirrorSync, make sure that you have a valid SSL connection installed on the web server where MirrorSync is installed. You can test this by going to https://yourServerAddress/MirrorSync. Once that is working, all you need to do is check the SSL box in the spoke configuration screen in MirrorSync.

To encrypt data between MirrorSync and FileMaker Server, follow the documentation from FileMaker on enabling SSL encryption in FileMaker Server. Once that is complete, JDBC communication is automatically encrypted. If you choose the XML option, be sure to check the SSL checkbox in the MIrrorSync hub configuration screen. If you select the XML option, and get an error saying that you have a self-signed certificate, then you will need to follow the Custom SSL Certificate setup instructions. You may also need to follow the instructions at Web Server settings are set correctly. MirrorSync configures these during installation but doesn't always succeed.

I need my database to be HIPPA compliant, what should I do with MirrorSync?

HIPPA compliance depends on a large number of requirements. However, one of those requirements is to transmit only encrypted data. See the question above if you experience difficulties. Please keep in mind that encryption is one piece of HIPPA compliance, so consult the Department of Health and Human Services for more information.

What ports are required for MirrorSync? Can I change them?

During the MirrorSync configuration client only requires port 80, although it will connect more quickly if all ports are open.

For the connection between MirrorSync and your server, it depends on which type of database server you're using:

• FileMaker Server with XML: 80 and 443 (if using SSL)
• FileMaker Server with JDBC: 2399
• MySQL: 3306
• MS SQL: 1433
• Oracle: 1521

For the connection between MirrorSync and offline devices running FileMaker Pro or Go, MirrorSync transmits all non-container data (text, number, date, time, timestamps) on standard HTTP ports 80 or 443 (if SSL encryption is being used).

Container data is also transmitted over standard HTTP ports, if 1) you are running MirrorSync 2.1 or later, 2) using FileMaker 13 Pro or Go, and 3) the container data is 10 megabytes or smaller. This 10 megabyte limit can be adjusted by setting the $$base64Limit global variable in the MirrorSync script. Contact support@360works.com for more information on this setting.

If these three conditions are met, MirrorSync will only use standard ports 80 or 443, which are allowed by default on almost all firewalls.

If these three conditions are not met, MirrorSync will connect as a guest of FileMaker Server over port 5003, which is the regular port for communication betwen FileMaker Pro and FileMaker Server. This will likely require custom configuration of your company firewall, if you have one.

Will MirrorSync work on a VPN?

Yes. MirrorSync runs over standard HTTP protocols (which are VPN compatible) for non-container data, and standard FileMaker protocols (which are also VPN compatible) for container data.

Is MirrorSync compatible with Network Address Translation (NAT)?

Yes. When configuring MirrorSync, select the option that says 'My internal and external IP addresses are different.' This will write the sync script so that if it detects it's running on the same LAN as MirrorSync, it will use the internal IP address, and if it's running outside the MirrorSync LAN, it will automatically switch to the external IP address.

Sometimes, it is impossible for the MirrorSync script to tell whether it is on the same LAN as the MirrorSync server. For instance, if MirrorSync is configured on a network where the MirrorSync server is running at IP address 192.168.1.5, and then a user tries to sync from some other network with the same IP address range (192.168.x.x and 10.1.1.x are common IP address ranges), the MirrorSync script will think that it's on the same LAN as the server, and will try the internal IP address, which will fail. If this happens, it will try for 60 seconds and then automatically switch to the external network. If the external network succeeds, this will be remembered for as long as the file is open, so future syncs will try the external network first.

If you know that the user is on an external network (for example, if you present a dialog asking the user whether they are inside the LAN or not), you can force MirrorSync to try external before internal or vice-versa. To do this, set the global variable $$MIRRORSYNC_CONNECTION to the word 'internal' or 'external' before running the MirrorSync script. This will allow you to override the normal search order.

Primary key / serial numbers

What is the difference between 'MirrorSync-managed' and 'Developer-managed' primary keys? Do I need to change how I do my primary keys? Which one should I pick?

Before answering this question, it's first necessary to explain why primary keys are a complex issue with synchronization. With traditional incrementing serial numbers, one database might have records numbered 1 through 10. Another database might have records 1 through 50. If we create a new record on the first database, it will get assigned the next number in the sequence (11). However, if we try to write that record to the second database, it will conflict with record 11 that already exists there. Here are several approaches to solving that problem:

1. Use Universally Unique Identifiers (UUIDs) as primary keys. These are typically long, 36-character strings that look like this: "D2EF9F69-5DEA-4FE3-9095-162C77F76FBF". They are sufficiently random to statistically eliminate the possibility of duplicate values. This makes them ideal for syncing databases - you can safely write records from one database to another without worrying about conflicting IDs. MirrorSync (and most other sync frameworks) supports UUIDs.
2. Combine a traditional serial number with some delimiter, such as each user's initials or a file ID. This way, user 1 would have primary keys "1.1", "1.2", "1.3", and so on. User 2 would create primary keys "2.1", "2.2", "2.3", etc. This has the advantage of being shorter and more readable than UUIDs, but the added management consideration of assigning unique identifiers to each user. MirrorSync can help to configure this for you, as explained later.
3. A variation on the second solution would be to assign each user a particular numeric range, so that user 1 generates primary keys in the range of 1-10,000; user 2 generates primary keys 10,001-20,000; and so on. This has the advantage of pure numeric values (instead of text), but the disadvantage of the possibility of conflicts if a user exceeds the expected number of records. MirrorSync can help with this approach as well.
4. A very different approach is to allow conflicting primary keys to exist on each separate database, without ever writing those primary keys to other databases. When record #11 is written from the first database to the second (in our original example), instead of being written with primary key 11, it is written with primary 51 (the next number in the sequence on the second database). This has the advantage of the shortest possible primary keys, which are pure numeric values, with no possibility of conflicts. It is also the way that the majority of existing databases are designed. MirrorSync supports this method (and is the only sync framework that does, to our knowledge). It creates an internal table to translate between the primary keys on all database that are syncing, so that when record #11 is later updated, MirrorSync knows to change record #51 in the second database. MirrorSync also re-writes foreign keys when they are written from one database to another, so that foreign keys that contained '11' on the first database will be re-written with '51' in the second database.

Options 1, 2, and 3 are considered 'Developer-managed', because MirrorSync writes the primary keys unmodified between the databases being synced. It is the developer's responsibility to pick some scheme that ensures that the same primary key is never used for different records on different databases. There are other variations on the same theme (ie. one database gets odd numbers and the other gets even numbers), but they all are treated the same by MirrorSync. Developer-managed keys must NOT have the 'prohibit modification' option set, because MirrorSync needs to write to this field (to make it match the device where the record originated).

Option 4 is considered 'MirrorSync-managed.' The developer is not responsible for the uniqueness of primary keys across different databases; MirrorSync takes care of this for you. You may enable the 'prohibit modification' option for MirrorSync-managed keys, because MirrorSync only reads from this field, it does not write to it.

If you have no preference for serial numbers or UUIDs, we generally recommend UUIDs. The advantage to UUIDs is that they can easily be merged manually in the event that the sync does not work for some reason. However, serial numbers are fully supported, and if it's difficult to switch to UUIDs, stick with what you have. If you need 'user-friendly' numbers for things like invoice numbers, job numbers, or check numbers, read the next FAQ on user-friendly serial numbers before making a decision.

We highly recommend AGAINST using any meaningful data in your primary key. This applies to data modeling in general, not just MirrorSync. This means that primary keys should not be based on anybody's name, initials, social security, office location, company, username, etc. However, if you must use a schema like this, you should select 'Developer-managed' primary keys. Remember that when using developer-managed keys, YOU are responsible for making sure that the same primary key is never created in the same table on more than one device.

These are some common mistakes made with primary keys when syncing: Template:Mbox

Template:Mbox

What if I need to assign a user-friendly serial number to my records that stays the same when it is synced? For example, an invoice number?

The problem with MirrorSync-managed serial numbers is that they are not suitable for user-visible numbers, such as invoice numbers. That's because the primary key will be different on one device than another. If you're using the primary key serial number as your invoice number, it is clearly a problem if your invoice number is different between your laptop and the server!

There are several solutions to this problem. One of the first things to establish is whether your database uses a single field as both the primary key and the user-visible value (we'll refer to it as the 'invoice number'). It is always preferable (even when not syncing) to have these be separate fields from each other. Invoice numbers / job numbers / user visible numbers should NOT be what you use as a primary key in your database. Primary keys are internal database identifiers, and should not do double-duty as a user-readable value.

If the fields are separate, the problem becomes simpler to solve, because you have the flexibility to change the value of the invoice number without breaking relationships. See the section below on write-back values for the recommended approach.

If the same field is being used for the primary key and the invoice number, and if it is feasible to split this into two separate fields (one for relationships, and one for display/searching), you should do so. Unfortunately, this can sometimes be a very big job - creating the new field is easy, but then you either need to find every place that that field is used in the user interface and point it to the newly created invoice number field, or else you need to find every place that it is used in the relationship graph and re-point that to the newly created primary key field. If you do not want to do this, then see the section below on MIRRORSYNC_DATABASEID for the recommended approach.

Write-back values for user-visible numbers

Let's say that you have a UUID as your primary key field, as well as an invoice number field. Using the write-back approach, the invoice number field will be blank when a record is created in an offline file. When that invoice record is synced to the server, it will get assigned the next invoice number, and that number will be written back to the invoice number field on the offline file. This approach does not work unless the invoice number field is separate from the primary key.

Advantages of this approach:

• Invoice numbers are in a simple, short numeric sequence, just like they would be without syncing.

Disadvantages:

• The offline file does not get an invoice number until the record is synced for the first time. If this does not work in your use case, see 'MIRRORSYNC_DATABASEID' below for an alternative approach.

To use this approach, follow these steps:

• If you do not already have a serial number field in your table, create one. If you have a serial number which is used as a primary key, that's usable. We'll call that field 'serialNumber' in our example.
• Define your invoiceNumber field as a text or number field with this auto-enter calculation: If( Get( MultiUserState ) = 2; serialNumber; "" ). This will leave the invoice number blank when working offline, but fill it in when connected directly to the server.
• In the primary key selection screen in MirrorSync, select invoiceNumber as the write-back field. This will cause MirrorSync to write invoiceNumber from the server back to the offline file when the record is first synced.
• Your choice of MirrorSync-managed or Developer-managed is unaffected by this setting; pick appropriately depending on whether you are using serial numbers or UUIDs as your primary key.

MIRRORSYNC_DATABASEID for user-visible numbers

Let's say that you have a single serial number as your primary key, which is doing double-duty as an invoice number field. You can use the $$MIRRORSYNC_DATABASEID global variable to help. Using this approach, MirrorSync ensures that each file being synced gets assigned a unique sequential number, starting from 1. You can use this number in conjunction with a traditional serial number to create a unique number than can be used as a user-visible field as well as a primary key. The serial numbers will either be text (ie. "1.1", "1.2", "1.3"…) or numeric (10,001 for user 1, 20,001 for user 2, 30,001 for user 3…).

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.
• IDs are not sequential, so you can't assume that an ID with a higher value was created before or after another ID.
• If you are using numeric version, limits users to creating a fixed number of records before they start conflicting.
• If you are using text version, you must switch all primary key and foreign fields from 'number' type to 'text.'
• Setup is a bit more complex than write-back fields.

To use this approach, follow these steps:

• Set the 'MirrorSync setup' script as your startup script. If you already have a startup script, call the 'MirrorSync setup' script from your existing script. This will set the $$MIRRORSYNC_DATABASEID global variable. This clientId field is stored in the MirrorSync table, and is assigned when the user does their first sync. Each synced database will get a unique, sequential clientId. Manually run the script now to set that global variable before proceeding.
• We assume you have a serial field called 'serialField', and that your relationship graph uses this field as a key field. Start off by duplicating this field, to create a new field called 'serialField copy.' Do NOT place 'serialField copy' onto the sync layout for the table.
• Change serialField to an auto-enter calc. Uncheck the box that says 'Do not replace existing value'. Also make sure that the 'Prohibit modification of value during data entry' is unchecked. Set the formula to one of the following:

For text keys, use this formula. This will result in primary keys that look like "1.1", "1.2", "1.3" from device 1, and "2.1", "2.2", "2.3" from device 2. Feel free to modify as desired. Be sure to change the field type from number to text. Also change all foreign keys that relate to it to text.

$$MIRRORSYNC_DATABASEID & "." & serialField copy

for numeric keys, use this formula. This will result in primary keys that look like "10001", "10002", "10003" from device 1, and "20001", "20002", "20003" from device 2. Replace whatever number you want instead of 10,000 to give a wider or narrower range of numbers.

$$MIRRORSYNC_DATABASEID * 10000 + serialField copy

• For primary key configuration in MirrorSync, select Developer-managed, and set 'serialField' as your primary key.

How do I configure and use two foreign keys as a primary key?

This is a common configuration for join tables in many-to-many relationships. In the primary key configuration screen, use the drop down menus to select the two foreign keys.

Please keep in mind primary keys need to be unique. If using two foreign keys, there may be an instance where an error occurs about a duplicate node ID, since FileMaker is not validating the fields together and making sure they are always unique in a table. To avoid this, make sure:

1. Ensure that the combination of two foreign keys only ever occurs once in the database.
2. If you need to be able to have multiple join table records with the same foreign keys, add a regular serial number field to the join table and use that as the primary key, instead of the compound foreign keys.
3. The foreign key fields must both have the 'Not empty' validation in order to appear in the pull-down menu of eligible fields. If your join table needs to have foreign keys that may be empty, you should add a traditional single primary key to the table instead of using compound foreign keys as an identifier for MirrorSync.

Why does MirrorSync need to know about foreign keys?

MirrorSync uses foreign keys for two purposes. If you are using MirrorSync-managed primary keys, then it needs to know about the foreign keys because it needs to rewrite them when it writes between databases (see the primary key section above).

In addition, even if you're using developer-managed primary keys, such as UUIDs, MirrorSync needs to know about relationships so that it can insert/update parent tables before children tables. This allows validation rules that check referential integrity to work correctly. MirrorSync deletes records in reverse order from insertions (delete children, then parents) in order to avoid triggering cascading deletions, which are less efficient.

MirrorSync 2 adds a new feature that automatically detects foreign keys, if you are using FileMaker 12 or later, so this step should not add any time to the configuration process. If you are using FileMaker 11 AND you are using developer-managed primary keys AND if you do not have any referential integrity validation making sure that foreign keys point to valid parent records, you can safely skip this step.

Configuration questions

What type of sync configurations are supported by MirrorSync?

There are a lot! MirrorSync can sync between any combination of FileMaker Pro/Go, FileMaker Server, MySQL, SQL Server, Oracle, Salesforce, Amazon RedShift, and any JDBC-compatible database, such as PostgreSQL. Here is the complete list of supported configurations:

• FileMaker Pro or Go syncing with FileMaker Server
• FileMaker Pro or Go syncing directly with MySQL / Oracle / SQL Server / JDBC database / Salesforce / RedShift (in this configuration, FileMaker Server is needed during sync setup, but not for actual syncing)
• FileMaker Server syncing with MySQL / Oracle / SQL Server / JDBC database / Salesforce / RedShift
• FileMaker Server syncing with another FileMaker Server
• MySQL / Oracle / SQL Server / JDBC database / Salesforce / RedShift syncing with another MySQL / Oracle / SQL Server / JDBC database / Salesforce / RedShift

In all of these configurations, one database is designated as the 'hub', and one or more other databases are called the 'spokes.' The hub database is where the data starts when configuring MirrorSync, and the spoke database(s) will be set up to match the hub during the initial sync. There can be multiple spokes, although they must be the same type of database. Here are some examples of multi-spoke configurations:

• FileMaker Server hub with many FileMaker Go spokes
• FileMaker Server hub with many FileMaker Server spokes
• FileMaker Server hub with many MySQL spokes
• MySQL hub with many FileMaker Server spokes
• Oracle hub with many MySQL spokes

MirrorSync configurations with SQL Server require Java 8 or above.
MirrorSync does NOT support syncing FileMaker Pro/Go directly with other FileMaker Pro/Go devices without using some database such as FileMaker Server as the hub. It also does not support different types of spoke databases, such as syncing a FileMaker Server hub with a MySQL spoke and an Oracle spoke.

If you need to sync with a database or API that is not on this list, contact us and we'll get you a quote to support it. We've created custom implementations for state governments that use MirrorSync to sync with Web XML API's. We've also synced FileMaker Server with iTunes libraries for music companies. We're always open to a new challenge!

Should I use JDBC or XML to sync with FileMaker Server?

MirrorSync 1 and 2 used the XML Web Publishing Engine to read and write from FileMaker Server. Support for syncing using FileMaker's JDBC driver was added in MirrorSync 3. This is a major feature, because JDBC is significantly faster than XML in most, but not all, cases. It also supports record streaming, which means that while it's still possible to run out of memory during a very large sync, it won't be due to the amount of data stored in the records. It is also significantly more efficient for transferring container data. For all of these reasons, we recommend that most users select the JDBC option.

However, there are still valid reasons to select the XML option: 1) If you want to customize the sync to only sync certain records for certain users, this can be done with JDBC, but it requires you to write an SQL qualifier. XML, on the other hand, works using traditional FileMaker scripting. If you prefer to use traditional FileMaker find requests, then you should select XML (See '[[sql|How can I filter records using SQL?' for some helpful tips). 2) If you want to trigger custom actions to happen when a record is inserted, deleted, or updated, this cannot be done using JDBC, and will require XML instead with the MirrorSync Customization script. 3) If MirrorSync is not on the same LAN as your FileMaker Server, for instance when doing server-to-server syncs between data centers, JDBC tends to be slow. Try both options to see which one is faster. 4) When using SQL qualifiers with large record sets (i.e. larger than 10,000 records or so), JDBC can often be slower than XML. Try XML to see if it's faster.

Does MirrorSync support FileMaker Cloud?

Yes, MirrorSync is compatible with FileMaker Cloud, but it is a very different architecture than regular FileMaker Server. In particular, you cannot install software on it, and there is no XML Web Publishing Engine. These are important differences to be aware of when using MirrorSync with FileMaker Cloud:

• You must enable incoming access to port 2399 on your FileMaker Cloud instance in order for MirrorSync to communicate with it using xDBC. To do this in the AWS Console, follow the instructions in this 30 second video: https://youtu.be/PzfUDBpJifQ
• MirrorSync must run on a separate computer from your FileMaker Server. 360Works offers MirrorSync hosting which can communicate with FileMaker Cloud running on your server. You can also get a list of approved hosting providers from our documentation. If you know how to set up Tomcat using SSH on Linux, you can run MirrorSync on your own Linux box on AWS.
• Since MirrorSync is not running on your FileMaker Server, you will not be able to use the download link feature to get copies of your database. Use the FileMaker Admin Console to get an offline copy of the database to distribute to your users.
• Since all files are required to be encrypted on the server, and since you will be syncing with offline copies of these files, all of your syncing users will need to know your server encryption password.
• The setup process requires you to run the MirrorSync setup script at various points in the process, in order for MirrorSync to retrieve metadata that it would normally get via the XML Web Publishing Engine. If you're used to setting up MirrorSync on regular FileMaker Server, be sure to read the instructions carefully to know when to run this script.

Does MirrorSync need the databases being synced to be identical?

No, not in MirrorSync 2 or later. MirrorSync is able to sync between databases with completely different tables and fields from each other. In addition, it can sync just a subset of records, fields, and tables. If you are syncing different databases, for example a dedicated mobile file with a much larger server database, select the option 'Sync with a separate mobile file' (for FileMaker Go) or 'Sync with a separate server file' (for server-to-server sync). With this option selected, MirrorSync will allow you to match up your layout and field names with a simple drag and drop interface.

I'm not seeing my databases from the Choose database button. What's happening?

Make sure XML publishing is enabled for the account you are using in MirrorSync. Also be sure the database is accessible from the machine you are installing MirrorSync on.

To test the XML Web Publishing Engine, try going to the URL http://yourServer/fmi/xml/FMPXMLRESULT.xml?-dbnames. You should see an XML document listing the databases in FileMaker Server. If you do not, or find a 404 or 401 error, then the XML Web Publishing Engine may not be configured correctly. Contact FileMaker tech support for help getting this running.

If there is an authentication required at that test URL, try disabling IIS authentication. FileMaker Server authenticates its password-protected databases; however, this method disables the IIS authentication layer. This will also disable authentication for other websites that are using IIS on that server.

1. From the Control panel, navigate to Administrative Tools > Internet Information Services (IIS) Manager
2. Select the website in IIS and choose Action > Properties
3. Navigate to the Directory Security pane, and select Edit for authentications. This button may differ in various Windows versions.
4. In the Authentications Methods pop up:
   • Make sure Anonymous Access is enabled
   • For Authenticated access, disable the authentication methods.
5. Press OK

Configuration without FileMaker Pro Advanced

Since FileMaker Pro does not have the ability to copy and paste tables, you'll need to follow these instructions to create the MirrorSync table.

1. Locate the XML schema that came with your MirrorSync download.
2. Open FileMaker Pro and navigate to File -> Import Records -> XML Data Source.
3. In the "Specify XML and XSL Options" window, choose the "File" radio button, then click "Specify...".
4. Navigate to your MirrorSync download folder and choose the MirrorSync.xml file from the XML Schema subfolder.
5. Click continue to bring up the Import Field Mapping window.
6. Open the "Target" drop down menu, and select "New Table ("MirrorSync")"
7. Verify that the source fields and target fields have been matched correctly.
8. Click import.

After the MirrorSync table is created, you'll need to modify some of the fields:

Field	Options
id	serial number, with modification prohibited
modstamp	modification timestamp, with modificaton prohibited
lastErrorMessage	globally stored
webServerResponse	globally stored
_gSync1	globally stored
_gSync2	globally stored
_gSync3	globally stored
_gSync4	globally stored
_gLastInsertTable	globally stored
_gLastInsertResult	globally stored
container	Container data type

To achieve this, follow these instructions:

1. Navigate to File -> Manage -> Database...
2. Navigate to the "Fields" tab, highlight the id field and click the "Options..." button.
3. Check the box next to "Serial Number." Set it to generate on creation.
4. Check the box next to "Prohibit modification of value during data entry."
5. Navigate to the "Validation" tab and check the boxes next to "Not Empty" and "Unique Value" then press OK.
6. Next, highlight the modstamp field and click the "Options..." button.
7. Check the box next to "Modification" and ensure that the drop down menu says "Timestamp (Date and Time)."
8. Check the box next to "Prohibit modification of value during data entry."
9. Navigate to the "Validation" tab and check the box next to "Not Empty" then press OK
10. Next, highlight the lastErrorMessage field and click the "Options..." button.
11. Navigate to the "Storage" tab and check the box next to global storage and press OK.
12. Repeat the last two steps to make webServerResponse, _gSync1, _gSync2, _gSync3, _gSync4, _gLastInsertTable, and _gLastInsertResult all be global fields.
13. Finally, highlight the container field, and select "Container" from the "Type" drop down menu, next to the "Options..." button.

You are now finished manually configuring your MirrorSync table. Click the OK button to close the database management window, and return to the MirrorSync configuration. (Be sure to save your changes!)

Separation model / multiple file solutions

MirrorSync will work fine with multi-file solutions. You need to make sure that one of the files in your solution has a reference to every table that you want to sync. When using the MirrorSync download feature, be sure to select the multi-file option so that your offline users will receive all of the files for the solution.

If you are using the separation model with a UI and data file, configure MirrorSync to use the UI file. After the setup is complete, copy the MirrorSync table from the UI file to the data file, update the relationship graph to point to the table in the data file, and then delete the table from the UI file. This way, you'll have all of the scripts and layouts in the UI file, while the MirrorSync internal metadata will be stored in your data file.

How do I sync External SQL Source (ESS) tables?

MirrorSync supports ESS tables, but it's very slow compared to using MirrorSync to simply sync the database from the external SQL database into your FileMaker Server. For this reason, we do not recommend ESS unless syncing is not an option for some reason.

If you would like to use ESS tables, there are additional steps. First, set up sync layouts and primary keys, modification timestamps, and creation timestamps. Next, make a duplicate of your file and host it on FileMaker Server. Make the following changes to that duplicated file ONLY. We'll need to remove the ESS references to allow the user to access the data offline by turning the shadow tables into regular tables.

• First, delete the data source. This is vital to preventing duplicate records and other artifacts of using ESS. Open File > Manage > External Data Sources and delete the OBDC data source.
• Next, open the File > Manage > Database and navigate to the Tables tab. Copy any ESS tables. Then delete them. Make sure NOT to delete the table occurrences. After all the italicized tables are gone, paste the tables back in. This ensures they are local FileMaker files, not references to external data.
• Make sure the primary keys, modification timestamps, and creation timestamps are correct. The primary key needs to be an auto-entered serial number or generated UUID.
• Open the relationships tab in the File > Manage > Database and relink the missing tables.

Now you're ready to configure MirrorSync! Start the configuration process, and select "Sync with a separate mobile file" on the third screen. Choose the file that was duplicated and modified, and continue with configuration as normal.

Why use MirrorSync instead of ESS?

Before configuring MirrorSync to sync between FileMaker Server and some SQL database, such as MySQL, you should consider whether using FileMaker's External SQL Source (ESS) is a better option for you. ESS allows you to access SQL tables directly from within FileMaker, as if they were local FileMaker tables.

Some advantages of using ESS:

• It is built into FileMaker and FileMaker Server, therefore there is no cost, although there is often a cost for the ODBC drivers to access the SQL database.
• There is somewhat less setup for ESS than MirrorSync, although you still need to install ODBC drivers and configure the ODBC DSN on the server. Using MirrorSync's ability to create FileMaker tables (this is done in the last page of the MirrorSync configuration process), the difference in setup time is not that significant.
• With ESS, there is less work when fields are added, deleted, or changed; go into field definitions and click the 'sync' button. With MirrorSync, you need to walk through the configuration process again so MirrorSync can get an updated list of fields.

Using MirrorSync, the SQL data is synced to local tables in your FileMaker Server. This gives you your own copy of the data that always matches what is in the SQL database. Advantages of this approach:

• It is much, much faster than using ESS. For doing reports on large tables, it is literally hundreds of times faster. For instance, we used to use ESS for pulling sales reports from our MySQL online store database. It took 40 minutes to pull a report for the quarter. By switching to MirrorSync and pulling the same reports with local FileMaker tables, it takes less than 10 seconds (240 times faster).
• Having local tables allows you to access and create field indexes, which is necessary if you want to use value lists, unique validation, or the 'paste from index' feature. It is also necessary for relationships that are not based on primary keys.
• Sorting records on local FileMaker tables is much faster than sorted records via ESS. Sorting more than a few thousand records from ESS becomes unusably slow.
• You can add your own additional fields to local tables (with ESS, you can add unstored calcs and summary fields, but nothing else)
• You can access data in your local tables regardless of whether your internet connection is working or not.
• You can rename the tables and fields in your local copy and use MirrorSync's mapping feature to match them up with the ESS database.
• ESS does not work with existing FileMaker tables. MirrorSync allows you to sync existing tables with the SQL database.
• Unless you configure an ODBC DSN on everybody's laptop (not an option for iOS), ESS will not work if you make a local copy of the database to your own laptop. With MirrorSync, you can copy the database from the server to your own computers and iOS devices and keep all of the data.
• MirrorSync fully supports binary data in BLOB / Container fields. ESS does not.
• Local tables fully support multi-user record locking and broadcast notifications when records are changed. ESS does not.
• MirrorSync allows a developer to write custom SQL queries to only retrieve certain records. ESS does not (although a DBA could create an SQL view to solve this problem).
• MirrorSync allows custom SQL statements for generating primary keys for new records inserted into SQL. ESS does not.

In summary, ESS may be a good choice when you have a small amount of data which is accessed infrequently with simple list and detail views. MirrorSync is a better choice for data that is large, mission-critical, and accessed often.

I configured my file in FM11 and everything worked great. I just converted it to FM12 and now it doesn't sync at all. What's going on?

After converting your file, you'll need to edit the configuration and replace the MirrorSync script steps with ones generated for the FileMaker 12 file. This is due to the fact that the "Insert from URL" script step doesn't exist in FileMaker Pro 11.

Can I sync the same database server-to-server, and also server-to-client?

Yes, MirrorSync 3 and later have the ability to configure multiple sync configurations in a database, so you could have one configuration for syncing iPads with FileMaker Server, and another configuration for syncing the same database on FileMaker Server with MySQL, for instance.

Can I have different sync scripts to sync different tables?

Yes. Since MirrorSync 3 and later have the ability to have multiple configurations per database, you can do this. For instance, you could have a 'full sync' which syncs all tables in both directions, and then a 'save work order' sync which would only sync a handful of tables one-way from the offline device to the server. You could then attach the 'save work order' script, which would be very fast, to a script trigger that runs whenever a work order is completed.

Can I receive notifications of sync problems?

Yes, you can. There are two different settings related to this.

One is the admin e-mail address, which is configured in the MirrorSync setup process. By entering an e-mail address here and specify a severity from the pull-down menu, you can receive an e-mail message when certain events occur. You can select to receive e-mails every time a sync is done, only when there is a warning, only when there is a fatal error, and you can also specify whether you'd like to receive detail information with the affected primary keys.

The second setting related to this is in the MirrorSync.xml file. By modifying this file with a text editor, you can set the 'supportEmailAddress' field. When this is set, any bugs reported by users will be cc'd to this e-mail address, and tech support responses from 360Works will go to this e-mail address instead of back to the user. This allows your users to report any problems they encounter, while letting you (the developer) make any necessary technical adjustments. This configuration file is located here:

Mac: /Library/360Works/Applications/conf/Catalina/localhost/MirrorSync.xml
Win: C:\Program Files\360Works\Applications\conf\Catalina\localhost\MirrorSync.xml

Are repeating fields supported?

Yes, as of version 2.506. Repeating container fields are not currently supported.

Sync questions

Does MirrorSync work with timezones?

Yes and No.

For offline users who are syncing, Yes. When MirrorSync gets a list of all modifications, it will automatically detect the offline user's timezone and clock drift and compensate for it.

For server-to-server sync, Yes. MirrorSync can detect FileMaker server's current time and compensate for it, just like it does for offline users.

For non-syncing users connecting directly to FileMaker Server, No, not automatically. You'll need to perform a small work around to make this work. It gets a little complicated, so an example makes it more clear. Let's say your FileMaker Server is hosted in New York. A user in New York runs a sync at 12:30 PM Eastern Time. 30 minutes later, a user in San Francisco modifies a record on the server at 1 PM Eastern time / 10 AM Pacific Time. Due to the way that FileMaker Server works, that modification timestamp on the server in New York will say 10 AM. It will not be converted to Eastern Time, nor will FileMaker Server store any time zone information for anybody to tell that it was actually representing Pacific time. Essentially, it's just an incorrect value. When the next sync runs, MirrorSync is going to request records modified since the last sync at 12:30 PM, which is going to miss the change made by the San Francisco user, even though it was made since the last sync.

The solution to this problem is very easy - it actually takes less time to do than it did to read that explanation! Let's say that your table contains a regular modification timestamp called 'Modification timestamp'. Create a new field called 'Host modification timestamp' (or whatever you prefer), and set it to auto-enter this calculated value (substituting your own field name in place of 'Modification timestamp'). Be SURE to uncheck the box titled 'Do not replace existing value of field (if any)'.:

Case( Modification timestamp; Get( CurrentHostTimeStamp ) )

Don't forget to add it to your sync layout, and REMOVE the regular modification timestamp from the sync layout!

Use this field as the modification timestamp in the MirrorSync configuration process, instead of the regular modification timestamp.

This works by using the FileMaker Server clock, instead of the user's clock, for the modification timestamp. Now, when the San Francisco user modifies a record at 10 AM Pacific Time, FileMaker Server in New York will store 1 PM (the server's local time when that change was made) in the Host modification timestamp field, and MirrorSync will work with time zones correctly.

Can I share a device among multiple users?

Yes, but…

If all users on that device log in with the same username, then it's probably OK, as long as you make sure that you are not using record level access privileges on the offline device that would grant different record access to different users.

If you want each user who shares a device to log in with different usernames, then the solution is to put a separate copy of the file on the device, one for each user. Make sure that each user is only able to login to the file that is assigned to them.

MirrorSync prevents multiple users with different usernames from sharing the same offline file, because if record level access privileges or script filters were configured differently for each user, that would lead to the sync process incorrectly deleting the wrong records on the server, which would be very bad.

Does MirrorSync sync container fields?

Yes, MirrorSync works with FileMaker container fields. Container fields do take longer than other field types to synchronize, so remove them from your sync layouts if you don't need them to be synced.

MirrorSync supports external container fields (both Secure storage and Open storage), as well as regular (not external) container fields. It does not support referenced container fields (When the user checks the box 'Store only a reference to this file' when attaching the file)

What about SuperContainer? Does MirrorSync work with that?

Yes, it does. However, with SuperContainer the approach is very different. Only the URLs are synchronized, not the actual files - they remain on the SuperContainer server. The advantage is syncing is very fast - there is no binary data being transferred. The disadvantage is that you'll only have access to the files stored in SuperContainer when you have working network access from your computer or iOS device.

Does MirrorSync sync external container fields?

Yes, MirrorSync works with external container fields. There are no extra steps to take when syncing with FileMaker Pro or FileMaker Server. If you are using external container fields on an iOS device AND if you are syncing with a full copy of the database (as opposed to an empty clone), there are a few steps after configuration that are required.

1. Download a copy of the database from MirrorSync or FileMaker Admin
2. Open the copy on a computer, and navigate to File > Save a copy as...
3. Under the type drop down, choose "self-contained copy (single file)"

This ensures the copy of the file embeds the containers into the file for easy use with FileMaker Go and offline functionality.

PLEASE NOTE: there is an unfortunate behavior in FileMaker in this process: when saving the self-contained copy, FileMaker updates the modification timestamp for every record that has an external stored container field. That's not a problem normally, but when used with MirrorSync, it will make the initial sync very slow. The reason for this slowdown is due to MirrorSync assuming that all of those records have been modified on the client, so it writes all of the server data to the client (including the container data).

The workaround for this is to uncheck the modification timestamp auto-entry before saving the self-contained copy, and then turn it back on after the copy has been saved. This modification timestamp behavior has been reported to FileMaker, Inc. and will hopefully be resolved soon.

Why doesn't my iPhone / iPad show a progress bar when I click the download link? Is anything happening?

When you create a download link to share with users, it creates a URL that users can click on to download a copy to their own device. For large databases and/or slow networks, this can take a very long time. With iOS 6, you got a little progress bar that fills across the tab until the file downloads. Unfortunately, iOS 7 removed this download progress indicator, so it may look like nothing is happening after you click the download link. There's nothing that can be done to fix this: we suggest filing a feedback suggestion with Apple directly or using a different browser (most other iOS browsers show a progress bar). If you're really convinced that the downloads are not working and you've waited a long time (sometimes up to an hour for very large files), send us a problem report using the link on the MirrorSync launch page. Be sure to tell us what time the download started so that we can find it in the log.

Does MirrorSync require users to sync with full access accounts?

No. MirrorSync requires a full access account only once: to paste in the table, scripts, and layout. When users run the sync, they will use their own user account privileges, with whatever access that grants them. See the 'Customizing MirrorSync' section below for tips on restricting user accounts.

If you are using field-level permissions, for example if you set certain fields to be read-only or no access, then that will cause a problem during syncing. The MirrorSync script running on the offline device will not be able to write to the read-only field on the offline file, since it runs with the same limited permission as the offline user. To solve this problem, set the 'MirrorSync' and 'MirrorSync customization' scripts to run with full access. Do this on both the server file as well as the offline copy. This will allow MirrorSync to read and write fields that the user themselves may not have read or write access to. If you do this, you will not be able to use record-level access privileges to restrict which records are synced with the offline copy - use the MirrorSync customization script instead.

Does MirrorSync do conflict resolution?

Yes, MirrorSync has robust conflict detection and field-level merging (added in version 2). It will detect when the same record(s) are modified on both database and take action. There are two settings in the configuration client that will control how this works:

• Merge the changes together: If MirrorSync sees that all of the changes made in the databases were to different fields (ie. firstName on the server and lastName on the client), it will automatically merge the changes together. It will not tell the user that a conflict occurred, it will just write the merged record to both the server and the client. If any changes were made to the same field, it is treated as a conflict and not merged.
• Flag the edit as a conflict: MirrorSync will treat all modifications to the same record as a conflict, regardless of whether they were made to the same fields or different fields.

If a conflict occurs, then MirrorSync will resolve it based on the next configuration option:

• User picks: MirrorSync will present a web-based interface to the user, summarizing all conflicts and showing them exactly which fields were changed on both databases. It color-codes the actual sections of text that were changed, making it easy for even inexperienced users to make the best choice. The user is able to pick one entire record over another, pick individual fields from each record, and even manually edit the result to combine changes from both records. They also have the option to automatically select the most recent change (see next option).
• Most recent change wins: MirrorSync will pick whichever record was last modified as the winner of the conflict. If Joe changes a record on his iPad at 3:00 PM, Kate changes the same record on her iPhone at 3:30 PM, and Tom changes the record on the server at 4:00 PM, then when Joe or Kate sync their database, Tom's record will be selected as the winner, regardless of when Joe and Kate do their sync.
• Hub always wins: MirrorSync will always pick the value on the hub (typically FileMaker Server) as the conflict winner, regardless of other considerations. This is not generally recommended, because it is essentially first-sync wins behavior: Whoever syncs to the hub first will win conflicts with other offline users who modify the same record and sync. This setting can be useful if changes made by directly connected users should be given priority over offline, syncing users.
• Email administrator: This is very similar to 'User picks', except that instead of the user who is doing the sync deciding, an e-mail is sent to the administrator email address specified in the configuration. The administrator can resolve the conflict using the same web-based user interface described above, after which the user will be able to sync normally.

Is MirrorSync transactional? What happens if the connection is lost while syncing?

MirrorSync is not 'transactional' in the strictest sense. If the connection is dropped during a sync, it is possible for records from one table to be written to the server, while records from another related table may not be written. However, MirrorSync keeps track of which records have been written and which ones have not, and it will resume from where it was on the next sync and complete everything as if the first sync had finished. If a record on the server cannot be written because it is locked (being edited), the offline user will get a warning error message telling them this. MirrorSync will continue to retry that edit operation each time the sync script is run.

Can I use external authentication with MirrorSync?

Yes, MirrorSync supports externally authenticated accounts with no modifications. So long as the username on the local copy matches the username on the server, you can set up one password for local access and another for external authentication with the server. The passwords do not need to match between the local file and the hosted file.

Does MirrorSync respect validation settings in FileMaker?

Yes, IF they are set to 'always' (as opposed to 'Only during data entry', which is the default). The 'Allow user to override during data entry' has no effect on whether MirrorSync respects validation. If you are using UUIDs for your primary keys, we recommend setting a 'Unique value' constraint on them with the 'Always' choice selected. This protects against duplicate records, which can happen if MirrorSync is mis-configured.

Does MirrorSync retain formatting on text fields in FileMaker?

No. The formatting metadata does not get pulled due to limitations in the FileMaker XML or JDBC data source. This means that text color, style, and font information will be stripped out and converted to plain text during the sync. Some customers have come up with their own workarounds using the GetAsCSS function and custom functions to translate formatted text to plain text and then back again, but this is not something that 360Works can assist with.

Server-to-server sync

How do I set up server-to-server syncing?

For the most part, the process for setting up server-to-client sync is almost identical to server-to-server sync. There is one difference to be aware of:

• In the spoke configuration, you select 'copy or clone of existing file' or 'separate file.' If the first option is selected, you don't actually put the file on the other server during the setup process - go through the setup process, and when you're finished, save a copy or clone from the hub server using the MirrorSync download feature and upload it to the spoke server. If you select 'separate file', then you will need to have the file up and running on the spoke server during configuration.

How do I run a server-to-server sync?

There are four ways that server-to-server syncs can be triggered:

1. By using the 'Sync Now' button in the MirrorSync configuration client.
2. By checking the 'Auto Sync' checkbox in the MirrorSync configuration client, and selecting a desired frequency in seconds. This will continue to run as a background service, even if the MirrorSync configuration client is quit.
3. By running the MirrorSync script in FileMaker Pro / Go which is connected as a guest of FileMaker Server.
4. By triggering the sync URL, which can be found in the MirrorSync script. Contact 360Works for assistance with this.

How can I be notified of a problem that occurs during server-to-server sync?

There is an administrative e-mail address that is set during MirrorSync configuration. If you set this, you will receive e-mail notifications whenever the sync fails (or succeeds, depending on what granularity you set admin e-mails to). These e-mails are sent via Amazon Web Services, and thus do not require you to configure an SMTP server.

Performance questions

What is the performance like for server-to-server sync?

A real-life example is the 360Works online store database. We set up MirrorSync 3 to do a one-way sync from the live MySQL database to our FileMaker 15 Server. This database has 445,296 records across 20 tables.

Running an initial sync, which syncs all of the MySQL data into an empty FileMaker database, took 1,612 seconds (27 minutes), which breaks down to an average of 276 records per second. This is 6.1 times faster than MirrorSync 2, which averaged 45 records per second for the same setup.

Incremental syncs run every minute and take 1-2 seconds.

What is the performance like when multiple clients are syncing simultaneously?

Very good. We set up a benchmark with 250 simultaneous syncing clients to test. You can view the results on the main MirrorSync page at http://360works.com/MirrorSync

How well does MirrorSync perform on slow networks?

MirrorSync is very efficient over slow networks, because it sends very little data other than the actual record changes, and it transmits this information in large chunks, which is more efficient. There are 2 HTTP request at the beginning of the sync, 1 at the end, and usually 1 HTTP request per table that contains any modifications. For example, if you have a 20 table solution, and there are changes in 5 of the tables, it will usually take 8 HTTP requests (2 + 5 + 1) to complete the sync. By comparison, the FileMaker home page takes 65 HTTP requests to load in a web browser.

What can I do to speed up syncing?

Before talking about ways to make MirrorSync faster, let's first talk about what type of operations take longest:

• For client-server-syncs (ie. iPads syncing with FileMaker Server), the first initial sync for the first device is always the slowest, because MirrorSync needs to download metadata (primary key, modification timestamp, creation timestamp, record ID, record modification count) for every record in every table in the database. After that, any initial sync on any device will be much faster. If you reset the sync data (by right-clicking the configuration), or delete and re-create the configuration, MirrorSync will need to re-scan every record again.
• Initial syncs from a recent server copy are much faster than initial syncs from a copy made a long time ago.
• Of course, incremental syncs are faster than initial syncs.
• Any time a large number of records are written during a sync, the next sync on that device/database will take longer than usual.
• Inserts are generally faster than updates, especially for SQL databases like MySQL.
• MirrorSync can tell whether records are deleted. Checking to see if anything was deleted takes no extra time. However, if records were deleted, then MirrorSync scans the primary keys to find which record(s) were deleted, which does take longer.
• Container fields take much longer to sync than non-container data.
• The number of tables affects the speed of syncing: A good general rule is about .5-1 second of overhead per table, plus the time to actually sync changes.

MirrorSync is pretty fast without any special tweaking, but faster is always better. Here are some tips for squeezing out the best possible sync speed from MirrorSync:

• SSD drives: MirrorSync does many thousands of tiny read and write operations to its internal database when syncing large record batches. This type of usage will benefit greatly from the ultra-low latency of SSD drives. You will see a noticeable change in sync speed by installing MirrorSync on an SSD drive. You don't need to use an SSD for your entire hard drive - you can just put MirrorSync on the SSD by using a symbolic link in OS X or changing the install location on Windows. The SSD performance benefits will improve all sync operations, but it will be most noticeable when doing very small syncs with few or no changes, and initial syncs for large database with hundreds of thousands of records.

• Use FileMaker Server 14 or later, and experiment with both JDBC and XML for server communication. In most cases, JDBC is significantly faster, but there are exceptions where XML is faster. For instance, when the FileMaker Server is not on the same LAN as MirrorSync, XML is usually faster. Also, it is sometimes faster to use the MirrorSnc customization script (which requires XML) than it is to use SQL filters (which use JDBC).

• When MirrorSync fetches changed records from FileMaker Server, it requests all changes since 10 minutes prior to the last sync. This 10 minute overlap compensates for users whose clocks are slow. If you're using the Host modification timestamp trick outlined in the timezone section above, then the user clock doesn't matter, and you don't need this 10 minute overlap. You can get rid of it by editing the MirrorSync.xml file and change the 'fmServerOverlapSeconds' parameter from 600 (10 minutes) to 5 (5 seconds). The file is at:

Mac: /Library/360Works/Applications/conf/Catalina/localhost/MirrorSync.xml
Win: C:\Program Files\360Works\Applications\conf\Catalina\localhost\MirrorSync.xml

• Use serial numbers instead of UUIDs, especially for large record sets with narrow tables. The difference between a 5 character serial number and a 36 character UUID adds up, especially when transferring large numbers of records that mostly consist of foreign keys, such as join tables. Don't make this change if you have a good reason for using UUIDs.

• Container fields hold lots of data and are sent individually instead of in batches like non-container data. If there are containers that you don't need to sync, remove them from the sync layouts.

• Container fields are transferred when any field on the same record is changed, even if the container has not been modified. To prevent unnecessary container syncing, move container fields to their own table, so that they will only be transferred when they are changed.

• If you want to just sync a subset of records, instead of the entire database, you can use either record level access privileges or scripted filters (see the customization section below for more details). Scripted filters are much, much faster then record level access privilege restrictions.

• If you're on FileMaker Server 10 or 11, switch to 12 or later to use XML, or 14 or later to use JDBC. The XML Web Publishing Engine was just not very fast in FileMaker Server 11. It is greatly improved in later versions of FileMaker Server. JDBC, which is usually faster than XML, requires FMS 14 or later.

• Speaking of the XML Web Publishing Engine, there is a configuration that causes a substantial slowdown when syncing with FileMaker Server 13 (this is not an issue with FileMaker Server 10, 11, or 12. It's also not an issue with FileMaker Server 14). This adds about a second onto every XML request, so requests that would normally take about 20 milliseconds wind up taking around 1,100 - 1,200 milliseconds. To fix this, open up the file at FileMaker Server/Web Publishing/publishing engine/jwpc-tomcat/conf/server.xml. Find this section:

<Connector URIEncoding="UTF-8" acceptCount="100" 
address="127.0.0.1" 
compressableMimeType="text/html,text/css,text/plain,application/javascript,application/json" 
compression="on" compressionMinSize="2048" connectionTimeout="20000" 
disableUploadTimeout="true" enableLookups="false" maxHttpHeaderSize="8192" 
maxSpareThreads="75" maxThreads="150" minSpareThreads="5" port="16020" 
protocol="HTTP/1.1" redirectPort="8443"/>

Remove the part that says address="127.0.0.1", so that it looks like this:

<Connector URIEncoding="UTF-8" acceptCount="100" 
compressableMimeType="text/html,text/css,text/plain,application/javascript,application/json" 
compression="on" compressionMinSize="2048" connectionTimeout="20000" 
disableUploadTimeout="true" enableLookups="false" maxHttpHeaderSize="8192" 
maxSpareThreads="75" maxThreads="150" minSpareThreads="5" port="16020" 
protocol="HTTP/1.1" redirectPort="8443"/>

Save this change, then restart the Web Publishing Engine. Test that the WPE is working by using the URL "http://yourServer/fmi/xml/FMPXMLRESULT.xml?-dbnames" - it should show you an XML list of database names. If it doesn't, you might need to restart the computer. After this comes back up, your syncs should be noticeably faster, especially for <100 changed records or for no-change syncs.

• Flag records instead of deleting them: MirrorSync is very fast at detecting deleted records. However, it's always faster to sync edits and inserts than to scan for deletions, especially in large tables with more than 100,000 records. If you want to optimize performance, it might make more sense to flag records as being deleted instead of actually deleting them. That makes it into an edit operation, which is not affected by the number of records in the table.

• If you are starting off with an empty clone, and the initial sync is taking a very long time, you can pre-populate the empty clone table. See the next question: "Can I pre-populate my offline database to speed up initial sync?"

Can I pre-populate my offline database to speed up initial sync?

Yes. There are several approaches to this:

If you want the offline user to get a complete copy of the file from the server, and then just delete certain records based on filtering criteria, then you do not need to do anything special. Just select the option in the spoke database to 'Sync with an empty clone or complete copy of the main file' when you select the spoke database in the MirrorSync configuration. When you download the database or create a download link, specify the option for a full copy. The initial sync will run very quickly in this configuration, and will take care of deleting records that the user does not have access to based on filtering criteria.

If you're syncing with a copy of the server file, but for file size reasons, you don't want them to download a complete copy of the database, follow these steps:

• Duplicate your main database and delete all of the records that you don't want the offline users to download
• Host this duplicate file on the same FileMaker Server as the main file.
• In the configuration editor, select the option to 'Sync with a separate mobile file', and then select that duplicate file as the mobile file.

With these steps finished, when your users use the download link feature of MirrorSync, it will download the trimmed-down duplicate file instead of a copy of the main file.

If you need to selectively delete certain records prior to them downloading a copy of the file, but you need to customize this for each user: This is pretty simple - just make an offline copy for that user, delete whatever data you don't want the user to have, and then send it to the user. Keep in mind that if the user has record-level access to all the data, and if you haven't set up any filters in your MirrorSync customization script, then the initial sync will put those deleted records right back into the offline file, so make sure you only delete the records that the user won't get during the sync.

If you are syncing with a separate mobile file that you want to pre-populate, here are the steps to follow:

• Import the records you want from the main file into the mobile file. Be sure to uncheck the box that says "Perform auto-enter options while importing". The primary keys, modification timestamps, and creation timestamps need to match between the mobile file and the server file. Make sure that you import most or all of the records that the user will have access to during the sync - it's faster for MirrorSync to sync an empty table than a table that has a small fraction of the desired records.
• After the import, create a new record in the MirrorSync table on the hosted database. Set the type to 'Server'. Modify the sync4 field (you'll need to switch to layout mode to see which one that is), and set it to the timestamp when you ran the import, using the format 'YYYY-MM-DD hh:mm:ss' (in 24 hour time).
• Make sure that your offline users run a sync before making any database changes. Any new records that they create in the mobile file will be deleted during the initial sync. It's OK for them to add new records normally after the initial sync has successfully completed.
• It's easy to make a mistake in this process. Don't hesitate to contact 360Works support if you'd like help with this.

Security

How do I enable SSL encryption for syncing?

See the question in the configuration section on this topic.

How do I make all of my users log in with the same username and password to the hub database?

In the 'spoke' section of the MirrorSync configuration process, select the option to log in with 'Hub database credentials configured in previous screen'. This will use whatever username and password you use for configuring MirrorSync as the username and password for running the actual syncs, regardless of the username / password used to open the FileMaker Go/Pro database. If you select this option, users will not be prompted for a password when they run the sync. For FileMaker Server, you may filter the records as described in the customization section below (How can I filter records on a per-user basis if everybody is logging in with the same Filemaker account). For non-FileMaker databases such as SQL Server, you can filter records as described in the customization section below (How can I filter records using SQL).

If you select the option to sync with the hub database credentials, then whatever username and password you enter during the MirrorSync configuration for the hub will also be used for users to sync with it. This is convenient, as users will not be prompted to enter a password, but it is potentially less secure, since all users will be treated as the same user by the hub databases. It also makes it possible for a malicious user who is able to open the FileMaker client database to sync with the server, without knowing any passwords, so be sure to put a strong password on the client file to prevent this. The hub password is not embedded or stored in the client file, it is only stored on the MirrorSync server.

The default option, which is to use FileMaker client credentials, is a more secure option. The user will be prompted for their password when they first sync. The sync will then use their username and password for all requests to the hub database server. This then makes it easy to filter records on a per-user basis, and the user's account name will be used for any actions taken on the FileMaker Server.

Customizing MirrorSync

What is the MirrorSync customization script?

Many of the following tips reference the MirrorSync customization script. This is a place where you can add custom behavior before, during, and after the synchronization process. Changes to the MirrorSync customization script are never overwritten by MirrorSync (unlike the main MirrorSync script, which is overwritten whenever you re-paste the script steps).

The MirrorSync customization script is ONLY used with with FileMaker client, or with FileMaker Server when the XML option is selected. When using JDBC, the only server-side customization that can be done is filtering which records are synced, and that is done using an SQL statement instead of the MirrorSync customization script.

I don't want to sync my entire database to my offline users. Can I just sync certain tables, fields, or records?

That's a three-part question with a three-part answer.

Syncing certain tables is very easy: When you're configuring the sync using the MirrorSync configuration utility, only include layouts corresponding to the tables that you want to sync.

Syncing certain fields is also very easy: Just don't include any fields on your sync layouts except the ones that you want to sync.

Syncing certain records is only slightly less easy: For SQL databases, or for FileMaker Server with the JDBC option, jump to the 'How can I filter records using SQL' topic below. For FileMaker Server with XML, edit the 'MirrorSync Customization' script. Go to the 'hub' section (not the 'spoke', that should almost never be modified) and follow the instructions in the 'FindChanges' documentation there to constrain the found set to just the records you want to sync for the current user. Remember that the script will be running as the user doing the sync, so you can use the Get( AccountName ) function to determine which records are available. Be careful regarding the use of $$globalVariables - they will NOT be persistent across multiple calls to the customization script (except for $$MIRRORSYNC_USERTOKEN - see next question).

Any records that are excluded by your search criteria will not be synced to the user. In addition, if those records were previously synced, they will be deleted from the user's device. This makes it very easy to create check-in / check-out workflows with MirrorSync, where jobs are synced to iPads, completed, and then deleted from the device.

To test your customization script, go to the sync layout that you want to test, show all records, and run the MirrorSync customization script. If you don't get the results you expect, use the Script Debugger to tweak it.

Another approach to limiting record access is with FileMaker's record level access restrictions. Any restrictions you place on the user's privilege set will be respected by MirrorSync, and the user will only receive those records when they do a sync. Records which were previously visible which switch to non-visible will be deleted on the next sync. However, we don't recommend this approach for large record sets, because it is much, much slower than the customization process described above.

If you decide to limit the synced records, you may choose to distribute an empty clone to your users instead of a full copy of the database. That way, the initial sync will only pull the records into their offline file that they have access to on the database. Keep in mind that if they have access to many thousands of records, that could make the initial sync slower than if you distributed a full copy of the database that already contains all of the record data, and let the initial sync delete the extra records.

How can I filter records on a per-user basis if everybody is logging in with the same Filemaker account?

Use the $$MIRRORSYNC_USERTOKEN to accomplish this. This is a special global variable that MirrorSync passes from the client to the server when a sync runs. You can set this global variable to whatever you want on the client, and it will be set on the server so that the MirrorSync customization script can use it when searching for records. This global variable is the one exception to the 'no global variables' rule for server-side filtering, so use it any way you want. You'll notice that it is nested in a conditional in the MirrorSync customization script that sets it. That 'If' statement prevents it from being set on the server, which is important because that would override the value sent by the client.

How can I filter records using SQL?

In the screen where you select your primary keys, check the box titled 'Custom SQL qualifier'. This will pop open a dialog where you can write any arbitrary SQL qualifier to restrict which records the user will get. Note that you can use the {MIRRORSYNC_USERNAME} and {MIRRORSYNC_USERTOKEN} (see previous tip) substitution variable to customize the search based on the account name that they used to log into FileMaker Go/Pro with - even if you are using a single shared account for logging into the hub database.

Keep in mind that {MIRRORSYNC_USERNAME} will be in whatever upper / lower case the user entered it, and also remember that FileMaker SQL queries are case sensitive by default. This means that if you were expecting the username to be 'bsmith', but the user logged in with 'BSmith', then {MIRRORSYNC_USERNAME} will contain 'BSmith'. If this is a concern, it's a good idea to use the UPPER function in the SQL qualifier to make the username and the field being searched against both upper case.

You can test your SQL qualifier in this screen to make sure that there are no syntax errors. However, this will only work when editing a configuration, not when creating a new one, so complete the configuration without filtering and then edit it to test the SQL filtering.

MirrorSync will append your SQL qualifier to the auto-generated SQL ending with the table name. For instance, if you used the SQL qualifier "WHERE (category='active')", then when MirrorSync generates the complete SQL, it would look like this:

SELECT "column1", "column2" FROM "Your Table Name" WHERE (category='active') AND modificationTimestamp={TS '2016-07-15 9:00:00'}

It's a good idea to put your search terms in parentheses, because MirrorSync will append an AND condition with the a timestamp. For instance, if you used this search qualifier: "WHERE status='active' OR status='pending'", that will be re-written as "WHERE status='active' OR status='pending' AND modificationTimestamp={TS '2016-07-15 9:00:00'}", which would give incorrect results. However, if you wrote your query as "WHERE (status='active' OR status='pending')", then it would rewritten as "WHERE (status='active' OR status='pending') AND modificationTimestamp={TS '2016-07-15 9:00:00'}", which would give the desired results.

• If you want to write qualifiers that use related field data, you can do that using join statements, like this. Note that you cannot use table aliases for the base table in the query:

INNER JOIN secondTable T2 ON ("YourTableName".PK=T2.FK) WHERE ("Your Table Name".category='active' AND T2.someOtherFlag=1)

This would then only retrieve items from the base table where the base table has the category set to 'active' and the second related table has someOtherFlag=1.

• If you want to filter records based on the current user's account name, you can use the {MIRRORSYNC_USERNAME} substitution, like this:

WHERE assignedAccountRep = '{MIRRORSYNC_USERNAME}'

If you are using FileMaker Server as the hub database, there are a few tips to be aware of. FileMaker's JDBC driver uses mostly standard SQL syntax for writing queries. For reference, you can view the documentation at https://fmhelp.filemaker.com/docs/15/en/fm15_sql_reference.pdf. Here are a few FileMaker-specific tips to be aware of:

• If using date vales in your qualifier, use the date format DATE 'yyyy-mm-dd'. For instance, if you wanted to only sync records since February 1st, 2016, you would use this custom SQL qualifier:

WHERE myDateField >= DATE '2016-02-01'

• If you want to filter records based on some user selection, then set the $$MIRRORSYNC_USERTOKEN global variable on the client before running the sync. This will then be passed to the MirrorSync server, where it is available using the {MIRRORSYNC_USERTOKEN} substitution. For instance, you could pop up a custom dialog asking the user for a job number. Their selection would be stored to the $$MIRRORSYNC_USERTOKEN global variable, and could then be filtered on the server by using this custom SQL qualifier:

WHERE jobNumber = '{MIRRORSYNC_USERTOKEN}'

• You can combine all of these qualifiers using AND statements. For instance, let's say that you only wanted to sync active jobs, where the related user in the staffMembers table matches matches the current username, due in either 30, 60, or 90 days, depending on a user selection. The SQL qualifier would look like this:

INNER JOIN staffMembers T2 ON ("Your Table Name".userId = T2.primaryKey) WHERE T2.accountName = '{MIRRORSYNC_USERNAME}' AND "Your Table Name".jobStatus = 'active' AND "Your Table Name".dueDate <= (CURRENT_DATE + {MIRRORSYNC_USERTOKEN})

• FileMaker's JDBC driver is very fast when searching on a single table, but it can be slow when doing joins. If you have a large set of records and find that a custom SQL qualifier for related fields is making the sync take a very long time, it might be worthwhile to switch to XML and compare sync speeds.

Refer to the FileMaker SQL Reference PDF for more details. If you would like our help writing or reviewing a custom SQL qualifier, that is outside the scope of our free support, but it is a service that is available at our standard hourly rate.

How can I tell if a record created in FileMaker Go has been synced with the server?

During configuration, select any field as a write-back value (see write-back values in the primary key section, above). Make sure that value is only set to auto-enter a value on the server, by checking that Get(MultiuserState) = 2. If the field is blank, the record has not yet been synchronized. If the field contains a value, then it was either created on the server, or created in FileMaker Go and synced with the server.

Another approach you can take is by modifying the didInsert section of the customization script (see next question), but the write-back approach is significantly faster when syncing.

How can I run a script on the server when a record is synchronized?

Maybe you want to send an e-mail to an administrator when a user completes a delivery, creates a request for a quote, or deletes a job in production. To do these types of operations, modify the MirrorSync customization script. There is a section in the script for 'DidUpdate', 'DidInsert', and 'WillDelete'. Make sure you modify those in the 'Hub' section - it's very rare that these should be modified on the spoke. In the relevant section of the script, check the current layout, and if it's the one where you want to take action, go ahead and do whatever you want in this section. You can freely go to other layouts, do searches / inserts / updates / deletions, send e-mails, or take any other action, as long as you finish the script on the same layout and record where you started.

If MirrorSync detects that the record was modified as a result of a 'DidInsert' or 'DidUpdate' script, it will re-do the client search, taking into account any scripted record restrictions in the 'FindChanges' section. For example, if you want jobs to be removed from the user's iPad when a record on the 'SyncJob' layout has its status changed to 'Completed', take these steps:

1. Modify the 'FindChanges' to exclude records whose status is 'Completed' when Get(LayoutName) = "SyncJob". (To do this type of search in FileMaker, enter Find mode, Set the status field to 'Completed', Omit the current record/request, and then Constrain Found Set)
2. Modify the 'DidInsert' section to blank out the modification timestamp when Get(LayoutName) = "SyncJob".
3. Do the same thing in the 'DidUpdate' section.

Now, whenever an offline user updates or inserts a record in the 'SyncJob' table, the script will blank out the modification timestamp. This triggers a change, which causes MirrorSync to re-do the search, which will exclude the complete job from the search, which will automatically remove it from the iPad.

You might ask, why blank out the modification timestamp? The answer is that it actually doesn't matter what you do, as long as you make some change to the record at all. Blanking out the modification timestamp is a harmless change, because FileMaker will immediately replace the blank value with a new timestamp. You could just as well set any field on the record to its current value, or make some more meaningful change, such as filling in a date field with the data that the job was completed.

I don't want users to see any dialogs when the sync runs, can I prevent them from showing?

Yes, set the global variable $$SILENT_MODE to "1" in the MirrorSync customization script. This will prevent MirrorSync from showing any dialogs unless an error occurs, and for required information like login and conflict resolution.

• If you would like to also skip the login dialog, you can select the spoke option to sync with the hub database credentials. This will prevent MirrorSync from prompting the user to enter their password.
• If you would like to prevent the conflict resolution dialog from displaying, pick any option other than 'user decides' in the conflict resolution section of the MirrorSync configuration process.

I don't want users to see the MirrorSync window at all when a sync runs. Can I prevent that from showing?

If you are running MirrorSync on FileMaker Pro, then yes. Set the $$MIRRORSYNC_HIDE_WINDOW variable to 1 in the MirrorSync customization script. This will move the MirrorSync window off-screen, where the user cannot see it. This is not recommended for large sync operations that will take longer than a few seconds, because without that status display, users may think that their computer is frozen.

If you are running MirrorSync on iOS, then this cannot be hidden. That is because MirrorSync needs to open a new window to manipulate the found set without losing the current selection, and new windows cannot be hidden offscreen on iOS devices. The $$MIRRORSYNC_HIDE_WINDOW variable will be ignored on iOS.

Can I block other MirrorSync dialogs from being displayed to the user, or change the wording?

Yes. Using the MirrorSync customization script, all dialogs, even error messages, can be suppressed or modified. Look towards the bottom of the MirrorSync customization script, and you will see a section that includes a dialog for each type of message that is displayed to users during a normal sync. You may freely customize the message displayed to the user, or the title of the buttons (but be sure that the first button still has the same meaning, and the second button still has the same meaning). If you want to hide the dialog from showing, you can disable the script step that displays it, but you must decide which option you would like to select, and exit the script with that button number. You can use this technique to display messages to your users in their preferred language.

Can MirrorSync run automatically when my users finishes a job/invoice/widget? Can it run at startup?

Yes, absolutely. First of all, we recommend setting the 'MirrorSync setup' script as your startup script, or calling it from the startup script. This will check to see if the file is running offline, and whether it has ever been synced before. If not, it will prompt the user to do the initial sync. In addition, the MirrorSync setup script sets the $$MIRRORSYNC_DATABASEID global variable, which is necessary for some primary key strategies (see the Primary Key section above).

Remember that the MirrorSync script is a regular FileMaker script that can be set to run at any time by the developer. For example, if there is a critical section of your application that requires users to see what the latest value is before they make a change, trigger the sync when the user navigates to that screen, and again when they are finished editing in that screen. You could make it so that checking a box, clicking a button, or any other condition that you choose can trigger the sync to happen.

Also keep in mind that you can create multiple sync configurations - so you could sync just a certain table, maybe with a different sync direction than usual, and attach that configuration to the button for the fastest possible performance.

Can MirrorSync run automatically every x seconds/minutes?

Yes, you can use an On Timer script to trigger this. However, keep in mind that when the script runs, it commits the current record and pops open a new window that could be open for a few seconds or longer. This could get annoying if it happens in the middle of something that the user is working on. For this reason, we recommend the approach in the previous question of linking your script sync operations to some user-initiated action. The exception to this is unattended computers - if you want to have a computer that is running MirrorSync all the time for purposes of having an extra copy of the database replicated, without a user working on it, then by all means go ahead and use an On Timer script step (and be sure to see the FAQ above about disabling dialogs).

Customizing the MirrorSync layout

Feel free to make cosmetic changes to the 'MirrorSync' layout that is pasted into your solution. However, follow these tips before making changes:

• Make sure you are able to successfully sync on FileMaker Pro and FileMaker Go before making customizations.
• Make a backup copy of the unmodified MirrorSync layout first.
• It is important that you preserve the tabs on the layout, and that the same fields remain in those tabs. The other thing to be careful about is that there are two 1x1 pixel web viewers on the MirrorSync layout. Make sure that those remain visible on the layout.
• If the sync stops working after you make the changes, then you may have inadvertently lost the web viewers on the layout. Restore them from the backup copy of the layout.

Getting rid of duplicates

Let's say that you have two users that independently create new records for the same client. On your server, you want to automatically delete any records that get inserted with the same client name as a record that already exists. The place to put the de-duping script would be the 'DidInsert' section of the MirrorSync customization script. Your script should get the client name (or whatever field(s) you want to use to de-dupe) of the newly inserted record and do a search for it. If only one record comes up, then there is nothing else to do. If multiple records come up, then delete whichever ones you want to get rid of. That will automatically trigger a re-sync to happen after the sync finishes. You could of course do more sophisticated operations such as combining values from the second record into the first, or simply mark the records as potential duplicates for an administrator to clean up later rather than actually deleting them.

Licensing

How does licensing and pricing work for MirrorSync?

MirrorSync is completely free to use, with no limitations on features, for syncing FileMaker Server with a single copy of FileMaker Pro or FileMaker Go. You can optionally purchase additional server configurations or devices.

The supported configuration types are:

• FileMaker Server with FileMaker clients (Go or Pro)
• FileMaker Server with FileMaker Server
• FileMaker Server with any SQL database (Oracle, MySQL, MS SQL Server, Salesforce, and Amazon RedShift are fully supported. You may also sync with any database with a JDBC driver).
• SQL database with SQL database
• SQL database with FileMaker clients (Go or Pro)

Device prices range from $95 to $40, depending on the total quantity including the devices about to be purchased, as well as the free device that comes with MirrorSync. The pricing tiers are at 1, 50, 10, 40, 60, and 100.

Go to our online store to see all pricing details.

Is MirrorSync included in the 360Works Portfolio Bundle?

Yes. MirrorSync 3 is free for one device, but Portfolio Bundle (http://360works.com/portfolio) subscribers will receive an additional 10 device pack (for a total of 11 devices). Server-to-server configurations are not included in the Portfolio License, and must be purchased separately. There is also a special licensing program for FileMaker Business Alliance (FBA) members; contact us at support@360works.com for details.

Is there a vertical market license type for MirrorSync?

No, but we'll be happy to explain how the existing licensing pricing works for vertical market solutions. Contact 360Works support for details.

Does MirrorSync use any concurrent connections on FileMaker Server?

MirrorSync 2.1 and later does not use any concurrent connections for non-container data (text, number, date, time, timestamps). If you are syncing container fields, then MirrorSync may need to connect to FileMaker Server for the duration of the sync (it disconnects as soon as the sync finishes), depending on what version of FileMaker Pro/Go you are using, as well as the size of the container field contents.

The table below shows an 'X' for any situation where MirrorSync needs to connect as a guest of FileMaker Server.

FileMaker Pro/Go version	Download containers from server	Upload container to server (<10 megabytes)	Upload container to server (>10 megabytes)
11	X	X	X
12	-	X	X
13 and later	-	-	X

Miscellaneous questions

Is MirrorSync localized into any non-English languages? Does it work in other languages?

At this time, MirrorSync localized to English, Japanese, and French. If you would like to offer help with translation, please contact support@360works.com to let us know. You should still be able to add MirrorSync to your solution in other languages. See the previous section on customizing dialogs for tips on localizing your solution.

I only have an offline copy of my file left, and need to upload it back to the server. What do I do?

If necessary, you can re-upload an offline copy of the file to FileMaker Server and use it with MirrorSync. Be sure to remove the "Client" record in the MirrorSync table, and keep only the "Server" record. To do this, navigate to the MirrorSync layout, switch to the Debugging tab, and perform a find for "Server" in the type field. Select the inverse of the records found by pressing the pie icon in the status bar. Then, delete those other records.

Troubleshooting/Known Issues

Installer Hangs

If the MirrorSync installer hangs during installation, please force quit the installer and then follow the instructions below to recover. This typically occurs when you've had a previous install go bad for whatever reason and the 360Works service is in a bad state.

Mac

1. Launch the Terminal and run the following commands:
   1. cd /Library/LaunchDaemons
   2. sudo launchctl unload com.prosc.TomcatDaemon.plist
2. Now download a fresh copy of MirrorSync and rerun the installer.

Windows

1. Open the Services application
   • You can launch it by searching "Services" in the start menu or launching the task manager (Ctrl + Alt + Del) and going to the "Services" tab.
2. Find the "360Works" service
3. Right click it and click "Stop"
4. Now download a fresh copy of MirrorSync and rerun the installer.

Misc. Installer Trouble

If you're running into trouble with the installer, please try doing the following:

• Run as Administrator
• Temporarily disable firewall
• Temporarily disable anti-virus

Resetting sync data

MirrorSync keeps an internal SQL database of all primary keys, modification timestamps, and internal record IDs for every device being synced. Sometimes this internal database can get out of date, especially if primary keys are modified in the database. If you suspect that this is the problem, you can reset this internal data, which essentially starts MirrorSync over again as if it had never synced anything. However, before doing this, be aware that this will cause all existing offline files to no longer be able to sync. Don't do this if you have users with unsynced changes in their offline files! If that's not a concern, you can right-click (or control-click on Mac) the name of the configuration in the MirrorSync admin utility, and select 'Reset SyncData'. After doing this, try the sync again with a new offline file to see if the problem is resolved.

The ProscTomcatDaemon.jar file could not be started.

Example of error message

If you receive this message during installation, and you have Kerio VPN client installed, you will either need to remove Kerio in order to install MirrorSync, or you will need to install MirrorSync manually. The Kerio VPN client, by default, uses the same port that we use to communicate with the ProscTomcatDaemon.

The maximum number of users are currently using this copy of FileMaker Pro

Example of error message

There is a bug in FileMaker Pro and FileMaker Pro Advanced that may occur when pasting the MirrorSync script, or editing the MirrorSync script. This occurs due to the references found in the script, which will reference both the internal and external IP address. FileMaker then opens both of those references, which triggers the licensing server as two copies of FileMaker Pro. This error would occur for any script that contains both references to a single hosted file.

There are two work arounds for this:

1. Upgrade your single user license to a volume license. You can contact FileMaker directly to upgrade your license if you have 5 or more copies.
2. Paste the script and let FileMaker close due the licensing error. The script will save appropriately, and should work correctly.

If you require assistance on this issue, please let us know.

I keep getting a 102 'Field is missing' error, but I know my Sync layouts have all the right fields, what's wrong with it?

Make sure that the sync layouts are matching in both the offline and server copy. If you have repeating fields, make sure the fields show the maximum number of repetitions that are defined in the field definitions. In other words, if a field can repeat 5 times, make sure the repetitions in the inspector show 5.

Sync fails every time with error: Last sync failed: java.sql.SQLException: Error in table <Sync Layout Name>: Parse Error During Update 1: -1

This issue arises if a syncing solution has more than 998 syncing fields. The limit for the number of variables Filemaker will allow in a Let statement is 1000. During our Server <-> Client transactions, we require two variables for internal use, so the maximum syncing field limit per table is 998.

Repetitions count as a field in this total. For example, if you have 101 fields and your last field has 100 repetitions that'd be a total of 200 out of the 998 limit.

Getting an error 502 during sync or configuration

This is caused by a configuration in IIS (on Windows) or Apache (on Mac OS X) that times out after when waiting for a request. Sometimes, very complex configurations or operations may take longer than this timeout period to complete, causing the web server to send a 502 error and stop the request.

Testing Timeout

You can test whether or not the timeout setting is the issue by triggering this URL (replace your server address in the URL), which should wait 3 minutes and then return a successful test result. If you get an error instead, then you definitely need to change the timeout settings as detailed in the rest of this section: http://yourServerAddress/MirrorSync/timeoutTest?duration=180

Updating Timeout on Windows

Instructions + reference images for increasing this timeout on Windows in Internet Information Services (IIS): Update IIS timeout

Updating Timeout on Mac OS X

To increase this timeout on FileMaker's built in Apache webserver, open up this file in a text editor of your choosing: /Library/FileMaker Server/Admin/admin-helper/WEB-INF/conf/mod_proxy.conf

• Locate this line:
  • ProxyPass /fmi/ http://127.0.0.1:16021/fmi/
• And change it to this:
  • ProxyPass /fmi/ http://127.0.0.1:16021/fmi/ timeout=600
    • timeout=600 denotes a 10 minute timeout. Adjust as necessary, where 1200 is 20 minutes, 3600 is an hour, etc.
• Now restart FileMaker's built in Apache webserver using the following command in Terminal:
  • fmsadmin restart httpserver

If for some reason you get your timeout up to 7200 (2 hours) and still fail in your testing, subsequent timeout updates need to be changed in an additional file.

File: /Library/FileMaker Server/HTTPServer/conf/httpd.conf

• Locate this line:
  • ProxyPass /MirrorSync ajp://127.0.0.1:42423/MirrorSync retry=1 timeout=7200
    • Keep in mind the /MirrorSync portion is dependant upon your installation method. If you installed using a hosting provider install, this portion will be whatever name you set during the installation.
• Update the timeout=7200 designation to whatever you're updating your mod_proxy.conf to.

Finally, restart the httpserver as your would in the normal process.

Getting error "The 'MirrorSync' and 'MirrorSync setup' scripts are obsolete and need to be re-generated. Please delete them, as well as the MirrorSync layout." after following instructions

If you're still getting this error after following it's steps, make sure you don't have another layout or layout folder with the "MirrorSync" name. If you have a folder named "MirrorSync", you'll want to rename it to something else and then delete and paste the MirrorSync scripts again. Same thing applies to scripts, if you have a script folder with the same name as a MirrorSync script ("MirrorSync", "MirrorSync Customization Script", "MirrorSync setup") you'll want to rename it to somethign else and paste the MirrorSync scripts again.

Container fields are not syncing

1. If you are running FileMaker 10, 11, or 12, and your container fields are not able to sync, then check the External Data sources (File -> Manage -> External Data Sources). Make sure that this is pointing to the right server address. These get automatically set when the MirrorSync script is pasted, but if the address of the FileMaker Server is later changed in the MirrorSync configuration, MirrorSync cannot automatically edit the External Data Sources for you. This is not an issue with FileMaker 13 and MirrorSync 2.1 or later, which uses an HTTP POST (instead of connecting as a guest of FileMaker Server) to transfer container data.
2. If you are running FileMaker Server 12, and will be syncing with FileMaker Go 13, then be sure to use FileMaker Advanced 13 (not 12) to copy and paste the MirrorSync script steps. There is a known bug (http://forums.filemaker.com/posts/8d3a4548bd) that causes container images inserted from FileMaker Go 13 to FileMaker Server 12 to become inaccessible via the XML Web Publishing Engine. If you use FileMaker Pro/Advanced 13 when pasting the script steps, we are able to work around this bug by using an HTTP POST for container data.

Container fields are corrupt when they are downloaded

Export the field contents and see if it is 4 bytes long. If it is, then it is being affected by a known bug in FileMaker Server 12 (http://forums.filemaker.com/posts/8d3a4548bd): If an iPad or iPhone running FileMaker Go 13 connects directly to FileMaker Server 12 and uploads a container field, it adds an invisible attribute to the container data which prevents it from publishing using the XML Web Publishing Engine correctly - it just returns a 4 byte value representing the file type. Since MirrorSync uses the XML WPE to retrieve container data by default, this causes the problem. There are several possible solutions to this problem:

1. Enable xDBC access on your FileMaker Server, and enable the fmxdbc extended privilege for your syncing users. This will cause MirrorSync to switch from the XML Web Publishing Engine to the JDBC driver, which is not affected by this bug.
2. Switch from FileMaker Server 12 to 13, which is not affected by this bug
3. Do not connect directly to FileMaker Server 12 from FileMaker Go 13 - use MirrorSync instead to write records to the server. Containers inserted by MirrorSync are not affected by this bug.

"You must have a network connection when you use this software for the first time"

If you are running with OS X 10.9 (Mavericks) and if you have Java 6 installed, the Mavericks installer loses the list of trusted SSL certificates. This prevents MirrorSync from being able to communicate with the license server. To fix this problem, install this Java 6 update from Apple: http://support.apple.com/kb/DL1572?viewlocale=en_US&locale=en_US

If you want to verify that this is really the cause of the problem, look at the MirrorSync log file in C:\Program Files\360Works\Applications\logs or /Library/360Works/Applications/logs. If you see the text "the trustAnchors parameter must be non-empty", then this is definitely the problem.

Missing field <fieldName>, is has either been re-named or deleted in the local file

If you have field-level access privileges that prevent all access to certain fields, you will get this error whenever you try to sync. The solution is to set the MirrorSync script to run with full access on the client and server, as described here: http://docs.360works.com/index.php/MirrorSync_advanced_topics#Does_MirrorSync_require_users_to_sync_with_full_access_accounts.3F

Error 201: "Field cannot be modified"

If you have field-level access privileges that prevent the offline user from modifying certain fields, you will get this error when records are modified on the server. The solution is to set the MirrorSync script to run with full access on the client and server, as described here: http://docs.360works.com/index.php/MirrorSync_advanced_topics#Does_MirrorSync_require_users_to_sync_with_full_access_accounts.3F

Setup process leaves blank records in some tables

The MirrorSync setup process creates temporary records in tables to automatically detect primary keys, modification timestamps, and creation timestamps. It attempts to delete these temporary records after it finishes. If the temporary records are not being deleted, check to see if they have a cascading delete rule to a table stored in an external file. If so, check to makes sure that your username and password allows access to this external file, and also that the FMXML extended privilege is enabled for that username in the external file.

Server returned a 500 (Internal server) error

This can happen for several reasons, but the two most common are the Web Publishing Engine running out of memory, or a timeout happening in the Web Publishing Engine. It's not obvious which problem is the root cause unless you look at the Apache log file (for FileMaker Server 13, this is in the /Library/FileMaker Server/HTTPServer/logs directory). If you see an entry like this, it means a timeout occurred:

[error] (70007)The timeout specified has expired: proxy: read response failed from 127.0.0.1:16021 (127.0.0.1)

Fixing a timeout is not hard. Use a text editor to modify the file at /Library/FileMaker Server/Admin/admin-helper/WEB-INF/conf/mod_proxy.conf Find the line that looks like this:

ProxyPass /fmi ajp://127.0.0.1:16021/fmi

Add a timeout=1200 parameter (this sets a 1,200 second / 20 minute timeout), like this:

ProxyPass /fmi ajp://127.0.0.1:16021/fmi timeout=1200

If it's not a timeout problem, it's probably a memory issue in the Web Publishing Engine. This is more likely in FileMaker Server 11 or 12 than 13.

For very large record sets, we recommend using JDBC instead of XML Web Publishing. JDBC is much less susceptible to running out of memory. To do this, simply enable xDBC on your FileMaker Server, and make sure that the fmxdbc extended privilege is enabled for your syncing users. MirrorSync will automatically switch to JDBC if these conditions are met. If switching to xDBC is not an option, or if it does not solve the problem, then you will need to increase the memory allocation for the Web Publishing Engine.

To adjust the Web Publishing Engine memory allocation, edit this file:

Mac: /Library/FileMaker Server/Web Publishing/publishing-engine/jwpc-tomcat/bin/catalina.sh
Windows: C:\Program Files\FileMaker\FileMaker Server\Web Publishing\publishing-engine\jwpc-tomcat\bin\catalina.bat

Look for a line that contains the text "-Xmx512M". The 512 is the amount of megabytes to allocate. Change to to a higher number, such as 1024 or 2048 (be sure to keep the 'M' after the number), save the file, and then restart the Web Publishing Engine using the FileMaker admin console. Then try the sync again. FileMaker Server 13 comes with this number pre-set to 2048, so it's probably not necessary to adjust it unless you're running FileMaker Server 10, 11, or 12.

These memory settings won't take effect unless the WPE is restarted, which you can do using the 'fmsadmin restart wpe' command.

OutOfMemoryError

This means that MirrorSync itself ran out of memory. This can happen when syncing very large batches of records. This error can also manifest as a "GC overhead limit exceeded" error.

If you get this error during an incremental sync (not the initial sync), and if you are not expecting to get a large number of changes during the incremental sync, check your MirrorSync customization script. Make sure that you are not doing a 'Show all records' or 'Perform find' which would lose the found set - only do a 'Constrain records' to reduce the size of the found set.

If you get this error during an initial sync, and you have tables with more than 100,000 records, then you will probably need to increase the MirrorSync memory allocation for the initial sync, especially when syncing the first device.

MirrorSync has a 512 megabyte allocation by default. This is typically sufficient for syncing change batches of around 200,000 records, although that number gets much smaller if the records contain a lot of information. Now divide that number by the number of devices simultaneously syncing - so if you expect up to 4 people to be syncing simultaneously, MirrorSync can probably handle up to 50,000 changes from each user with the default memory allocation. This assumes no container fields; if you are using container fields with FileMaker Go/Pro 13, then those are temporarily stored in memory in MirrorSync, and may potentially cause a memory error even with a single user.

If you need to increase the memory allocation, first make sure that you're running MirrorSync 2.2 or later, and then modify the setenv file. The process is very similar to the WPE instructions, above, but the file is located here:

Mac: /Library/360Works/Applications/bin/setenv.sh
Windows: C:\Program Files\360Works\Applications\bin\setenv.bat

Look at the line that begins:

CATALINA_OPTS="-Dcom.prosc.tomcat=true -Xmx512M

Modify the -Xmx parameter to allocate the desired amount of memory. Below is an allocation of 1 Gigabytes

CATALINA_OPTS="-Dcom.prosc.tomcat=true -Xmx1G

The new memory settings won't take effect until Tomcat is restarted, which can be done using the 360Works Admin.jar, which is in /Applications on OS X and C:\Program Files\360Works\ on Windows.

Reading/writing incorrect timestamp values from MySQL

MirrorSync uses the timezone configured in the MySQL server. Most of the time, this is fine. However, some applications (such as Magento) may ignore the MySQL configured time zone and instead default to GMT/UTC timezones when writing time values. In that case, MirrorSync may not detect changes made in MySQL and/or it may write incorrect time values to other databases. To fix this, modify the MirrorSync.xml configuration file. Find this entry and change the value to "GMT".

<Parameter name="mysqlTimezone" value="" />

The MirrorSync.xml configuration file is located at C:\Program Files\360Works\Applications\conf\Catalina\localhost\MirrorSync.xml on Windows, /Library/360Works/Applications/conf/Catalina/localhost/MirrorSync.xml on Mac.

MirrorSync configuration client 'hangs'

This is an issue that we have observed on OS X, where you may get the beachball and the configuration client can lock up / freeze / hang when entering text into a dialog. So far, we have been able to trace all of these occurrences to one of the following 3rd party extensions:

• Rescue Time
• BetterSnapTool
• BetterTouchTool
• Caffeine

If you encounter this issue, check to see if you have any of these applications running, either by looking at the top-right menubar corner or using the Activity Monitor application. Quit them if they are running. If you are experiencing this problem and you do not have one of these applications running, go to your Users & Groups system preference, click the 'Login items' tab, and then use Activity Monitor to quit out of all applications listed as a login item. If that still does not solve the problem, please contact us at support@360works.com and we will help troubleshoot the problem.

Error 10 occurs during sync

IIS Manager editing the connectionTimeout property

This issue typically occurs when a syncing client opens a connection to the MirrorSync server for a container upload, but takes longer than the connectionTimeout property configured in IIS. This can be solved by making a change to the connectionTimeout Property in the IIS Manager application.

• From Administrative Tools, open the Internet Information Services Manager application.
• Choose the top level (server name) node in the tree on the left.
• Choose the "Configuration Editor" option under the management header (from Features view).
• Expand the "Section" drop down list and expand the system.applicationHost node, then choose "webLimits"
• The connectionTimeout property is the first property available, and defaults to 2 minutes.
• Increase the connectionTimeout from 2 minutes to something more appropriate (30 minutes seems to work well).
• Be sure to apply the change before closing the IIS manager application.

Handshake alert: unrecognized_name

This issue can occur when the host name used to access your SSL enabled FileMaker Server does not match the host name resulting from reverse looking up the Server's IP address. For instance if you have an internal DNS rule providing a CNAME pointing to your FileMaker Server which is available externally by a different primary DNS entry.

Typically the host name used to reach the server should match the servers established host name. If it does not, such as in the example above the mechanism performing this validation may be turned off.

• Access the setenv.bat or setenv.sh file in your 360Works/Applications/bin/ directory, and modify the first CATALINA_OPTS string. add to the end of the string: "-Djsse.enableSNIExtension=false" making sure to include a space between the new parameter and the previously last parameter in the string.
• Restart the 360Works Application Server.

Configuring MirrorSync creates gaps in serial numbers

This is known and expected behavior. When MirrorSync auto-detects primary keys, modification timestamps, and creation timestamps, it does so by creating a new test record, checking to see which fields are writeable and which ones have auto-enter options on them, and then deletes that test record. This will result in the next number in the serial number sequence to be used up.

I forgot my MirrorSync admin password

Re-run the MirrorSync installer and you can reset the password to whatever you want. Running the installer will not lose or overwrite any configurations.

I'm getting a MySQL error that starts with 'Packet for query is too large'

MySQL has a default limit of 4 megabytes for BLOBs, which are basically MySQL's version of a FileMaker container field. You can increase this limit by adding these lines (or something similar, read MySQL documentation for full details) to your MySQL configuration file:

max_allowed_packet = 1024M innodb_log_file_size = 1024M