SQL SERVER – Discussion – Scale-up vs Scale-out Architectures

Note: NuoDB is a complete re-think of relational databases with innovative support for the Cloud’s dynamic, asynchronous nature. Download NuoDB to experience the Scaling Out scenario discussed in this blog post.

Scaling Up

There are many different ways of scaling SQL Server to accommodate more transactions and throughput. The general scale-up approach includes:

  • 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

As long as larger machines are available and your organization has the means to purchase them, then scaling up your database to increase performance is the best route. However, even if you have the means to scale up your infrastructure you will always be bound by the availability of bigger and faster hardware. Given this limitation on Moore’s Law and other organizational concerns such variable workloads and applications contending for the same dataset, additional approaches are required.

Common examples of variable workloads and applications are OLTP systems that need to be reported on by various departmental stakeholders. Other examples include financial applications that are aggregating financial transactions, marketing systems that measure segmentation performance, and other CRM/ERP systems. Developers and administrators have come up with some standard, generally accepted methods for supporting these demands on data. They include:

  • Replication techniques to enable various applications to connect to replica databases and eliminate computational and I/O constraints on the master
  • Clustering configurations to handle failover and availability concerns
  • Vertical and horizontal database partitioning to optimize query performance

Scaling Out

The introduction of SQL Azure and its ability to be deployed on to the cloud makes scaling out an option. Many instances of SQL Azure databases can be federated to support a single scaled out application. However, this approach is really no different than what developers have been doing with MySQL for many years. Federation is simply a managed method of sharding your database that comes at a cost.  Developers begin with database sharding and then that leads into application level sharding.  More sharding/federating means more complexity in your application architecture. Things databases are very good at such as query processing and managing constraints now become the developer’s responsibility.

Scale Everything

Databases have come a long way and what Microsoft is doing with SQL Azure is amazing. Their integrated development and deployment architecture gives them a clear advantage among .NET developers, administrators, and architects. However, a major shift in database technology is underway. New players are emerging that are changing how applications are built and how they are scaled. Some of the new entrants include NoSQL and NewSQL databases. NoSQL technologies might be outside the scope of this article mainly for their lack of SQL and ACID support, so let’s focus on a new database called NuoDB. Although the company might not agree that they are a NewSQL database, I will broadly characterize them as such for the sake of simplicity.

NuoDB is a database company in Cambridge, MA that I have been following for sometime. Unlike many NewSQL technologies on the market, they have decided to take a fresh approach to building a cloud database. They call it an emergent architecture, roughly modeled around how emergence manifests itself in nature. The example they use is a flock of birds and how birds flock and migrate from point A to B without any individual bird responsible for the flock as a whole. Let me break it down in more salient terms.

NuoDB is a distributed cloud database that supports SQL and ACID transactions. A single logical NuoDB database can be deployed on one or many cloud machines to support the scale-out model without the need to partition, shard, or replicate.

Brokers act as load balancers that connect clients to transaction engines. Transactions engines are responsible for executing SQL and dynamically cache data for read/write purposes. Transaction engines are a key component of NuoDB that enable it operate in a distributed environment without partitioning or sharding. These processes are able to asynchronously communicate with each other to fetch data that might be cached in memory, eliminating the need for sharding and replication. As updates are processed, transaction engines asynchronously broadcast changes to their peers to maintain consistency. Storage managers are responsible for serving up data to transaction engines and serializing data to disk for durability. A typical scale out architecture might look like this:

Each process in the NuoDB architecture represents a virtual or physical cloud resource but the database as a whole continues to represent itself as a single logical database to any client that’s connected to it.

This article was a brief introduction to one of many new database architectures that are making their way into mainstream applications. I like NuoDB because they are actively pursuing the Microsoft community with their public .NET Beta program. If you’re like me, and are interested in new approaches to solving database problems, I’d recommend you check out NuoDB and their .NET program.

Download NuoDB to experience the Scaling Out scenario discussed in this blog post.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Exit mobile version