Server Farm

Question

Server Farm

MegaDBA

Hall of Fame

Points: 3518
More actions
October 23, 2003 at 7:29 am

#83339

Hi,
Can anyone explain to me the concept of a server farm as it would relate to a SQL environment? Or where I can get more information and/or resources?
I understand the overview of the server farm but would like more examples and perhaps a topology diagram of a farm which includes SQL servers.
Or is anyone working in or currently developing this concept?
Any ideas would be helpful.
Thanks,
aurora01

Aurora

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Richard L. Dawson Ten Centuries Points: 1062 More actions · Answer 1

Hi aurora01,

Just from the way Sql Server works the only way I could see this working would be to use federated servers. Each server or server cluster would handle a separate part of the data. If you used it to support a web site there would need to be code in the pages that would route the data to/from the correct sql server. An alternative to that would be to use distributed partitioned views against federated servers to provide the data IO.

I'll be interested in what others have to say about this.

Richard L. Dawson

Sql Server Support Engineer

ICQ# 867490

Richard L. Dawson
Microsoft Sql Server DBA/Data Architect

I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Piggy-backing on what Richard has already said, the concept of a server farm in the Windows environment is typically applied to either web or an application tier, not SQL Server.

I'm sure it is possible using a hardware load-balancer that can maintain persistent state to create a SQL Server server farm, but it's probably more headache than its worth just thinking about trying to keep the data in synch. SQL Server 2000 is designed for high-availability. It has limited high-scalability using federated databases and the like, but it's not designed for that.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

Keith Henry Hall of Fame Points: 3759 More actions · Answer 3

A problem with SQL servers is that they can't share load unless it is done programatically at the application level. I find unioned views across linked servers really don't provide and advantage once you account for all the additional IO and Netowrk used.

You can scale a lot in one box though, Multiple filegroups across different arrays can vastly improve IO performance, and the latest servers are pretty damn powerful. The question is really: how much scale do you need?

SQL clustering is from the bad old NT days where a solid day of uptime was an event! In the NT world you basically had a row of NT servers and you just prayed that they didn't all go down at the same time. Win 2000 has been a lot better and the cost of clustering it (both in hardware and maintenance) isn't really justified unless you need high availiability.

Keith Henry

DBA/Developer/BI Manager

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

johnsonj SSCrazy Points: 2159 More actions · Answer 4

This is about website server using Site Server and SQL and setting up a server farm. It might help depending on what you are doing. http://www.microsoft.com/siteserver/ssrk/rk_manage_whitepapers.htm

Joe Johnson

NETDIO,LLC.

Joe Johnson
NETDIO,LLC.

MegaDBA Hall of Fame Points: 3518 More actions · Answer 5

Thansk for all this great info.

I am sorry I neglected to provide some pertinent information about our server farm concept.

It appears that our topology would include Web servers, and application servers, database servers. Replication would also be involved.

In a nutshell, we are proposing to have the production database replicated near "real time" to a back-up DRP server. We are also proposing a server farm for IIS and application servers.

More thoughts are welcomed.

Aurora

Richard L. Dawson Ten Centuries Points: 1062 More actions · Answer 6

Hi aurora01,

That's a lot easier as the concepts of a server farm apply much more easily to web and application servers.

For the data store you will need to do some prognosticating to determine approximate sizing. Depending on what you are trying to do you may still want to use some sort of distribution/federation scheme. It's hard to say without an understanding of your business model

I did work for one company that did payment processing for certain state agencies. They were processing upwards of 35,000 transactions in a 10 hour period on a single 2-cpu server. That might give a better idea of what Sql Server can do.

Richard L. Dawson

Sql Server Support Engineer

ICQ# 867490

Richard L. Dawson
Microsoft Sql Server DBA/Data Architect

I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.

swjohnson Hall of Fame Points: 3254 More actions · Answer 7

Just to let you know what we are using in our Web Farm/DMZ.

We have 12 IIS 5.0 servers that are load balanced and two SQL Servers. Depending upon the application, they connect to the appropriate database server. Each DB server for us is an Active-Passive Cluster with its own shared disk array (Raid 5 if I remember correctly). Each machine of the cluster is a Dual Intel P4 2.8Ghz with 3GB RAM.

Each production SQL Server is then mirrored (we use DoubleTake) to its disaster recovery server in another state. We're in Nebraska and our DR site is in Pennsylvania where our HQ is located.

If you need near real-time you might want to look into Log Shipping instead of Replication. But your choice will depend more on what you are wanting to accomplish--disaster recovery or data synchronization.

At this point, we don't need to use Federation or Replication but if we grow at the rate we are now, we will probably go to doing some Federation.

Like Keith said, you can really set things up nicely with File groups and fast disk arrays and couple that with a monster machine and you shouldn't need more than one SQL server in your web farm.

Hope this helps.

SJ

Accepting all invites

Jonathan SSC-Insane Points: 20427 More actions · Answer 8

quote:
Thansk for all this great info.
I am sorry I neglected to provide some pertinent information about our server farm concept.
It appears that our topology would include Web servers, and application servers, database servers. Replication would also be involved.
In a nutshell, we are proposing to have the production database replicated near "real time" to a back-up DRP server. We are also proposing a server farm for IIS and application servers.
More thoughts are welcomed.

Do not use replication for a Disaster Recovery ("DRP") server, use clustering or log shipping instead. Use Active/Passive failover clustering if you want the highest availability; use log shipping if you want to support a read-only near-real-time data warehouse copy of the database. Either of these methods will have less negative performance impact on your database server and database than would replication. The cost of a SAN to use with clustering will be offset as you will need fewer disks and only one SQL Server license.

--Jonathan

MegaDBA Hall of Fame Points: 3518 More actions · Answer 9

You guys are awesome!!!!

I appreciate all your takes. Here's what was given to me for my recommendations:

Idea before DBA input:

Our environment includes 3 Web Servers(IIS) which are load balanced, 2 application servers, that are load balanced and 3dB servers. They would like to see each environment web, app and dB replicate to Hot Backups at the Disaster Recovery Site.

My rec's:

I agree with swjohnson with mirroring the servers. And Johnathan makes a great point regarding clustering or log shipping vs. replication.

I'm interested in what you guys think about this.

Again, thanks, you all are very much appreciated.

aurora01

Aurora

Jonathan SSC-Insane Points: 20427 More actions · Answer 10

quote:
Here's what was given to me for my recommendations:
Idea before DBA input:
Our environment includes 3 Web Servers(IIS) which are load balanced, 2 application servers, that are load balanced and 3dB servers. They would like to see each environment web, app and dB replicate to Hot Backups at the Disaster Recovery Site.

Unless you have three separate databases, I think the specifier is misinformed. As others replied above, it is a nasty and usually unrewarding job to build a federated database. Scaling up rather than out is the way to handle more transactions with SQL Server.

Your DR site should have duplicate hardware and applications, but the only data needing to be mirrored should be in the databases. SQL Server replication is not designed for, nor particularly suited to, disaster recovery. Unless you're going to buy some very high-end remote mirrored SAN systems, log shipping is probably the best solution for (loosely) synchronizing data over a WAN for disaster recovery.

--Jonathan