.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Post New Resource Bookmark and Share   

SQL Azure - Querying SQL Azure Database Usage Billing information

Posted By:Karthikeyan Anbarasan       Posted Date: June 28, 2011    Points: 200    Category:    URL: http://www.dotnetspark.com  

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.
 

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/

image

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

screenshot_02

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

image

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

image

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.

image

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

image

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

image

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

image

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

image

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.

image

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



     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend