• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

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?

  • 2
1 Solution
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!
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now