6 Replies Latest reply on Apr 9, 2012 6:36 PM by GordonShewach

    Best way to migrate a multiple file database to a single-file database

    wrwaugh

      I have been asked to help a client take control of a database designed by a volunteer who built a this solution rather 'organically' over a several years. It was last built on FM10 but is a multiifile solution. One file has many tables but it still references about ten other files, each with only one table.

       

      The client wants to host the file on a shared server and in order to control costs of hosting wants to limit the number of databases being hosted.

       

      Is there any guide to how best to get this multi-file database into one-file (or many fewer files) database? An application that would assist? Good advice?

       

      I would appreciate any guidance you all can offer.

        • 2. Re: Best way to migrate a multiple file database to a single-file database
          PeterWindle

          Depending on the size and complexity of the solution, I would be tempted to leave the files as separated files. Why?

           

          Let's say you move all tables into the one file. That means you now have a SINGLE point of failure. Also, with larger solutions, they tend to be a little un-reliable in terms of getting corrupted. (Sorry FileMaker, but they just DO). Also, if you ever need to add fields to a table, you only affect the file that the field exists in, rather than the whole solution (if all tables are in the one file).

           

          Perhaps one thing worth considering is creating a data-separation model, creating a new GUI file, using the existing files as the source tables.

          Why? Because it allows you to move all the layouts and scripts into a single newly created GUI file, whilst leaving the existing solution in working condition, no need to take the solution off line, just create a new one, copying layouts etc from the existing solution into a new one.

          NOTE: There are pros and cons to all different techniques. Sadly, no quick and easy way to get them all into one that I am aware of.

           

          The biggest problem with creating a NEW GUI file is that some layouts are very difficult to re-create. Therefore, it may be worth starting this approach by using the file that has the most layouts and scripts to become the new GUI file. (Save a copy of the file, give it a new name, change it's file references to point to the existing files, remove all the tables from the GUI file.)

          Leave the existing files AS IS, until you are sure that the new GUI files work perfectly. Once they are ready, then the scripts and layouts in the existing file can be stripped out and you can be left with a fairly bare bones files for each table on the server.

           

          Does this make sense?

          1 of 1 people found this helpful
          • 3. Re: Best way to migrate a multiple file database to a single-file database
            AllegroDataSolutions

            FWIW - There is a new option in FileMaker 12 under File-Save a Cop As... called "Save a self-contained copy (single file)". I haven't tried it yet.

            1 of 1 people found this helpful
            • 4. Re: Best way to migrate a multiple file database to a single-file database
              wrwaugh

              Thank you all... I will see what FM12 offers and otherwise probably leave it alone, until I can re-wrtie the whole thing.

              • 5. Re: Best way to migrate a multiple file database to a single-file database
                GordonShewach

                I was intrigued when I saw this new option, too. FMP Help says:

                 

                "Create a copy of the current file with all container data that is stored externally (both open and secure storage) embedded in the file’s container fields"

                 

                Not a solution for merging multi-file databases into 1.

                 

                Gordon Shewach

                Desktop Services

                Ann Arbor, MI

                • 6. Re: Best way to migrate a multiple file database to a single-file database
                  GordonShewach

                  The value of having a single file/multi-table db vs. a multi-database suite is enormous in my opinion (https://fmdev.filemaker.com/message/76137#76137). The solution to single-point of failure is a robust backup strategy allowing you to return to a pre-corruption copy of the database.

                   

                  There is a tool out there called FMPro Migrator that can automate the process of merging databases. I've used it a couple times. Once on a medium size solution and it worked great and saved a ton of hours. A second time I used it on a large solution (64 databases, thousands of scripts, fields, hundreds of relationships) and it did some things great and some things not. We would have been better off rewriting from scratch for the large solution. Tech support from the author of FM Pro Migrator was outstanding.

                   

                  I think a majority of people on this list will recommend rewriting from scratch. You'll end up with many improvements besides for the single file db because you'll use newer techniques to code things you coded in older versions of FMP. Great if cost is no object. But that's often not the case and FMPro Migrator can certainly save you development time for a medium-size or smaller solution.

                   

                  Gordon Shewach