Scale Out Your SQL Azure Database

 

Scaling up an on-premise SQL Server is the concept of adding more computing resources to your existing hardware, like memory, processing power or storage. The purpose is to allow your SQL Server to handle more queries, gain performance or store more data. When you do decided to go to the cloud you should realize that SQL Azure doesn’t scale up like SQL Server – it scales out. Scaling out is adding additional machines with the same data and dividing the computation between them. This article will discuss the differences between scaling up and scaling out and how you need to plan for scale out on SQL Azure to gain performance.

Due to hardware and licensing costs of SQL Server, it is much easier to add resources to existing hardware, than it is to deploy an additional replicated SQL Server on new hardware to gain performance.

 

The problem with scaling out (adding more machines) in an on-premise SQL Server installation is that this adds a degree of complexity to the design that deters DBAs from this path. The DBA now has to consider clustering, deploying a replication schema, etc. It is easier to purchase bigger hardware and redeploy your current databases on it, replacing the existing hardware, then it is to deploy as a cluster.

 

In essence, the SQL Server DBA has been trained to scale up, however hardware costs prohibit infinite scale up. If you have to double your server capacity (RAM, DISK, CPU) by purchasing a new server every time you double your workload and you can’t put the old server to use – your server costs get exponentially greater. In contrast, if you can add an additional server in a SQL Server cluster every time your work load doubles (scaling out) then your expenses grow at the same rate as your demand for resources. This is the beauty of scale out – additional resources cost you the same as the current resources – your costs grow linearly with your hardware.

 

However, most DBAs rely on Moore’s law: Hardware speeds, disk storage, and memory capacity doubles every two years. This means that you can double the performance at the same price as the original cost of your two-year old servers. So, if your workload doubles every two years, you end up purchasing a server twice as big (to scale up) at the same cost as the old server. In this case, all you have to do is purchase a server that will meet your current needs for two years – and hope that your capacity needs do not double beyond that.

SQL Azure

Currently, you cannot scale up SQL Azure processing and memory resources – you are allocated a fixed amount of computing and memory resources per database. Through SQL Azure load balancing, you might gain more resources on a particular machine in the database center, but you cannot crack the case and add more RAM to SQL Azure. For more information see: Inside SQL Azure.

You can, however, scale up SQL Azure storage to the limit of 50 Gigabytes. This is the same as increasing the storage system on an on-premise SQL Server. After 50 Gigabytes you need to scale out your storage.

 

The inability to scale up confuses many people and leads them to think that SQL Azure doesn’t scale at all. It does scale – it scales out. Not only does it scale out, it scales out at a fixed cost; double the resource costs you roughly double the price.

This table will help determine your options for scaling:

 

Need Better Performance*

Need More Storage

SQL Server

Scale Out or Scale Up

Scale Out or Scale Up

SQL Azure

Scale Out

Scale Up To 50 Gigabytes Then Scale Out

* You have other options for better performance than just scaling, you can also optimizing your queries, see: Improving Your I/O Performance for more information.

Scaling Out With SQL Azure

In order to scale out with SQL Azure, you need to be able to partition your data, dividing the database across multiple databases. Each of these databases represents a fixed amount of computing and memory resources; the more databases that you divide your data across, the more computing and memory resources you have at your disposal.

 

Usually scaling involves work that a DBA is used to doing, for example:

 

  • Adding more RAM.
  • Upgrading the storage system.
  • Clustering two on-premise SQL Servers.
  • Optimizing stored procedures.

However, partitioning your data involves working with the developers that wrote the application that queries the database. Currently, you can’t scale out your SQL Azure database without modifying the client access layer to be aware of the partitioning. This is generally is the developers area, not the DBAs.

 

Vertical partitioning divides a table into multiple tables that contain fewer columns. For SQL Azure, we take this one step further, that the tables the data is stored on are in different databases. In other words, scaling out your storage, memory and processing power across multiple databases. For more information see: Vertical Partitioning.

 

Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. Like vertical partitioning, horizontal partitioning scales out your storage, memory and processing power across multiple databases. For more information see: Horizontal Partioning.

 

Another option is to shard your database, using multiple databases to store the same data; each database contains an identical copy of your data. With sharding, you scale out the processing power and RAM, however the amount of data stored stays the same. The benefit of sharding is that your application doesn’t have to make multiple queries to collect all the data – since all the data can be found in one of the many databases. However, for each additional database you are paying for the redundant data to be stored – paying for data storage to gain performance For more information see: Sharding with SQL Azure.

Summary

In order to efficiently scale, whether with an on-premise SQL Server or SQL Azure in the cloud, you need to be able to scale out. In order to scale out, you need to design databases that scale – which means that the application developers need to be involved in coding the database that can be vertically or horizontally partitioned, or sharded.

Comments

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape