Database Scalability: Vertical and Horizontal Explained

Database scalability was the topic of a recent customer call. The customer asked me to help decide: one server with 64 cores, or two servers with 32 cores? I explained that the answer is not always simple. After about 30 minutes of conversation, they understood what I meant, so I took a moment to blog about it.

Historically, database systems took two main approaches to scalability. Scalability lets a database hold more user data and process more application workload. You can scale up (called vertical scalability) or scale out (called horizontal scalability). For both relational and non-relational technologies, sharding is one of the most common scale-out implementations. I explained both concepts to the customer next.

Vertical Database Scalability

To scale up usually means adding physical resources: more CPU, memory and storage for a server, or a bigger server. In essence, vertical scalability has these traits:

• Application compatibility comes first, with no need for code changes.
• Administration is lighter, with only a single system image to manage.
• Hardware configurations tend to be more expensive, although fast-evolving hardware offers efficient components with great price-performance ratios.
• Software costs, typically charged by the number of cores, can increase.

This approach also has at least two limits:

• What happens when a workload cannot fit onto the best-equipped hardware configuration?
• What if a workload is highly variable? Why make an upfront investment in an expensive, large-capacity system that could go underutilized much of the time? For this reason alone, many cloud providers do not rely only on vertical scalability.

Horizontal Database Scalability

Horizontal scalability handles variable workloads by hosting data across multiple databases. Unlike scaling up, scaling out can reduce costs by using less sophisticated hardware. That frees resources for application development and for data and system maintenance.

You can use any of several well-known approaches to scale out a data tier. The one you choose depends on your workload and on the applications the data store supports. Most people choose functional partitioning, where a data set is split by business or organizational function.

Two of the most common scale-out techniques are:

• Data is fully replicated across all nodes. One primary copy accepts changes. The other active replicas are typically read-only, as in SQL Server AlwaysOn Readable Secondaries or Replication. This is a good fit for read-intensive workloads such as reporting, where readers can connect to any server. Writers can connect only to the primary copy, which causes a bottleneck in write-intensive workloads.
• Read and write operations are distributed across a number of nodes. With some distribution logic, a given transaction is fully satisfied by entities on a single node. Some data is typically replicated across all nodes. Activity or transactional data is partitioned by a key, simple or composite. The key applies to all entities in the same logical group. Sharding means this approach.

Some of these abstract concepts are implemented in the cloud. On Azure, the implementation is called Elastic Databases, and earlier it was called Federated Databases. Even though such techniques exist in the cloud, architects, developers and DBAs must design and implement them for on-premises systems.

Your Experience

I am sure you have worked on some of these design concepts in your own environments. What have you done for scale-out and scale-up scenarios, and why? Sharing your experience helps everyone. Please tell me in the comments.

Database scalability is not a choice between big and many, it is a match to your workload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.