SQL Azure Partitioning Pattern

While working with a customer at our Microsoft Executive Briefing Center (Microsoft EBC), one of the things we need to do was find a simple and effective way to partition SQL Azure.

The problem with SQL Azure is that depending on your partitioning strategy, you can end up allocating increments that you don’t need and still pay for it.  The distinction is that for Azure Storage, you pay for storage used, but with SQL Azure you pay for the allocated increments.  See Windows Azure Pricing.

After checking with our colleagues, one of the approaches we liked is to partition by customer ID ranges.  By adding this level of indirection, you can optimize or reduce unused increments because you already know the ranges and usage, and you can change the ranges and allocations if you need to.   Here is a visual of the approach:

Thanks to our colleague Danny Cohen for the insight, clarification, and suggestions.