Microsoft SQL 2008/2012 High Availability Guidance

Looking to implement a new MS SQL Database setup in our data center and need some guidance on how to properly implement High Availability in MS SQL.  We are currently looking at using 2008 R2 but want our solution to be upgradable to 2012 when it is released.

Anyway, our requirements are fairly simple.  We need to be able to setup 2 separate database servers that each have their own copy of the data so that they can run both together for load balancing and individually for high availability.  Our total data storage will probably be in the area of 4TB and the majority of the storage and transactions will be related to a SharePoint Server Farm.

It is important that we can have both load balancing and high availability in our setup.  

What are our options to do this in the simplest way possible?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Having both updatable is asking for problems,  could read-only for reporting is a sollution
Are you limited to MS solutions, or are 3rd party tools an option?

There is no "load balancing" for SQL, so round-robin multiple instances is the only way to go to emulate that.

A pure MS solution would be to create a SQL Cluster, using Windows Enterprise edition and SQL Enterprise Edition, along with a SAN for storage.  Create two SQL instances, one for each node.  Pick an instance to host the Farm Configuration database, but then as you create new webapps/site collections, put THAT SQL database on the 'next' server, round-robin the databases. .

You could also use HyperV clustering to create a SQL VM that can failover to other nodes, but that's just HA, not load-balancing.  Again, you'd need to create another SQL VM and home some databases on one node, and the other databases on the other node.  This solution doesn't require Enterprise Editions of Windows and SQL!  (Save some $$$ that you'll need to spend on disks!)

That's a lot of work, and 4TB of databases will require LOTS of disk I/O.  I recommend you go to HP ActiveAnswers and look for their SharePoint Sizer Tool.  Even if you are not an HP shop, this is a great tool to ensure you have sufficient performance.

Make sure you have a knowledgeable DBA available to answer the hard questions about proper care and feeding of a large sharepoint database!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gerald ConnollyCommented:
for high IOPS, use smaller disks but lots of them, use RAID-6 or preferable RAID10, remember to keep DB and logs on seperate volumes not on same RAIDset if you can help it.

When you say high availabilty how much downtime can you sustain? does it need to be seconds or will hours be accetable? The shorter the downtime, the more expensive it gets!

Are you going to have the two servers in the same location or in different buildings/locations?
Gerald ConnollyCommented:
will it need to be data replication or will log shipping do?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.