Load balancing Microsoft SQL Server ? Active/active?

Posted on 2011-09-07
Last Modified: 2012-06-27
We are currently running a SQL 2008 server with 20GB of memory and 8vCPU on a VMware host. This server is heavily utilized and mission critical. We would like to create an active/active (or load balanced SQL server) so that we can improve performance by splitting it in half essentially as well as provide better uptime. I believe if I do active/active and want a passive failover node I will need a 3rd one as passive correct?

Basically, what would be the best way to improve performance on a VM that is heavily used and already has a big chunk of resources.
Question by:ThorinO
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 13

Expert Comment

ID: 36498827
SQL Server cluster does not help with performance. It helps with availability.

Active/Active helps you only in the case if you can separate databases between sql server instances. There are no load-balancing technologies in the scope of the single database with exception of Scalable Shared Database ( which is read-only.

My best bet would be to look at the system in general and check if there are some performance tunings/optimizations that can help.
LVL 10

Author Comment

ID: 36498865
That's what it seemed like from the reading I did. So there is no real way to scale out unless you can break out the databases correct? If that is not possible then you are stuck scaling up correct?
LVL 13

Accepted Solution

dwkor earned 500 total points
ID: 36498935
Technically yes, you are right. Although if system redesign was the option, I'd consider sharding in one way or another. Not every system would benefit from that but there are a lot of cases when it could be appropriate.

I did the presentation on one of recent SQL Saturday events on that topic - you can download the slide deck from (Sql Sat #79). It could give you a couple of ideas in terms of possible system architecture.

As the side note, neither system redesign nor scaling-up would be my first choice. I'd always start with system analysis and performance tuning. This typically gives biggest bang for the buck. Especially on initial stage when 80/20 rule still apples :)
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 10

Author Comment

ID: 36499017
Unfortunately we recently did a redesign where we distributed LUNs, upgraded to 2008, switched to a paravirtualized SCSI adapter, vmxnet3 NIC driver, removed old databases and jobs, etc. At this point I think we need to throw more resources at it (which I don't want to do) or break some of the databases out to another server to offset the load.

We have another active/passive SQL cluster which is only running a few databases for a new application. I really like the increased availability which the current standalone lacks.
LVL 13

Expert Comment

ID: 36499114
Well, all those things are more or less hardware and infrastructure changes. It would help but usually it would not solve the problem. Have you done any performance related analysis? Please don't take me wrong - maybe the system is perfect and definitely outgrows the hardware. But it always worth to look.

What are top 5 waits in the system? What are the execution plans for most IO expensive queries? Sometimes a few indexes could make the huge difference.
LVL 10

Author Comment

ID: 36499156
Not too sure to be honest, we have another guy who generally handles the database side of things and I am more on the systems side.
LVL 13

Assisted Solution

dwkor earned 500 total points
ID: 36499231
You need to work together :)

There are a few methods that could give you and him/her good ideas what is going on with the system and what problems system are facing. (wait stats, perfmon, query stats DMV, etc).  Those methods are simple and can provide you some information within hours. Well, let's put it in that way - don't invest into the hardware and infrastructure changes until database professional proves that it's required. You're masking performance issues with HW rather than solving them.

And yes, regardless of performance issues, cluster is good for availability if you can afford it.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
subtr returning incorrect value 8 70
SQL - Curser to do an insert based on a select 2 26
simple bash script needed to unpack tar.gz 4 29
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question