6 Replies Latest reply on Aug 10, 2012 10:06 PM by glennsingleton

    Making a file "read-only" to act as an archive

    glennsingleton

      I have a solution with 1 main file that has 100's of fields, layouts and scripts. Easily a 1000 records could be added each year, if not more.

       

      Performance slows as every year goes on. I was wondering what others do to create an archive that is read only.

      This is FM11 on a server by the way.

       

      Here's my idea :-

      0. Do a backup, stop the server, open fmp on the server

      1. Do a Save As - Copy of Current file and call it archive (this should retain all the relationships)

      2. Delete from the Archive all the records that will be kept in the "Active" file

      3. Make the default auto-open user only have read-only privileges for everything, maybe enable printing

      4. Make a simple script in the "Active" file that opens the archive

      5. After checking the records in the archive, delete the active file records that are now in the archive.

      6. Start the server and add the "Archive" file to the list of hosted files

       

      The only "gotcha" I can think of is if I have elevated any scripts to "Run with full access" and they do set fields, deletes etc.

       

      What do you think ?

       

      Singo

        • 1. Re: Making a file "read-only" to act as an archive
          beverly

          Glenn, Is there some reason you need to retain all the scripts and layouts (and other "fluff") in the ARCHIVE file?

           

          If not, then just export (as FileMaker) the records you need to save and make the file as read-only permission. You can show this file in the Original (on the relationship graph and layout), should you need to access the records for any reason.

           

          Also remember that you are saving any lookup and/or calculation fields that you may need to archive for historical reasons if you just save the DATA and not all the fluff.

           

          Just curious,

          Beverly

          1 of 1 people found this helpful
          • 2. Re: Making a file "read-only" to act as an archive
            glennsingleton

            Beverly

             

            Thanks for the answer.  This file has many related files, this is the main "data" files and then there are the "processing" files that are much smaller, less used but when they are hold important information. The size of these files are very small compared to the main data file.

             

            Once a data file is "Closed" the data must be retained in some format for 7 years, and referred to every once in a while, including the data in the "processing" files.

             

            Therefore the need to keep all relationships, scripts & buttons active, but not allow the "data" to actually change, which retains the modification "time stamp" on the record but allows viewiing I HOPE.

             

            I am sure it will take a bit to get right, but the performance boost for finds, reporting and other functions in reducing the main "data" file, in my testing, are significant.

             

            Singo

            • 3. Re: Making a file "read-only" to act as an archive
              Malcolm

              My suggestion is to leave the records in place because 1000s of records per annum is not really going to slow down your database significantly.

               

              Many people have reasons to want to work with a single years records and you may be one of them. If that is the case, consider creating a new user interface file for each year. It can be linked to the current database by using the date to create a relationship. With this method it is very easy to clone the UI file and update the year.

               

              Using exactly the same method you could create a UI file which has a single record for each year. In this way you only need to add a new record to the UI file with each passing year.

               

              malcolm

              • 4. Re: Making a file "read-only" to act as an archive
                glennsingleton

                Thanks for your reply Malcolm, that would be ideal, but the relationship to the current records could get rather complicated, but that's OK if it doesnt make it slower than without it.

                 

                So you see no reduction in speed with a table of 15000 records, 300+ fields, 200+ layout, 50+ relationships, if a table view only shows 2000 in a served environment with 30 concurrent users ?

                 

                If you don't mind me asking, is this theoretical or practical advice ?

                 

                In what sort of environment, fm version, server OS, web-enabled, number of users, number of records, number of relationships, number of fields do you base this on? A lot to ask I know.

                 

                Appreciate any info as this sounds ideal for a number of reasons if I can implement it.

                 

                I can try this with 2 users on my test setup, but in production I can't experiment at all.

                 

                Singo

                • 5. Re: Making a file "read-only" to act as an archive
                  Malcolm

                  So you see no reduction in speed with a table of 15000 records, 300+ fields, 200+ layout, 50+ relationships, if a table view only shows 2000 in a served environment with 30 concurrent users ?

                   

                  This is practical advice with lots of experience to back it up. Those numbers are all OK. The only thing that is unusual is that you have such a large number of fields in one table. Are they all data, ie, text, number, date, time or is it 250 unstored calc fields?

                   

                  If you use a UI file to act as a Year filter then you are only ever dealing with a subset of the records, lets say a couple of thousand. So, the benefit of the UI file is that you are always working with the subset, not the full set of records. 

                   

                  It doesn't really matter whether you run FMS on Win or Mac. FM Server likes to sit on it's own machine. Best performance comes with good hardware, fast networks and fast hard drives.

                   

                  malcolm

                  • 6. Re: Making a file "read-only" to act as an archive
                    glennsingleton

                    Malcolm

                     

                    Thanks, just what I wanted to hear.

                     

                    This is a legal database, so there are hundreds of data fields, approximately 30 fields that are keys to related files and as few as possible unstored calcs and now very few global fields. Quite a lot of the data fields are lookups as the data must remain as it existed on the date of creation.

                     

                    Hardware is up to speck, my preference in hardware is stated but not up to me, but I get HP servers with FM approved server OS, doing not much else except file sharing, plenty of RAM, GB Network connections and large fast internal hard drives. I can't complain about that.

                     

                    The speed & installation of the FileMaker is my responsibility and I just want to do that right.

                     

                    Your advise is appreciated.

                     

                    Singo