2 Replies Latest reply on Mar 20, 2014 6:40 AM by beverly

    FM move backend to MS SQL?

    hwangeruk

      Is it technically feasible to not use the Filemaker database, or Filemaker server and use SQL as a backend replacement, simply using FM as the interface?

      (And even if possible, is it practical to do so?)

        • 1. Re: FM move backend to MS SQL?
          Vincent_L

          Possible, but slow as hell. You'd better off importing the MySQL tables

          • 2. Re: FM move backend to MS SQL?
            beverly

            What Mr. L said (for MS SQL or MySQL or Oracle) - as slow sometimes.

             

            Large datasets, especially are best served with Views (basically canned/saved queries) to narrow the sets. Then go to FIND MODE, navigate to an FMP layout that is a form view, Perform the find, then and only then switch to Browse (which happens automatically when performing the find). That way you are not downloading more than you need. Avoid table or list views of the SQL sourced data, until you have the set really narrowed (including the # of fields/columns).

             

            Also, depending on the setup of the SQL database (tables), it may have dependencies which are just best handled by whatever PHP (or other app) used to create/edit the data.

             

                  

            • is the data indexed on any columns in SQL?

                  

            • is the schema optimized to prevent overly-sized columns (VARCHAR100 when VARCHAR2 would do)?

                  

            • do you have a fast connection between the SQL and FM?

                  

            • are you needing to create additional fields in FM to adjunct the SQL data?

                  

            • do the SQL tables have proper Primary Keys?

                  

            • do you have access to the SQL as well as the FM?

                  

            • do you have all the necessary permissions to the SQL data (it can be locked down to view only, perhaps)?

                  

            While it can be done (as I have done this), it must be done carefully. And perhaps it depends on why you need to do this and what functionality you hope to gain from FM that you cannot from other means.

             

            Using IMPORT to get a copy of the data (with SELECT), may be advisable, to perform reports and edits. Then use Execute SQL (script step, not the function) to make the INSERTS, UPDATES & DELETES, rather than straight ESS (which is mostly a 'portal view' into the actual data).

             

            Variations of all of these may be necessary (the admin directly from SQL server or web-based interface, the use of ESS and the use of the Import & Execute SQL), if you pursue this path.

             

            Beverly

            SQL DBA,  FM DBA & webmaven