Azure SQL Database Elastic Scale Part 1 - What is Sharding?

This week, Microsoft introduced an update to Azure SQL Database Elastic Scale Preview. This update is good news because it positions Azure SQL Database to be able to do what other cloud databases (such as MongoDB and other NoSQL databases) do really well: Scale Horizontally to cope with spikes in demand. In this blog post (Part 1), I am going to have a look at what it means to Scale Horizontally and what Sharding is all about. In Part 2, I am going to go through an implementation of Sharding in Azure Elastic Scale using PowerShell commands.

 

Scaling databases is essentially figuring out what to do when the load on your database becomes too large for the database to handle. You generally have two options: Scaling Up and Scaling Out. Scaling Up (Vertical Scaling) involves increasing the resources supplied to the SQL Server. In on-premise versions of SQL Server, Vertical Scaling would involve "buying a better box". You could do things such as add memory to the server or move the instance to a box with a CPU with more cores (and then pay a lot more for licensing those cores). In Azure SQL Database, you can Scale Vertically by increasing the Service Tier (and increasing the Performance Level within that Service Tier). 

 

Scaling Out (Horizontal Scaling) is increasing the number of databases so that each individual database is handling less data. In on-premise versions of SQL Server, if there is 1 large table that is creating a bottleneck, you could Partition that table to improve the query performance on that table and Scale Horizontally within that database (yes, I know that is an ultra-simplistic explanation). Other options for Horizontal Scaling are explained in Scaling Out SQL Server such as creating Scalable Shared Databases, Peer-to-Peer Replication, Linked Servers and Distributed Queries and Distributed Partitioned Views. I'm not going to go into the benefits of each scenario. If you're interested, the article will do a much better job explaining things than I will.

 

One major thing that has been missing up to this point is the ability to spread data easily across multiple databases, which is known as Sharding. Sharding is something that NoSQL databases such as MongoDB and CouchBase do really well. But what is Sharding and why has it become such a popular way of increasing database performance?

 

In order to introduce Sharding, I am going to first describe a sample database application, and then show how Sharding can be used to Horizontally Scale the application. The sample application is an Employee Payroll System. In this system, all of the records are contained in a single database. The system experiences a very high demand every month, when a link to the employee pay slip is mailed to each employee. During that time, all of the employees use the system to check their pay details. The IT Department is considering using Sharding to Horizontally Scale the database to improve performance. The logical database structure for the system is shown below.

 

 

Sharding is a means of spreading records across multiple databases in order to decrease the load on any one particular database. With Sharding, records are grouped by a particular key, known as a Sharding Key. All of the records that are associated with a particular Sharding Key are known as a Shardlet. In the Employee Payroll System example, the Sharding Key is the Employee ID from the Employees table. The Shardlet would be all of the records that are associated with that particular Sharding Key: the Employee Record, the associated Payroll Records and the relevant Payroll Detail Records. The different databases that store the collections of Shardlets are known as Partitions. In the diagram below, the Shardlet is identified by all of the records that are related to the Employee ID 1 (EID: 1), which is the Sharding Key.

 

 

That all sounds great, but how does an application know where to look for a particular piece of data? If I am sent a link with my Employee ID in it, and that link takes me to the Employee Payroll System, how does the system know which database to look into for the appropriate information? If the application has to search each database, there will not be any benefit to the application Scalability.

 

Data Partitioning is accomplished through the use of a Shard Map. A Shard Map gives the application the ability to look up a particular Sharding Key and find out on which Partition the Shardlet resides. There are two types of Shard Maps that are supported by Azure SQL Database Elastic Scale: List and Range Sharding. In List Sharding, each Sharding Keys is uniquely associated with a particular Shardlet. So when the application looks for Sharding Key 1, the Shard Map knows to go to the appropriate Partition.

 

In Range Sharding, a range of Sharding Keys are associated with a particular Partition that contains the appropriate Shardlets. In Range Sharding, a low value and a high value is given for a particular group of Sharding Keys, and all of those Sharding Keys would be directed to a particular Partition. In this type of Sharding, if the application was looking for Sharding Key 7, the Shard map would find the appropriate range, say 1-25, and direct the query to the appropriate Partition. The type of Shard Map that is chosen tends to reflect your choice of Sharding Key. 

 

In the Employee Payroll System, the IT Department has decided that it will Shard its database into 4 Partitions before the payroll email is sent out each month. Using this method, the load on each database will decrease by 75% when compared to the load on the original database. Range Sharding will be used for the Shard Map. Employee Records with Employee ID's in the lowest 25% of the Employee table with be moved to the first Partition, Employee ID's in the second 25% will be moved to the second Partition, and so on. Once the data has been moved to the appropriate Partition, the Shard Map will be updated to reflect the appropriate ranges. For the purpose of the example, we will say that there are 100 Employees. Employees 1-25 would go to Partition 1. Employees 26-50 would go to Partition 2. Employees 51-75 would go to Partition 3. Employees 76-100 would go to Partition 4. This example is illustrated in the diagram shown below.

 

 

That is a very simplistic example of the Sharding that can be accomplished with Azure SQL Database Elastic Scale. In the next blog post, I will use examples in PowerShell to show how to create the Partitions and Shard Map and show how an application could run a query to find data in the appropriate Shardlet.