Brendan Tompkins [MVP]

Blog First. Ask Questions Later.

Help Wanted : SQL Server Scaling

We're currently looking at scaling up some critical systems here at the Port. Scaling the Web tier is a no-brainer, slap some load balancers in front of our web servers with sticky connections, sort out any ViewState issues, and presto - double the horsepower.

So the next step is for us to scale our SQL server. There are currently two ideas being batted around.

1) Clustering

2) Load balancing with hardware and a software solution (or sql replication) to manage the data synchronization

My question is, has anyone done one or the other? What are some issues you've encountered? Option #1 seems to me to be the most straight-forward.

Thanks in advance!

Brendan

P.S. If anyone out there has real-world experience doing this and may be interested in a short (< 1 week) consulting gig, send me an email at brendan dot tompkins @ gmail.com

Published Nov 22 2004, 07:14 AM by Brendan Tompkins

Comments

Jimmy Nilsson said:

Hi Brendan,

When you say "clustering", you mean shared-nothing, right?

If so, are you going for DPV or a custom solution?

But before doing that, have you done the simple things like off-loaded statistics reports and such, and had a first transaction analysis? Most often, you could analyze and fix transactions a couple of times and each time take away the current "problem" so you get lots of free resources.

Best Regards,
Jimmy
www.jnsk.se/weblog/
###

# November 22, 2004 2:26 AM

Brendan Tompkins said:

Thanks Jimmy,

By Clustering, I mean one big honking server, yes shared nothing. Actually I should back up and ask what are all of the options for scaling sql in the first place?

I know SQL from a devlopers perspective, so this is new to me. I did get Precise Indepth setup to monitor the critical servers last week, and we're looking at the statistics stuff. I have a feeling that many of our problems are not hardware related. But a SQL server had a hard-drive failure last week, and so they're wanting some fault tolerance. I was put on the team to try to figure it out. Not really sure really why, but should be interesting to say the least...

# November 22, 2004 2:35 AM

Brendan Tompkins said:

Good link here:
http://www.sql-server-performance.com/dk_massive_scalability.asp

# November 22, 2004 2:37 AM

David Douglass said:

I can't tell you the best way to scale in your situation, but I can give you some general advice based on my experience clustering SQL server in a 2 node active/passive configuration:

1) put some room in the schedule to work out the kinks

2) do a detailed deployment diagram showing all the pieces and the relationships. Use attributes to show details like IP address, machine name, etc.

3) test the deployment. In the case of high reliability, clustering alone isn't enough, application changes are also necessary.

# November 22, 2004 5:23 AM

Kevin Blakeley said:

We are currently researching some options as well and came across this product to help with sql clustering

http://www.neverfailgroup.com/products/nfsql.asp

# November 22, 2004 5:50 AM

stefan demetz said:

if you got the money, go for a Unisys ES7000, I have seen them in action, they're are fantastic HW, all certified bits and superbly managed ... at the end it saves you money
(btw, have no affiliations)

# November 22, 2004 7:22 AM

Srdjan said:

Here are 2 products that I found are worth looking at, while reaserching the same problem:

1) http://www.polyserve.com/sol_windows_sql.html

2) http://www.xprime.com/

Srdjan

# November 24, 2004 2:11 AM

Name (required)

Your URL (optional)

Comments (required)

Remember Me?

Enter the numbers above:

Add

About Brendan Tompkins

Brendan has been programming with .NET since the first public beta and is owner and operator of Port Technology Services, a consultancy company providing .NET application development services to the Maritime industry. In July, 2007, he was awarded the Microsoft MVP award for ASP.NET. He's also a proud co-founder of failed .COM startup Intrinsigo, and has had a hand in the failure of numerous other businesses. He currently runs CodeBetter.Com and Devlicio.us, and lives in Norfolk, Virgina with his wife Tiara and son Ian.