Best load balance configuration with Windows 2008 and SQL Server 2008

Question

I have two servers running Windows 2008 Web Edition and SQL 2008 Standard Edition, how would be the best approach to load balance them?

Should I use database mirroring or log shipping? Is Win2008 NLB easy to configure by a developer, since I'm not a sys admin?

What I'm looking for is to be able to have my sites running in case of a single server malfunction, and also to be able to easily add more servers if the demand grows. My sites runs in ASP.Net 3.5 and all of them already store the session in SQL Server.

Answer 1

SQL Server by itself doesn't support load balancing. You can have active/passive failovers with the mechanisms you described (database mirroring and log shipping) and there's a lot of other options as well, like clustering or replication.

The two questions to start with are:

• How long can you afford to be down? (aka your Recovery Time Objective, or RTO)
• How much data can you afford to lose? (aka your Recovery Point Objective, or RPO)

The more time you can afford to be down and the more data you can afford to lose, then the easier and cheaper solutions become to implement. The less downtime and less data, the tougher it is to implement.

For example, synchronous database mirroring will guarantee that you never lose a transaction. Transactions are committed on both database servers before the result is returned to the client. Unfortunately, you suffer a pretty big performance impact under heavy loads, and there's minimal management utilities built in - you want to have a full time DBA to manage this kind of thing.

At the other extreme, log shipping every 15 minutes would mean that you could lose 15 minutes of data (or more) and it might take 15-60 minutes to get back online after a failure. However, it's cheap, has a very low performance impact and it's fairly easy to set up.

I've got a RPO and RTO introduction over on BrentOzar.com, and an introduction to SQL Server's HA and DR features. If you go through those, you'll be better armed to come back and ask more specific questions. Hope that helps!