SQL Server Architecture

SQL Server Architecture

Post by Dean » Thu, 13 Jul 2006 05:55:12

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.

SQL Server Architecture

Post by TGluY2hpIF » Thu, 13 Jul 2006 10:45:01

If the availability of the WRITE servers is important enough that the cost of
having and maintaining another server is no issue, I'd create a local cluster
in addition to the database mirroring setup you described. Failover
clustering is easy to maintain and mostly transparent to the client. This
way, you have the failover clustering for system failure and database
mirroring for database failure, and hopefully you would need to failover the
cluster more often than you would database mirroring.



SQL Server Architecture

Post by Dean » Thu, 13 Jul 2006 17:58:46

Interesting idea. I'm trying to work out the scenario in which the
cluster that you suggest will be used. My thinking goes as follows

1. Primary WRITE database fails --------- Mirror WRITE database kicks
2. Primary WRITE hardware / OS fails -------- Mirror WRITE database
kicks in ------ Cluster on the primary server fails over ------ Primary
WRITE database comes back on the cluster secondary hardware -------
Mirror WRITE database fails back to primary WRITE database (which is
now running on the secondary in the cluster).

.... my big assumption is that SQL mirroring will recognise a fault on
the primary server before Windows Clustering does. This means that the
SQL mirror will become active before the Windows cluster has failed
over. Once the Windows cluster has failed over, the mirror can then
pass the baton back to the primary database, which is now running on
the secondary hardware.

Does that sound right?

I'm also interested to hear opinions on the architecture in place for
the READ databases. Will it work?

Thanks for your input!