Archive

Posts Tagged ‘SQL Azure Bandwidth monitoring’

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

May 8, 2011 2 comments

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

Advertisements

How to monitor SQL Azure bandwidth usage by yourself ?

May 2, 2011 Leave a comment

SQL Azure provides the following System Views

sys.bandwidth_usage returns the following output columns

image

Using this System view you can calculate the Bandwidth usage and cost using the following queries

 

DECLARE @Cost AS MONEY = 0.15;

SELECT CAST (time AS DATE) AS DateofUse,

direction,

class,

time_period,

sum(Quantity) AS QuantityUsed,

sum(Quantity) * @Cost / (1048576) AS [CostOfUsage (in $)]

FROM sys.bandwidth_usage

WHERE class = ‘External’

GROUP BY CAST (time AS DATE), direction, class, time_period;

This query gives you the following detailed output

image

Using the following Pivot query, you can get date wise InBound and OutBound traffic and its associated cost

DECLARE @Cost AS MONEY = 0.15;

SELECT DateOfUse,

[Egress] AS [OutBoundTraffic],

[Egress] * @Cost / 1048576 AS [OutBoundCost],

[Ingress] AS [InBoundTraffic],

[Ingress] * @Cost / 1048576 AS [InBoundCost],

[Egress] + [Ingress] AS [TotalTraffic],

([Egress] + [Ingress]) * @Cost / 1048576 AS [TotalCost]

FROM (SELECT CAST (time AS DATE) AS DateofUse,

direction,  quantity

FROM sys.bandwidth_usage

WHERE class = ‘External’) AS BU PIVOT (SUM (Quantity) FOR Direction IN ([Egress], [Ingress])) AS Pvt;

image

You can use these queries to collect data from SQL Azure and monitor usage from your application itself on day to day basis.

%d bloggers like this: