Manage SQL Azure server using PowerShell

Windows Azure Platform Powershell Cmdlets has been released and available for download at

It supports creating SQL Azure server, removing an existing SQL Azure server, managing the firewall and resetting the Administrator password.

For more detailed information, please visit


How to create Co-Administrator for SQL Azure Server ?

SQL Azure Co-Administrator option has been released in July 2011 Service Release

In this article, we will see How to add a Co-Administrator for your subscription

After logging in to Windows Azure Management Portal, click on “Database” tab


After navigating to Database section, click on “User Management” in Tool bar


In User Management screen, You can see the currently available Service Administrators and Co-Administrator for all available subscriptions


Click on “Add New Co-Admin”


In Add New Co-Administrator screen, specify the new users’ Windows Live ID and then select the subscription he can administer and then click “Ok”


Now we have successfully created a new Co-Administrator, Now he can login and create or drop servers, create or drop databases and new co-administrators as well


To remove a subscription or remove a Co-Administrator, click on Co-Administrator name and then click on “Manage Co-Admin”


If you have multiple subscriptions, you can remove the subscription which the co-admin should not manage, if you remove all subscriptions, the co-admin account will be removed automatically.

Now let us login using Co-Administrator login and see how the users are displayed


Since the Co-Administrator already has a subscription, he is displayed as Service Administrator for one of the account and Co-Administrator for other account.

Hope you all find this information helpful !!!

Programmatically Managing SQL Azure using REST API

In May 2011 SQL Azure Service Update 7, SQL Azure Management REST API was released.

SQL Azure Management REST API can be used for

    Currently it support Synchronous calls, there is no support Asynchronous calls.

All calls should be authenticated using X.509 Certificate and this certificate should be added as Management Certificate in Management Portal

Please read this article on How to create a Certificate

Hope you all find this information useful !!!

How to access SQL Azure from anywhere or How to disable SQL Azure Firewall ?

After reading my previous post regarding SQL Azure firewall configuration, one of my friend asked Whether there is an easy way to provide access any IP Address, so that he don’t have to worry about not able to connect using 3G wireless card to connect to SQL Azure

Yes, there is an easy way to do that…

Login to SQL Azure Management Portal and select the subscription and the SQL Azure server for which you want to provide access and then click on “Firewall Rules


Click “Add”



Click “Ok” to save the Firewall rule

Now you should be able to access this SQL Azure server from anywhere in the world.

Note: Please be aware that by adding the above rule, the SQL Azure firewall is pretty much disabled effectively. Obviously this is not a recommended practice.

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 */
   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


How to calculate Monthly cost of Database Usage ?

   1:  /* How to calculate Monthly Cost of DB Usage */
   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)


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

%d bloggers like this: