Solved

Load balancing Microsoft SQL Server ? Active/active?

Posted on 2011-09-07
7
340 Views
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.
0
Comment
Question by:ThorinO
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:dwkor
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 (http://technet.microsoft.com/en-us/library/ms345392.aspx) 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.
0
 
LVL 10

Author Comment

by:ThorinO
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?
0
 
LVL 13

Accepted Solution

by:
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 http://aboutsqlserver.com/presentations/ (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 :)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 10

Author Comment

by:ThorinO
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.
0
 
LVL 13

Expert Comment

by:dwkor
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.
0
 
LVL 10

Author Comment

by:ThorinO
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.
0
 
LVL 13

Assisted Solution

by:dwkor
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.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Read about achieving the basic levels of HRIS security in the workplace.
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now