sql-serverperformanceweb-applicationshardware-infrastructure

Would it ever be wise to have a SQL server per web server?

I'm wondering if, under the circumstances that

  1. You get lots more reads than writes
  2. Your SQL server of choice is cheap/free and offers a fast mirroring/replication service
  3. Your database isn't insanely large

rather than having separate SQL servers it would be better to have an instance of SQL on each machine getting instant updates from the master. This way there would be no network latency when doing all the read queries, but there would be a per box performance hit as the SQL instance has to execute. Would this be better overall for performance? Are there any other pros/cons that might come up?

Solution

  • Your SQL Server should always be on a different box to the webserver, of that there is no question.

    How many DB servers and webservers you have, and how they mirror (or otherwise) is up to how you scale your application.

    You have SQL Server on a different machine because it needs (and deserves) a lot of RAM.