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.