5 Replies Latest reply on Jul 27, 2017 6:21 AM by nscheffey

    Best database strategy for remote locations




      We currently have a sales quoting system built in FileMaker that's part of a larger custom ERP. Our database is hosted at our headquarters in NYC, and our salespeople at showrooms around the world access it by VNCing into a local computer in NYC and running the FM client from there. This is to minimize latency, since the database is very old and inefficient (tables with hundreds of fields) causing it to be unusably slow at latencies over 30ms. Obviously this is not an ideal situation so we are looking to rewrite the quoting system using modern technologies and practices.


      My question is what strategy to use for the architecture of the new system. Options include:


      • FM Pro clients connecting directly to FM Server. Maybe we can rewrite the database to be efficient enough to be usable at the latencies in our most remote showrooms (100ms). With optimized schema architecture, leveraging PSOS for complex reporting, clean simple layouts, etc. is this achievable?
      • Local FMP files on user machines at remote locations that sync to our FileMaker Server at headquarters. Probably using a sync framework like GoZync, Mirror Sync etc. Eliminates latency issues but introduces sync issues.
      • WebDirect. Is this affected by latency to server?
      • Server to Server sync, with FMS instances located close enough to each showroom to minimize latency, and then kept in sync with something like Linear Blue SyncServer.
      • Application virtualization using something like Citrix XenApp. Would be a transition for our Mac based users to Windows but if its the best option we are open to it.


      I'd like to know what people have had success with. The FM platform has made a lot of great advances very recently, so I want to make sure we are using the best software development strategy we can. What would FileMaker Inc. recommend we do?


      Thanks in advance for any feedback, very appreciated!

        • 1. Re: Best database strategy for remote locations

          Just for starters, I'm going to look at the FileMaker and Webdirect options only because the syncing options introduce points of failure to an already working system and probably aren't necessary (though I can't be sure not knowing the details of your system).


          My suggestion for your development team is to rent an AWS or FileMaker Cloud server in Australia to do your testing.   Australia has quite a bit of lag to the US, 200ms+ typical.


          nscheffey wrote:



          • FM Pro clients connecting directly to FM Server. Maybe we can rewrite the database to be efficient enough to be usable at the latencies in our most remote showrooms (100ms). With optimized schema architecture, leveraging PSOS for complex reporting, clean simple layouts, etc. is this achievable?

          What you say can work, make the db and layouts more efficient, throw processing at the server with PSOS.  This can make it better but it is still effected by latency. A lot of development hours may be involved in this and it is not as quick as webdirect.


          WebDirect. Is this affected by latency to server?

          Webdirect is amazing overseas, I am in Australia and have a solution in America and it is paiiiinfully slow in FileMaker (feels like minutes to load a complicated table).  Webdirect however is not effected.  If you can re-work your remote layouts to work in Webdirect then your problem would be solved.



          As for Citrix Xen App or RemoteApp I haven't seen them working in practice with FileMaker so they could be an option, but since you are still streaming to the client I could imagine it won't be much better.  The only question I can ask there is how are the users VNC'ing in?  Is it via a video sharing style app (e.g. Team Viewer or Apples Remote Desktop), or is it via RDP or Citrix which send more efficient GDI information which makes it a bit nicer (still only as fast as the lag but more efficient).

          3 of 3 people found this helpful
          • 2. Re: Best database strategy for remote locations

            We use Citrix to provide access to Filemaker and I'm hoping that we can eliminate that approach in the future. It enables an inefficient design to be remotely accessed, but it adds an additional layer of complexity and set of possible failure points that we'd prefer not to have.

            1 of 1 people found this helpful
            • 3. Re: Best database strategy for remote locations

              We built our business originally around Citrix XenApp and more recently around Microsoft RemoteApp (Remote Resources). Both stream only FileMaker to each computer, we do not as standard provide any virtual desktops and on a restricted basis do allow other streamed apps, such as Microsoft Word and Acrobat to be opened by FileMaker if necessary.


              Phil is correct that this does add a layer of complexity, but in our view (with technology at its current state) it is the most efficient method of delivering our FileMaker solutions, which by the nature of the businesses involved tend to be rather complex ones.


              The major issue for Mac users is a Windows interface being presented to them, although with v16 it is difficult to often tell the difference between a Remote Resource streamed copy and a local Mac copy. The major factor to manage is uploading and downloading files, as 'Desktop', 'Documents', etc. in the Open, Save As, Export windows are usually those from the server (folder redirection cannot be relied on) and not the user's computer. We've built our solutions around this delivery platform and our solutions have HR modules where users can set their preferred folder on their local disk to save and upload files to/from (we auto name exports as much as possible).


              There are other issues, such as lack of drag and drop from the desktop and inability to send email using Mail or Outlook, but again we've built internal email sending into our systems with full HTML and multiple attachment support.


              Citrix does a good job on allowing the 'command' key for shortcuts, whereas the Remote Desktop for Mac currently doesn't, so keyboard shortcuts are a pain. However, there is a beta version of Remote Desktop in development by Microsoft, with only the Remote Resources element listed as outstanding, which should resolve this in the future. Microsoft have been very good at reacting to reported problems and communication with them has been a refreshing experience recently (the same cannot be said for Citrix).


              Originally the Citrix option was setup purely to allow office compatible speeds for worldwide access. We have customers in Asia with poor connections and not only did this resolve the speed issues, but if a connection was dropped, reconnection allowed the user to continue work from the same point they were at prior to the disconnection.


              It was our expectation that streaming using either of the above methods would become less necessary with the advent of WebDirect , improved FileMaker performance and increasing bandwidth. We still expect that this will eventually happen, but not in the foreseeable future due to the 'Apple disease' of annual operating system updates that Microsoft has taken further to 2 updates a year. This causes instability, for instance FMP v16 is only compatible with Mac OS X v10.11 and v10.12, the means that a worldwide system will have to be updated every 2 to 3 years on each computer to accommodate new hardware. The streamed approach would allow FMP v16 to run on pretty much any Mac and even as far back to Windows XP and Vista.


              Also, when FileMaker needs updating or upgrading, this is completely centralised on a few servers, not every computer accessing the system. We are also dependent on 3rd party plug-ins and again this is managed completely centrally.


              Setting up users is a simple case of configuring in Windows Control Panel or installing the free Remote Desktop, or Citrix Receiver software and FileMaker can be preconfigure with the user name, settings changed, plug-ins installed and server/favourites setup in the 'Launch Center'. Changing/swapping computers and office relocations require very little, if any involvement by us.


              Originally our systems were viewed much like comparing a family saloon car against a sports car, expensive but very fast, if you want the speed you had to pay for it. However, with the hidden cost of server and desktop maintenance and support, the central management is possibly starting to prove to be as, if not more, cost efficient on larger installations. The users love it as everything is our responsibility, not theirs and they need no local or internal IT support to use their FileMaker databases.


              There are issues to consider. Citrix previously provided the better Mac experience, but currently there appears to be an issue since the SDI release of FM v16 and currently (shortcut key excepted) Microsoft Remote Resources is our favourite.


              WebDirect continues to be the most interesting development, but changes in browsers (remember when Firefox was the most web standards compliant?) and complementary web technologies still provides compatibility challenges outside of the FileMaker developer's control.


              The above is written after 6.5 years evolution of our systems with customers worldwide. Our XenApp and RemoteApp servers are on the same VLAN as the FileMaker Servers within our infrastructure supplier's hosted servers, hence performance can often be faster working this way than an office LAN - our development team are split between England and Ireland and all our development is via these servers as well.


              We do respect everyone who prefers to stick to the traditional methods of providing FileMaker, but we could not provide the service we do using anything other than the above (again, with the technology we currently have available).


              Apologies if there are any typos above, but creating a posting this long on Jive is one of the worst experiences, thank goodness I'm not epileptic with this constant flashing!


              Kind regards



              2 of 2 people found this helpful
              • 4. Re: Best database strategy for remote locations

                Thanks Carl for the detailed reply, this is very helpful. It sounds like WebDirect might be the way to go here, FMI is definitely investing their technology and marketing resources in it so that's a good sign. Appreciated!

                • 5. Re: Best database strategy for remote locations

                  Thanks Andy for the in depth thoughtful reply. I know several of my developer friends at large organizations swear by Citrix for remote access. Sounds like the Windows/Mac issue is getting better. Appreciate the info!