SQL Azure - Querying SQL Azure Database Usage Billing Information

Introduction:

In this article we are going to see how to use the Manage portal to query and check the usage of the SQL Azure database and the billing information of the database using the in-built views.

Overview:

Microsoft has provided this great view to check the usage of the data with SQL Azure under the subscription online. Since we are licensed on a pay as you go basis we have this kind of environment to check the daily usage and so when we need to we can limit the usage as needed. This provides greater control of the small business to deploy and use the application according to internal requirements.

Microsoft provides a view "sys.database_usage" to query the database usage billing information. Let us see the step by step process of how to get the billing information as shown in the following steps.
Steps:

Log in to the Azure portal using the following link. You can see the screen look similar to below.

http://www.microsoft.com/windowsazure/

Login to the portal using your Microsoft Windows Live credentials with Azure credentials to the Management Portal and you will see the screen as shown in the screen below:

Now we can see the Database Menu at the bottom left; click on that to go to the Database Subscription window as shown in the screen below:

Clicking on the subscription name will provide the complete details of the server created and the new database created as shown in the screen below:

We need to select the Master database to query the complete details of the server and that view is available only with the Master database. After selecting the Master database select the Manage option as shown in the screen below.

Now we are prompted to enter the credentials to login to the server as shown in the screen below. Doing this is something like when we enter SQL Server Management Studio we are asked to enter the login credentials.

After entering valid credentials our page will look like the following with the new SQL Azure Management Portal:

Now select the New Query option available at the top menu as shown in the screen below.

We can use this like a normal query analyzer as we do with the traditional SQL Server database locally. Now write a query to fetch the view details as shown in the screen below.

Query – select * from sys.database_usage

We can see the result as shown in the above screen. Here we can see the time (Event occurred time), sku (Edition – Business or Web) and quantity of the database used for the given day.

Now lets us see how to view the cost of the database usage using a small calculation as per the subscription. Say a web edition is priced at 9.99$ per month for the number of days in a month say 31days then it would be 9.99$/31 which would give us the single day account. Since we have only one quantity of the database it would be calculated as 1*9.99/31 for a web edition. In a similar manner for a Business edition this logic can be extended according to the priced amount (Since for business edition it's priced at 99.99$). We can see the result as shown in the screen below.

Script:

Select sku, sum (

case when sys.database_usage.sku = 'web' 

then (quantity*9.99/31)   

when sys.database_usage.sku = 'business' 

then (quantity*99.99/31)   

end ) as 'cost'     

from sys.database_usage     

where datepart(yy,time) = datepart(yy, getutcdate()) and     

datepart(mm,time) = datepart(mm, getutcdate())     

group by sku

So the database usage costs 0.322258$ per day for our use of it which is shown in the above screen.

Conclusion:

So in this article we have seen how to check the database usage of the subscription using the SQL Azure Management Portal.