Floating FB popout byF5debug

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

[more]

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/

clip_image002

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

clip_image003

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

clip_image004

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

clip_image006

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.

clip_image007

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

clip_image008

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

clip_image009

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

clip_image010

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

clip_image011

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.

clip_image012

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

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Comments (1) -

IT Support Dublin
IT Support Dublin
2/8/2013 10:43:03 AM #

It continually amazes me exactly how blog owners for example your self can find some time plus the commitment to carry on creating exceptional posts. Your website is fantastic and one of my own ought to read weblogs. I simply want to say thanks.

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter