Were contemplating using sqlServer for all of our cloud deployments - just because we have to use it for most of our on premises clients, and we are experiencing challenges using postgres for in-house stuff during syncing and keeping the postgres system up to date (had to write some date functions to match the sqlServer functions) and we constantly loose identities during data syncs.
Seems using one DbServer model overall is the simplest route for the future, so we are investigating the MS license models before jumping with both feet again. We’ll need around 20 Servoy Users to connect immediately + our own Navicat connections etc. The users will grow - beyond 200 during this year and more later for the cloud app, so a processor license looks likely in the future. But for now …
Q. When Servoy connects to a sqlDb is there one connection per user or one per solution?
Q. Connection pooling has been mentioned before, does this mean that several connections to the Db may be made for a single user in a single session?
Appreciate feedback on this - and any experience others have of licensing sqlServer (CAL’s etc).
Kahuna:
Q. When Servoy connects to a sqlDb is there one connection per user or one per solution?
Q. Connection pooling has been mentioned before, does this mean that several connections to the Db may be made for a single user in a single session?
Servoy Server has a connection pool to the database server. All the connected clients can use these connections in the connection pool so this means when 2 queries are fired in sequence it might be using 2 different connections in the pool. Only when you use a database transaction then 1 connection is used for this client session up until you commit/rollback.
Thanks Robert - you just earned that award over again LOL
So, for the hard of thinking … such I am today:
A single Client in Servoy may consume more than one unique Db connection and as such (in the SQL Server example) more than one CAL.
Is that correct?
I had hoped that (similar to the way Apache when hosting web sessions seems to host a single Db server connection) the Servoy Server would make only one unique connection to the DB Server, and the pooling was handled in Servoy. Black Art…
Correct.
When you load a form for instance it might use 2-3 database connections at the same time to load all the info (forms, elements, data) as fast as possible. At least if I am not mistaken.
ROCLASI:
Correct.
When you load a form for instance it might use 2-3 database connections at the same time to load all the info (forms, elements, data) as fast as possible. At least if I am not mistaken.
That’s a pain is’nt it!
Thank’s Robert - at least points wehere we probably should’nt be going
On every request for data from a client to the server, the server takes a connection from the pool to process the request and then puts the request back into the pool.
If you set the connection pool to max. 20 connections, then if there are more requests than available connections in the pool at a certain point in time, the requests will be on hold on the server until a other request is served and the connection is released back to the pool.
The time a connections is “leased” from the pool is pretty much the time it takes for the database to process the requests aka return the result of the query/queries.
The big catch is: when you use transactions (real database transactions) and/or locking, a dedicated connection is reserved for the client for the duration of the lock/transaction. This is just how database connections work, not something Servoy has come up with.
Thus, the idea is to keep locks/transactions as short as possible.
Note that a pool of connections is per database server configured on the Servoy Application Server.
Thanks Paul - was’nt suggesting this was a deficiency in Servoy at all BTW, just in this context it pushes us back towards Postgres as the SQL Server route will get expensive fast with this number of connections. We had (naively) expected the same number of CALs to equal the number of Servoy licenses at most. Alas - back to the drawing board and looking for simpler ways to keep data in sync across multiple vendors.
Thanks for the feedback.
Thanks Paul - was’nt suggesting this was a deficiency in Servoy at all BTW
I wasn’t thinking you did, just wanted to make sure it was clear to all
In real life, I think it’s a fair assumption that with Servoy you need a lot less concurrent database connections than you have concurrent clients. While it can be that the requests from a single client are distributed over multiple database connections taken from the pool, those leased on those connections are very short-lived. So even when there are no connections in the pool left at a certain moment in time, a fraction of a second later there are free connections again.
As soon as transactions/locks com into play, all requests from a certain client are processed over the same connection (especially in the case of transactions it would go horribly wrong otherwise).
Does this mean you have to start using transactions to keep the number of used database connections down? NO
Does this mean you need more pooled connections than the max. number of concurrent users? Most likely not: users cannot manually alter data faster than the database can process the requests (in general).
The SQL Server licensing model is complicated but if you are using the CAL model it does not depend on the number of connections Servoy makes to the SQL Server but the number of clients that use your system. Microsoft call it a multiplexed environment. So if you have 20 users accessing the system then you need 20 CALs even if these clients never directly connect to the SQL Server.
For smaller system you have the option of SQL Express which is a free licence. There are limits on database sizes but it may do the job until your user count grows and it makes sense to get a processor licence.
irennie:
The SQL Server licensing model is complicated but if you are using the CAL model it does not depend on the number of connections Servoy makes to the SQL Server but the number of clients that use your system. Microsoft call it a multiplexed environment. So if you have 20 users accessing the system then you need 20 CALs even if these clients never directly connect to the SQL Server.
For smaller system you have the option of SQL Express which is a free licence. There are limits on database sizes but it may do the job until your user count grows and it makes sense to get a processor licence.
Thansk Iain - we hadn’t realised that the Db limits were raised in R2. So we’ll probably take that route short term anyway!
Thanks for the heads-up, saved us some immediate hassle.