I have a Delphi XE2 DataSnap server (Windows service) connected to a backend MS SQL Server 2008 (same server box) serving REST client requests.
Everything has been working great for some time until recently, I had an issue where for some reason the DataSnap service lost connection to the SQL Server.
The service failed to re-establish a connection and I had to restart the DataSnap service to continue.
This got me thinking because currently the service only uses 1 SQL connection (TADOConnection) shared for all the client requests. I did this because I didn’t want the overhead of instantiating a new SQL connection for every client request.
I’m considering whether it actually would be better to have a separate SQL connection for each request and if the overhead would be noticeable – can anybody comment/advise on this?