Westwind Web Connection server on LAN: can SPT (MS SQL) work ? 4

[Steve Yu]

Hello colleagues,
We have a working Web Connection server on a LAN (internal web) that employs .dbf and .idx only currently.
Is it possible to add SPT coding to access the MS SQL tables that are on the same LAN concurrently ?
This is our effort to expand exiting desktop applications to include access from the web.
Help appreciated.

Steve Yu

 

[Chris Miller]

Yes, in very short. There's no reason to exclude an SQL Server running on the same server as a web server and so there's even less problem, if you only want them to run in the same LAN. Besides, even if an SQL Server would be outside of the LAN somewhere else in the internet (cloud) you could make a connection.

For the performance of the whole system you may need to redimension (i.e. extend) the server to run both a web and SQL Server on it, but you could also split the roles.

And both the web server and clients could even connect to the SQL Server, you're not limited to making the web server a middle tier between clients with the frontend UI and a database server, even though that's the usual 3 tier architecture of a web solution, there's nothing ruling out the client as a "thick" client desktop application also does correctly work on an SQL Server backend on top of the web backend you also have, because you don't have to restrict your desktop solution to the frontend browser tier/role, only.

 

[Steve Yu]

Yes, in very short. There's no reason to exclude an SQL Server running on the same server as a web server and so there's even less problem, if you only want them to run in the same LAN. Besides, even if an SQL Server would be outside of the LAN somewhere else in the internet (cloud) you could make a connection.

For the performance of the whole system you may need to redimension (i.e. extend) the server to run both a web and SQL Server on it, but you could also split the roles.

And both the web server and clients could even connect to the SQL Server, you're not limited to making the web server a middle tier between clients with the frontend UI and a database server, even though that's the usual 3 tier architecture of a web solution, there's nothing ruling out the client as a "thick" client desktop application also does correctly work on an SQL Server backend on top of the web backend you also have, because you don't have to restrict your desktop solution to the frontend browser tier/role, only.

Thanks for the confirmation, Chris.
Actually, and it may not make any difference, our web server and the SQL server where MS SQL is installed are on separate hardware physically but on the same LAN and currently they operate totally independently of each other.
You did not mention West Wind in your response; but I assume SPT would work alongside current .dbf and .idx structure.
Just wonder how to code the SPT in West Wind; it'd be much different from desktop applications in that, I assume, I'll have to initiate the MS SQL connection each time a page is called on the web vs just one time when the desktop application is started.

Steve Yu

 

[Chris Miller]

I assume, I'll have to initiate the MS SQL connection each time a page is called on the web vs just one time when the desktop application is started.

Yes, and that's not unusal for a web application. Even if you don't work in PHP and look at beginner tutorials for PHP web scripts with a MySQL backend making the connection at the PHP script start is normal, and closinit when the script end is even automatic. So for performance of that, usually the SQL Server will make use of a feature of connection pooling, so that the next web request making a ddb connection will actuall get it's connection handle from a pool of handles the server kept open. I remember you ha to explicitly do somethign to get there in classic ASP using VB, but that's long long ago.

Well, the point it, this is not at all unusual and also works flawless.

 

[Chris Miller]

The task at hand will be adding permissions to the database server for the web server account, usually, as the client account doesn't necessarily loops through to the sql backend server. In the analogy of PHP and MySQL the PHP server side processing very often connects as root (equal to MSSQL sa) user with MySQL.

 

[Steve Yu]

The task at hand will be adding permissions to the database server for the web server account, usually, as the client account doesn't necessarily loops through to the sql backend server. In the analogy of PHP and MySQL the PHP server side processing very often connects as root (equal to MSSQL sa) user with MySQL.

We use root account only for MS SQL login and control access within the applications based on Active Directory login names.
It'd be extremely cumbersome and not practical to configure permissions for near 100 users.

 

[Chris Miller]

We use root account only for MS SQL login and control access within the applications based on Active Directory login names.
It'd be extremely cumbersome and not practical to configure permissions for near 100 users.

Surely you'd not do this on a per user basis, but define usergroups as roles. That's managable.

 

[Steve Yu]

Surely you'd not do this on a per user basis, but define usergroups as roles. That's managable.

We followed the advise of our consultant to simplify the access; we certainly don't want to make it more complicated than it needs to - keep in mind we converted to MS SQL from simple .dbf VFP where no access check whatsoever is done. All security has been built into the applications.
BTW, our SQL server is hosted inside multiple firewalls; the idea of access across public space is palpable from technology point of view but not ideal if you consider the current state of Internet environment.

 

[Chris Miller]

I'm just saying what's possible and practiced in other companies. If you stick to using sa access, I'd recommend not do do so from desktop clients directly, in that case, but only through the web server as the middle tier for that.

 

[GTGeek88]

WWWC has built-in code to query back-end databases.

 

[Steve Yu]

WWWC has built-in code to query back-end databases.

Please kindly provide a link.

 

[Steve Yu]

WWWC has built-in code to query back-end databases.

Never mind, I found it in the client toolbox, Class wwSQL.
Did not know there is one.
Thanks for the reminder.

 

[Chris Miller]

Of course, even if there wouldn't be, you can do SQLCONNECT or SQLSTRINGCONNECT in your VFP code, why should SPT functions not work in Web Connection?

Well, I actually came back here to add to arguments against your doubts: No matter if a database server is on the same server as a webserver or these roles have dedicated servers, the actual hardware servers (or VMs, too) are typically direct neighbors, so connection times are almost zero. That's far better than the connection time desktop clients have. Most of the time to connect to SQL Server from a desktop client goes in forwarding the request to the servers database engine, MSSQL has its browser service to listen for incoming connection requests to SQL Server instances and provides informations about the instances for faster connection handling. When going for the three tier architecture of a web server script making a connection you may not even need this service as you'd connect to a direct neihgbor, if not on the same machine.

There's also one advantage you typically make use of as a developer when webserver and database server are both running on your developer machine: The db connection can use shared memory, the fastest lane to the database. The only advantage to separation of the web and db server is not needing one single highly dimensioned server with enough capacities to process all incoming requests, even if you could count database requests to the web requests, when there wouldn't be concurrent uses of the database server not originating in web requests. As a developer you're the only user of your machine and there it's not a concern, but if you can manage to have that architecture the web application can profit from that, it only becomes a disadvatage again, if the load is very heavy, like millions/billions of users, thousands or even hundreds of thousands concurrently, as it's for Facebook, Google, etc. You're likely not in that boat, though.

For on premise solutions for a few hundred users, perhaps thousand, of which you'd have a few tens concurrently acting, the only disadvantage of having many roles on the same server is safety against services getting down or slow. From the perspective of an application driven by web connection it's not helpful if only web or database server are available, the application becomes unusable when only one of them is down. too. So that architecture of separate roles only has advantages if there's anything that can continue to work woth only one of them.

 

[tomk3]

Hello,
just one thing : AFAIK you cannot run MS-SQLserver on a domain controller

 

[Chris Miller]

Makes me wonder, but I never tried, anyway. If someone would use a Windows Server Essential Edition that's limited to 1 CPU socket with at max 10 cores. That would sound like much when compared to what I started with in the 80s/90s, but is not something I'd use for SQL Server anyway. So when you'd not use this even in a small environment for small buisinesses, it's surely never an option in any company. As a stand alone freelancer you could run everything on one server, though. You'd even be able to do without a domain controller and do everything on a laptop. I don't think there's a technical limitation, it's just not the roles you combine in one server, domain controller and database server.

 

[Steve Yu]

Of course, even if there wouldn't be, you can do SQLCONNECT or SQLSTRINGCONNECT in your VFP code, why should SPT functions not work in Web Connection?

Well, I actually came back here to add to arguments against your doubts: No matter if a database server is on the same server as a webserver or these roles have dedicated servers, the actual hardware servers (or VMs, too) are typically direct neighbors, so connection times are almost zero. That's far better than the connection time desktop clients have. Most of the time to connect to SQL Server from a desktop client goes in forwarding the request to the servers database engine, MSSQL has its browser service to listen for incoming connection requests to SQL Server instances and provides informations about the instances for faster connection handling. When going for the three tier architecture of a web server script making a connection you may not even need this service as you'd connect to a direct neihgbor, if not on the same machine.

There's also one advantage you typically make use of as a developer when webserver and database server are both running on your developer machine: The db connection can use shared memory, the fastest lane to the database. The only advantage to separation of the web and db server is not needing one single highly dimensioned server with enough capacities to process all incoming requests, even if you could count database requests to the web requests, when there wouldn't be concurrent uses of the database server not originating in web requests. As a developer you're the only user of your machine and there it's not a concern, but if you can manage to have that architecture the web application can profit from that, it only becomes a disadvatage again, if the load is very heavy, like millions/billions of users, thousands or even hundreds of thousands concurrently, as it's for Facebook, Google, etc. You're likely not in that boat, though.

For on premise solutions for a few hundred users, perhaps thousand, of which you'd have a few tens concurrently acting, the only disadvantage of having many roles on the same server is safety against services getting down or slow. From the perspective of an application driven by web connection it's not helpful if only web or database server are available, the application becomes unusable when only one of them is down. too. So that architecture of separate roles only has advantages if there's anything that can continue to work woth only one of them.

Thanks for the elaboration.
Our doubt: 'devil is in the details'. Let me explain: with our currently in-production desktop applications, we have a fairly elaborate routine of setting up MsSQL connectivity that is run one time at the start of an application, including a number of shared SQL functions (for example DC_autoupdate(&arcust,"c_alias") that will sync cursor c_alias with SQL table 'arcust01' field by field ); my question is where do I place this startup routine in my WWWC, or any web server ?
On separation of web and database servers, there is no issue here. Our MSSQL is installed on a Windows server with max processing power but the web server serves a very minor role and is installed on a regular PC on the same LAN as other servers for product browsing only. We are just trying to replicate one desktop function (maybe more later) that can be performed from a web page on a cell phone. And that is where SPT comes into play.

 

[Chris Miller]

the web server serves a very minor role and is installed on a regular PC

Well, that's not a good decision. Web is stateless, so you don't keep a connection open. If the web server is not on the same server as MSSQL, it should still run on a server, or needing to reconnect with every request becomes a pita, all aspects aside that make it feasible, still.

And you shouldn't take it as challenge to make your web server stateful, the web is stateless by intentional design, not as a design flaw.

Admitted: Someone with web application development experience might say I'm telling bullshit, and isn't even wrong, as the stateless nature of the http protocol doesn't mean that no states are kept. A clear example you experience as a web user is that you only log in once, and then a session is established until you either log out or the session times out. There is a state that's kept, but then it's also not kept up as the state of a desktop application that is the process it is and runs until the user exits or shuts down the computer.

There are mainly two modes of operations regarding sessions: Eithter the login sets a session coookie in the users browser and that is repeatedly sent over to the server with each request so the server can look up what user the request comes from or there's a bearer token, that's likewise sent to the server with every subsequent request after the login. The two options are quite well descirbed here: https://www.criipto.com/blog/session-token-based-authentication

1. I don't think you ever thought about that and now you're at programming the user frontend to that you're not getting all that automatically, as the browser does, but would need to program it into your frontend.

2. These mechanisms keep a state, the user reidentification. Look into the details of the linked article and you see the more recent modern bearer token approach is still described as having a stateless nature. Because every request has to contain the info, session id or bearer token, to know who made that request. There's the oAuth 1.0a scheme where every subsequent request is signed and there's not just a constant value to reassure which user the request is from, the signature uses a key pair mechanism and allows the verification that's a prove of authantic request and not just proving to know a token or session id that could be stolen.
3. Still, most any usual web scripts open and close a database connection for every request, you don't keep a connection open.

Now you can ask, why not keep a connection open that's not even specific to a user but just a general connection to the database server. Well, the way a web request is handled is the PHP script or your web connection script ending, there's no variable kept, no state kept, so what you would need to introduce is a persistent object or process that does so. I doubt you get that much of it, I don't have used web connection myself to know or tell you whether something like that may be foreseen, but it's not the normal nature of a web application to have anything that's kept running aside from the web server itself to receive and forward requests to the single scripts executed that respond and finally close down again.

There's the relativela new concept of web workers, that's a client side (JS) feature, not a server side feature, though. So if you want to keep something up and running that keeps a SQL Server connection the only feasible mechanism you have, I think is running a COM Server EXE on the web server PC in paralle to IIS that could be started like an OLE automatable appication could be started and that you "connect" to by a web connection script via GetObject() instead of CreateObject(), that would only always instanciate a new COM object instance. The stability of that depends on how stable that COM Server would be and the security of it, too, how you enable any process to attach to it that way or require some means of authentication, too. And overall, you're going through more hoops, just to have an SQL connection handle kept open. Which is the reason for 3, again, server side code just is there to answer one request with a response and then "die".

What's commonly saved are the responses, ie. you cache a response when it is potentially the same result for a time out, if no forever, and cache it, there's a lot about cahing mechanism, but you don't keep open a database connection for convenience. What's happening on that level is what I also already described in my firs post, the connection pooling, making it faster to reconnect.

 

[Steve Yu]

Well, that's not a good decision. Web is stateless, so you don't keep a connection open. If the web server is not on the same server as MSSQL, it should still run on a server, or needing to reconnect with every request becomes a pita, all aspects aside that make it feasible, still.

And you shouldn't take it as challenge to make your web server stateful, the web is stateless by intentional design, not as a design flaw.

All we are trying to do here is to replicate one desktop function on a web platform so people with a cell phone can perform the same desktop function within the range of our wifi coverage area.
VFP question on web: what is the effect and scope of 'Set procedure to ...' ? Can I make it shared by all requests ?

 

[Chris Miller]

The web connection framework is catching all requests coming in from IIS, will determine a handler, a class you write, create a new instance of that class and run a method of it, that's where your code comes in. You have nothing to rely on you get forwarded a request object that gives you access to the request, parameters passed in etc.

The point is the instance of your class is new, anything that you store in properties of your class instance will not exist in the next request.

I get the impression you never used web connect before, though you start saying you have a web connection server running and using DBF based data. I can only suggest you wet your feet and go into writing something starting from what you already have.

 

[Steve Yu]

The web connection framework is catching all requests coming in from IIS, will determine a handler, a class you write, create a new instance of that class and run a method of it, that's where your code comes in. You have nothing to rely on you get forwarded a request object that gives you access to the request, parameters passed in etc.

The point is the instance of your class is new, anything that you store in properties of your class instance will not exist in the next request.

I get the impression you never used web connect before, though you start saying you have a web connection server running and using DBF based data. I can only suggest you wet your feet and go into writing something starting from what you already have.

Our version of the WWWC server is very heavily modified and I do see some shared logic by all sessions; not sure how that all tie together. We've been doing it on the fly and it has worked well so far. Let me try adding the SQL logic and will report back to you shortly.
Always appreciate your help.