Scalability, Sweet Scalability

 

Dino Esposito

March 8, 2001

Many, many years ago, when I was a shy, junior programmer, any occasion to snoop in the nerve centre of project management looked like an exceptional stroke of luck to me. Either officially invited to high-level meetings or deceitfully eavesdropping under the table, for years I've heard the word scalability unremittingly resound in the air as a boring refrain. "How do we increase scalability?", "Is the approach you suggest scalable enough?", and "We need much more scalability in the middle tier."

Scalability, sweet scalability. But what is scalability?

Whatever it is, I thought that for my role, I would have been recalling other people's attention on the issue of scalability. As it turns out, you never know what's going on in project management.

I also remember seeing the word scalability extensively used in many academic books, pretending to cover principles and techniques of distributed systems. "Scalability is always a decisive factor in a good design."

Scalability, sweet scalability. But (again) what is scalability?

More recently, I've seen the adjective scalable become the preferred "tool" to qualify the features of any sort of software technology for distributed environments. So I formed the idea that to boost my career, I needed to invest more in my own scalability. And here's the rub. What does scalability mean at the end of the day? And in the context of today's database servers and hardware?

Scalability Gains

Speaking abstractly, I believe that there are two basic ways to dress up a system with the crucial attribute of scalability. One way is optimizing the system logically at the design level. You think of it independently from any software tool and infrastructural element, and then build it up physically. In practical terms, this means minimizing the impact of bottle-necks and critical resources while exploiting the natural parallelism of tasks wherever possible.

The other approach is the exact opposite. You privilege the built-in features of a certain mixture of hardware and software, and reserve a minor role for your solutions and creativity. Basically, you leave the responsability of scalability and interoperatibility up to the tools that you've chosen to handle this task.

Incidentally, the former approach has been in use for years when, in the Pre-Internet era, people were particularly concerned about performance and robustness, and seemed to ignore the issue of scalability. (Which is indeed a relative issue in absence of wide-range Internet connectivity.)

Years ago, the limited offer of feature-rich and affordable integrated solutions led people to mainly focus on design issues, paying careful attention to the database structure and the computational cost of the various operations.

This hardware-independent vision of scalability appears to have been overcome today where the availability of relatively cheap and powerful hardware relegates optimization and design efficiency to secondary positions in the overall project management priorities.

A golden rule of computational complexity states that only the fastest algorithms take fullest advantage of faster hardware. Bear this in mind when scalability is involved.

The Growth Factor

In general terms, scalability refers to the ability of a system to maintain, if not improve, its average performance as the number of clients grows. Scalability thereby looks like a neat, simple, cloudless concept.

However, scalability can be abstract as well. Unfortunately, it is not a property of a system that you can programmatically toggle on and off, or somehow directly control. By contrast, it is a system attribute that stems out from the combination of all of the other attributes, the overall design and implementation, and the interaction model of choice.

The intrinsic level of scalability in a distributed system can't be easily detected with a monitoring or analysis tool. On the other hand, there are a number of implementation aspects (abundance of critical resources, design bottle-necks, lengthy yet necessary tasks,and excessive serialization of operations) that constitute a sort of circumstantial evidence of limited scalability.

But without stress-testing the system in a real-world production scenario, you can't say that a given system is, or is not, scalable enough.

Scalability relates to performance to some extent and is a non-issue if the system is well built and applies reasonable and thoughtful schemas.

It's amazing to see how an invisible system attribute like scalability suddenly becomes the main culprit of any performance inefficiency that a system may experience. Design your components paying due attention to the computational cost of the operations and you're positioned extremely well, whatever is the future evolution of the system.

Scalability is always a factor that influences the growth of a system. In a nutshell, a system that needs to grow is a system with a current performance that doesn't meet the expected number of users. How do you structurally improve the performance of such a system? Simply put, with more powerful hardware or with a better combination of hardware and software.

In modern days, these two options have been renamed to more intriguing and marketable expressions. The hardware-centric approach is called scaling up. The trickier mix of hardware and software is known as scaling out.

To control the growth of your system, make sure you either scale up or scale out. But make sure you maintain the system's ability to scale.

Scalability, sweet scalability. Is this just the definition that best fits?

Scaling Up

Scaling a system up basically means that you migrate lock, stock, and barrel under the umbrella of a new and more powerful hardware system. Once the new system is ready, you backup tables and applications and go online. The impact on existing code and system organization is minimal.

Don't think, however, that the path to scale up is strewn with roses. Scaling up has a down side with a couple of points that deserve more attention. First and foremost, a system that scales up has a single point of failure and will eventually be subject to some sort of hardware limitation. Scaling up increases the processing power of a server by using a more powerful computer. While it may seem outside of today's perception, the growth of processing power in a single piece of hardware has a physical upper threshold that will one day be reached.

Second, approaching this upper limit has considerable costs both in terms of time (over a certain limit, the technology may take years to double the power), prices and, last but not least, power consumption and office space.

This said, scale up is reasonably the first option to consider because of the limited impact it has on the existing structure.

Scaling Out

Scale out increases the processing power of the system as a whole, opposite to augmenting the power of the single piece of it acting as the server. A scale out system is inherently modular and formed by a cluster of computers. Scaling out such a system means adding one or more additional computers to the network.

In a scaled-out, highly partitioned environment, you should employ a more abstract and hardware independent concept of processing power. The total processing power is the sum of the physical speed of each computer mediated by the data and applications partition spanned over the nodes.

Scaling out poses no limit to the growth of a system. Which is definitely beneficial at this point in time. Scaling out, though, requires a huge work of redesign and reimplementation. A system that has been conceived according to a single-server logic must be rethought and reorganized to fulfill scale out requirements.

You must decide how to partition data across multiple DBMS servers. The applications path to data must be carefully optimized through proper execution plans. A good, proactive and frequent analysis of the users activity is crucial to fine-tune the system during its lifecycle.

At this price, you have a virtually infinite system that you can add processing resources to in the middle tier as well as the data tier to meet the increasing number of users and workloads.

Notice that for a scalable system the critical aspect is not how high the number of expected users can be. The real point to consider is how quickly this number is expected to grow. The relative growth of the numbers is much more important than absolute numbers.

Designing a system for a rather constant audience of one-hundred million users is much easier than building a system for one hundred users today, but expected to dramatically multiply over time. From this, you understand that the demand for scalability is particularly strong in industrial-strength, e-commerce Web applications because these breeds of systems are exposed to significant and sudden growth of users.

How SQL Server 2000 Scales Out

Ascertained that scale up is relatively easy to implement, and relatively inexpensive at least for low volumes of data and users, from a software perspective a scale out technology is by far more interesting and challenging. You cannot reasonably scale out without the help of off-the-shelf software products working in the back-end tiers.

One scale out model is the clustering model you find in COM+ and Windows® 2000. All the servers in the business tier have identical copies of the COM+ components. Then, the Windows 2000 load-balancing service running in the background takes care of dispatching new requests against the components according to their individual workloads.

From the application's point of view, you see a single entity—the set of COM+ components. You code against them irrespective of the number of different servers they're running on, ignoring the role of the underlying load-balancer. Scaling out here is as easy as adding a new fully configured Windows 2000 server with the proper set of components installed.

In this clustering model, two distinct entities cooperate: the application's components and the system's load-balancer. Such a model can't easily be applied to the data tier. In fact, you have only one software entity—the DBMS.

SQL Server 2000 supports a different model of clustering called federated servers. The network makes a group of servers all running SQL Server visible to applications. All these public instances of SQL Server are autonomous, managed independently, with different tables and even featuring different settings.

The main aspect of a DBMS workload is the data. Applications are primarily responsible for a well-balanced distribution of data across multiple servers. SQL Server 2000, on its own, provides the built-in ability to support updateable views of data physically partitioned across multiple servers.

You can decide to span your tables over the multiple instances of SQL Server available through the network. You can wrap this data together whenever needed. You do this through partitioned views, a special breed of views that enjoy a special support from the runtime of SQL Server 2000.

Partitioned Views

Partitioned views apply to federated tables, namely critical tables that span across two or more servers. Federated tables are created through a process known as horizontal partitioning which divides a given table into a federation of smaller tables. Application-wise, a federated table appears like a single, monolithic view.

To balance the workload, member tables are placed on separate machines. They have the same format as the original one but each contains only a portion of the rows. Member tables may have any name, but to increase the location transparency it is recommended that you give the member tables the same name as the original.

Constituent tables are normally designed to contain equally sized portions of data. The distinction is done on a unique, non-updateable, primary key. To guarantee integrity and consistency, you must also ensure that no record is duplicated. A CHECK constraint on each member table is the preferred way of doing so. The partitioning column cannot allow nulls or be a computed column.

A partitioned view is a normal view involving distributed SELECT statements that apply to structurally identical tables and put data together through UNION ALL clauses.

CREATE VIEW MyView AS
   SELECT * FROM Server1.Database.Owner.MyTable
UNION ALL
   SELECT * FROM Server2.Database.Owner.MyTable 
UNION ALL
   SELECT * FROM Server3.Database.Owner.MyTable
UNION ALL
   SELECT * FROM Server4.Database.Owner.MyTable

This distributed view must be created on any of the involved servers and each server must be visible to the others as a linked server. They should preferably have the lazy schema validation option set to true. This attribute determines whether the schema of linked remote tables will be checked. Set it to true and SQL Server skips the check resulting in better performance. In this particular case, being lazy has no subtle side-effects.

Partitioned views were originally introduced with SQL Server 7.0. However, with SQL Server 2000 and a couple of significant improvements, they evolved to the rank of important tools for scaling out systems s.

In SQL Server 2000, partitioned views can be updateable and enjoys special optimization from the query optimizer aimed to minimize the need for cross-server processing. The key advantage of a federated table is balancing the workload between available servers. This is clearly an advantage as long as any server can accomplish the assigned tasks locally.

A partitioned view is automatically updateable under the following conditions:

• It is formed by the results of individual SELECT statements merged together using the UNION ALL clause.
• Each SELECT statement works on a single table (that is, no JOIN allowed).
• The table is a local or linked table.
• The table contains no timestamp column.

A linked table must be referenced using any of the following possibilities: the fully qualified name (server, database, owner, table name), the OPENROWSET, or the OPENDATASOURCE function.

The INSERT, UPDATE, and DELETE statements you run against an updateable partitioned view must undergo a number of constraints to be effective. For example, you cannot insert new rows if the table contains an identity column. You must specify all the columns including those with a DEFAULT constraint. Updates and deletions are not allowed if the view is self-joined, or joined with any of the other member tables.

Scale Out in Practice

The clustering model that SQL Server 2000 delivers is not just for everybody. It has been specifically designed to help with high-end OLTP enterprise systems and particularly aggressive Web applications.

To be effective it requires partitioning of data and, moreover, partitions must follow a logical schema. All the related data must reside on the same server and data must lend itself to logical splitting.

A good understanding of the data is an absolute must. In addition, the shape of the data shouldn't change too much over time. And if you know it will change, you should know the future shape in advance and then plan the partitions aware of that.

Having related data on the same node is a viable issue; otherwise you quickly lose in network latency what you've just gained with a cautious load-balancing strategy.

Once you finished with data partitioning, even if you've been incredibly successful and smart, you're only half the way. In fact, physically moving the data to the cluster of choice remains up to you as well as arranging backup and monitoring solutions.

Scale out technology is decidedly hard to implement and a much more intrusive approach than scaling up. Design issues sum up with practical obstacles like the lack of special tools to manage scale out clusters as a single entity. Some of these tools are expected to ship as part of the next version of SQL Server, SQL Server 2005.

Scale out scalability looks promising and intriguing but scaling up on single servers in most cases still represents the safest way to go.

To Scale Up or Out?

It's a fact that a third-generation Web service may have serious requirements in terms of hardware. In principle, you have a question to solve: to scale up or scale out?

Scaling up increases over time the power of a single hardware system. Scaling out moves your system towards a growing farm of interconnected but smaller systems.

A system that scales-up is more exposed to failures, inherently less robust, and not upgradeable over a certain threshold. It can also be costly in terms of power consumption, room, and price. On the other hand, scaling a system up is as easy and seamless as making a backup and restore.

A system that scales-out is inherently more robust, expandable, and overall less expensive even though dealing with a single machine is always better than coping with dozens or hundreds.

However, the list of pros and cons for both approaches is somewhat relative and absolutely project-specific.

Scaling up or -out depends on the nature of the system. Scaling out is certainly recommendable as long as a single instance of SQL Server, possibly running on a separate multi-processor machine, pays the bill of your data access needs. Incidentally, this is the model of scalability behind Web farms.

However, the Web farm scale out model is just one way to look at horizontal scalability. If you need to process high volumes of concurrent data (for example, a OLTP system), then you probably need multiple cooperating servers to turn into reality the old motto of divide-et-impera. In this case, your data must be properly reorganized. It's a huge amount of work that can't be slated by haphazard. Before embarking on a project like this, make sure your system is ready for a such a quantum leap. That is, make sure it can be considered an OLTP system.

As a general rule, while scaling out looks more promising, scaling up should always be considered first and discarded only with good reason.

Scalability's the Way to Go

In spite of the theory of scale up and scale out technologies, there are a couple of points to be made:

• Scalability is about computational complexity and indirectly about performance.
• Scaling up with hardware or scaling out using special database services should be considered only when you have already reached a good compromise between the optimal computational complexity of the tasks and the implementation efforts.

However, I know that employing faster hardware is always faster than improving algorithms, optimizing query execution plans, and finding out and eliminating bottle-necks. And it also helps with deadlines!

Dialog Box: Rock Around the ADO Clock

My boss asked me "What's the difference between ADO and ADO.NET?" I answered that ADO.NET is yet another data access layer that we will end up using, not totally aware of pros and cons. I see that ADO code works in .NET, but your ADO rock'n'roll article made my certainty quite rocky <g>

I agree that at first sight, it's hard not to think of ADO.NET as "yet another data access layer." However, at a second look, things are different. ADO.NET is and remains the only recommendable way to go for data handling applications running under .NET.

In recent years, I have seen people rushing into ADO and dropping optimized and fine-tuned RDO code. They obtained all but exceptional results. However, only the poor ADO that was put on the rack pleads guilty of high treason.

Likewise, I expect to see that poor ADO.NET will become the scapegoat of bad migration projects. No code is perfect, but system code is almost always better than yours and mine. That said, the RDO-to-ADO upgrade could have been questionable. Why touch code that's working well just to move from ODBC to OLE DB and to a richer object model?

With ADO.NET things are completely different. ADO.NET is the clear choice once you opt for .NET as your server platform. ADO.NET is the only set of classes you have to work with data.

Technically speaking, you can stick to ADO but you'll end up paying a steep price in terms of .NET-to-COM marshaling. The key to ADO.NET is that you study it and work with it in a controlled environment before you start your actual real-world coding. How many people did this when they moved to ADO?

ADO.NET is a more appropriate model than ADO for today's Web-driven world. At the same time, the ADO.NET object model has been aligned to ADO concepts as much as possible, and wherever suitable and sensible. To move to ADO.NET, get started now and your learning curve will be surprisingly simple and short. Developers don't need to learn too many new concepts in order to use ADO.NET.

However, they must reset their ADO mind-frames and start thinking according to another model. ADO.NET is anything but "just another data access layer." You don't have to choose it between ADO and ADO.NET. The bigger decision is whether or not you want to choose the .NET platform.

Dino Esposito is Wintellect's ADO.NET expert and a trainer and consultant based in Rome, Italy. Dino is a contributing editor to MSDN Magazine and writes the Cutting Edge column. He also regularly contributes to Developer Network Journal and MSDN News. Dino is the author of the upcoming Building Web Solutions with ASP.NET and ADO.NET from Microsoft Press, and the cofounder of https://www.vb2themax.com/. You can reach Dino at dinoe@wintellect.com.