4 web server, active/passive sql servers

[maclav]

Hi
I am working on futur proofing a very busy site and have some ideas on how to speed things up/prevent high louds killing the server.
Web have 4 loud balanced webservers which all point to 1 active sql server with another sql server being passive waiting for the other one to die.
Instead of this I was thinking of getting 2 web servers to run their transaction on 1 server and 2 to run against the other and then get the 2 servers to run the others transaction logs.
Has anyone any knowledge of weather this is a good plan or is there some angle i have not thought of.
Thanks for any help
Ian

 

[maclav]

also does sql 2005 allow duplex transactional replication?

 

[mrdenny]

What is Duplex Transactional Replication? SQL has nothing by that name.

You can't have two active SQL Servers running the same database and have each server replay the others transaction log.

You've basically got a couple of options.

1. Setup Merge Replication between the two SQL Servers so that either one can be updated. (Good luck, it's a pain to go, and you'll probably end up with all sorts of identity issues).

2. Get a third SQL Server. Take the current SQL Server and make it the server that the application writes to. Use Transactional Replication to replicate all the tables to the other two SQL Servers and setup a load balancer in front of the two servers that are the subscribers. All requests to read data go to the VIP, while all write requests go to the publisher.

This is a very stable setup.

How much load do you have that you need to split the database between two servers? It's very rare that systems actually need to be split out in this fashion.

The best bet is to always have a single SQL Server in the middle that all web servers connect to. That's the only way to guarantee that the data will be the same for all web servers.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MDXer]

I would check other areas for bottlenecks before assuming that the issue is SQL and the only fix is to add more servers. Whats the servers memory like? What is the cache hit ratio? Do you have enough disk IO to support the load? CPU utilization?


I have seen systems where everyone pushed for a bigger faster server only to run perfmon and discover that cpu utilization was minimal.

You can buy bigger, faster or more servers but if the hardware solution isn't configured properly you are just wasting money.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[maclav]

We dont currently have a problem my job is to futur proof the infrastructure. Part of this was looking ah how the passive (fall over) server could be put to use.
Currently we are looking at using mrdenny's option 2 and having all writes going to server 1 and having the reads spread out across both servers.
Thanks for help though.
Thanks
Ian

 

[mrdenny]

You should note that my option 2 has you getting an third server and spreading the load across that server and the currently passive server.

That option doesn't do anything for high availability as if the write server were to fail you would be down until that server was fixed.

Passive servers are insurance. Big expensive insurance. They really shouldn't be utilized unless it's really needed.

If you aren't having any performance issues currently then rebuilding your environment into a system like either one I setup is going to be a lot of work, without really any payoff.

If it ain't broke don't fix it.

What sort of Active/Passive setup are you in? Are you using MSCS, Mirroring, something else? (Just curious, the lecture is over, at least for the moment )

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog