We run mirrored SQL Server 2008 with auto-failover. No worries about hardware failure or data corruption issues. Open DBF's over a network with high volume runs the potential for weird problems and record locking contention.
If _I_ were doing the reporting, I'd want my Requests and Sessions on SQL like you say. However, all our corporate side is still VFP Fat Clients so they're more used to working with DBF's. Not that it'd be a big deal to get the few reports we do against our RequestLogs and such ported to SQL.
It's just I don't know if, given the fact I'm already moving the data off the local server, there's any real reason to add SQL into the mix right now. However, if there is, I'd rather tackle it now while I'm making the move. Hope that makes sense.
I used to run DBF's on a single box. When we switched to the web farm we also switched to SQL Server 2008 and am MUCH happier with performance and ability to tweak. Not to mention SQL Server 2008's built-in solutions for handling failovers gracefully.
Thanks for your reply. Glad to know someone's out there already doing this. We are just under 4 million pageviews a month. Do you mind if I ask whether you are doing pages or all in EXE? I don't think I have to ask if you're doing COM or not. With that traffic you are most certainly doing COM. Are you running managed module or ISAPI DLL?
I do very little scripting. I'm actually not doing COM yet because of some framework changes I need to make first. I really need to get this done - it's high on my priority list.
All our code is compiled in the Server EXE running in COM mode with 8 apps running. Our meat and potato hits average 0.05, our cart hits average 0.14, and our search is the real hog at about 0.90. It is very rare that we have a hit about 1 second at the server.
99.9% of our requests are handled through the .exe. I make abundant use of intelligent caching of mostly-static data. I don't use Rick's caching class though.... rolled my own.
You mention searches being the hogs.... this may be the best reason to switch to SQL Server... full-text indexing built-in (though there are workarounds for full text indexing in DBFs).
However, we like to be proactive about improving performance. Again, thanks for your reply.
We currently run at about 10% capacity on average but when things are cooking that jumps to 50%+ The way our stacks are setup now, we can just add web servers to bring on additional capacity - that is until the DB becomes the bottleneck.
Personally I would not recommend using network shared DBFs across multiple high-load web servers.
Here's quick run-down of our setup:
2 separate fiber pipes to the internet
monitored DNS with auto-failover
2 completely redundant stacks of hardware which includes:
2 routers
2 load balancers
2 firewalls
6 web servers
2 DB servers (primary and mirror)
2 APC Power Distribution Units (for the single power supply equipment like routers, firewalls, switches, etc.)
4 APC 2200 UPSs - each UPS is powered by separate electric circuit
Basically we have made every effort (within reason) to eliminate all single points of failure for as near to 100% uptime as we can get.
I'm happy to share my experiences but by no means do I consider myself an authority on the "right" way to do things. In other words, my 2 cents is worth face value.