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.
