SQL 2005 Network Load Balance

Question

SQL 2005 Network Load Balance

active_elk

Grasshopper

Points: 20
More actions
October 20, 2010 at 7:06 am

#142244

Hi,
We currently have a SQL 2005 Standard installed on a Windows 2003 Standard server.
What we wanted to do is to buy another hardware, install SQL 2005 Standard on a Windows 2003 Standard server and make SQL 2005 NLB.
May I know is this possible?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It is not.

SQL is clustered for availability, it has no scale-out capabilities built in. You can build a scale-out solution yourself, typically with peer-to-peer replication, but it's not trivial.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

active_elk Grasshopper Points: 20 More actions · Answer 2

Hi GilaMonster,

So am I correct to say is that there is no built-in NLB option for SQL 2005?

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

There is no built in NLB for any version of SQL. That's 2000, 2005, 2008, 2008 R2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

russell-154600 Ten Centuries Points: 1375 More actions · Answer 4

We use peer to peer replication, then point our front end servers (web servers) to different SQL boxes. It's not trivial, but it isn't that difficult either.

Peer to Peer is just transactional replication for the most part. Biggest concern really is identity columns, which are easily handled by reseeding them, so the identities never collide. For example in a 3 peer solution, the identites would be seeded:

Server A (1, 3)

Server B (2, 3)

Server C (3, 3)

I've also seen where people will add a column to transactional tables to indicate the ServerID or ServerName as well.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 5

Replication can have delays, and it's not a scale out solution. It can mimic it if the loads are not too high, but there's no guarantee that one node will have the same data as other nodes.

Gail is correct, no NLB for SQL Server (yet).