x

Can I load balance two SQL Server instances?

I have an overloaded server. If I set up another instance on separate hardware, can I somehow load balance my clients among two servers?

more ▼

asked Nov 13, 2009 at 09:18 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

You can but you have to create your own load balancing tier code to sit in the middle. You could also use distributed partitioned views (strictly sharding rather than load balancing).

Or you could consider purchasing the PDW product when it comes out: http://www.microsoft.com/sqlserver/2008/en/us/parallel-data-warehouse.aspx

However, just because your server is overloaded doesn't mean you need to scale out of the box. Maybe a server upgrade or some database tuning would be a more cost effective way to solve the problem.

more ▼

answered Nov 14, 2009 at 05:06 AM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

I am not aware of any direct ways to do this, but you can certainly approximate it using replication, especially if at least some of the clients are read only.

Assuming some of your clients are read only, you can set up transactional replication between a Master Server(publisher) and one or more Reporting Servers(subscribers). Applications that need to write will write to the publisher, but the read only clients will interact with the Reporting Servers without putting any strain on the Master Server.

If all clients need to read and write you can set up something to accommodate that with merge replication, but that gets more nuanced and complicated quickly.

more ▼

answered Nov 14, 2009 at 02:40 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

There is no true load balancing for SQL Server....yet, not that would allow you to send a client to > 1 SQL Server instances.

An "overloaded server" usually means not enough RAM or disk. Those can be dealt with in much easier ways, except for very large databases, with more RAM (64GB with Windows Server 2008 and 64-bit SQL Server) and advanced disk systems, spreading the load over multiple disks.

At the database architecture level, you could also implement:

  • Federated database servers
  • Partitioning
  • Various forms of replication
more ▼

answered Nov 14, 2009 at 02:44 PM

Jim Orten gravatar image

Jim Orten
646 8 8 10

(comments are locked)
10|1200 characters needed characters left

You might consider new SSAS feature called scalable shared database. Read about this in Introduction to New Data Warehouse Scalability Features in SQL Server 2008

more ▼

answered Nov 14, 2009 at 04:11 PM

Yuriy Rozhok gravatar image

Yuriy Rozhok
66

(comments are locked)
10|1200 characters needed characters left

Overloaded database? What exactly is your definition of that?
In my experience, this is because of badly written code (oh god, how many times have a seen this?)
It is more cost-effective to rewrite code than buy new hardware.
In the series of Phil Factor Speed Phreak competitions, we have over and over seen code being rewritten to gain a 10-fold (and more!) speed.
How much would it cost to buy hardware to get same speed increase?

more ▼

answered Nov 14, 2009 at 05:04 PM

Peso gravatar image

Peso
1.6k 5 6 8

+1 - the other thing is that optimization doesn't have to take long - My second CLR entry in competition 2 took about 3 hours to write, in total. If that was in a real-world scenario, it would be time well spent...
Nov 15, 2009 at 07:58 AM Matt Whitfield ♦♦
It's easy to say new hardware, and that's a valid alternative, but it isn't always easy to purchase new hardware, nor is it easy to rewrite code. While you can mentimn that, it's helpful to also address the question.
Nov 15, 2009 at 06:51 PM Steve Jones - Editor ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x249

asked: Nov 13, 2009 at 09:18 PM

Seen: 13037 times

Last Updated: Nov 13, 2009 at 09:18 PM