Scaling your SQL Server system

by Tim Chapman | Mar 19, 2007 7:00:00 AM

Tags: Storage, Databases, Performance management, Tim Chapman, database, server, performance, database server, hardware, Microsoft SQL Server, SQL Server Newsletter

Takeaway: When your database servers become busy and performance deteriorates, the fix may be as straightforward as taking another look at your current application and query designs. Here's a comparison of scaling up vs. scaling out your SQL Server systems.

What do you do when your database servers become busy and performance deteriorates? Is it better to purchase more hardware to beef up your servers, or do you rethink your database server design so your database platform is highly scalable?

If you've ever worked in a reasonably sized shop, you have likely run into these questions at some point. Sometimes the answer is to simply take a look at your current application and query designs to improve performance.

This article presents a contrast between scaling up vs. scaling out. Next week, I explore different approaches for scaling out your database applications. In the third and final installment of this series, I will provide a more in-depth look at one of the scenarios presented in part two.

Analysis phase

Before you decide to scale your database environment, you should establish performance baselines; then, as you make changes to queries/schema, check these changes against your baseline for improvement. Once you make your current system as efficient as possible, you should reevaluate whether you still need to scale.

Scale up vs. scale out

Scale up and scale out are two methods that you can use to alter your database environment to increase performance. Here's a comparison of the good and the not-so-desirable points of both.

Scaling up
In this approach, you purchase new or improved hardware for your database machine. This added hardware may include faster controllers, a faster disk subsystem, more RAM, more processors, etc.

• Benefits: Adding new hardware to a machine will undoubtedly speed up your applications. In most environments, simply scaling up the server will add enough performance to be satisfactory. You can greatly increase performance of these with the 64-bit versions of SQL Server, which allow for more memory and processing power.
• Downside: The hardware for enterprise-level server machines is typically expensive, so this can be a prohibitively pricey endeavor. If you hit the ceiling to what you can do by scaling up, it may be time to scale out.

Scaling out
In this approach, you take your database(s) and partition it so that different parts of data are partitioned on separate database servers. This option typically requires significant application layer and database layer changes to be successful.

• Benefits: This allows your design to be more scalable because, if your current structure is experiencing a bottleneck, you can further divide your design to more machines to distribute the processing. The division typically takes the form of dividing logically-related tables into different servers or horizontally dividing your tables so that each database server owns a portion of the full data set. You can also mix these two methods. Another benefit to scaling out is that it can potentially handle huge transactions loads. And, you can use commodity hardware, which can be substantially cheaper than what you have to buy to scale up. In addition, it is usually not necessary to purchase the enterprise-level servers that scaling up requires.
• Downsides: This is much more difficult to develop than the scale-up approach. In fact, it is often recommended that you only scale out when your current system is already handling an average of N number of transactions per minute and is not capable of handling more. This form of scaling is so difficult because it is harder to maintain, has many more moving parts, and is hard to logically divide data or tables so that processing is distributed evenly on the separate servers.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Print/View all Posts Comments on this article

Sure, how about some discussion of clustering? rob.dominguez@... | 03/22/07

I wish it were.... chapman.tim@... | 03/26/07

is it possible to do load balancing by actve/active clustering??????? rabisahu3@... | 04/30/08

Question Scaling your SQL Server system javareda | 04/29/08

Thank you Mr. Javereda, but i need some more help on this SERVER CLUSTER.. rabisahu3@... | 05/06/08