You’re building an application in Xojo with an SQL Server back-end.
There are three major approaches that jump to mind, depending on the situation:
WEB BROWSER <–> WEB APP ON SERVER <–> SQL SERVER
or
NATIVE CLIENT APP <–> SQL SERVER
or
NATIVE CLIENT APP <–> NATIVE SERVER APP <–> SQL SERVER
Am I overlooking any approaches?
For responsiveness for users, reliability, security, and future scalability, which way do you go in 2017?
My initial thoughts:
#1: Clearly best if clients are external and/or not managed by you, especially if they are running multiple versions of multiple OS’s…
…but if all clients are managed and internal, the other approaches MAY be better, and I’m somewhat torn between #2 and #3:
#2 : SQL server is handling ~100 simultaneous connections, each doing a few requests/minute #3: SQL server is handling 1 connection, doing 100’s of requests/minute (and server app is effectively a proxy, translating between non-SQL requests/responses for clients and SQL requests/responses on the SQL server, and possibly caching some frequently used data)
#3 is probably safest and most secure, but #2 is potentially faster and requires lower server specs… and how would #1 compare in Xojo 2017 for speed and performance and reliability?
We have an Accident and Emergency application supporting 4 hospitals in England and this is running on an internal network over the 4 sites. We are using the browser->web app->sql server i.e. 1) in your list.
This runs very well with about 50-100 users.
The advantages for us are that there is no client software to maintain so updates, which are frequent, involve updating one server application (and SQL stored procedures). Also, we load balance our web application and this is very effective although the reasons for load balancing are more to do with sessions occasionally hanging which requires a restart of the app. With several instances running, you can push users to another instance and then restart the stuck instance without any down time. Our bottleneck is more on the SQL server with frequent updates to the same set of tables and this is then down to the database design.
The speed of the web apps on an internal network is quick and we don’t feel there’s any advantage to having a native application. But that depends on what your app needs to do. Ours is a lot of form filling with some graphic manipulation done at the browser.
Also, the web app->sql server approach allows you to have your web app public but your sql server to be behind a fire wall. Number 3) would also allow for this.