14 Replies Latest reply on Jan 2, 2017 3:29 PM by Malcolm

    Upgrading / maintaining database

    avallejo

      Hi all.

       

      Consider the following scenario.

      I have set up a database, uploaded to the server, have about 5 people acessing it via intranet on desktops and iThings.

      Now I want to make modofication to the database, alter some layouts and maybe adding some, and even maybe including new tables and fields.

      What's the best course of action? This is my first serious multiuser database, and I'm no pro, just a doctor running this db in our small community hospital...

       

      best 2017 to all!

       

      Andre

        • 1. Re: Upgrading / maintaining database
          philmodjunk

          I'm going to run over a list of methods here in brief. You can websearch and forum search on each or you can ask for more detail from me on any of these:

           

          1) Consider changing your database over to a data separation solution. Scripts, layouts, etc are in one file. All your data is in another file. With this approach, you can make updates to the interface file (the one with the layouts) and when you are ready, you just replace the current interface file with the new.

           

          There's a relatively simple method that you can use to split a one file solution into data and interface files.

           

          2) Data separation reduces the problems that can occur when updating a file but does not eliminate them. Sometimes, you have no alternative but to:

          a) close or block access to the hosted file so that no more changes can be made to it

          b) take a clone (empty of data) copy of your new version and use import records to import all data from the current working copy into this clone. Update any serial number settings at the same time to avoid getting duplicate serial numbers after the new copy goes back into use. This entire process can and should be scripted to avoid mistakes and should be tested on back up copies before you do it with the current copy of the file.

          c) replace the old copy with this new copy on your server and enable access to the file.

           

          3) Not all changes need be made to an offline copy and then that copy substituted for the original. You can make script and layout changes to the hosted copy while it is in use provided that you recognize the risks and take steps to minimize the consequences of a mistake.

           

          I support a very large (100 plus files 300+ users), solution as part of a team. Once a week, we take down the system for 1 to 2 hours late at night when I take all of the team's requested data level changes and implement them while the system is not hosted, but rather open in FileMaker Advanced, but we make other changes to the live system after first developing and testing the change in other copies of our system. These changes are to be carefully documented with steps taken to ensure that we can quickly roll back a change if we discover that the change has introduced an unexpected problem that got by our offline testing. Note that layout objects and script steps can be copy/pasted and this is how we usually transfer the new changes from a test copy to the live copy. I also will sometimes add a layout object--such as a new button to a layout, but use Hide Object When to keep the new object from being visible to anyone but a user with [full access] so that I can test it with out letting users to interact with it prematurely.

          1 of 1 people found this helpful
          • 2. Re: Upgrading / maintaining database
            john_wolff

            Hi Andre,

             

            One of the safer ways to make exploratory changes is to have a copy of your file hosted on a separate development server, the free one available with your Community membership, which has to be running on a different machine but that could be your client workstation.

             

            Make the changes you want there, get them tested by one of your users, then copy the new elements to your production file. You will need FMPA to copy and paste tables, fields and layouts to the production file. I find the biggest challenge is keeping the two versions readily identified when you copy and paste.

             

            I HTH,

             

            John

            1 of 1 people found this helpful
            • 3. Re: Upgrading / maintaining database
              avallejo

              Thanks a lot. I will study the options with care. I have been considering the splitting option, it seems the best one for layout develops, tough may fall short for bigger changes (which are of course much less frequent...)

              Best,

               

              André

              • 4. Re: Upgrading / maintaining database
                Malcolm

                philmodjunk has suggested using a data separated model. I agree with him. Having the data separate from the UI can be extremely useful. The beauty of the separated system is that you can close the databases on the server, switch the UI file without having to touch the data.

                 

                If you do not use a data-separated system you'll need to keep an accurate record of all the changes that need to be made to bring the development changes into the live system. Document your changes in a way that is easy to refer to. You want to be able to look at a record of change that tells you how the system changed. Whether you do that in notebooks or in a database is up to you. It needs to be easy enough for you to do it and stick with it. Otherwise, you risk having a working DEV system but a broken live system because you missed a step somewhere in the transfer.

                 

                Communicate all changes to the database users, especially during development prior to change, so that they know what is going on.

                1 of 1 people found this helpful
                • 5. Re: Upgrading / maintaining database
                  gdurniak

                  This need not be complicated

                   

                  You can edit the files "live",  preferably off hours,  and your users will see the changes immediately

                   

                  We do this all the time,  with up to 50 users

                   

                  Some IT Departments prefer a separate "development" server,  where changes are made,  and thoroughly tested, before going "live".  The problem is,  this takes a LOT longer,  and,  you then have to import your "live" data for each table into a Clone of the edited file,  which is a pain.  You can setup scripts to do the imports

                   

                  It does not sound like you need a "Data Separation" Model,  and for a FileMaker beginner,  really not a good idea. Data Separation adds some unnecessary complexity, and is really not what FileMaker is all about. Better to keep it simple

                   

                  greg

                   

                  > I want to make modifications to the database, alter some layouts, including new tables and fields.

                  What's the best course of action? This is my first serious multiuser database, and I'm no pro

                  2 of 2 people found this helpful
                  • 6. Re: Upgrading / maintaining database
                    avallejo

                    Thanks all for the insights. My database is not to big (for now) but I'll be constantly adding new elements like reports and graphics based on the same data and tables. So it seems logical to go the split way. It seems much easier to swap only the front end and not worry about the data itself. I tested the idea and it feels seamless, I don't even know they're split. Will upload the solution to a cloud server to see how it goes, and if if works right I'll probably invest in a local FM Server...

                    Peaceful 2017 to all, we really need it!

                    • 7. Re: Upgrading / maintaining database
                      philmodjunk

                      You can edit the files "live",  preferably off hours,  and your users will see the changes immediately

                       

                      Yes you can. You can also corrupt the files, catch users "between stools" (cause an unexpected change in behavior in the middle of one of their tasks) and even lock all users out of editing data in a given table for several minutes while data model level changes are committed back to the server--and this can happen in the middle of a script being performed by a user and thus have potentially catastrophic consequences for your data.

                       

                      Some changes are fairly low risk. Others--those you would make to field and table definitions are high risk where a network glitch can, in some cases, corrupt the file if they happen at just the wrong moment. Haven't tested this in version 15, but I found out a number of versions ago that simply opening auto-enter calculation in the editor to just look at the calculation locked all users out of making any changes to records in that table until I closed the editor.

                       

                      The problem is,  this takes a LOT longer,  and,  you then have to import your "live" data for each table into a Clone of the edited file,  which is a pain.

                       

                      I agree that it's a pain, but it's almost never needed if you close your database files, open them with advanced and make the data level changes followed by re-opening them on the server after the changes have been made. Much of the changes developed and tested on a developer set of the files in your solution can be copy/pasted into the working copy once you know that they will work as expected.

                      • 8. Re: Upgrading / maintaining database
                        gdurniak

                        That's just silly

                         

                        If you have the luxury of shutting down the system,  then any method you use is fine

                         

                        greg

                         

                        > close your database files, open them with advanced and make the data level changes followed by re-opening them on the server after the changes have been made

                        • 9. Re: Upgrading / maintaining database
                          philmodjunk

                          That's just silly

                           

                          There's nothing silly about taking the system down for updates. That shut down is for rarely more than one hour per week as we use that time to make the needed data level changes. Other than the weekly maintenance window, our solution is up 24/7 and used by 300+ personnel.

                          • 10. Re: Upgrading / maintaining database
                            srzuch

                            Not only is it not silly, it is not unusual to have maintenance periods during the month.  One of my financial clients is a Goldman Sachs customer, and several weekends a month, Goldman's online trade entry system is down for maintenance.

                             

                            Obviously, 24/7 systems do not have such a luxury, but really, how many FileMaker systems cannot be scheduled to be shut down a few hours a month, or week?

                             

                            Regarding the data separation model, but speaking as a newbie in the FileMaker world, it seems to me that many changes would required touching the database structure.  FileMaker defines many business rules/procedures in it's tables and fields, and relationships.  Even adding a new total to a report may require a database structure modification.  In addition, unlike SQL databases or even Microsoft Access, you can't change the database structure via scripts (programming).   Not saying the data separation model does not have advantages (I use it extensively in my Access systems) ...

                             

                            Steve

                            • 11. Re: Upgrading / maintaining database
                              philmodjunk

                              Regarding the data separation model, but speaking as a newbie in the FileMaker world, it seems to me that many changes would required touching the database structure.  FileMaker defines many business rules/procedures in it's tables and fields, and relationships.  Even adding a new total to a report may require a database structure modification.  In addition, unlike SQL databases or even Microsoft Access, you can't change the database structure via scripts (programming).   Not saying the data separation model does not have advantages (I use it extensively in my Access systems) ..

                              .

                               

                              But much of those modifications are via table occurrence references. These, and the relationships that link them would (for scripts and layouts) be part of the interface file. The only table occurrences and relationships needed in the data file would be those that are needed at the data level--such as a relationship needed to implement a calculation field or auto-enter field option. So not as many of those changes as you might think (though some indeed are) will require a data file modification.

                              1 of 1 people found this helpful
                              • 12. Re: Upgrading / maintaining database
                                Malcolm

                                srzuch Changes may require touching the structure, especially in early stages of development. It doesn't outweigh the benefits of the system.

                                 

                                The addition of a field to one of the tables in the data file is light work, compared to updating the entire data set across however many tables there are and ensuring that the serial number fields are updated, etc. Adding a summary field only requires a few minutes to enter the field options. The down time involved in moving the data back in can be considerable. The developer either does it in the down time ( late night, no sleep ) or creates a down time ( stop work everyone ). 

                                 

                                Most of the other work you describe is done in relationships and that is done in the UI file. It may be necessary to have relationships in the data file if you want to have a calculation field based on a relationship. Ever since we were given the ExecuteSQL function we can perform a lot of those lookups and calculations without needing relationships in the data file at all.

                                1 of 1 people found this helpful
                                • 13. Re: Upgrading / maintaining database
                                  srzuch

                                  srzuch Changes may require touching the structure, especially in early stages of development. It doesn't outweigh the benefits of the system.

                                   

                                  The above in bold was not something that I stated.

                                   

                                  Yes, I do question the advantages of data separation especially in the early stages of development (many database structure changes required ... not just UI related) with low record counts (making importing not a large time consuming event, I hope.   But I have seen the advantages in other development tools of separating the UI from the database, and will continue the practice in FM when appropriate.

                                   

                                  Steve

                                  1 of 1 people found this helpful
                                  • 14. Re: Upgrading / maintaining database
                                    Malcolm

                                    I didn't mean to suggest that you were saying that. It was my opinion.

                                     

                                    There are pro's and con's for data separation. In general, I'm in favour of it. It's a very easy thing to do (even for existing solutions) and brings a lot of benefits. The downside relates to the fact that you have to maintain multiple files. Without external authentication you need to script the control of user accounts. You have to be aware of multi-file permissions, and the effect of globals, variables and scripts, because you'll bump into them occasionally. Like most things, they are only a problem the first time, once you've bumped into the problem you know how to avoid it. For all of that, the biggest nuisance that I find in a separated solution is that you cannot open directly to the table definition of the current table. :-) Once the system is stable, you can almost forget that the data tables are separate.

                                     

                                    In a very small database, you may decide that it's easier to be single file. Small databases do tend to grow. Though, if there are going to be advantages to data separation, you can do it piecemeal as required.

                                     

                                    Malcolm