1 Reply Latest reply on Jun 11, 2009 12:53 AM by FluffyBear

    Concurrent access to SQL Server tables from a Filemaker 10 Pro Advanced application

    bhatiav2@mail.nih.gov

      Title

      Concurrent access to SQL Server tables from a Filemaker 10 Pro Advanced application

      Post

      We have an environment where we have a number of Info Path applications accessing SQL Server 2005 tables. We are in the process of designing a number of Filemaker applications that would also like to store their data in SQL Server 2005 tables. The question we have is should the two applications have their own sets of tables or can they share the same set of tables. Both these applications will be accessed by a small volume of users during the day. The underlying concern being concurrent access and locks that might impact the integrity of the data. We would love to hear from users who might have had similar challenges and how they resolved these issues.   

        • 1. Re: Concurrent access to SQL Server tables from a Filemaker 10 Pro Advanced application
          FluffyBear
            

          On the SQL side you shouldn't be having issues.  Unless you have a large number of write to the SQL server, most SQL servers do server locking very well to the point where you have have hundreds of insert per second while the database is active for large amount of data retrival.  There is always a theoretical limit to currurent insert and slowdown or possibly insertion failure, but I haven't really see any database have data integrity problems as a result of take locks. 

           

          The problem with SQL and FM have more to do with data out of date problem.  We have noticed in our application that it seem to not refresh at all after the same record on the SQL server changes for quite a while.  Someone can be looking at records linked in from SQL and still see those record minutes after I've deleted them from the SQL database.  This is the part where you can have 2 people change the same record based on what they see, even though what they see might be different as changes occur and FM is not refreshing the data.  Having 2 separate set of SQL tables or databases does not help at all in this case unfortunately.

           

          We keep control records in FM and data records where the changes a very very limit but where there are much more data records in SQL databases.  It's a hybrid system that work very well.