Hands-On Lab
SQL Azure: SQL Azure Tips and Tricks
Lab version: 0.0.1
Last updated: 8/9/2011
Contents
OVERVIEW ............................................................................................................................................. 3
EXERCISE 1: MANIPULATING THE SQL AZURE FIREWALL VIA API’S .............................................. 5
Task 1 – Create sample firewall rules via the SQL Azure portal. ........................................................ 5
Task 2 – Programmatically retrieve firewall rules ............................................................................. 9
Task 3 – Programmatically add firewall rules .................................................................................. 20
Task 4 – Programmatically delete firewall rules .............................................................................. 24 EXERCISE 2: MANAGING CONNECTIONS – LOGGING SESSIONIDS ............................................... 28
Task 1 – Implement a SessionId class ............................................................................................. 28
Task 2 – Output SessionId to the console ....................................................................................... 35 EXERCISE 3: MANAGING CONNECTIONS – THROTTLING, LATENCY AND TRANSACTIONS........ 37
EXERCISE 4: SUPPORTABILITY – USAGE METRICS ........................................................................ 37
Task 1 – Establish a connection to SQL Azure ................................................................................. 38
Task 2 – Calculate SQL Azure bandwidth costs ............................................................................... 43
Task 3 – Calculate SQL Azure Database costs.................................................................................. 48 SUMMARY ............................................................................................................................................ 53
Overview
SQL Azure makes the power of Microsoft SQL Server available in a Cloud Hosted offering. Working with SQL Azure should be a familiar experience for most developers because, for the most part, it supports the same tooling and development practices currently used for on premise SQL Server applications. However, there are some small differences between working with SQL Azure and working with on-premise SQL Server. Some of these differences are inherent in the way that SQL Azure has been architected and some will only apply during the Community Technical Preview phase. This Hands-On-Lab will walk through a series of tips and tricks, which are important to working with SQL Azure, such as managing your connection in the event of throttling and querying the metrics views. Objectives
In this Hands-On Lab, you will learn about:
; Manipulating the SQL Azure firewall via API’s
; Managing connections
? Logging sessionId’s
? Throttling and latency
? Transactions
; Supportability
Prerequisites
To ensure that you can successfully complete all exercises of this lab. Please refer to the Setup
document for this lab. This can be located in this labs directory Setup folder. This document will guide you through ensuring all the dependencies for this lab are installed on your machine.
Figure 1
Setup Directory
Exercises
This Hands-On Lab comprises the following exercises:
1. Manipulating the SQL Azure firewall via API’s
2. Managing Connections – Logging SessionId’s
3. Managing Connections – Throttling, Latency and Transactions
4. Supportability – Usage Metrics
Estimated time to complete this lab: 60 minutes.
Exercise 1: Manipulating the SQL Azure firewall via API’s
The new firewall feature will deny all connections by default, thereby addressing a major security concern of storing data in the cloud. The new firewall features allows you to specify an allow list of IP addresses that can access your SQL Azure Server. New connections can be added via the SQL Azure portal as well as programmatically.
In this exercise, you will learn how to programmatically retrieve, create and delete firewall rules for your SQL Azure database.
Note: This exercise requires you to have a SQL Azure database. If you do not have one, please refer to the Introduction to SQL Azure lab to set one up.
To learn more about SQL Azure Firewall refer to the MSDN article:
SQL Azure Firewall:
http://msdn.microsoft.com/en-us/library/ee621782.aspx
How to: Configure the SQL Azure Firewall
http://msdn.microsoft.com/en-us/library/ee621783.aspx
Task 1 – Create sample firewall rules via the SQL Azure portal
SQL Azure allows users to add firewall rules via SQL Azure portal. In this task, we are going to add some sample firewall rules.
1. Go to the website https://sql.azure.com
2. Login with your Windows Live account.
Figure 2
Logging into the Azure Services Portal
3. Select the SQL Azure project from your project list that you wish to work with (if you have more
than one SQL Azure project).
Figure 3
The SQL Azure portal
4. Select Firewall Settings tab.
Figure 4
Firewall settings tab
5. Select Add Record.
Figure 5
Adding an exception to the firewall
6. Enter “Allowed Host 1” and your IP address shown in the Custom Firewall Settings dialog, into
IP Range, and click on Submit.
Figure 6
Adding an exception to the firewall
Note: You can specify an IP range or just one single IP address by entering the same IP address in the two fields. By specifying your IP Address now, it allows you to connect programmatically to this SQL Azure project later in this lab.
7. Repeat step 5 and 6 for the following values.
Name Start IP Address End IP Address
Allowed Range 1 192.168.0.0 192.168.0.255
Allowed Range 2 192.168.1.0 192.168.1.255
Note: These IP addresses are for demonstration purposes only. We will later retrieve these firewall rules programmatically. They have no real effect on your SQL Azure server, as they are private IP Addresses.
Your firewall settings should look like the following:
Figure 7
Firewall settings
Task 2 – Programmatically retrieve firewall rules
SQL Azure allows us to programmatically retrieve firewall rules; in this task we are going to retrieve the
firewall rules that we entered via SQL Azure portal in task 1. 1. Open Microsoft Visual Studio 2008 as Administrator, from Start | All Programs | Microsoft
Visual Studio 2008 right-click Microsoft Visual Studio 2008 and choose Run as Administrator.
2. If the User Account Control dialog appears, click Continue.
Figure 8
Running Visual Studio 2008 as administrator
3. In Visual Studio 2008, open the beginning solution from File | Open | Project/Solution…
Figure 9
Opening a project or solution
4. In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse
to C:\PDC09AzureTrainingKit\Labs\SqlAzureTipsAndTricks\Source\Ex01-
ManipulatingFirewallviaAPI\begin, select FirewallAPI.sln in the folder for the language of your
preference (Visual C# or Visual Basic) and click Open.
5. In Visual Studio 2008, open the Solution Explorer from View | Solution Explorer.
Figure 10
Opening the Solution Explorer
Figure 11
Firewall API solution
Note: FirewallAPI is a plain .NET Framework 3.5 Console Application.
6. Add a new item to the project by right clicking on the FirewallAPI project
7. In the context menu select Add | New Item…