Scalability is the ability of a system to support an increased workload by adding incremental system resources without fundamentally changing the system design or architecture. We must distinguish the scalability from reliability, because reliability is the ability of a system to manage the workload without experiencing failures or stopping altogether. Let's take a look at the options we have to increase SQL Server scalability with hardware infrastructure.
Options for SQL Server scalability
Scaling up is adding new resources, such as processors and memory, to existing servers. For example, we can gain performance advantages by adding extra processors and memory to scale up our SQL Server, but the performance gain per unit of cost will show diminishing returns toward the high end because the hardware is proportionally more expensive.
Scaling out is the addition of new servers to the existing infrastructure, which increases system resources as data is partitioned or replicated through these new servers. The new servers cooperate to share the processing of a particular part of the system. For example, we can scale out the performance of an existing two-node active/active SQL Server cluster by adding two more SQL Server nodes. These new SQL Servers will double the performance of our cluster, because load will now be spread to four nodes instead of two nodes.
Choosing between scale up and scale out
When considering enterprise architecture for a new data center, we have to decide between scaling up and scaling out for each case. For example, consider a scenario where your active/active production SQL Server cluster has two nodes with two processors each. This production SQL Server cluster has experienced performance issues in recent months, which is related to recent business growth. After investigation, you decide to scale this production SQL Server cluster. In this situation, we must decide whether to use the existing two-node SQL Server cluster with four processors in each node (scale up), or four nodes with two processors in each node (scale out).
The decision to choose between scaling up and scaling out depends on several factors:
- Scaling out means less downtime compared with scaling up. When you scale out, you simply add new servers to the existing SQL Server infrastructure, whereas when you scale up, you improve your infrastructure by adding hardware resources (such as CPU, memory and disks) to your existing SQL Servers, which requires downtime.
- Scaling out offers better availability because if one server fails, the overall system still continues to operate.
- Scaling out adds more memory to your system without the requirement for 64-bit architecture.
- Scaling out architecture can be more expensive, because it increases data center operational costs to power and cool more servers. It also increases the cost of software licenses, requiring additional licenses for additional servers. Moreover, all these additional servers will be managed by database administrators, network administrators and storage administrators, so we have account for labor costs.
Considerations for SQL Server scalability
Before scaling in any direction, make sure you are getting the best performance from your existing SQL Server infrastructure through application and database optimization. You can resolve most performance and scalability issues through tuning. For example, if your system bottlenecks are at the processor or memory level, it could be because of bad application or database design. In that case, scaling your SQL Server infrastructure is not the best option.
The following are guidelines you should consider before you decide to scale up or scale out your SQL Server infrastructure:
- Optimize application and database code before scaling up or scaling out. I have often seen underperforming systems where the application and database code does not release locks to resources such as tables when they're no longer needed. Inefficient locking can lead to serious performance problems.
- Optimize the application and database design. SQL Server typically experiences processor, memory and disk I/O bottlenecks when the application databases have inefficient indexes or bad database design.
- Archive historical and reporting data within databases. Consider partitioning your large database tables for better performance.
- Periodically review the application and the database code and try to identify SQL Server scalability issues sooner rather than later, so you can modify the application and database design and mitigate performance and scalability problems.
- Create database and log files on separate disks to ensure that they are not competing for disk space and disk access. You can further minimize the possibility of a disk I/O bottleneck by placing files on disk subsystems connected to a different disk controller. Furthermore, place tempdb on a separate disk drive from user databases for improve disk I/O performance.