This blog is intended for the FMS service operator to gain understanding of the relationship between the FMS service and the SQL Server FMS database.
When working with the FMS service and the SQL Server database connection it is important to note that each task will run in an executable thread within the executing multi-threaded FMS Service. When data is required from the database, each thread will initiate a unique session with the database to read/write the specific data. It is possible, for some long running task with minimal to no activity, for the database to close the session as part of the database service manager optimization processing. The FMS service will detect this state and cause the session to re-open when the FMS Service requires database access.
In most cases a task that needs SQL Server database access will open a session, initiate the transaction(s) necessary to complete the task and close the session. On occasion, often due to unstable networking however there can be other causes, the session will fail or, due to transaction failure, FMS will close the session prematurely. FMS Service is designed to detect the loss of session connectivity, and restart the connection to the database.
The FMS service is robust enough to detect SQL Server side connection issues and recover with minimum disruption to the workflow. This alleviates the necessity of determining a specific connection in SQL Server with a task/thread in the FMS service.