Windows Azure Platform Powershell Cmdlets has been released and available for download at http://wappowershell.codeplex.com/releases/view/73463
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
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 !!!
In May 2011 SQL Azure Service Update 7, SQL Azure Management REST API was released.
SQL Azure Management REST API can be used for
- Create SQL Azure server – http://msdn.microsoft.com/en-us/library/gg715274.aspx
- Delete SQL Azure Server – http://msdn.microsoft.com/en-us/library/gg715285.aspx
- Reset Administrator Password for SQL Azure Server – http://msdn.microsoft.com/en-us/library/gg715272.aspx
- Get List of SQL Azure Servers available in the subscription – http://msdn.microsoft.com/en-us/library/gg715269.aspx
- Create Firewall Rules for specified SQL Azure Server – http://msdn.microsoft.com/en-us/library/gg715280.aspx
- Delete Firewall Rules for specified SQL Azure Server – http://msdn.microsoft.com/en-us/library/gg715277.aspx
- Get List of Available Firewall Rules for specified SQL Azure Server – http://msdn.microsoft.com/en-us/library/gg715278.aspx
- 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 http://msdn.microsoft.com/en-us/library/gg432987.aspx
Hope you all find this information useful !!!
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 “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.
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],
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
For connecting to SQL Azure, your IP Address should be added to SQL Azure Firewall, you can do this normally by SQL Azure Management Portal, but adding them one by one using Web Interface might be little difficult for SQL Azure Administrators
SQL Azure provides the following System Procedures to Manage SQL Azure Firewall policies
–To query the list of Active Firewall rules
SELECT * FROM sys.firewall_rules
To add a new IP Address or Range of IP Address to SQL Azure Firewall
–Query to Add or Update One or Range of IP Addresses to Firewall Policy
EXEC sys.sp_set_firewall_rule @name = N’Office’,
@start_ip_address = ‘XXX.XXX.XXX.1’,
@end_ip_address = ‘XXX.XXX.XXX.255’
–Query to Remove or Delete an IPAddress or IP Address Range from SQL Azure Firewall
EXEC sys.sp_delete_firewall_rule @name = N’Office’
If you look at the above screen shot, The policy with id 7 has been removed.
Using these System SPs, you can script your firewall policies and deploy them quickly on your new SQL Azure server, without having to use SQL Azure Management portal
After reading my last article one of my friend asked, whether there is any easy way to Backup SQL Azure databases, without writing scripts or custom programs, so I was searching for 3rd party products and found the Red Gate SQL Azure Backup Tool, its still in Beta, but works pretty good.
You can download it from the following link
Currently its available for Free to download
After downloading, extract the zip file and run the program
Specify the name of the SQL Azure Server name and provide username and password on click on Database selection to pick the database you want to backup
Next Enter the Local SQL Server Name (Destination Backup Server) and specify Credentials, then enter the name of the Local database to be created for backup from Azure
Warning: Existing database cannot be used for backup. You need to use a new database name only
Click “Next” to proceed
Click on “Start Backup” to start the backup process
Status of Backup process will be displayed in the progress bar, depending upon the size of the database
Backup has completed successfully !!!
Some of the features, I would like to see in this product is
1. Integration with SQL Server Management Studio or Red Gate SQL Backup Pro
2. Option to run from Command Line, so that we can it as Windows Task and automate it
3. Option to run backup for multiple databases
Easy to Use
Small and Lightweight
Need to create a new database every time to create a backup