SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 load balancing two nodes with sql db?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andyl9063
Starting Member

USA
19 Posts

Posted - 11/28/2012 :  15:35:27  Show Profile  Reply with Quote
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.

Edited by - andyl9063 on 11/28/2012 15:37:35

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/28/2012 :  15:47:42  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 11/28/2012 :  15:57:06  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/28/2012 :  16:09:27  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 11/28/2012 :  16:52:49  Show Profile  Reply with Quote
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?

Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/28/2012 :  16:54:55  Show Profile  Visit chadmat's Homepage  Reply with Quote
How busy is your server? You can shard data across servers.

-Chad
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 11/28/2012 :  17:02:51  Show Profile  Reply with Quote
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
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/28/2012 :  17:10:11  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 11/29/2012 :  09:42:52  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/29/2012 :  10:07:01  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 11/29/2012 :  10:28:46  Show Profile  Reply with Quote
object_name counter_name instance_name cntr_value cntr_type
Batch Requests/sec 106414453 272696576

does that seem high?

Edited by - andyl9063 on 11/29/2012 10:32:29
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/29/2012 :  11:12:19  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 11/29/2012 :  11:18:57  Show Profile  Reply with Quote
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?
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 11/29/2012 :  12:41:55  Show Profile  Reply with Quote
batch requests/sec average is 48.251
Max is 436.087
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/29/2012 :  13:06:13  Show Profile  Visit robvolk's Homepage  Reply with Quote
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?
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 11/29/2012 :  13:45:42  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/29/2012 :  13:57:36  Show Profile  Visit robvolk's Homepage  Reply with Quote
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/
Go to Top of Page

andyl9063
Starting Member

USA
19 Posts

Posted - 11/29/2012 :  14:10:14  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/29/2012 :  14:13:10  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000