Partitioning is a technique of splitting up data into smaller subsets across multiple databases for better manageability, availability and scalability. As customers’ data needs grow, they have the option to either scale up or scale out.
The primary advantage of scaling out vs. scaling up is that you can get more work done by parallelizing the workload over distributed servers and improving throughput. Scaling up always has a ceiling as opposed to Scale out with virtually no limits. Depending
on how the application is architected, scale out techniques require minimal downtime where you can provision servers while managing the availability of servers through a configuration file. While hot add memory and processor options exist in high end systems,
typically they do end up taking downtime as a precaution. Scale out techniques are being successfully applied in several applications such as eBay, Flickr, YouTube, Facebook etc.
Partitioning can be achieved in several ways, the two major categories being Horizontal Partitioning and Vertical Partitioning. While the benefits of partitioning seem well understood, successfully implementing a partitioning technique and achieving the
partitioning benefits depends on the nature of workload of businesses and a very carefully planned and tested partitioning key.
For example, if you consider a typical online store, all inventories belonging to a particular country can be put into one database. Similarly, sales, customer information belonging can be distributed based on the country, country being the partitioning
key here. Another partitioning key in the above scenario could be an alphabetical list of countries. Further partitioning can also be done by using a state as a partitioning key. A combination of criteria can also be considered for a partitioning key, depending
on the type of application and the nature of workload.
Typically, data is normalized for better performance. In horizontal partitioning, logical groups of data are stored together. There are some key considerations to achieve the benefits of horizontal database partitioning.
SQL Azure is a cloud based service offering relational database capabilities as part of the Azure platform. SQL Azure is based on Microsoft SQL Server and offers a subset of the features available on on-premise SQL Server. It provides a highly available,
scalable multi-tenant database service hosted By Microsoft.
Currently, SQL Azure is offered in two editions – a Web edition and a Business edition. The latest offerings and the database size limits with the offerings is available at
Pricing Overview. You can use the SQL Azure portal to create one SQL Azure server, per subscription. The SQL Azure portal provides a user interface that you can use to provision servers and databases.
A SQL Azure server is not a physical server like an on-premise instance of SQL Server. Instead, it’s a logical grouping of user databases. Each SQL Azure server comes with a read only
master database. The master database maintains a list of databases and keeps track of logins, and their permission levels. The
master database also contains usage metrics.
Applications that need tens or hundreds of databases for a short period of time due to a seasonal/anticipated increase in demand can do so seamlessly and these additional databases can be de-provisioned when the usage drops. This kind of scale out capability
with SQL Azure greatly benefits customers from having to purchase high end hardware to sustain these occasional peaks in demand and helps them save infrastructure costs from under-utilized hardware that they would otherwise incur when scaling out on-premise.
The pricing structure for SQL Azure is available at
Azure Pricing. When scaling out with SQL Azure customers can accurately calculate the costs they would incur and also very accurately estimate the cost savings when they need to scale down. Customers can also get real-time cost estimates of the number of
databases they have and their usage charges from the portal with very little effort.
Although database partitioning can help improve performance, scalability and costs, it can be a challenging effort to implement successful partitioning scheme. Some applications have a natural partitioning scheme that the applications can take advantage
of while some applications may have to be re-architected to become scalable, although the benefits may be well worth it. SQL Azure provides the platform where applications can scale out from one or two databases to tens or hundreds of databases very seamlessly
without incurring heavy infrastructure costs. Customers also do not lose any infrastructure investments if they need to scale down due to changing usage patterns.
· SQL Azure Portal
SQL Azure Developer Center
· SQL Azure Team Blog
Ed Price MSFT edited Revision 2. Comment: Title case. Added line rule.
Bookmarking this one. Thank you very much for this great article, Dinakar!
Carsten Siemens edited Revision 14. Comment: Fixed misspelling and added tag: has comment
it will be good if you could include storsimple article :)
The current guidance from Microsoft SQL on sharding can be found here: