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

[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.