5 Replies Latest reply on Dec 5, 2015 11:20 AM by francishunger

    Deployment of multiple instances (in several cities)




      I've programmed an CRM solution involving so far FM 12 Adv and one host and 4 peers in one office. It may be soon that my client moves forward to expand to other cities. I'm thinking about the options to


      A) Deploy an individual solution at each office (decentral solution)

      + smaller amount of data per database results in faster finds and scripts

      - difficulties and higher costs in deployment and updating, since all installations have to be handled individually


      B) Central deployment on filemaker server

      - large amounts of data accumulate fast when used with 5 or more cities

      + easier update process


      There would be many things to discuss, but I would like to focus on speed questions: If I would prefer Option B, the most likely way to go would be a login at the beginning with user names accociated to cities. Now in every layout I'll have to constrain the found set to the particular city, the same for scripts and maybe even with some formulas. This is, where I fear performance problems. If on every operation a constraint must be calculated, what would you suggest would be the best way to implement that (Relation/Layout-Script-Trigger/ ...) ?


      Thanks in advance!

        • 1. Re: Deployment of multiple instances (in several cities)

          Francis -


          The "best practices" method for restricting record access is to use the security model and restrict access based on privilege set. This is done using a Boolean calculation based on a condition associated with the user (such as UserInstance or UserCity) versus a field that's tagged to match in each record. Then, the calculation is:


               table::UserCity = currentUserCity


          FileMaker will then test each record as it comes across to determine whether the user should have access or not. At its most basic level, records that do not match the criteria will be marked with a "<No Access>" indicator in all fields, which can be annoying to users; hence, it's often best to combine this technique with other techniques such as Script Triggers or instance-specific interface files that are configured only to reveal the correct records to the user. However, the actual privilege set method should not be abandoned, as all other techniques rely on interface-level control and can be bypassed through Export Records, ExecuteSQL and other means.


          As far as performance is concerned, I think you'll find the performance impact of record-level access privileges to be small compared to the impact of going from a locally-hosted database (presumably this is peer-to-peer?) to a server-hosted database over the open Internet (WAN). The fundamental reason for this is network bandwidth and latency considerations. There have been several discussions on the forum and in other locations (example: Jon Thatcher's sessions at DevCon) on the impacts of network on database performance and how to optimize a solution to adjust for them. I suggest you do a search for terms like "bandwidth", "WAN", "latency" on the forum and read through the posts.





          • 2. Re: Deployment of multiple instances (in several cities)

            Mike makes a few good points. Record level access control works. If you really do not need to data from other cities to be accessed but want the server in one place you might find a way to work out having the records from each city in a separate table that are all related in some way to another table. As you add a city you could add a table. You might need to be careful about how the scripts work when doing this. You can easily set the layouts to vary the tables used based on location or login. This would keep the deployment costs down but keep each office only working in its own table of unique records with the ability to look at other city records if they needed to. Without knowing the number of users or records and such it is hard to say how the server speed wll be affected.


            The speed is more dependent on the connections and not the actual server.


            It sounds like you are afraid of the solution coming to a grinding halt over WAN. If you make a few well thought out decisions this will not be too much of an issue. Get the best internet connections you can afford. Make sure layouts are WAN friendly.


            Will it be slower than when you are in the main office? Yes. Will it feel way too slow? Likely not. You may be worried about nothing.


            I would suggest maybe testing a WAN connection with your current hosted file. Not very difficult to do and easy to test. You might be happy with what you see.


            When we moved from peer-to-peer over to Sever the speed was noticably better locally. I was suprised. I have a number of layouts that are image / field heavy and they load up pretty quick over remote connection from anywhere in the world. Finds take a few seconds longer when you want to access the server from half way around the globe using a mobile wifi unit, but nothing too bad. I was also worried that it would be too slow and that just was not the case.


            I would say the connection quality at the server would be the most important. That made the most difference for me.

            • 3. Re: Deployment of multiple instances (in several cities)

              Hi Mike and Tom,


              thanks a lot for your extensive insightful answers. I'll go for "restrict access based on privilege set". From that arises another question:


              Cities may be stored in a field "StoredCityID" at the creation of each record. So each table would have a field "StoredCityID". This gets compared to the user currently logged in (CurrentCityID), which is set by the start script as a global field in the Options Table.   

              SomeTable::StoredCityID = OptionTable::CurrentCityID



              In the current solution for each record the username is already stored in all tables. I could fetch the related UserCityID for each user from the table that holds the user privileges and the "UserCityID" and compare that to the global CurrentCityID.


              SomeTable::UserID–>UserPrivilegeTable::UserCityID   =   OptionTable::CurrentCityID

              Am I right, assuming that variant A is performing better, because it can directly operate on the stored field "StoredCityID" and not via a relation?


              Thanks in advance for your thoughts.

              • 4. Re: Deployment of multiple instances (in several cities)

                Yes, A will be faster.


                Moreover, B will not really work if a user gets relocated to another city over time...

                • 5. Re: Deployment of multiple instances (in several cities)

                  Thanks again for your helpful comments!