Best Practices using d/b server and web server

k heitz
k heitz used Ask the Experts™
on
Hi Experts ~
I'm looking for guidance on best practices when working with a database server, and a web server.
We have both and all our sites are hosted on the web server.
The work is all done on the d/b server and for many sites, .csv files are created from a SQL query.

My question:
Outside of reducing d/b server traffic - Is there any performance benefit on the site to storing those resulting .csv files on the web server vs. the d/b server?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Best performance will always be running DB + HTTP servers on one machine.

The lag of network speed, will always run far slower than using a local domain socket for HTTP <-> DB conversations.

For .csv files... To little detail... If you're running some query that sequentially reads 1,000,000s of records... repeatedly... every few minutes... you'll require some serious horsepower.

Tip: When creating your .csv files run your process using nice -19 + just ignore this matter completely. Running processes like this means all other processing takes precedence over your .csv file generation, so process will only run when resources are free.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Is there any performance benefit on the site to storing those resulting .csv files on the web server vs. the d/b server?

Kindly let us know the size and record count of that csv file so that we can get a fair idea on what would be better..
Ideally, it would be better to have all data stored in Database and then request only the required data to each and every site..
If you have one or more csv file per site, then it would be difficult to manage all these files and need to ensure whether the local site was able to retrieve data properly or not from those csv files..
So, would recommend storing data in DB Server and fetch only the required data required no. of times..
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>Best performance will always be running DB + HTTP servers on one machine.

From a security standpoint you should never do this.  Unless the database is driving the web page markup.  You want your database as far away from the Internet as possible.

You typically have your Web Servers sitting in a DMZ.  They only talk to app servers on the other side of a firewall and those app servers are the only ones that talk to the database server, ideally behind another firewall.  I also prefer the app servers only access the database via stored procedure calls.  It is just one more security layer.

If you need data converted to CSV files to be passed to the web server for download, where you generate them has a lot to do with the complexity and sizes.  I would be inclined to have the database serer generate them and pass them off to the app servers.  The database server has the data and should have the horsepower for quick generation.
k heitzsoftware developer

Author

Commented:
Thank you all for the insight!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial