Cap your SQL Azure Database Automatically: Limit the Size

Tuesday, October 26, 2010

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:
“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

To get more idea on this see SQL Azure Team Blog

HTTP Error Code: 400 Message: No tenant signing key of type X509 certificate is provisioned.

Tuesday, October 5, 2010

After the September release if you are configuring your service namespace as per old method you might get following error:

HTTP Error Code: 400
Message: No tenant signing key of type X509 certificate is provisioned.
Trace ID: 2c46fa55-8ae8-443b-9f8a-ab885593c3fb

This is caused because your token signing certificate is not configured properly. In order for Federation Metadata to work, this signing certificate should be configured for Service Namespace.

You have to do this by selecting the value in "Used For" set to "Service namespace". To perform this under your Service Namespace, select 'Certificate and Keys' and then in "Token signing Key/Certificate" under Used for select "Service namespace". This will solve the issue.

No tenant signing key of type X509 certificate is provisioned


Access Control Service will use a Service Namespace certificate or key to sign tokens if none are present for a specific relying party application. Service Namespace certificates are also used to sign WS-Federation metadata.

For SAML tokens, ACS uses an X.509 certificate to sign the token. ACS will use a relying party's certificate, if the relying party has its own certificate. Otherwise, the service namespace certificate is used as a fallback. If there isn't one, an error is shown.

The Appfabric ACS needs a service namespace certificate configured in order to sign Fed metadata. Without this, the Fed metadata cannot be signed and attempting to view it will fail.



2009 ·Techy Freak by TNB