SQL Server Architecture

Giganews Newsgroups
Subject: SQL Server Architecture
Posted by:  Deano (jhde…@gmail.com)
Date: 11 Jul 2006

I need to design an architecture to support what will become a heavily
hit web site.  We have a UNIX guy looking after the web servers and it
is my job to sort out the SQL side.

The ratio of reads to writes in the database will probably be 75:25.
Availability and scalability is very important.

My current thinking is to split out the servers that will be read from
and written to.

Primary WRITE server = Win2003 Enterprise with SQL2005 Enterprise
Secondary WRITE Server = Win2003 Enterprise with SQL2005 Enterprise

These WRITE servers will use the mirroring functionallity in SQL2005.
They will be in the same geographical location and I'm thinking that
they should use the high availability model.  The web site will then be
coded to deal with the failover.

READ servers will be WIn2003 Standard and SQL2005 Standard.  They will
be distributed around the world.  Each READ server will subscribe to a
transactional publication from the WRITE server and receive any changes
in as close to real time as possible.

Read requests will be load balanced across the SQL servers and since
they all accept a real time feed from the WRITE server, it should not
matter which server deals with the request.  I appreciate there may be
some latency between different READ servers, but I don't think this
will be a problem.

So, what do you think?  I would really appreciate any suggestions
people may have.

Replies