Getting Started With SQL Azure series:

1. What is SQL Azure? 2. Creating First SQL Azure Database 3. Provisioning and Billing Model 4. SQL Azure Architecture 5. SQL Azure's security model 6. Migrating Databases to SQL Azure 7. Administration Tasks - I 8. Administration Tasks - II 9. Developing SQL Azure applications 10A. Getting started with SQL Azure reporting and SQL Azure Data SYNC 10B Conclusion

Now, in this module, we are going to see following administration tasks:

1. Managing firewall rules
2. Managing SQL Azure Servers, Databases and Logins
3. Troubleshoot connection issues
4. Monitoring database size and costs
5. Monitoring SQL Azure availability
6. Migrating to SQL Azure

Before we get started, I just want to point out that there are two ways in which you can manage SQL Azure:

1. SQL Server Management Studio 2008 R2 (SSMS) or above
2. Silverlight App to manage a SQL Azure database that is integrated in the Azure Management Portal

In this module, I will use SSMS since it is widely used. In addition, it is little more powerful than the web based silverlight app since it is a desktop-based tool. I would leave it up to you to explore the Manage Tool and study tasks that you cannot perform up there.

Powershell cmdlet’s are also available for you to use. You can download the cmdlet’s from http://wappowershell.codeplex.com/. I would be referring to SQL Azure cmdlet’s when I discuss – how to manage firewall rules and in, how to manage SQL Azure servers, databases and Logins.

Ok. Let us get started.

1. Managing Firewall Rules:

If you have been following the series, you know that we have discussed the security aspects of SQL Azure in Part 5. In that module, we discussed what SQL Azure firewall is. Just to recap:

The SQL Azure firewall protects data by blocking IP addresses that are not granted permission to access the SQL Azure Server

Now, how do you manage firewall rules?

One way is to manage SQL Azure Server’s firewall rules from Azure Management Portal:

Second way is to make use of the SQL Azure management cmdlet’s. it would let you

1. Add/Delete/update SQL Azure firewall rules

2.. List all SQL Azure firewall rules.

Third way is to manage the firewall rules via SSMS or the “manage tool”. you need to login to the master database of the server with the server level prinipal login to execute following queries:

List all firewall rules:

select * from sys.firewall_rules

Add/Update firewall rule:

E.g. for single IP: exec sp_set_firewall_rule N‘justanexample1′,’29.9.19.89′,’29.9.19.89′

E.g. for IP range: exec sp_set_firewall_rule N‘justanexample2′,’0.0.0.1′,’0.0.0.100′ 

If the firewall rule name already exists, then it would update it.

Delete firewall rule:

exec sp_delete_firewall_rule N‘justanexample1′

So we saw how to manage firewall rules.

2. Managing SQL Azure servers, databases and Logins:

- One way is to connect to the SQL Azure server via SSMS.

   a. You can add/drop databases here

   b. Carry our development on SQL Azure

   c. Manage logins

   Among other things. And the environment is similar to that of SQL server.

   Though, Just note that not all features of SQL server are supported by SQL Azure and so may not find options to carry out those tasks on SQL Azure via SSMS

This is how an Object explorer in SSMS looks when I connected it to a SQL Azure DB:

- You can create/drop servers from the Azure management portal

- You can also make use the Azure management cmdlet’s to 

   a.  Enumerate through SQL Azure servers for a subscription

   b. Add/Delete SQL Azure server

   c. Set Admin Password for the SQL Azure server

- you can connect to a SQL Azure Database through “manage tool” from the Azure management portal

- you can add/drop databases via Azure Management portal

- you can manage servers in different subscriptions from Azure management portal.

3. Troubleshoot connection issues

If you are facing connection issues, following are common troubleshooting steps:

Step 1: Check the SQL Azure credentials.

Step 2: Check the SQL Azure firewall

Step 3: If you are connecting to SQL Azure via a local machine, make sure that the outbound communication TCP communication on port 1433 is allowed.

Step 4: If you are connecting to SQL Azure service via SSMS (running locally) – make sure that the SSMS version is supported.

Step 5: Retry Connection because it may so have happened that your connection to the service may have been closed.

Step 6: Make sure that the SQL Azure database that you are trying to connect to is available via the Azure service Dashboard

For detailed article, please refer:   http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connectivity-troubleshooting-guide.aspx 

4. Monitoring database size and costs

First, let’s see how we can monitor the database size. And it is important, because if the database reaches the Max Size then the create/insert/update commands give an error. So all you can do is increase the MAXSIZE or Drop/Delete/Truncate data.

Now, This information is easily available on the Azure management portal and also in the “manage” tool  (Silverlight app to manage SQL Azure DB). In fact, the manage tool has a nice graphic to indicate how much space is left! have a look:

Now we can also monitor the size via TSQL with the help of a DMV i.e. sys.dm_db_partition_stats. Run the following query on an User database with login that has VIEW DATABASE STATE permission level :

select sum(reserved_page_count) * 8.0 / 1024 as “size in MB” from sys.dm_db_partition_stats

And Now let’s see how we can monitor the cost of the SQL Azure service. We have discussed the billing model in Part 3 of the series – And to view the cost incurred for using the service, you can go to the billing portal (MOCP) or you can run the following TSQL commands to monitor costs.

Execute following queries on Master database with server level principal login.

a. Query to calculate data transfer cost for the current month:

select sum(
    case when sys.bandwidth_usage.direction = 'Egress' 
            then (0.15 * quantity/ (1024*1024) )  
        when sys.bandwidth_usage.direction = 'Ingress' 
            then (0.00 * quantity/ (1024*1024))  
    end 
    ) as "cost"
 from sys.bandwidth_usage 
 where datepart(yy,time) = datepart(yy, getutcdate()) and    
 datepart(mm,time) = datepart(mm, getutcdate()) and 
 class = 'external'

b. Query to calculate cost based on database size for current month:

select sku, 
    sum (
    case when sys.database_usage.sku = 'web' 
            then (quantity*9.99/31)   
        when sys.database_usage.sku = 'business' 
            then (quantity*99.99/31)   
    end ) as 'cost'     
    from sys.database_usage     
    where datepart(yy,time) = datepart(yy, getutcdate()) and     
    datepart(mm,time) = datepart(mm, getutcdate())     
    group by sku

To know more about the queries. please refer:

http://beyondrelational.com/blogs/parasdoshi/archive/2011/04/18/viewing-sql-azure-database-billing-information-through-dynamic-management-view-s-sys-database-usage-and-sys-bandwidth-usage.aspx 

5.  Monitoring SQL Azure availability.

There is a service dashboard which let’s you view the status of many Azure components including SQL Azure. Visit: http://www.microsoft.com/windowsazure/support/status/servicedashboard.aspx 

6. Migrating to SQL Azure.

We have already discussed about migrating to SQL Azure. please refer, part 6 of this series. Just to recap, following table gives you a bird’s eye view on tools that you can employ to migrate SQL server databases to SQL Azure:

*More on Dac v2.0 : http://sqldacexamples.codeplex.com/

Conclusion

Thus in this module, we discussed few administration tasks in SQL Azure.

Update: Read part 9 - http://beyondrelational.com/blogs/parasdoshi/archive/2011/12/05/getting-started-with-sql-azure-part-9-developing-sql-azure-applications-lt-lt-paras-doshi.aspx