Cap your SQL Azure Database Automatically: Limit the Size
You might already be aware that you can set an upper limit for your SQL Azure Database. This way we can specify that your database size should not grow beyond this upper limit, to avoid being billed without your knowledge. Not only this you will not be billed based on your cap size, rather you will be billed on the basis of the storage you are currently using. You will be charged for the range within which your database size lies.
What i mean by this is, we have different ranges in SQL Azure on which we are billed : e.g. For the web edition those ranges are 0-1 Gigabytes, and 1-5 Gigabytes. These are different for Business Editions.
So, suppose you specify your cap size to be 5 Gigabytes, and you are currently using around 0.6 Gigabytes of storage. Then you will be charged based on the range 0-1 Gigabytes. However, when your database size increases, say above 1 Gigabytes, your database grows automatically till the cap size. This time you will be billed for the range 1-5 Gigabytes. The database will stop growing automaticaly when it reaches the cap size you specified.
Once cap-size is reachedand you try to use more storage you will get error 40544, the error message is:
This excpetion can be caught in your C# code based on the Exception number of 40544.
One limitation to auto growth is that SQL Azure currently will not let you switch between web edition and business edition. To automaticaaly change the cap-size based on growth and to switch from Web Edition to Business Edition some code is required.
To implement this we must catch the excpetion 40544 and write a logic to automatically switch the edition or increase the cap. What may be useful here will be following queries:
Knowing Current Cap of your Database
Current Size of your Database
Increase Cap Size and Switch Edition
To get more idea on this see SQL Azure Team Blog
What i mean by this is, we have different ranges in SQL Azure on which we are billed : e.g. For the web edition those ranges are 0-1 Gigabytes, and 1-5 Gigabytes. These are different for Business Editions.
So, suppose you specify your cap size to be 5 Gigabytes, and you are currently using around 0.6 Gigabytes of storage. Then you will be charged based on the range 0-1 Gigabytes. However, when your database size increases, say above 1 Gigabytes, your database grows automatically till the cap size. This time you will be billed for the range 1-5 Gigabytes. The database will stop growing automaticaly when it reaches the cap size you specified.
Once cap-size is reachedand you try to use more storage you will get error 40544, the error message is:
“Msg 40544, Level 20, State 5, Line 1
The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. “
This excpetion can be caught in your C# code based on the Exception number of 40544.
One limitation to auto growth is that SQL Azure currently will not let you switch between web edition and business edition. To automaticaaly change the cap-size based on growth and to switch from Web Edition to Business Edition some code is required.
To implement this we must catch the excpetion 40544 and write a logic to automatically switch the edition or increase the cap. What may be useful here will be following queries:
Knowing Current Cap of your Database
SELECT DATABASEPROPERTYEX ('yourDataBaseName' , 'MaxSizeInBytes' )
Current Size of your Database
SELECT SUM(reserved_page_count) * 8192
FROM sys.dm_db_partition_stats
Increase Cap Size and Switch Edition
ALTER DATABASE AdventureWorksLTAZ2008R2 MODIFY (EDITION='BUSINESS', MAXSIZE=10GB)
To get more idea on this see SQL Azure Team Blog
No comments: