How to monitor SQL Azure Database Usage Cost on daily basis by yourself


SQL Azure provides us the following system view “sys.database_usage”to get the details of database quantity used per day

How to calculate Daily Cost Usage ?

   1:  /* How to calculate Daily Cost of DB Usage */
   2:   
   3:  SELECT time, sku, quantity,  
   4:      CASE sku 
   5:          when 'Web' THEN Quantity * (9.99 / 30)
   6:          WHEN 'Business' THEN Quantity * (49.99 / 30)
   7:      END AS DailyCostofDB
   8:  FROM sys.database_usage order by time

image

How to calculate Monthly cost of Database Usage ?

   1:  /* How to calculate Monthly Cost of DB Usage */
   2:   
   3:  SELECT CAST(datepart(yy, TIME) AS Varchar) + ' - ' + DateName(mm, TIME) [Billing Month], 
   4:      SKU, 
   5:                    SUM   (     CASE WHEN USAGE.SKU = 'Web'
   6:                                      THEN (Quantity * 9.99/30)
   7:                                 WHEN USAGE.SKU = 'Business'
   8:                                            THEN (Quantity * 99.99/30)
   9:                                END ) AS CostInDollars
  10:  FROM              sys.Database_Usage USAGE
  11:  GROUP BY    CAST(datepart(yy, TIME) AS Varchar) + ' - ' + DateName(mm, TIME), SKU
  12:  ORDER BY CAST(datepart(yy, TIME) AS Varchar) + ' - ' + DateName(mm, TIME)
  13:   

image

You can use this queries in a SSIS Package to collect data from SQL Azure on daily basis and store it in local server and prepare reports across various different SQL Azure servers

I hope you all find this useful to monitor the cost of SQL Azure database usage

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group.

2 thoughts on “How to monitor SQL Azure Database Usage Cost on daily basis by yourself”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s