Archive

Posts Tagged ‘SQL Azure Admin’

Manage SQL Azure server using PowerShell

September 21, 2011 Leave a comment

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

http://wappowershell.codeplex.com/documentation

How to create Co-Administrator for SQL Azure Server ?

July 15, 2011 Leave a comment

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

image

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

image

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

 image

Click on “Add New Co-Admin”

image

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”

image

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

image

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

image

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

image

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

June 29, 2011 Leave a comment

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 http://msdn.microsoft.com/en-us/library/gg432987.aspx

Hope you all find this information useful !!!

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

May 21, 2011 Leave a comment

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

image

Click “Add”

 

image

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

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

How to Configure SQL Azure Firewall using Queries

May 4, 2011 1 comment

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

image

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’

image

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

Backup SQL Azure Database using Red Gate SQL Azure Backup Tool

May 4, 2011 1 comment

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

http://www.red-gate.com/products/dba/sql-azure-backup/

Currently its available for Free to download

After downloading, extract the zip file and run the program

image

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

image

Click on “Start Backup” to start the backup process

image

Status of Backup process will be displayed in the progress bar, depending upon the size of the database

image

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

Advantages:

Easy to Use

Fast

Small and Lightweight

Disadvantages:

Need to create a new database every time to create a backup