When it comes to scaling SQL Server to accommodate more transactions and throughput, there are several approaches that can be taken. In this article, we will discuss the different methods of scaling SQL Server and explore the concept of scaling out.

Scaling Up

The traditional approach to scaling SQL Server is known as scaling up. This involves adding more resources to the existing server to increase its performance. Some common methods of scaling up include:

• Adding more CPU to increase computational performance
• Adding RAM to increase query and data caching
• Adding more storage, such as SSDs, and partitioning various I/O processes to different physical disks

Scaling up can be an effective way to improve performance, especially if larger machines are available and your organization has the means to purchase them. However, there are limitations to scaling up, such as the availability of bigger and faster hardware.

To overcome these limitations and address variable workloads and applications contending for the same dataset, additional approaches are required.

Scaling Out

The introduction of SQL Azure and its ability to be deployed on the cloud has made scaling out an option. Scaling out involves distributing the workload across multiple instances of SQL Server databases. This approach allows for increased scalability and performance.

One method of scaling out is through database sharding, where the database is divided into smaller, manageable parts called shards. Each shard is then hosted on a separate server, allowing for parallel processing of queries and transactions.

Another approach to scaling out is through federation, which is a managed method of sharding your database. Federation allows for the creation of multiple instances of SQL Azure databases that can be federated to support a single scaled-out application.

While scaling out can provide significant benefits in terms of scalability and performance, it also introduces complexity in application architecture. Developers need to manage query processing and constraints, which were previously handled by the database.

New Players in the Database Landscape

In addition to traditional SQL Server scaling approaches, there are new players in the database landscape that are changing how applications are built and scaled. These include NoSQL and NewSQL databases.

NoSQL databases, while outside the scope of this article, offer alternative approaches to data storage and retrieval. They often lack SQL and ACID support but provide high scalability and flexibility.

One notable NewSQL database is NuoDB. NuoDB takes a fresh approach to building a cloud database, with an emergent architecture inspired by natural phenomena like flocking birds. It is a distributed cloud database that supports SQL and ACID transactions.

NuoDB’s architecture allows for a single logical database to be deployed on one or many cloud machines, supporting the scale-out model without the need for partitioning, sharding, or replication. Load balancers connect clients to transaction engines, which execute SQL and dynamically cache data for read/write purposes. Storage managers serve up data to transaction engines and serialize data to disk for durability.

By eliminating the need for partitioning and replication, NuoDB offers a simplified approach to scaling out. It is actively pursuing the Microsoft community with its .NET Beta program, making it an interesting option for those looking for new approaches to database scaling.

Conclusion

Scaling SQL Server to accommodate increasing transactional demands and throughput requires careful consideration of different approaches. Scaling up by adding more resources to the existing server can be effective, but it has limitations.

Scaling out, through methods like database sharding and federation, offers increased scalability and performance. However, it introduces complexity in application architecture.

Exploring new players in the database landscape, such as NuoDB, can provide alternative approaches to scaling out. These databases offer innovative architectures that simplify the scaling process.

As technology continues to evolve, it is important for developers and administrators to stay informed about the latest advancements in database scaling techniques. By understanding the different approaches available, organizations can make informed decisions to meet their growing data demands.