A SQL Azure tip a day (11) – The credit card constraint

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

This is the 11:th day I blog about SQL Azure, and it’s the 11:th day I open more addresses on the Firewall, because of my mobile internet.

Iguess the database soon will open to anyone in the whole world! I can’t wait until I get my fibre network! Once you sign up for the SQL Azure subscription, your credit card get charged for what you use. The charge will depend of number of databases, edition, size and outbound data transfer. If you want to keep track of the SQL Azure cost, there are two DMV:s you can use, sys.bandwidth_usage and sys.database_usage.

Since the service release in December there is a cost limit of $499.50 for every database larger than 40Gb, and you can use a script like this to keep track of your usage cost: DECLARE @priceWeb DECIMAL(4,2)=9.99; DECLARE @priceBusiness DECIMAL(4,2)=99.99; SELECT SKU, SUM(CASE WHEN U.SKU = ‘Web’ THEN (Quantity * @priceWeb/DAY(EOMONTH(U.TIME))) WHEN U.SKU = ‘Business’ THEN (CASE WHEN Quantity>40 THEN 40 ELSE  Quantity END * @priceBusiness/DAY(EOMONTH(U.TIME))) END ) AS [TotalCost] FROM sys.Database_Usage AS u WHERE u.TIME >=DATEADD(D,0-DAY(GetUTCDate()),GetUTCDate()) AND u.TIME <=EOMONTH(GetUTCDate()) GROUP BY SKU; To get the cost for the data transfer you can use the following script: DECLARE @cost_per_mb DECIMAL (3,2)=0.15 DECLARE @mb INT = 1024*1024; SELECT u.Time_Period, u.Direction, @cost_per_mb * u.BandwidthInKB/@mb AS [TotalTransferCost] FROM ( SELECT Time_Period, Direction, SUM(Quantity) AS [BandwidthInKB] FROM sys.Bandwidth_Usage u WHERE u.TIME >=DATEADD(D,0-DAY(GetUTCDate()),GetUTCDate()) AND u.TIME <=EOMONTH(GetUTCDate()) AND class = ‘External’ AND u.Direction = ‘Egress’ GROUP BY Time_Period, Direction) AS u;

Well, as you already know, you pay for the size of your database, and you pay for the data transfer. To be cost efficient, you really need to design your database correct to avoid redundant data and you need to stop using SELECT *, or including more columns than you need. Otherwise you’ll pay for an overhead that you actually can avoid. This is not a new recommendation, it has been there since the early year of relational databases, but the majority of all developers have ignored it since hardware got cheap. If your business is using a price model that is usage based you would gain a lot from using SQL Azure that is matching your pricing model.