Hands-On Lab
SQL Azure: SQL Azure Tips and Tricks
Lab version: 0.0.1
Last updated: 12/15/2009
Contents
Overview 3
Exercise 1: Manipulating the SQL Azure firewall via API’s 7
Task 1 – Create Sample Firewall Rules Via the SQL Azure Portal 7
Task 2 – Programmatically Retrieve Firewall Rules 11
Task 3 – Programmatically Add Firewall Rules 21
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 33
Exercise 3: Managing Connections – Throttling, latency and transactions 36
Exercise 4: Supportability – Usage Metrics 36
Task 1 – Establish a Connection to SQL Azure 37
Task 2 – Calculate SQL Azure Bandwidth Costs 41
Task 3 – Calculate SQL Azure Database Costs 46
Summary 51
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
The following is required to complete this hands-on lab:
· IIS 7 (with ASP.NET, WCF HTTP Activation)
· Microsoft .NET Framework 3.5 SP1
· Microsoft Visual Studio 2008 SP1 (or above)
· SQL Server 2005 Express Edition (or above)
· Windows Azure Tools for Microsoft Visual Studio (November 2009)
Setup
For convenience, much of the code used in this hands-on lab is available as Visual Studio code snippets. To check the prerequisites of the lab and install the code snippets:
1. Run the SetupLab.cmd script located in the lab's Source\Setup folder to check dependencies and install any missing prerequisites.
2. Once you have verified every prerequisite, follow the instructions to install the code snippets.
Using the Code Snippets
With code snippets, you have all the code you need at your fingertips. The lab document will tell you exactly when you can use them. For example,
To add this code snippet in Visual Studio, you simply place the cursor where you would like the code to be inserted, start typing the snippet name (without spaces or hyphens), in this case LabNameEx01RunmethodCS, watch as Intellisense picks up the snippet name, and then hit the TAB key twice once the snippet you want is selected. The code will be inserted at the cursor location.
Figure 1
Hit TAB to select the highlighted snippet.
Figure 2
Hit TAB again and the snippet will expand
To insert a code snippet using the mouse rather than the keyboard, right-click where you want the code snippet to be inserted, select Insert Snippet followed by My Code Snippets and then pick the relevant snippet from the list.
To learn more about Visual Studio IntelliSense Code Snippets, including how to create your own, please see http://msdn.microsoft.com/en-us/library/ms165392.aspx.
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 1
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 2
The SQL Azure portal
4. Select Firewall Settings tab.
Figure 3
Firewall settings tab
5. Select Add Rule.
Figure 4
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 5
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 AddressAllowed 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 6
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. In Visual Studio 2008, open the begin solution from File | Open | Project/Solution…
Figure 7
Opening a project or solution
3. In the Open Project dialog, browse to Ex01-ManipulatingFirewallviaAPI\begin inside the Source folder of this lab, select the FirewallAPI.sln file in the folder for the language of your preference (Visual C #or Visual Basic) and click Open.
4. In Visual Studio 2008, open the Solution Explorer from View | Solution Explorer.
Figure 8
Opening the Solution Explorer
Figure 9
Firewall API solution
Note: FirewallAPI is a plain .NET Framework 3.5 Console Application.
5. Add a new item to the project by right clicking on the FirewallAPI project and selecting Add | New Item… in the context menu:
Figure 10
Adding a new item
6. In the Add New Item dialog, choose the Code category and select Class in the Templates list. Enter the name FirewallRule.cs (for Visual C# Projects) or FirewallRule.vb (for Visual Basic Projects) and click Add.
7. Open the FirewallRule.cs file (for Visual C# projects) or FirewallRule.vb file (for Visual Basic projects). Replace the contents of the FirewallRule class with the appropriate code snippet.
(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall Rule class – C#)
C#
using System.Net;
namespace SqlAzureTipsAndTricks.FirewallAPI
{
public class FirewallRule
{
public FirewallRule(string name, string startIp, string endIp)
{
Name = name;
StartIp = IPAddress.Parse(startIp);
EndIp = IPAddress.Parse(endIp);
}
public string Name { get; set; }
public IPAddress StartIp { get; set; }
public IPAddress EndIp { get; set; }
}
}
(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall Rule class – VB)
Visual Basic
Imports System.Net
Public Class FirewallRule
Private _name As String
Private _startIp As IPAddress
Private _endIp As IPAddress
Public Sub New(ByVal name As String, ByVal startIp As String, ByVal endIp As String)
Me.Name = name
Me.StartIp = IPAddress.Parse(startIp)
Me.EndIp = IPAddress.Parse(endIp)
End Sub
Public Property Name() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property
Public Property StartIp() As IPAddress
Get
Return _startIp
End Get
Set(ByVal value As IPAddress)
_startIp = value
End Set
End Property
Public Property EndIp() As IPAddress
Get
Return _endIp
End Get
Set(ByVal value As IPAddress)
_endIp = value
End Set
End Property
End Class
Note: Here we are defining a FirewallRule class with three basic properties; Name, StartIp and EndIp. Notice that these properties correspond to the fields shown when we were entering the sample firewall rules via SQL Azure portal. We also define a simple constructor that takes on the three properties.
8. Add another class to the FirewallAPI project by right clicking on it and selecting Add | New Item… in the context menu:
Figure 11
Adding a new item
9. In the Add New Item dialog, choose the Code category and select Class in the Templates list. Enter the name Firewall.cs (for Visual C# Projects) or Firewall.vb (for Visual Basic Projects) and click Add.
10. Open the Firewall.cs file (for Visual C# projects) or Firewall.vb file (for Visual Basic projects) and replace the contents of the Firewall class file with the appropriate code snippet.
(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall class – C#)
C#
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace SqlAzureTipsAndTricks.FirewallAPI
{
public class Firewall
{
public string ServerName { get; set; }
private string Login { get; set; }
private string Password { get; set; }
private string MasterConnectionString { get; set; }
}
}
(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall class – VB)
Visual Basic
Imports System.Data.SqlClient
Public Class Firewall
' Fields
Private _login As String
Private _masterConnectionString As String
Private _password As String
Private _server As String
Public ReadOnly Property ServerName() As String
Get
Return Me._server
End Get
End Property
End Class
Note: Here we are defining a Firewall class with three basic properties; m_server, m_login, m_password. We will later use these properties to build a connection string to connect to SQL Azure.
11. Insert the following class constructor at the bottom of the Firewall class definition:
(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall Constructor – C#)
C#
public Firewall(string server, string login, string password)
{
ServerName = server;
Login = login;
Password = password;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = string.Format("tcp:{0}.database.windows.net,1433", ServerName);
builder.InitialCatalog = "master";
builder.UserID = Login;
builder.Password = Password;
builder.Pooling = true;
MasterConnectionString = builder.ToString();
}
(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall Constructor – VB)
Visual Basic
Public Sub New(ByVal server As String, ByVal login As String, ByVal password As String)
Me._server = server
Me._login = login
Me._password = password
Dim builder As New SqlConnectionStringBuilder
builder.DataSource = String.Format("tcp:{0}.database.windows.net,1433", Me._server)
builder.InitialCatalog = "master"
builder.UserID = Me._login
builder.Password = Me._password
builder.Pooling = True
Me._masterConnectionString = builder.ToString
End Sub
Note: Here we are defining a constructor for the Firewall class; it accepts three arguments: server, login and password. These arguments are then used to build a connection string that connects to the ‘master’ database in the given SQL Azure server.
12. Insert the following appropriate code snippet directly under the code in the previous step.
(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall GetRules method – C#)
C#
public List<FirewallRule> GetRules()
{
List<FirewallRule> rules = new List<FirewallRule>();
using (SqlConnection conn = new SqlConnection(MasterConnectionString))
using (SqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "SELECT name, start_ip_address, end_ip_address FROM sys.firewall_rules";
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
rules.Add(new FirewallRule(reader["name"] as string, reader["start_ip_address"] as string,
reader["end_ip_address"] as string));
}
}
}
return rules;
}
(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall GetRules method – VB)
Visual Basic
Public Function GetRules() As List(Of FirewallRule)
Dim rules As New List(Of FirewallRule)
Using conn As SqlConnection = New SqlConnection(Me._masterConnectionString)
Using cmd As SqlCommand = conn.CreateCommand
conn.Open()
cmd.CommandText = "SELECT name, start_ip_address, end_ip_address FROM sys.firewall_rules"
Using reader As SqlDataReader = cmd.ExecuteReader
Do While reader.Read
rules.Add(New FirewallRule(TryCast(reader.Item("name"), String), TryCast(reader.Item("start_ip_address"), String), TryCast(reader.Item("end_ip_address"), String)))
Loop
End Using
Return rules
End Using
End Using
End Function
Note: Each of the firewall rules that we specified via SQL Azure Portal are stored in a system table called “sys.firewall_rules” which resides in the “master” database we are connecting to. Here we are executing a T-SQL script to select out the name, start_ip_address and end_ip_address.
13. Press Ctrl + Shift + S to save all the changes.
14. Open the Program.cs file (for C# projects) or Module1.vb file (for Visual Basic projects).
15. Add the following to the file open in the previous step.
(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall PrintAllRules method – C#)
C#
static void PrintAllRules(Firewall firewall)