ORDINA BLOGT

Sharding with SQL Azure Federations is expensive

Since Microsoft introduced it's new pricing model in February 2012 SQL Azure has become a lot cheaper.

  • 28 maart 2012

Since Microsoft introduced it's new pricing model in February 2012 SQL Azure has become a lot cheaper. However, when you partition your databases, the advantages of the new pricing model are lost.

Let me give you an example:
1 database of 10 GB will cost $45.95 per month.
10 partitioned databases of 1 GB each will cost 10 x $9.99 = $99.90

In other words, it's more cost effective to have one big database then to divide the database into several smaller databases. Which is a pity, because with several smaller databases you'll get better performance and parallelism (each database has its own cpu cores, memory, etc.).

Let's look at a few more examples, and see that the price difference can get pretty big:

1 database of 50 GB will cost $125.87 per month
10 databases of 5 GB will cost $259.74 per month

1 database of 50 GB will cost $125.87 per month
50 databases of 1 GB will cost $499.50 per month

1 database of 150 GB will cost $225.77 per month
150 databases of 1 GB will cost $1498.50 per month

Bottom line is that the more efficient your software is (with regards to performance of queries), the less need there will be to split a database into partitions. Ergo, efficient software will keep costs down. One quick win is to not use Entity Framework in places where you want efficient queries, but write them by hand. If for example you want to update a record, Entity Framework always does a select query first and then the update query. This can be rewritten into one query if you do it manually.