13 Replies Latest reply on Feb 23, 2016 9:50 AM by beverly

    ESS change all ODBC table references on existing file to new database name with identical data structure


      for ease of reference (other articles I have found and read)






      I have encountered an unexpected (limitation?) in ESS, and I'm curious if anyone has found a workaround which is more current than the articles referenced (those articales are from 2008-2010).


      Here is my scenario.


      I have an internal tool built on FM for a specific region (North America), it's been pretty successful, except I chose to move 4 tables of RAW data into a SQL Database so my Filemaker file didn't have 10 GB of RAW data in it (a. file bloat, b. backup bloat, c. increased chance of corrupted FM file). SQL also permitted me to optimize some of the summary processes, so that state-wise calculations can be performed very quickly, about 10 times faster than when this same data was in Filemaker. Of course, I felt like I had made a slam-dunk because the users are making changes in FM tables throughout the day and the raw data is being allocated based on these changes. From the user perspective everything is virtually transparent, they can FIND against the ESS data, insert new data, perform exports, and we have provided a way to summarize the found data (little clunky).


      Now I've been asked to transplant this successful tool into another region (Europe), and I thought to myself "I'd really like to have a single code base shared by both regions (and any future regions)". So I figured (incorrectly), I would clone the FM database, give it a new name, clone the SQL backend tables into a new SQL database, and update the ESS/ODBC configuration to point the new FM clone at the new SQL clone to avoid breaking everything in the calculations and scripts. Ideally, this will end up in Asia, Middle East, Latin America regions as well (someday) and hosted on in-region servers performance should be really good as compared to trans-atlantic access (well that was my thinking).


      Simply put, this does not work. The only thing I can figure is that Filemaker stores internally the required DSN details to connect directly to the SQL source (I think I have seen similar behavior in MS Access integrations, and this is probably done during the manage database activity). It seems from reading that the only opportunity to "refresh" this information to accomodate name changes etc, is to either block access entirely for the ESS account to (1) trigger some "ReLink" button in Filemaker Database Management (which I have yet to see), or (2) manually relink every shadow table and deal with any fallout in scripts and calculations. While option 2 would be some work initially, I'm more concerned about having to repeat that process every time I perform a release, and it seems like the alternative would be to always have distinct code bases for each region (doubling, tripling effort to make all the same changes in scripts, layouts etc).


      Having read all this, my question is, has anyone found a really good process that would ease this given my scenario? If all the SQL references were in a NA-SQL-DATA and EA-SQL-DATA FM files and I was using separation model? What is the easiest possible way to trigger the Re-Link button, and will it address this problem entirely? I thought maybe the ODBC reference might be reset when the file was rehosted (nope). Was my mis-step thinking I could link a few tables to SQL Server using ESS two years ago?


      Any more current ideas on how to tackle this problem that I could investigate would be appreciated, or if based on this new future state design changes which would make future migrations more drop-in upgrades.


        • 1. Re: ESS change all ODBC table references on existing file to new database name with identical data structure

          You will need to refresh the shadow tables for ESS after changing the ODBC datasource, everything else (script/field/layout ref, etc..) should get handled gracefully by FileMaker if the tables and fields are identical.


          I have a similar situation ( EU/US client with similar front end in FM with SQL datasource for syncing) and I could not find any way around the limitation when using ESS.

          • 2. Re: ESS change all ODBC table references on existing file to new database name with identical data structure

            Missed that important prompt asking if I wanted this converted to a "Question", but thanks for the response Mike.


            When you refer to "Refresh the Shadow Tables", how have you accomplished this in the past for your project (which sounds very similar)?  At this moment I am assuming you mean using the relink option on the Tables tab, because Sync on the Fields tab seems to reuse the existing connection each time.


            I just tested and if I disable access to NA-SQL-DB (remove permissions for the ESS account), and reopen the Europe FM database I can see the "Missing Table" references and am presented with the "Re-Link" button.  Based on a quick glance it seems like that corrects the reference (meaning I can see the data from the correct Europe sql database afterwards), not sure if it is introducing latent script or calc issues (Hopefully not based on your prior response).  Disabling access for my NA app to perform the updates on the EA app seems goofy and dangerous, but might be the best option I've found so far.


            If I instead double-clicked the table in the relationship diagram and change it there, it definitely creates a new Table reference and moves the TO to this new object (because I get new duplicates for each table [Table_X remains but a new entry Table_X_2 appears]).  So that approach seems completely unuseable to me.


            Also, I'm certain I've migrated from a development environment to production without any issues before with ESS DSN, so I guess the problem appears when I need to change the DSN name (to operate both instances same server) or database name (point instance at regional data).

            • 3. Re: ESS change all ODBC table references on existing file to new database name with identical data structure

              Under the field list in file > manage > database there is a refresh button for shadow tables.


              If you’re getting missing reference messages you might not have the same issue I’m having.

              • 4. Re: ESS change all ODBC table references on existing file to new database name with identical data structure



                I was wondering if you could help me out as I am trying to repoint my FM solution to a different set of backend MySQL tables using similar to what you describe trying to do with your different regional databases.  I have tried so many different things to make the migration from one set of MySQL tables to another easier, but I am still having to rebuild the relationships each time I do anything. 


                This Relink button on the Tables tab--I have never seen it.  What conditions?  Where is the documentation on it?  The sync button does not suffice. 


                I am now using a data separation model with one FM file dedicated to making the connection to the MySQL file and the other handling all the workflow and UI.  I thought at least I could have two ESS focused files permanently pointing to their respective MySQL sources.  But even there, it seems that I have to rebuild the relationships every time I point a new front-end FM file at the backend FM file.  And even then, transitioning to the data separation model hasn't really resolved the issues.


                Right now I have two parallel FM solutions pointing to different copies of a MySQL database.   I can manually keep them parallel,  but lots to be nervous about still after my transition..


                Although I am now able to see the correct MySQL data in my Layouts and I can perform some of my workflow tasks as scripted things are not right in my Manage Database environment.  For starters, none of my relationships show up when I view the Table tab and all the basetable TOs pointing to the external FM tables show up as <<Data Source Missing>> on the Table Tab.  On the graph, however, where I manually reconnected each and every TO to the external FM file, preserving the original TO names,  everything looks like it is working fine.  But then it comes to modifying local calcs which are based on those relationships and I am unable to select the local TOs to re-build the calculations, so it is clear that FM has not updated the references.    I am at my wits end.  Thoughts?

                • 5. Re: ESS change all ODBC table references on existing file to new database name with identical data structure



                    Let me try to get a better written explanation from the Skeleton Key team ( greglane ) who ultimately provided the solution to this problem for me.  They were able to accomplish this without using data-separation model using SQL instances.  We currently have 4 regional instances of exactly the same tool using this approach with very good results.



                  My responses to your several questions:

                  1. Relink button not appearing.  I was only able to get this button to show by completely blocking access to the first database (the one which was used to create the original link), changing the DSN and then going in to fix the "Datasource missing" situation.  Since blocking access to the first database, also had the effect of shutting down the regional production instance I did not go this route.  If my original development "golden reference" was built on a development environment it might have worked, but still a headache just to get the button visible.
                  2. Separation model.  I went down this route, but lack enough exposure to the various workarounds and things to watch out for.  I ended up with scripts pointing at incorrect fields and tables, because all the linking in Filemaker seems to be tied to the underlying "Table ID" and "Field ID" which I found nearly impossible to maintain (you cannot set these yourself).  "Clone DB" from a "golden version" every time appears to be the solution, but turned out to be more complicated than the final solution.  I do use a separate "Regionalized" FM database to hold region-specific value lists and script logic, but I keep it simple and clone it for use in each new region.
                  3. Parallel Development.  This is also what I was trying to avoid.  I did not want to transfer changes from Dev (or NA prod) into 3-4 other systems.  There are product enhancement requests which could help with this - but for now it is quite tedious and requires almost OCD level of detail when tracking changes.  When we worked with a third-party they took a backup of our tool, did the changes, and then we migrated data from the "old version" into the "new version" using a script provided.  Instead I would recommend the following process:
                    • Develop on a DEV version, it is your golden instance, keep a backup in case of ever needing to restart from the point it was working 100%
                    • Test the daylights out of it.  Do your best to load-test or multi-user test using the DEV environment modeling activities closely to typical operation.
                    • Make any corrections resulting from testing issues, and retest -- still on DEV.
                    • Clone the new "golden release" from DEV
                    • Take Production instance OFFLINE so users cannot continue making changes to the data.
                    • Migrate data from each regional instance into the "golden clone" using a scripted process (these fundamentally do a data import from old table schema into new table schema, reset serial fields, and make any adjustments required by the new schema).  Goya also has a product called RefreshFM ( nickorr ) which can help with this if you do everything yourself.
                    • Update the "golden clone" ESS references to point to the correct backend SQL instances and other external data  (remember these clones will have DEV pointers unless you change them).
                    • Make any necessary SQL schema changes, using a SQL script that you built and tested.
                    • Archive the (now old) Production instance.
                    • Rename the "Golden Clone" using the original name of the Production instance and bring it online in FMS. Test for proper operation.
                  4. Manual Relinking.  My brief experience with this was that the process would sometimes reference the wrong table or field.  This creates issues that are a nightmare to troubleshoot causing all kinds of weird behaviors.  For me if I have to go and fix a bunch of scripts and calculations after doing it, it was not the solution.
                  • 6. Re: ESS change all ODBC table references on existing file to new database name with identical data structure


                    Many thanks for your thorough reply.  I will try the clone routine as you suggest.  I take your point to heart with the testing on dev. first.   This has put such a huge delay in my project and made me want to perfect everything in dev before I dare consider trying to move into production again.


                    Just to clarify, will I need to repoint EVERY table occurrence or just the base tables off of which all my relationships are built?

                    • 7. Re: ESS change all ODBC table references on existing file to new database name with identical data structure



                        In the approach SK guided us to we only have to:

                      • create a clone of Filemaker database, for me this means the "Release Version from Dev Server"
                      • create a clone of SQL backend - only if setting up a completely new instance. I did a regular sql backup, restored to new server and erased all the tables, then reset ID fields.
                      • create a new DSN for any new region pointing at the correct server name if one doesn't exist.
                      • update the cloned database ESS, using "Manage > External Data Sources" to point to the new DSN.
                      • we would need to use a migration script if there was already data for the region in a prior release. (FM-to-FM data)
                      • unless the SQL schema has been changed, the Production SQL data remains in place, there is no activity required on the data.  If the SQL schema changes in some significant way, either build a SQL specific migration script, or use recommended tools from DB vendor.


                        As far as I have seen there was no further relinking/resynch etc or script debugging required, which was the objective to keep maintenance to a minimum for my global roll-out.


                         Using this solution, everything about the backend database schema would have to be IDENTICAL  (the database name, schema name and object names are the same for every region).  The only way to distinguish one backend DB from another is by the server (or server\instance_name), but it is enough to trick Filemaker ESS into working as I needed.


                        Clearly from this exercise I am using ESS differently than Filemaker expected.  But the process of having a development instance which is separate from a production backend to me was very natural thinking and I toiled through several options like yourself all with similar problems and issues.


                        Now the issues I have are convincing the "customer", that it is better to do all significant changes as a release, mostly because they have years of experience with us doing them "live" when possible.   We separate "trivial" items (field placement, field formatting etc) which we do on every instance at the same time, from everything else which is delivered with a release.

                      • 8. Re: ESS change all ODBC table references on existing file to new database name with identical data structure

                        Thanks for the mention crw030!


                        By separating each instance of the solution onto its own set of servers, we were able to reduce everything down to a simple change in the DSN configuration for each instance. That allowed ESS to work reliably with identical .fmp12 files for each region.


                        If a multi-tenant solution is needed, you should be able to get by with changing the FileMaker External Data Source configuration to point to the correct DSN (where the backend database has identical tables and fields), but you'd have to do that in each instance every time you deploy. I don't think it's practical to relink at the table or TO level unless you're forking the solution and abandoning a single-code-base approach.


                        In Robert's case, we were using SQL Server, but I would expect the same to be true for MySQL data sources.

                        • 9. Re: ESS change all ODBC table references on existing file to new database name with identical data structure

                          I have run into the same issue


                          I have the SQLdatabase on LUNIX vm which I control.


                          My plan is to clone that with a new IP address, and on the FM sever create a new DSN to point to that


                          Then point the External data source to the new DSN address. I will also change the passwords so it CANNOT go to the old database


                          That should work right?

                          • 10. Re: ESS change all ODBC table references on existing file to new database name with identical data structure



                              I'm terribly sorry, but for some reason I never got this message sent in my forum weekly digest, or I missed it.


                              The way you are describing sounds right to me, but I personally didn't change passwords as part of my process.  I wouldn't think that would cause a problem, but I will say I found Filemaker ESS caches certain things about the connection that were unexpected for me (so test, test, test).



                            • 11. Re: ESS change all ODBC table references on existing file to new database name with identical data structure




                              One thing I found is that it changes the database name so if you switch to another source but the name if the database changes, all the to’s have to be relinked, which is time consuming for a big file.

                              • 12. Re: ESS change all ODBC table references on existing file to new database name with identical data structure



                                  I think for this approach to not become a nightmare, you would have to commit to having the database name fixed and only changing the Server/IP, at least based on what I saw and working with Greg Lane over at Skeleton Key.


                                  Relinking all the TOs, beyond being an enormous waste of time (every time you release), carries substantial risk of impact to scripts and all manner of other things in your Filemaker file (in my opinion).  This was exactly the issues I was trying to avoid, and leaving the database name exactly the same (and the structure including Table names etc) seems to address this issue.


                                  I currently have copies of my Filemaker Frontend and SQL Backend running for every region of my application  (North America, Latin America, Europe, Asia-Pacific) - but it required 4 separate Filemaker files (each built from the Dev clone) pointing through 4 different DSN names to 4 different SQL Servers  (SQL instances work also  i.e   SERVER\LA  and SERVER\EA both hosting clones of database DB from the same machine works fine).


                                  Each release, I take my development clone and region-by-region transfer the FM data into the clone, check the appropriate (region specific) permissions groups in the new file, correct the DSN and rehost the new file to complete the release. So far, knock on wood, I have not had to fix a script or Table occurrence link.



                                • 13. Re: ESS change all ODBC table references on existing file to new database name with identical data structure

                                  Yes! I did many SQL migrations (to new servers, new versions, etc.) Domain name is the 'safest' to use if you can, because the Domain Name Servers can be changed to any new IP much more easily than any reference in FileMaker (whether to SQL, on the web, or even within FM!)


                                  If database names and tables and fields are the same, you should only need to verify by checking the external tables and the SYNC button in define fields in FileMaker.