Load balancing Microsoft SQL Server ? Active/active?
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.
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.