SQL Azure Tips and Tricks

Document Sample
SQL Azure Tips and Tricks Powered By Docstoc
					Hands-On Lab
SQL Azure: SQL Azure Tips and Tricks
Lab version:    0.0.1
Last updated:   6/13/2010
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…
   Figure 12
   Adding a new item


8. 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.
9. 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.



10. Add another class to the FirewallAPI project by right clicking on it.

11. In the context menu select Add | New Items…




   Figure 13
   Adding a new item.


12. 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.
13. Open the Firewall.cs file (for Visual C# projects) or Firewall.vb file (for Visual Basic projects).
   Figure 14
   Firewall Class.


14. 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.



15. Insert the following code snippet directly above :
           a. C#: the closing bracket of public class Firewall
           b. VB: the End Class line
   (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.



16. 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.



17. In Visual Studio 2008, save all changes by selecting File | Save All.




   Figure 15
   Save All




18. Open the file:
           a. C#: Program.cs
             b. VB: Module1.vb
19. Insert the following code snippet:
             a. C#: in Program.cs directly under static void Main(string[] args) method.
             b. VB: in Modeule1.vb directly under the sub Main() method
   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall PrintAllRules method – C#)
   C#
   static void PrintAllRules(Firewall firewall)
   {
       var rules = firewall.GetRules();

       foreach (var rule in rules)
       {
           System.Console.WriteLine("Name:'{0}' StartIP:'{1}' EndIP:'{2}'",
   rule.Name, rule.StartIp, rule.EndIp);
       }

          Console.WriteLine();
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall PrintAllRules method – VB)
   Visual Basic
          Private Sub PrintAllRules(ByVal firewall As Firewall)

           Dim rules As List(Of FirewallRule) = firewall.GetRules
           Console.WriteLine("Current Firewall Rules for SQL Azure Srver: {0}",
   firewall.ServerName)
           Dim rule As FirewallRule
           For Each rule In rules
                Console.WriteLine("Name:'{0}' StartIP:'{1}' EndIP:'{2}'",
   rule.Name, rule.StartIp, rule.EndIp)
           Next
           Console.WriteLine()
       End Sub

       Note: Here we have a method that accepts a firewall as an argument, calls off to the GetRules
       method on that firewall instance to retrieve all instances. It then outputs all these rules to the
       console.



20. Insert the following code snippet directly above the:
             a. C#: static void Main(string[] args)
             b. VB: Sub Main()
   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Initialization – C#)
   C#
             static string serverName = "REPLACE_WITH_SERVERNAME";

             static string userName = "REPLACE_WITH_USERNAME";

           static string password = "REPLACE_WITH_PASSWORD";
   static Firewall firewall = new Firewall(serverName, userName, password);



   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Initialization – VB)
   Visual Basic
        Private serverName As String = "REPLACE_WITH_SERVERNAME"

        Private userName As String = "REPLACE_WITH_USERNAME"

        Private password As String = "REPLACE_WITH_PASSWORD"

       Private firewall As Firewall = New Firewall(serverName, userName,
   password)

     Note: Here we initialize the parameters serverName, userName and password. Make sure to
     replace them with your own details. We then initialize a firewall instance based on these
     parameters. This static firewall member will be used throughout this exercise. The userName
     field refers to username that can be seen in the Server Adminsitration page under
     Administrator Username.



21. Insert the following code snippet directly inside the:
           a. C#: static void Main(string[] args) method.
           b. VB: Sub Main() method
   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Main method – C#)
   C#
   Console.WriteLine("Current Firewall Rules...");
   PrintAllRules(firewall);
   System.Console.ReadLine();



   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Main method – VB)
   Visual Basic
   Console.WriteLine("Current Firewall Rules...")
   PrintAllRules(firewall)
        Console.ReadLine()

          Note: Here we pass the static firewall member to the PrintAllRules method that was created
          in an earlier step to output all the firewall rules to console.



    22. To start Debugging select, Debug | Start Debugging, or alternatively just press F5 on your
        keyboard.




        Figure 16
        Debugging


    23. You should see a console window open with the following output;




        Figure 17
        Expected output



          Note: These are the same firewall rules that we specified for this SQL Azure server via the SQL
          Azure portal.




Task 3 – Programmatically add firewall rules
In Task 1, we added firewall rules via the SQL Azure portal. In this task, we will add firewall rules
programmatically.
    1. In Solution Explorer, double click on Firewall class file to open it.
   Figure 18
   The Firewall class


2. Insert the following code snippet directly under the GetRules method.
   (Code Snippet – SQL Azure Tips and Tricks - Ex01 SetFirewallRule method – C#)
   C#
   public void SetFirewallRule(FirewallRule rule)
   {
       using (SqlConnection conn = new SqlConnection(MasterConnectionString))
       using (SqlCommand cmd = conn.CreateCommand())
       {
           conn.Open();
           cmd.CommandText = "sp_set_firewall_rule";
           cmd.CommandType = CommandType.StoredProcedure;

           cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = rule.Name;
           cmd.Parameters.Add("@start_ip_address", SqlDbType.VarChar).Value =
   rule.StartIp.ToString();
           cmd.Parameters.Add("@end_ip_address", SqlDbType.VarChar).Value =
   rule.EndIp.ToString();
           cmd.ExecuteNonQuery();
       }
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex01 SetFirewallRule 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: There is a stored procedure called ‘sp_set_firewall_rule’ in the master database in the
     October CTP release. In this method, we are establishing a connection to the master database
     and executing this stored procedure to add programmatically an additional firewall rule to the
     SQL Azure Server.



3. In Solution Explorer, double click on Program.cs or Module1.vb to open it.
4. Replace the content of:
          a. C#: static void Main(string[] args) method
          b. VB: Sub Main() method
   with the following code snippet
   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Call SetFirewallRule – C#)
   C#
   var startIp = "10.0.0.0";
   var endIp = "10.0.0.255";
   var name = "IP Example 1";
   var firewallRule = new FirewallRule(name, startIp, endIp);
   firewall.SetFirewallRule(firewallRule);
   Console.WriteLine("After Adding a New Rule...");
   PrintAllRules(firewall);
   System.Console.ReadLine();



   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Call SetFirewallRule – VB)
   Visual Basic
   Dim startIp As String = "10.0.0.0"
   Dim endIp As String = "10.0.0.255"
   Dim name As String = "IP Example 1"
   Dim firewallRule As New FirewallRule(name, startIp, endIp)
   firewall.SetFirewallRule(firewallRule)
   Console.WriteLine("After Adding a New Rule...")
   PrintAllRules(firewall)
   Console.ReadLine()



     Note: Here we initialize a FirewallRule instance with a startIp, endIp and a name. We then
     pass this FirewallRule to the SetFirewallRule() method we defined in the last step in the
     Firewall class
     We then output all the static Firewall member’s firewall rules to the console to make sure that
     the new firewall rule has indeed been added.



5. To start Debugging select, Debug | Start Debugging, or alternatively just press F5 on your
   keyboard.




   Figure 19
   Debugging


6. You should see a console window open with the following output;




   Figure 20
   Expected output

     Note: Notice that the newly created firewall rule is also listed in there.



7. You can also confirm that this firewall rule has been added correctly via the SQL Azure portal.
        Figure 21
        Updated firewall settings



Task 4 – Programmatically delete firewall rules
SQL Azure server firewall rules can be deleted via the SQL Azure Portal; in this task, we are going to look
at how to do this programmatically.
    1. In the Solution Explorer, double click on Firewall class to open it.
    2. Insert the appropriate following code snippet directly under the method :
                a. C#: public void SetFirewallRule(FirewallRule rule)
                b. VB: Public Sub SetFirewallRule(ByVal rule As FirewallRule)
        (Code Snippet – SQL Azure Tips and Tricks - Ex01 DeleteFirewallRule method – C#)
        C#
        public void DeleteFirewallRule(string name)
        {
            using (SqlConnection conn = new SqlConnection(MasterConnectionString))
            using (SqlCommand cmd = conn.CreateCommand())
            {
                conn.Open();
                cmd.CommandText = "sp_delete_firewall_rule";
                cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
               cmd.ExecuteNonQuery();
          }
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex01 DeleteFirewallRule method – VB)
   Visual Basic
   Public Sub DeleteFirewallRule(ByVal name As String)
           Using conn As SqlConnection = New
   SqlConnection(Me._masterConnectionString)
               Using cmd As SqlCommand = conn.CreateCommand
                   conn.Open()
                   cmd.CommandText = "sp_delete_firewall_rule"
                   cmd.CommandType = CommandType.StoredProcedure
                   cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name
                   cmd.ExecuteNonQuery()
               End Using
           End Using
   End Sub

       Note: There is a stored procedure called ‘sp_delete_firewall_rule’ in the master database in
       the October CTP release. In this method, we are defining a method that establishes a
       connection to the master database and executes this stored procedure to programmatically
       delete the firewall rule from SQL Azure Server. This firewall rule is identified by its name that is
       passed in as a parameter in this method.



3. In Solution Explorer, double click on Program.cs or Module1.vb to open it.
4. Replace the content of static void Main(string[] args) or Sub Main() method with the
   following code snippet.
   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Call DeleteFirewallRule – C#)
   C#
   firewall.DeleteFirewallRule("IP Example 1");
   Console.WriteLine("After Deleting Firewall Rule, IP Example 1");
   PrintAllRules(firewall);

   System.Console.ReadLine();



   (Code Snippet – SQL Azure Tips and Tricks - Ex01 Call DeleteFirewallRule – VB)
   Visual Basic
   firewall.DeleteFirewallRule("IP Example 1")
   Console.WriteLine("After Deleting Firewall Rule, IP Example 1")
   PrintAllRules(firewall)

   Console.ReadLine()

     Note: Here we directly call into the DeleteFirewallRule method with the firewall rule that we
     created in the last task, with the name “IP Example 1”.



5. To start Debugging select, Debug | Start Debugging, or alternatively just press F5 on your
   keyboard.




   Figure 22
   Start Debugging


6. You should see a console window open with the following output:




   Figure 23
   Expected Output



     Note: Notice that the firewall-rule “IP Example 1” that we created in the last task is no longer
     in the list.



7. You can also confirm that this firewall rule has been deleted correctly via the SQL Azure portal.
Figure 24
Firewall settings
Exercise 2: Managing Connections –
Logging SessionIds
Just like on-premise Microsoft SQL Servers, SQL Azure supports the function CONTEXT_INFO() as well. It
contains a SessionId value, which is returned when a connection is established to SQL Azure. They are
critical to debugging errors especially when dealing with Microsoft support, therefore developers should
get in the habit of logging these.
In the scenario where a front end website is hosted in Windows Azure, developers can very easily
leverage the Windows Azure’s RoleManager.WriteToLog() implementation to log messages and raise
alerts.
In this exercise, you are going to walk through the simple steps on how to retrieve this SessionId
programmatically.

Note: To learn more about context information in SQL Server and RoleManager in Windows Azure, refer
to the following MSDN articles and their related links:
Using Session Context Information:
http://msdn.microsoft.com/en-us/library/ms189252.aspx
CONTEXT_INFO (Transact-SQL):
http://msdn.microsoft.com/en-us/library/ms180125.aspx
RoleManager class:
http://msdn.microsoft.com/en-us/library/microsoft.servicehosting.serviceruntime.rolemanager.aspx
RoleManager.WriteToLog method:
http://msdn.microsoft.com/en-
us/library/microsoft.servicehosting.serviceruntime.rolemanager.writetolog.aspx




Task 1 – Implement a SessionId class
In this task, we will implement a SessionId class that will create a SQL connection to the master database
in the SQL Azure server, and then retrieve the SessionId for this connection.
    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 25
   Run as administrator


3. In Visual Studio 2008, open the beginning solution from File | Open | Project/Solution…




   Figure 26
   Opening a project or a solution


4. Browse to the file “%WindowsAzurePlatformKit%\Labs\SQLAzureTipsTricks\Source\Ex02 -
   Logging Connection Session Id\begin\ConnectionLoggingSessionId.sln” .If you wish instead to
   use VB code, browse to the file
   “%WindowsAzurePlatformKit%\Labs\SQLAzureTipsTricks\Source\ Ex02-
   LoggingConnectionSessionId\begin\VB\ConnectionLoggingSessionId.sln”
   Figure 27
   Begin solution


5. In Visual Studio 2008, open Solution Explorer from View | Solution Explorer.




   Figure 28
   Opening the Solution Explorer




   Figure 29
   The ConnectionLogginSessionId solution



     Note: ConnectionLoggingSessionId is a plain .NET Framework 3.5 Console Application.



6. Add a new item to the project by right clicking on the ConnectionLoggingSessionId project.
7. In the context menu select Add | New Item…
   Figure 30
   Adding a New Item


8. In the Add New Item – ConnectionLogginSessionId dialog, select either: Visual C# Items | Code
   in Categories window, and Class in the Templates window or Common Items | Code in the
   Categories window and Class in the Templates window.
9. Enter SessionId for the class name.
10. Click Add.




   Figure 31
   Adding a New SessionId
11. Open the SessionId class by double clicking on the file in Solution Explorer.




   Figure 32
   The SessionId Class


12. Replace the content of the SessionId class with the following appropriate code snippet:
   (Code Snippet – SQL Azure Tips and Tricks - Ex02 SessionId class – C#)
   C#
   using System;
   using System.Data.SqlClient;

   namespace SqlAzureTipsAndTricks.ConnectionLoggingSessionId
   {
       public class SessionId
       {
           public const int SessionIdInfoMessageErrorNumber = 40608;

             private const string DomainBaseAddress = "database.windows.net";

             private string m_server;

             private string m_login;

             private string m_password;

             public SessionId(string server, string login, string password)
             {
                 m_server = server;
                 m_login = login;
                 m_password = password;
             }
        }
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex02 SessionId class – VB)
   Visual Basic
   Imports System
   Imports System.Data.SqlClient

   Public Class SessionId

        Public Const SessionIdInfoMessageErrorNumber As Integer = &H9EA0

        Private Const DomainBaseAddress As String = "database.windows.net"


        Private m_login As String

        Private m_password As String

        Private m_server As String

       Public Sub New(ByVal server As String, ByVal login As String, ByVal
   password As String)
           Me.m_server = server
           Me.m_login = login
           Me.m_password = password
       End Sub

   End Class

     Note: Here we have defined a class called SessionId, with class member variables that are later
     used to build a connection string to SQL Azure. We have also defined a simple constructor that
     takes the SQL Azure server name, login and password for the server.



13. Insert the following code snippet directly under the SessionId class constructor:
   (Code Snippet – SQL Azure Tips and Tricks - Ex02 CreateSqlConnectionToMaster method – C#)
   C#
             public SqlConnection CreateSqlConnectionToMaster(out Guid sessionId)
             {
                 sessionId = Guid.Empty;

               SqlConnectionStringBuilder builder = new
   SqlConnectionStringBuilder();
               builder.DataSource = string.Format("tcp:{0}.{1},1433", m_server,
   DomainBaseAddress);
               builder.InitialCatalog = "master";
               builder.UserID = m_login;
               builder.Password = m_password;
               builder.Pooling = true;

                  SqlConnection conn = new SqlConnection(builder.ToString());
               conn.Open();

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT CONVERT(NVARCHAR(36),
CONTEXT_INFO())";
                string contextInfo = (string)cmd.ExecuteScalar();
                sessionId = new Guid(contextInfo);
            }

               return conn;
         }



(Code Snippet – SQL Azure Tips and Tricks - Ex02 CreateSqlConnectionToMaster method – VB)
Visual Basic
Public Function CreateSqlConnectionToMaster(ByRef sessionId As Guid) As
SqlConnection
    sessionId = Guid.Empty
    Dim builder As New SqlConnectionStringBuilder
    builder.DataSource = String.Format("tcp:{0}.{1},1433", Me.m_server,
"database.windows.net")
    builder.InitialCatalog = "master"
    builder.UserID = Me.m_login
    builder.Password = Me.m_password
    builder.Pooling = True
    Dim conn As New SqlConnection(builder.ToString)
    conn.Open()
    Using cmd As SqlCommand = conn.CreateCommand
        cmd.CommandText = "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())"
        Dim contextInfo As String = CStr(cmd.ExecuteScalar)
        sessionId = New Guid(contextInfo)
    End Using
    Return conn
End Function

 Note: Here we define a method called CreateSqlConnectionToMaster(). We first build out a
 SQL connection string with the correct DataSource, InitialCatalog, UserID, Password and
 Pooling values set.
 Note that just like default SQL Server On-Premise deployment, SQL Azure server runs on TCP
 port 1433.
 We then establish a connection to our SQL Azure server with this connection string, run the T-
 SQL script to retrieve CONTEXT_INFO() and pass it out as a sessionId via an out parameter on
 the method.
Task 2 – Output SessionId to the console
In your applications that uses the SQL Azure server backend, you should log this SessionId appropriately
with your application specific logging mechanism; for instance: in the Windows Azure scenario, we
should use RoleManager.WriteToLog() implementation to log this SessionId. This is important when
raising a support call with the Microsoft SQL Azure support team.
In this task, we will simply output the SessionId to the console for demonstration purposes.
    1. In Solution Explorer, double click on Program.cs or Module1.vb




        Figure 33
        ConnectionLoggingSessionId project


    2. Insert the following appropriate code snippet directly inside the method remembering to
       replace the serverName, username and password values with your own SQL Azure server
       settings
        (Code Snippet – SQL Azure Tips and Tricks - Ex02 Main method– C#)
        C#
                       var   serverName = "REPLACE_WITH_SEVERNAME";
                       var   userName = "REPLACE_WITH_USERNAME";
                       var   password = " REPLACE_WITH_PASSWORD ";
                       var   sessionId = new SessionId(serverName, userName, password);

                       #region Create SQL Server connection to SQL Azure

                       Guid SqlSessionId;

                       sessionId.CreateSqlConnectionToMaster(out SqlSessionId);
                       Console.WriteLine("Sql Connection Session Id: {0}", SqlSessionId);

                       Console.ReadLine();
                       #endregion



        (Code Snippet – SQL Azure Tips and Tricks - Ex02 Main method – VB)
   Visual Basic
   Dim   serverName As String = " REPLACE_WITH_SEVERNAME "
   Dim   userName As String = " REPLACE_WITH_USERNAME "
   Dim   password As String = " REPLACE_WITH_PASSWORD "
   Dim   sessionId = New SessionId(serverName, userName, password)

   Dim sqlSessionId As Guid

   sessionId.CreateSqlConnectionToMaster(sqlSessionId)
   Console.WriteLine("Sql Connection Session Id: {0}", SqlSessionId)
   Console.ReadLine()

     Note: Here we initialize the variables serverName, username and password for SQL Azure
     server, and create a SessionId instance based on these values. Please remember to replace
     these values to with your own SQL Azure server settings.
     We then call off to the CreateSqlConnectionToMaster() method we defined in the last task, it
     establishes a connection to the SQL Azure server and returns a SessionId.
     We then simply write this session Id to the console.



3. From Visual Studio, select Debug | Start Debugging. Or just press F5 on your keyboard to start
   debugging. You should see a GUID value written to the console;




   Figure 34
   Expected Output
Exercise 3: Managing Connections –
Throttling, latency and transactions
When working against a SQL Azure database you should be aware of the effects of Throttling, Latency
and Transactions.
Throttling, where SQL Azure automatically restricts new (or drops existing) connections, can occur when
your database (or server hosting your database) is under heavy load. This ensures you server and
database are protected from large numbers of connections and undo load.
However, it does mean you need to write your applications to handle a connection to dropping or
transactions failing.
There are not any SQL Azure specific techniques for addressing these issues. In fact, addressing these
issues is no different from handling them in the applications you build against on-premise databases.
Some common techniques include,

       Hold a connection open for as short a time as possible.
       Before issuing a command against a connection check it is still open, re-open it if SQL Azure has
        closed it. You may decide to implement a queue or other retry mechanism.
       Ensure you wrap all database calls in transactions – all exceptions (including ones thrown due to
        Throttling) should result in the transaction being rolled back.



Exercise 4: Supportability – Usage
Metrics
Upon commercial availability for Windows Azure, the following simple consumption-based pricing
model for SQL Azure will apply:
There are two editions of SQL Azure databases: Web Edition with a 1 GB data cap is offered at USD
$9.99 per month; Business Edition with a 10 GB data cap is offered at USD $99.99 per month.
Bandwidth across Windows Azure, SQL Azure and .NET Services will be charged at $0.10/GB for ingress
data and $0.15 / GB for egress data.

 Note: For more information on the Microsoft Windows Azure cloud computing pricing model refer to:
 Windows Azure Platform Pricing:
 http://www.microsoft.com/windowsazure/pricing/
 Confirming Commercial Availability and Announcing Business Model:
 http://blogs.msdn.com/windowsazure/archive/2009/07/14/confirming-commercial-availability-and-
 announcing-business-model.aspx

In this exercise, we will go through the mechanisms on how to programmatically calculate bandwidth
and database costs.



Task 1 – Establish a connection to SQL Azure
In this simple task, we will implement a class called Metrics and create a SQL connection to SQL Azure
database.
    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 35
        Run as administrator


    3. In Visual Studio 2008, open the beginning solution from File | Open | Project/Solution…
    4. If you wish to use C# code, browse to the file:
        “%WindowsAzurePlatformKit%\Labs\SqlAzureTipsAndTricks\Source\Ex04-
        Metrics\begin\C#\Metrics.sln”
   If you wish to use VB code, browse to the file:
   “%WindowsAzurePlatformKit%\Labs\SqlAzureTipsAndTricks\Source\Ex04-
   Metrics\begin\VB\Metrics.sln”




   Figure 36
   The Metrics begin solution


5. In Visual Studio 2008, open Solution Explorer from View | Solution Explorer.




   Figure 37
   Opening the Solution Explorer




   Figure 38
   The Metrics solution

     Note: Metrics is a plain .NET Framework 3.5 Console Application.



6. Add a new item to the project by right clicking on the Metrics project
7. In the context menu select Add | New Item…
   Figure 39
   Adding a new item


8. In the Add New Item – Metrics dialog, select either: Visual C# Items | Code in Categories
   window, and Class in the Templates window or Common Items | Code in the Categories
   window and Class in the Templates window. Enter Metrics for the Name.
9. Click Add.




   Figure 40
   Adding a new Metrics class


10. Open the Metrics class by double clicking on the file in Solution Explorer.
   Figure 41
   The Metrics class


11. Replace the content of the Metrics class with the appropriate code snippet.
   (Code Snippet – SQL Azure Tips and Tricks - Ex04 Metrics class – C#)
   C#
   using    System;
   using    System.Collections.Generic;
   using    System.Linq;
   using    System.Text;
   using    System.Data.SqlClient;
   using    System.Data;

   namespace SqlAzureTipsAndTricks
   {
       public class Metrics
       {
           public const decimal IngressCost = 0.10M;

             public const decimal EgressCost = 0.15M;

             public const decimal BusinessSkuCost = 100;

             public const decimal WebSkuCost = 10;

             private string m_server;

             private string m_login;

             private string m_password;

             private string m_masterConnectionString;
        }
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex04 Metrics class – VB)
   Visual Basic
   Imports System.Data.SqlClient

   Public Class Metrics

        Public Const IngressCost As Decimal = 0.1

        Public Const EgressCost As Decimal = 0.15

        Public Const BusinessSkuCost As Decimal = 100

        Public Shared ReadOnly WebSkuCost As Decimal = 10

        Private m_server As String


        Private m_login As String

        Private m_password As String

        Private m_masterConnectionString As String

   End Class

     Note: Here we define a few parameters that represent pricings for SQL Azure.
     IngressCost is incoming bandwidth cost per GB in US dollars.
     EgressCost is outgoing bandwidth cost per GB in US dollars.
     BusinessSkuCost is the cost for SQL Azure Business SKU per month in US dollars.
     WebSkuCost is the cost for SQL Azure Web SKU per month in US dollars.
     m_server, m_login, m_password are connection details which we will use to establish a
     connection to SQL Azure.



12. Insert the following code snippet directly under the line
           a. C#: private string m_masterConnectionString;
           b. VB: Private m_matserConnectionString As String
   (Code Snippet – SQL Azure Tips and Tricks - Ex04 Metrics ctor – C#)
   C#
   public Metrics(string server, string login, string password)
   {
       m_server = server;
       m_login = login;
            m_password = password;

           SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
           builder.DataSource = string.Format("tcp:{0}.database.windows.net,1433",
       m_server);
           builder.InitialCatalog = "master";
           builder.UserID = m_login;
           builder.Password = m_password;
           builder.Pooling = true;
           m_masterConnectionString = builder.ToString();
       }



       (Code Snippet – SQL Azure Tips and Tricks - Ex04 Metrics ctor – VB)
       Visual Basic
       Public Sub New(ByVal server As String, ByVal login As String, ByVal password
       As String)
           Me.m_server = server
           Me.m_login = login
           Me.m_password = password
           Dim builder As New SqlConnectionStringBuilder
           builder.DataSource = String.Format("tcp:{0}.database.windows.net,1433",
       Me.m_server)
           builder.InitialCatalog = "master"
           builder.UserID = Me.m_login
           builder.Password = Me.m_password
           builder.Pooling = True
           Me.m_masterConnectionString = builder.ToString
       End Sub

         Note: Here we define a class constructor that takes the server name, login and password for
         the SQL Azure server as parameters. Within the constructor, we build a connection string
         connecting to the master database in the given SQL Azure server.




Task 2 – Calculate SQL Azure bandwidth costs
SQL Azure server bandwidth usage is clocked against each database that resides in the SQL Azure server,
from the minute that the database is provisioned until the database is dropped. Bandwidth usage
metrics are logged to a system database table called “sys.bandwidth_usage” in the master database.
This table includes the following column schema;

         Column name       Details

         time              The time stamp when the bandwidth usage took place.

         database_name     The name of the database the bandwidth is used on
         direction           Only takes two values; Ingress or Egress meaning incoming or outgoing traffic

         class               Only takes two values; External or Internal; meaning external or internal traffic.

         quantity            The amount of data clocked in KB.
In this task, we will programmatically calculate the bandwidth usage cost for the designated SQL Azure
server.
    1. Directly under the code we inserted in the last step, insert the appropriate code snippet.
        (Code Snippet – SQL Azure Tips and Tricks - Ex04 GetBandwidthCost method – C#)
        C#
        /// <summary>
        /// Computes the accumulated cost of bandwidth for the current month.
        /// </summary>
        public decimal GetBandwidthCost()
        {
            decimal cost = 0;

            using (SqlConnection conn = new SqlConnection(m_masterConnectionString))
            using (SqlCommand cmd = conn.CreateCommand())
            {
                // Only external traffic is billed
                conn.Open();
                cmd.CommandText = "SELECT direction, sum(quantity) / (1024 * 1024) AS
        quantity " +
                                  "FROM sys.bandwidth_usage " +
                                  "WHERE time >= @monthStart AND class = 'External' "
        +
                                  "GROUP BY direction ";

                DateTime monthStartDate = new DateTime(DateTime.UtcNow.Year,
        DateTime.UtcNow.Month, 1);
                cmd.Parameters.Add("@monthStart", SqlDbType.DateTime).Value =
        monthStartDate;

                     using (SqlDataReader reader = cmd.ExecuteReader())
                     {
                         while (reader.Read())
                         {
                             if ((string)reader["direction"] == "Ingress")
                             {
                                  cost += (decimal)reader["quantity"] * IngressCost;
                             }
                             else
                             {
                                  cost += (decimal)reader["quantity"] * EgressCost;
                             }
                         }
         }
    }

    return cost;
}



(Code Snippet – SQL Azure Tips and Tricks - Ex04 GetBandwidthCost method – VB)
Visual Basic
''' <summary>
''' Computes the accumulated cost of bandwidth for the current month.
''' </summary>
Public Function GetBandwidthCost() As Decimal

    Dim cost As Decimal = 0

    Using conn As SqlConnection = New
SqlConnection(Me.m_masterConnectionString)

         Using cmd As SqlCommand = conn.CreateCommand

            ' Only external traffic is billed
            conn.Open()
            cmd.CommandText = "SELECT direction, sum(quantity) / (1024 * 1024)
AS quantity FROM sys.bandwidth_usage WHERE time >= @monthStart AND class =
'External' GROUP BY direction "
            Dim monthStartDate As New DateTime(DateTime.UtcNow.Year,
DateTime.UtcNow.Month, 1)
            cmd.Parameters.Add("@monthStart", SqlDbType.DateTime).Value =
monthStartDate

               Using reader As SqlDataReader = cmd.ExecuteReader
                   Do While reader.Read
                        If (CStr(reader.Item("direction")) = "Ingress") Then
                             cost = (cost + (CDec(reader.Item("quantity")) * 0.1))
                        Else
                             cost = (cost + (CDec(reader.Item("quantity")) * 0.15))
                        End If
                   Loop
               End Using

               Return cost

        End Using
    End Using
End Function
       Note: Here we define a method that calculates the bandwidth costs for our SQL Azure server.
       Because Ingress and Egress data have different charge rates, we will need to group quantity
       data by directions.
       We first open a connection to the master database, execute a SQL command to retrieve the
       sum of quantity data in GB, grouped by directions. Since we are only interested in the current
       month’s data charge, we filter the result set by passing a DateTime parameter representing
       the start of the current month.
       Lastly, we apply the respective charge rates to Ingress and Egress data returned, aggregate
       these costs and return it.



2. Open Program.cs or Module1.vb by double clicking on the file in Solution Explorer.

3. Replace the contents of the file with the following code snippet. Remember to replace the
   servername, logon and password with your SQL Azure account details.

   (Code Snippet – SQL Azure Tips and Tricks - Ex04 Main method – C#)
   C#
   using      System;
   using      System.Collections.Generic;
   using      System.Linq;
   using      System.Text;

   namespace SqlAzureTipsAndTricks
   {
       class Program
       {
           static void Main(string[] args)
           {
               var serverName = "Your SQL Azure Server Name"; // e.g.; hkp3qu6woc
               var login = "Your SQL Azure Server Login name";
               var password = "Your SQL Azure Server login password";

                    var metrics = new Metrics(serverName, login, password);

               Console.WriteLine("Bandwidth cost USD$: {0}",
   metrics.GetBandwidthCost());

                    Console.ReadKey();
               }
          }
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex04 Main method – VB)
   Visual Basic
   Module Module1

        Sub Main()

           Dim serverName As String = "Your SQL Azure Server Name" // e.g.
   hkp3qu6woc
           Dim login As String = "Your SQL Azure Server Login name"
           Dim password As String = "Your SQL Azure Server login password"

             Dim metrics As New Metrics(serverName, login, password)

           Console.WriteLine("Bandwidth cost USD$: {0}",
   metrics.GetBandwidthCost)
           Console.ReadKey()

        End Sub

   End Module

     Note: Here we declare and initialize variables for SQL Azure server name, login and password,
     create an instance of the Metrics object with these details. Remember to replace them with
     your SQL Azure account details. We then invoke the GetBandwitdthCost() method on the
     Metrics instance we defined earlier in this task.



4. Start debugging, from Visual Studio, Debugging | Start Debugging. Or just press F5 on your
   keyboard.




   Figure 42
   Debugging


5. You should see a console window open with the following output;




   Figure 43
   Expected Output
         Note: It outputs the cost in USD$ on the designated SQL Azure server.


Task 3 – Calculate SQL Azure Database costs
The SQL Azure server database charges on a database per month model. The table that holds database
usage data is “sys.database_usage” in the master database. This table keeps track of how many
databases there are for each SKU and when this number was changed.
So every time a new database is provisioned or an existing database is deleted, it logs an entry in this
table. This table includes the following column schema;

         Column name         Details

         Time                The date on which an existing database was deleted or new database is provisioned.

         SKU                 Business or Web

         Quantity            The number of databases for the respective SKU after the change.



In this task we programmatically calculate the Database costs for SQL Azure server.
    1. Open the Metrics class by double clicking on the file in Solution Explorer.




        Figure 44
        The Metrics class


    2. Directly under the GetBandwidthCost method, insert the following code snippet.

        (Code Snippet – SQL Azure Tips and Tricks - Ex04 GetDatabaseCost – C#)
        C#
        public decimal GetDatabaseCost(string sku)
        {
            decimal cost = 0;

            DateTime startTime = new DateTime(DateTime.UtcNow.Year,
        DateTime.UtcNow.Month, 1);
            DateTime cutoffTime = DateTime.UtcNow;
    using (SqlConnection conn = new SqlConnection(m_masterConnectionString))
    {
        conn.Open();
        decimal lastKnownQuantity = 0;

        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "SELECT TOP(1) time, quantity " +
                              "FROM " +
                              "(" +
                              " SELECT TOP(1) time, quantity FROM
sys.database_usage WHERE time <= @start_time AND sku = @sku ORDER BY time DESC
" +
                              " UNION ALL " +
                              " SELECT TOP(1) time, quantity FROM
sys.database_usage WHERE time >= @start_time AND sku = @sku ORDER BY time ASC
" +
                              ")T " +
                              "ORDER BY T.time ASC ";

               cmd.Parameters.Add("@start_time", SqlDbType.DateTime).Value =
startTime;
               cmd.Parameters.Add("@sku", SqlDbType.NVarChar, 50).Value = sku;

               using (SqlDataReader reader = cmd.ExecuteReader())
               {
                   while (reader.Read())
                   {
                       lastKnownQuantity = (decimal)reader["quantity"];
                       DateTime lowDate = (DateTime)reader["time"];
                       if (lowDate > startTime)
                       {
                           startTime = lowDate;
                       }
                   }
               }
         }
    }
    return cost;
}



(Code Snippet – SQL Azure Tips and Tricks - Ex04 GetDatabaseCost – VB)
Visual Basic
Public Function GetDatabaseCost(ByVal sku As String) As Decimal
    Dim cost As Decimal = 0
    Dim startTime As New DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month,
1)
       Dim cutoffTime As DateTime = DateTime.UtcNow
       Using conn As SqlConnection = New
   SqlConnection(Me.m_masterConnectionString)

             conn.Open()
             Dim lastKnownQuantity As Decimal = 0

             Using cmd As SqlCommand = conn.CreateCommand

               cmd.CommandText = "SELECT TOP(1) time, quantity FROM ( SELECT
   TOP(1) time, quantity FROM sys.database_usage WHERE time <= @start_time AND
   sku = @sku ORDER BY time DESC UNION ALL SELECT TOP(1) time, quantity FROM
   sys.database_usage WHERE time >= @start_time AND sku = @sku ORDER BY time ASC
   )T ORDER BY T.time ASC "
               cmd.Parameters.Add("@start_time", SqlDbType.DateTime).Value =
   startTime
               cmd.Parameters.Add("@sku", SqlDbType.NVarChar, 50).Value = sku

                 Using reader As SqlDataReader = cmd.ExecuteReader
                     Do While reader.Read
                          lastKnownQuantity = CDec(reader.Item("quantity"))
                          Dim lowDate As DateTime = CDate(reader.Item("time"))
                          If (lowDate > startTime) Then
                              startTime = lowDate
                          End If
                     Loop
                 End Using
             End Using

       Return cost
   End Function

     Note: Here we define a method that takes a SKU as a parameter and calculates the database
     cost for this month for the given SKU.
     Create a query for the last known database count on a day that is less than the start date or
     the first date when a database was created in SQL Azure server. This will be used as the value
     for database count on all days between the start of the month and the first date for which a
     database count change is noticed.



3. Insert the following code snippet directly under:

           a. C#: closing bracket of the using statement using (SqlCommand cmd =
              conn.CreateCommand())

           b. VB: the ‘End Using’ line, at the end of the statement Using cmd As SqlCommand =
              conn.CreateCommand
(Code Snippet – SQL Azure Tips and Tricks - Ex04 GetBandwidthCost 2– C#)
C#
if (lastKnownQuantity == 0)
{
    // There is no work to be done
    return 0;
}

// Query for database usage on each day to compute cost
DateTime currentDate = startTime;
while (currentDate <= cutoffTime)
{
    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT quantity " +
                          "FROM sys.database_usage " +
                          "WHERE time = @time AND sku = @sku ";

         cmd.Parameters.Add("@time", SqlDbType.DateTime).Value = currentDate;
         cmd.Parameters.Add("@sku", SqlDbType.NVarChar, 50).Value = sku;

         decimal costForDay = 0;
         object quantity = cmd.ExecuteScalar();
         if (quantity != null)
         {
              costForDay = lastKnownQuantity = (decimal)quantity;
         }
         else
         {
              costForDay = lastKnownQuantity;
         }

        costForDay = costForDay * (sku == "Web" ? WebSkuCost :
BusinessSkuCost) /
                                  DateTime.DaysInMonth(currentDate.Year,
currentDate.Month);

         Console.WriteLine("Database cost for '{0}' SKU for {1} = ${2} ", sku,
                           currentDate.ToShortDateString(), costForDay);
         cost += costForDay;

         currentDate = currentDate.AddDays(1);
     }
}



(Code Snippet – SQL Azure Tips and Tricks - Ex04 GetBandwidthCost 2– VB)
   Visual Basic
   If (lastKnownQuantity = 0) Then
       Return 0
   End If

   ' Query for database usage on each day to compute cost
   Dim currentDate As DateTime = startTime
   Do While (currentDate <= cutoffTime)
        Using cmd = conn.CreateCommand
            cmd.CommandText = "SELECT quantity FROM sys.database_usage WHERE time
   = @time AND sku = @sku "
            cmd.Parameters.Add("@time", SqlDbType.DateTime).Value = currentDate
            cmd.Parameters.Add("@sku", SqlDbType.NVarChar, 50).Value = sku
            Dim costForDay As Decimal = 0
            Dim quantity As Object = cmd.ExecuteScalar
            If (Not quantity Is Nothing) Then
                 costForDay = lastKnownQuantity = CDec(quantity)
            Else
                 costForDay = lastKnownQuantity
            End If
            costForDay = ((costForDay * IIf((sku = "Web"), 10, 100)) /
   DateTime.DaysInMonth(currentDate.Year, currentDate.Month))
            Console.WriteLine("Database cost for '{0}' SKU for {1} = ${2} ", sku,
   currentDate.ToShortDateString, costForDay)
            cost = (cost + costForDay)
            currentDate = currentDate.AddDays(1)
        End Using
   Loop

     Note: From the lastKnownQuantity and the respective date we derived from the last code
     snippet, we iterate through each day forward to today’s date, calculate the charges for each
     databasewith the respective SKU rates. We then return the aggregated charges.



4. Open Program.cs or Module1.vb by double clicking on the file in Solution Explorer.

5. Insert the following code snippet directly above the line Console.ReadKey()

   (Code Snippet – SQL Azure Tips and Tricks - Ex04 Call GetDatabaseCost – C#)
   C#
   Console.WriteLine("Total Web Edition database cost USD$: {0}",
   metrics.GetDatabaseCost("Web"));
   Console.WriteLine();

   Console.WriteLine("Total Business Edition database cost USD$: {0}",
   metrics.GetDatabaseCost("Business"));
   Console.WriteLine();
        (Code Snippet – SQL Azure Tips and Tricks - Ex04 CallDatabaseCost – VB)
        Visual Basic
        Console.WriteLine("Database cost USD$: {0}",
        metrics.GetDatabaseCost("Web"))

        Console.WriteLine("Database cost USD$: {0}",
        metrics.GetDatabaseCost("Business"))
        Console.WriteLine()



    6. Start debugging, from Visual Studio, Debugging | Start Debugging. Or just press F5 on your
       keyboard.

    7. You should see a console window open with the following output;




        Figure 45
        Expected output

          Note: We should see the cost for each database under each SKU as well as an aggregated cost
          value as well.




Summary
In this lab we looked at:
       How to programmatically create, retrieve and delete firewall rules in SQL Azure.
       How to programmatically retrieve a sessionId for a SQL connection to SQL Azure.
   Throttling, latency and transactions when working with SQL Azure.

   How to programmatically calculate bandwidth and database costs for SQL Azure.