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 inbuilt views.

Overview:

Microsoft has provided this great view to check the usage of the data with SQL Azure under the subscription online. Since we have license on Pay as you go we have this kind of environment to check the daily usage and see to it so that we can limit the usage as per our requirement. This way it provides great control for the small business to deploy and use the application as per the usage required internally.

Microsoft provide a view “sys.database_usage” to query the database usage information for bills. Let us see the step by step process of how to get the bill information’s as shown in the below steps

Steps:

Log in to the Azure portal using the below 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 will 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 master database to query the complete details of the server and this 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. This is some like while we enter SQL Server Management Studio we will be asked to enter the login credentials

Once we entered valid credentials our page will look like below with the new SQL Azure Management portal

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

We can use this as a normal query analyzer which 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. Similar wise for a Business edition this logic can be extended as per 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 price is cost at 0.322258$  as per our usage which is shown in the above screen.

Conclusion:

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