load balancing two nodes with sql db? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

High Availability (2008)

load balancing two nodes with sql db?

Author

Topic

andyl9063
Starting Member

19 Posts

Posted - 2012-11-28 : 15:35:27

So I have two servers live. They have same processor, everything is the same except different amount of ram.
Both have access to san
Server A:
windows: 2008 standard sql: 2008
Server B:
windows: 2008 R2 sql: SQL 2008 R2
Is it possible to have these two servers load balancing the traffic for the sql database?
SQL Cluster will also be setup to replicate the databases across the two nodes.
Any specific or best practice I should test? Also, any issues with the current setup?
If you have any other recommendations, I would love to hear them. My first time setting up nlb fresh.

Ultimate goal is to have two server hosting sql database that will spare the workload around.

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-28 : 15:47:42

Not really. You could do replication, but that is as close as you could get. In SQL 2012, you can segregate Read and Write workloads using AlwaysOn Availability Groups.

-Chad

andyl9063
Starting Member

19 Posts

Posted - 2012-11-28 : 15:57:06

that is the thing, our vendor software has not been tested in sql 2012.
any other recommendations?

I am sure other people run into this issue where their sql server is being overwork.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-28 : 16:09:27

Besides replication, you could do log shipping and restore in Standby mode. You'll have more latency than you would with replication though.

Are you using 2 different versions of SQL Server? That's going to complicate things.

andyl9063
Starting Member

19 Posts

Posted - 2012-11-28 : 16:52:49

no i can have two of the same sql server version.
so no one has actually spread the load between two sql server? how do you cope with a busy sql server?

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-28 : 16:54:55

How busy is your server? You can shard data across servers.

-Chad

andyl9063
Starting Member

19 Posts

Posted - 2012-11-28 : 17:02:51

this is my servers. Server cpu spikes to 50% and 18%.
Specs:
tem Value
OS Name Microsoft® Windows Server® 2008 Standard
Version 6.0.6002 Service Pack 2 Build 6002
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name
System Manufacturer HP
System Model ProLiant DL360 G7
System Type x64-based PC
Processor Intel(R) Xeon(R) CPU E5640 @ 2.67GHz, 2666 Mhz, 4 Core(s), 8 Logical Processor(s)
BIOS Version/Date HP P68, 9/30/2010
SMBIOS Version 2.6
Windows Directory C:\Windows
System Directory C:\Windows\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "6.0.6002.18005"
User Name
Time Zone Eastern Standard Time
Installed Physical Memory (RAM) 36.0 GB
Total Physical Memory 32.0 GB
Available Physical Memory 8.96 GB
Total Virtual Memory 49.0 GB
Available Virtual Memory 25.3 GB
Page File Space 17.1 GB
Page File C:\pagefile.sys

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-28 : 17:10:11

How many Batch Requests\sec? That looks like a pretty standard server these days, if not under powered. If you are running into performance problems, I would focus on :
1. Tune the workload/process
2. Increase hardware

before trying to design some complicated load balancing scenario.

-Chad

andyl9063
Starting Member

19 Posts

Posted - 2012-11-29 : 09:42:52

quote:
Originally posted by chadmat

How many Batch Requests\sec? That looks like a pretty standard server these days, if not under powered. If you are running into performance problems, I would focus on :
1. Tune the workload/process
2. Increase hardware

before trying to design some complicated load balancing scenario.

-Chad

how can you find batch\secs?
1. not sure, not really a dbadmin
2. I can upgrade processor and ram.

Thank you for your reply

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-29 : 10:07:01

Run this query:

select getdate(), cntr_value 
from sys.dm_os_performance_counters 
where counter_name = 'Batch Requests/sec'

It's a cumulative counter, so you'd have to collect these stats and then query to get the difference between samples. Alternately you can use Performance Monitor to measure it, it's under SQL Server:SQL Statistics. This will do the difference calculation for you.

andyl9063
Starting Member

19 Posts

Posted - 2012-11-29 : 10:28:46

object_name counter_name instance_name cntr_value cntr_type
Batch Requests/sec 106414453 272696576

does that seem high?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-29 : 11:12:19

It's better to run the counter from Perfmon, you'll get an instantaneous reading. You can't use the query I posted one time, you have to accumulate readings and compare them.

andyl9063
Starting Member

19 Posts

Posted - 2012-11-29 : 11:18:57

yea i just research a lot of perfomance counters.
i got a whole html file with a lot of counters
any thing on there I should look for specifically?

andyl9063
Starting Member

19 Posts

Posted - 2012-11-29 : 12:41:55

batch requests/sec average is 48.251
Max is 436.087

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-29 : 13:06:13

That sounds pretty low even for the hardware you currently have. I would still recommend upgrading the hardware if you can (RAM is really cheap). From what you posted earlier it doesn't sound like either box is particularly stressed, 50% CPU spike is nothing, and even 50% CPU sustained is not particularly worrisome. Nothing that requires load balancing.

What kind of disks are you using? What RAID level? Are data and log files on separate spindles?

andyl9063
Starting Member

19 Posts

Posted - 2012-11-29 : 13:45:42

the actual data of the sql server are on the same server, however, some of the other data that its accessing are on sans drive. The disks on the server are 15k rpm sata disks.
I dont think its a disk issue, i ran a lot of the performance counter and look at all the measurements, didn't see any issues or anything out of the ordinary.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-29 : 13:57:36

My next suggestion is to look at the waits SQL Server is encountering, Glenn Berry has a nice query for that: http://sqlserverperformance.wordpress.com/2010/04/18/a-dmv-a-day-%E2%80%93-day-19/

He also has a series of articles on using DMVs for performance analysis: http://sqlserverperformance.wordpress.com/2010/05/02/recap-of-april-2010-dmv-a-day-series/

andyl9063
Starting Member

19 Posts

Posted - 2012-11-29 : 14:10:14

i ran the query and im getting latch-ex at 38.47 for wait_time_s.
What are latch_ex ? I cannot find any good information on it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-29 : 14:13:10

http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx

More: http://www.sqlskills.com/blogs/paul/category/Latches.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources