Relational Databases Forum

DLAM
User at MH

I work for a healthcare company with less than 1,000 employees.

I am new to FileMaker Pro but my company uses FileMaker Pro (FM Pro) as the frontend development tool. More and more applications will be built using FM Pro.  

In the long run : 

1. Is FM Pro Embedded DB or MS SQL Server a better choice in the long run for the backend database?

2. What are the pros and cons of using these two databases if we must use FileMaker Pro as the front end tool?

3. If MS SQL Server has been chosen, will there be any compromise with FileMaker Pro on areas such as functionalities, features, programming methods, etc?

Thanks! I appreciate your help. 

Jeremy CoxIn general, I think that because FM is a closed system that uses its internal DB to handle not just the data used by the app but the data upon which the app itself is built. This and because the company's preference to use basic JDBC/ODBC or pre-built plug-in access to external data sources, the answer will depend on whether or not you're committed to the FM platform itself. If so, over the long run, you're better off sticking with that as a total package, backing up to an external, more traditional db. This way, you won't run into any performance, security, and management issues that would be inherent in using an external DB as the main source of app data. Honestly, given the closed nature of FM, if it's possible to migrate away, I'd suggest looking into an alternative like Caspio, which is actually built on top of MS SQL server running on AWS. Here are a few more thoughts in response to your questions as well: 1. Is FM Pro Embedded DB or MS SQL Server a better choice in the long run for the backend database? Overall, MS SQL Server should be your preference in terms of scalability, security, manageability, and functionality. But as mentioned above, it's only supported at arms-length by FM. 2. What are the pros and cons of using these two databases if we must use FileMaker Pro as the front end tool? I think if you focus on using the FM DB to handle the build/maintenance of the low-code app itself and simply query an external DB like MS SQL Server for customer-generated (not app artifact) data, then you'll have the best of both worlds. But that will come at a cost in performance, management, security, etc. as mentioned above. Given that you're in healthcare, I'd strongly suggest this approach, as I do not see the same levels of data privacy/security within FM compared to what comes native and as an add-on within MS SQL Server. 3. If MS SQL Server has been chosen, will there be any compromise with FileMaker Pro on areas such as functionalities, features, programming methods, etc? Yes, you won't be able to fully use MS SQL except as a source of data to populate the FM app front end. If you try to use MS SQL to manage app artifacts, I think the whole app will fall down, sadly or at a minimum only function as a basic app.
Martin KlierI have no experience with FileMaker, so I can't answer any FileMaker specific questions. But I am into the database business for a long time, so maybe I can help out at this point. If you are asking for long term solutions and looking at the technology side, one of the big vendor databases (Oracle, Microsoft, IBM...) will be way better. This comes from rock-solid backup and recovery strategies, scalability, advanced-cache recovery strategies, and, not at least, widely available knowledge. Knowledge is coming from a huge hit list on the internet and from people like me and my colleagues all over the world. With a small vendor's specific database, this may be harder, if not impossible. A quick read showed me that even FileMaker themselves suggest storing container data outside the embedded system, due to the risk of data loss. For larger environments, with concurrency, Oracle for sure is the best possible solution. But it may be a bit harder to get it to work, but as soon as it works, it's rock solid. MS SQL makes your initial life easier, but in the long term, it allows you to make many mistakes and not recognizing them until it is too late. Going for one of the big vendor databases has a downside: It introduces you to the big and ugly world of licensing. Using world-class features and having to pay for them is ok, but the issue is the terms of licensing. They vary widely, depending on the vendor, product, edition, and, the worst part, your internal infrastructure. Especially CPU based licensing in combination with current virtual environments can be challenging. User-based models, or working with Client Access Licenses (CAL) also has its pitfalls. As I said, it's not nice. But it is important to stay clean at this front. For a tease, as long as you stay small, vendors like Microsoft and Oracle (not sure with IBM) also offer free editions of their products, with limited sizes/resources, but with a large and valuable technology stack. Going for free technology? I'd always recommend PostgreSQL then. PGSQL is not as scalable as Oracle is, but if you don't need much click-and-play stuff, it gives you most of what MS SQL would also bring. But when reading through FileMaker's web page, I can see you need an "Actual Technologies Adapter", no clue if this has any downsides. One thing for sure: A database is no playground if you have important data to store. Make yourself familiar with the technology, or ask for educational support early in the process, to avoid long-term mistakes. People like myself are offering all kinds of help, from a free response in mailing lists up to on-site hands-on training.
ChrichtianNealI have used FM Pro for a paperless construction site and it work seamlessly.