13 Replies Latest reply on Jul 18, 2017 7:37 PM by planteg

    Advice for maintaining test, pilot and production databases?

    kenuy

      Just bought FM Pro 16 Advanced standalone to develop custom app to replace legacy ERP. Seeking advice for maintaining test, pilot and production DBs prior to rollout for 50+ employees, most of them on mobile devices.

        • 1. Re: Advice for maintaining test, pilot and production databases?
          mikebeargie

          Data separation model for sure, your data file lives separate from your interface file. This way you can have multiple interface files accessing the same data, or make migrations a lot easier on yourself. When you're ready to roll out, make a copy of your test UI file, and replace your production UI file, instant rollout of changes.

           

          Here's an introduction to the concept:

          FileMaker Separation Model: Splitting Files - YouTube

           

          Since you have an iOS component, you should be looking at potentially using an offline sync utility as well, so that the mobile users don't require a constant internet connection to connect.

           

          MirrorSync can handle that many users for offline iPads.

          360Works MirrorSync: FileMaker Sync for FileMaker Server, FileMaker Pro, and FileMaker Go on iPhone and iPad

           

          Highly would recommend a training course as well if you're new to filemaker. Cris Ippolite's courses on Lynda.com come highly recommended:

          FileMaker Pro - Online Courses, Classes, Training, Tutorials on Lynda

          2 of 2 people found this helpful
          • 2. Re: Advice for maintaining test, pilot and production databases?
            user4078

            If you don't know FileMaker very well you should know that a number of people (where I am one of) think that this is not possible in the classical sense, because there is no way to dump or restore data in and out given structure.

            There are ways to deal with this but it remains somehow 'astonishingly difficult', compared with the FileMaker way of doing things.

            • 3. Re: Advice for maintaining test, pilot and production databases?
              mikebeargie

              Not sure how astonishingly difficult you think it is.

               

              With some simple scripting and external references, it's quite easy to roll data back and forth between multiple data files.

               

              The missing functionality in filemaker is the ability to create schema spec files, EG run a script and it adds missing fields before importing the latest data. While this would make things a lot easier, it doesn't take a ton of effort to copy and paste some fields from one table to another and then press a button to run a script.

               

              However with proper documentation, scripting and commenting, it can be accomplished without an "astonishing" amount of effort.

              • 4. Re: Advice for maintaining test, pilot and production databases?
                philmodjunk

                "because there is no way to dump "

                 

                Export Revords

                 

                "or restore data in and out given structure."

                 

                Import Records

                 

                The main downside is the time this takes during which you can't allow users to modify data.

                 

                In addition to data separation, another trick to avoid having to import data is to copy/paste or import new elements that you've set and tested on your development system into your productoin version.

                 

                Scripts, custom functions and new tables can be imported

                Script Steps, Table Definitions, Field defs and layout objects can be pasted.

                • 5. Re: Advice for maintaining test, pilot and production databases?
                  mikebeargie

                  The “truncate table” script step also makes quick work of clearing out data if you need to “drop” a table before reimporting.

                   

                  I agree there are things you can do at a higher level to move the data faster, like ODBC with scripts to transfer the data in a more raw format.

                   

                  Export/Import actions executed with FMPA on the server itself, or a server-side script, both run at a pretty fast clip though; even for large data sets.

                  • 6. Re: Advice for maintaining test, pilot and production databases?
                    philmodjunk

                    Unless they changed this for server version 16, server side scripts, whether PSOS or by Schedule cannot import data Directly from one FileMaker file to another as you would normally do to import data from an older version of a file into a newer version. You can export to a data type file such as tab or CSV and then import that, but now you've both exported AND imported to get the data moved and I'd think that would chew up a lot of the time saved by doing it server side, but since we rarely need to import data in our system, I haven't had to try this for myself so if you have evidence showing me my error here, I will respectfully and eagerly listen to your correction.

                    • 7. Re: Advice for maintaining test, pilot and production databases?
                      mikebeargie
                      You can export to a data type file such as tab or CSV and then import that, but now you've both exported AND imported to get the data moved and I'd think that would chew up a lot of the time saved by doing it server side,

                      Exactly what I meant. But only if not having FMPA available on the server itself is impossible.

                       

                       

                      but now you've both exported AND imported to get the data moved and I'd think that would chew up a lot of the time saved by doing it server side,

                       

                      Not true, most of the country still has horrendous ISP connection speeds, and these operations over WAN are not good for performance at all. While you statement may be valid on LAN hosted servers, the trend has been leaning towards cloud hosted WAN servers for most environments.

                       

                      Since most migrations from a development to production copy will be done manually so they can be "babysat", it makes the most sense to have a copy of FMPA on the server itself to perform those actions. You'll need access to the server console anyways to disconnect users, close files, perform backups, etc..

                      1 of 1 people found this helpful
                      • 8. Re: Advice for maintaining test, pilot and production databases?
                        philmodjunk

                        You are right that I'm talking in terms of a LAN environment. I also did not consider using using FileMaker on the server machine to do the importing as something faster simply because in a LAN environment, it doesn't make much difference. I can download a file to another machine on the LAN, import into a clone of the new version and upload it with little added "cost" to the update process.

                         

                        So you make good points that I can happily agree with we just aren't thinking in terms of the same environments.

                        • 9. Re: Advice for maintaining test, pilot and production databases?
                          gofmp15

                          Old timers know that FIleMaker can use one GUI file to relate to MANY data files. Rather than just two files for the data separation model you could have many files in that model. And the script/GUI file can be restricted to some of those files/tables rather than have all of them.

                           

                          So, if you have one table that seems to be in constant need of revision, say your price list table, you could simply email everyone a copy of that table and when they download it over write the previous table or delete that table first.

                           

                          The reason why FileMaker Server can host 125 or so files is that this is a legacy issue going back to pre-FileMaker Pro 3 when relationships popped up and soon we could create 125 five files for our clients: my record was 110. Then came FileMaker Pro 7 (I think it was) when external files could now be internal tables and I made a few bucks merging all of those files into one file.

                           

                          Now using 2 files is a hot topic but really it could be 12 different GUI files (owner, sales, books, warehouse, etc) and not just one data file but 6 or 10 or 400...

                          • 10. Re: Advice for maintaining test, pilot and production databases?
                            wimdecorte

                            gofmp15 wrote:

                             

                             

                            Now using 2 files is a hot topic but

                             

                            It really isn't.  You'll find that the gist of all threads that discuss this is "while you can put al your tables and scripts and layouts in one file, use as many files as needed / wanted."  There are downsides and upsides to 1-file vs. many-files and there is lots of good information on it.  But there is no heat to the topic.

                            • 11. Re: Advice for maintaining test, pilot and production databases?
                              planteg

                              Hi Mike,

                               

                              The missing functionality in filemaker is the ability to create schema spec files, EG run a script and it adds missing fields before importing the latest data.

                               

                              There is a tool called SQL Editor SQLEditor for Mac OS X that let you design your database - tables, fields and relations. Once you are fine, it will create the schema in a variety of databases, including FileMaker. How can they remotely create tables, fields and relations inside FileMaker ? That question is always on my mind.

                               

                              Thanks

                              • 12. Re: Advice for maintaining test, pilot and production databases?
                                mikebeargie

                                Right, there’s ways to do it for sure, but they all require external apps. I was referring to native functions, which would make it faster.

                                • 13. Re: Advice for maintaining test, pilot and production databases?
                                  planteg

                                  By external apps, are you referring to the SDK or ODBC/JDBC ?