Docstoc

Lab

Document Sample
Lab Powered By Docstoc
					Hands-On Lab
SQL Azure Tips and Tricks
Lab version:    2.0.0
Last updated:   10/25/2011
Contents

OVERVIEW ................................................................................................................................................... 3

EXERCISE 1: MANIPULATING THE SQL AZURE FIREWALL VIA API’S ................................................ 5
       Task 1 – Creating Firewall Rules Via the SQL Azure Portal ................................................................... 5
       Task 2 – Retrieving Firewall Rules Programmatically ........................................................................... 9
       Task 3 – Adding Firewall Rules Programmatically............................................................................... 21
       Task 4 – Deleting Firewall Rules Programmatically ............................................................................ 25

EXERCISE 2: MANAGING CONNECTIONS – LOGGING SESSIONIDS ................................................. 29
       Task 1 – Implementing a SessionId Class ............................................................................................ 29
       Task 2 – Printing the Session ID to the Console .................................................................................. 34

EXERCISE 3: SUPPORTABILITY – USAGE METRICS ........................................................................... 37
       Task 1 – Establishing a Connection to SQL Azure ............................................................................... 37
       Task 2 – Calculating SQL Azure Bandwidth Costs ............................................................................... 41
       Task 3 – Calculating SQL Azure Database Costs .................................................................................. 46

SUMMARY .................................................................................................................................................. 54
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 an on-
premise SQL Server. Some of these differences are inherent in the way that SQL Azure has been
architected and some will only apply while the features for both environments converge.
This hands-on lab will walk through a series of tips and tricks that are important for working with SQL
Azure, such as managing connections and querying the metrics views.


Objectives
In this hands-on lab, you will learn about:
       Manipulating the SQL Azure firewall via APIs

       Managing connections and logging session IDs
       Supportability



Prerequisites
The following is required to complete this hands-on lab:
       Microsoft .NET Framework 4.0
       Microsoft Visual Studio 2010



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. Open a Windows Explorer window and browse to the lab’s Source\Setup folder.
     2. Double-click the Dependencies.dep file in this folder to launch the Dependency Checker tool
        and install any missing prerequisites and the Visual Studio code snippets.
     3. If the User Account Control dialog is shown, confirm the action to proceed.
 Note: This process may require elevation. The .dep extension is associated with the Dependency
 Checker tool during its installation. For additional information about the setup procedure and how to
 install the Dependency Checker tool, refer to the Setup.docx document in the Assets folder of the
 training kit.




Using the Code Snippets
Throughout the lab document, you will be instructed to insert code blocks. For your convenience, most
of that code is provided as Visual Studio Code Snippets, which you can use from within Visual Studio
2010 to avoid having to add it manually.
If you are not familiar with the Visual Studio Code Snippets, and want to learn how to use them, you can
refer to the Setup.docx document in the Assets folder of the training kit, which contains a section
describing how to use them.


Exercises
This hands-on lab includes the following exercises:
    1. Manipulating the SQL Azure firewall via APIs
    2. Managing Connections – Logging Session IDs
    3. Supportability – Usage Metrics


Estimated time to complete this lab: 60 minutes.
 Note: When you first start Visual Studio, you must select one of the predefined settings collections.
 Every predefined collection is designed to match a particular development style and determines
 window layouts, editor behavior, IntelliSense code snippets, and dialog box options. The procedures in
 this lab describe the actions necessary to accomplish a given task in Visual Studio when using the
 General Development Settings collection. If you choose a different settings collection for your
 development environment, there may be differences in these procedures that you need to take into
 account.




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 – Creating Firewall Rules Via the SQL Azure Portal
SQL Azure allows users to add firewall rules via the SQL Azure portal. In this task, you add some sample
firewall rules.
    1. Go to the website https://windows.azure.com.
    2. Sign in with your Windows Live account.
   Figure 1
   Signing into the Azure Services Portal


3. In the Windows Azure Management portal UI, select the Database option
4. Select the SQL Azure subscription that you wish to work with from your subscription list (if you
   have more than one SQL Azure subscription).




   Figure 2
   SQL Azure portal showing your list of subscriptions


5. Now, in the subscription select a the SQL Azure server.
6. Press the Firewall Rules button.




   Figure 3
   SQL Azure portal showing your list of subscriptions


7. Select Add.




   Figure 4
   Adding an exception to the firewall


8. In the Add Firewall Rule dialog, set the name of the rule to “Allowed Host 1”, copy your own IP
   address, which is provided for you in the dialog, into the start and end fields of the IP Range,
   and then click Submit.
   Figure 5
   Adding an exception to the firewall



     Note: You can specify an IP range or just a single IP address by entering the same value in both
     fields. By specifying your IP address now, it allows you to connect programmatically to this SQL
     Azure project later in this lab.



9. Repeat step 7 and 8 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. You 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 – Retrieving Firewall Rules Programmatically
SQL Azure allows you to retrieve firewall rules programmatically. In this task, you retrieve the firewall
rules that you entered via the SQL Azure portal in Task 1.
    1. Open Visual Studio from Start | All Programs | Microsoft Visual Studio 2010.
    2. In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse
       to Ex1-ManipulatingFirewallViaAPI\begin in the Source folder of this lab, select FirewallAPI.sln
       in the folder for the language of your preference (Visual C# or Visual Basic) and click Open.




        Figure 7
        Opening a Visual Studio project or solution


    3. In Visual Studio, open the Solution Explorer from View | Solution Explorer.
   Figure 8
   Opening the Solution Explorer in Visual Studio




   Figure 9
   Solution Explorer showing the FirewallAPI project (Visual C#)




   Figure 10
   Solution Explorer showing the FirewallAPI project (Visual Basic)



     Note: FirewallAPI is a standard console application.



4. Add a new class to the project. To do this, right-click the FirewallAPI project in Solution
   Explorer, point to Add, and then select New Item.
   Figure 11
   Adding a new item to the project


5. In the Add New Item dialog, choose the Code category from the Installed Templates list and
   then select the Class template. Enter the name FirewallRule.cs (for Visual C# Projects) or
   FirewallRule.vb (for Visual Basic Projects) and then click Add.
6. Open the FirewallRule.cs file (for Visual C# projects) or FirewallRule.vb file (for Visual Basic
   projects). Replace the FirewallRule class with the following (highlighted) code snippet.
   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Firewall Rule class – C#)
   C#
   namespace SqlAzureTipsAndTricks.FirewallAPI
   {
     /// <summary>
     /// Defines a firewall rule.
     /// </summary>
     class FirewallRule
     {
       /// <summary>
       /// Creates a new firewall rule with the provided name and the start and
   end IP addresses.
       /// </summary>
       public FirewallRule(string name, string startIp, string endIp)
       {
         Name = name;
         StartIp = System.Net.IPAddress.Parse(startIp);
         EndIp = System.Net.IPAddress.Parse(endIp);
       }

        /// <summary>Name of the firewall rule.</summary>
        public string Name { get; set; }
        /// <summary>Start IP address for the firewall rule.</summary>
        public System.Net.IPAddress StartIp { get; set; }

        /// <summary>End IP address for the firewall rule.</summary>
        public System.Net.IPAddress EndIp { get; set; }
    }
}



(Code Snippet – SQL Azure Tips and Tricks - Ex1 Firewall Rule class – VB)
Visual Basic
''' <summary>
''' Defines a firewall rule.
''' </summary>
Public Class FirewallRule

  ''' <summary>
  ''' Creates a new firewall rule with the provided name and the start and end
IP addresses.
  ''' </summary>
  Public Sub New(ByVal name As String, ByVal startIp As String, ByVal endIp As
String)
    Me.Name = name
    Me.StartIp = System.Net.IPAddress.Parse(startIp)
    Me.EndIp = System.Net.IPAddress.Parse(endIp)
  End Sub

    ''' <summary>Name of the firewall rule.</summary>
    Public Property Name() As String

    ''' <summary>Start IP address for the firewall rule.</summary>
    Public Property StartIp() As System.Net.IPAddress

    ''' <summary>End IP address for the firewall rule.</summary>
    Public Property EndIp() As System.Net.IPAddress

End Class



    Note: Here you define a FirewallRule class with three basic properties: Name, StartIp and
    EndIp. Notice that these properties correspond to the fields shown when you entered the
    sample firewall rules via the SQL Azure portal. You also define a simple constructor that
    initializes the three properties.
7. Add a new class to the project. To do this, right-click FirewallAPI in Solution Explorer, point to
   Add and then select Class. In the Add New Item dialog, set the name to Firewall.cs (for Visual
   C# projects) or Firewall.vb (for Visual Basic projects) and then click Add.
8. In the Firewall.cs file (for Visual C# projects) or Firewall.vb file (for Visual Basic projects), insert
   the following namespace directives at the top of the file.
   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Namespace directives – C#)
   C#
   using System.Data;
   using System.Data.SqlClient;



   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Namespace directives – VB)
   Visual Basic
   Imports System.Data
   Imports System.Data.SqlClient



9. Next, replace the contents of the Firewall class with the following (highlighted) code snippet.
   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Firewall class – C#)
   C#
   namespace SqlAzureTipsAndTricks.FirewallAPI
   {
     /// <summary>
     /// A simple class that allows for operations to be performed against the
   firewall defined in each SQL Azure server.
     /// </summary>
     class Firewall
     {
       // Login that has access to the master database on the server
       private string _login { get; set; }

           // Password for server login
           private string _password { get; set; }

           // Connection string that is used to connect to master database on server
           private string _masterConnectionString { get; set; }

           /// <summary>Server that is being manipulated.</summary>
           public string ServerName { get; private set; }
       }
   }
   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Firewall class – VB)
   Visual Basic
   ''' <summary>
   ''' A simple class that allows for operations to be performed against the
   firewall defined in each SQL Azure server.
   ''' </summary>
   Public Class Firewall

      ' Login that has access to the master database on the server
      Private _login As String

      ' Password for server login
      Private _password As String

      ' Connection string that is used to connect to master database on server
      Private _masterConnectionString As String

      ''' <summary>Server that is being manipulated.</summary>
      Public Property ServerName As String

   End Class



     Note: Here you define the Firewall class with three basic properties; _server, _login, and
     _password. You will later use these properties to build a connection string to connect to SQL
     Azure.



10. Insert the following class constructor at the bottom of the Firewall class definition.
    (Code Snippet – SQL Azure Tips and Tricks - Ex1 Firewall Constructor – C#)
   C#
   class Firewall
   {
     ...
     /// <summary>
     /// Creates a new instance of the firewall class for the provided server and
   credentials.
     /// </summary>
     public Firewall(string server, string login, string password)
     {
       this.ServerName = server;
       this._login = login;
       this._password = password;

        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
       builder.DataSource = string.Format("tcp:{0}.database.windows.net,1433",
   ServerName);
       builder.InitialCatalog = "master";
       builder.UserID = this._login;
       builder.Password = this._password;
       builder.Pooling = true;
       this._masterConnectionString = builder.ToString();
     }
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Firewall Constructor – VB)
   Visual Basic
   Public Class Firewall
     ...
     ''' <summary>
     ''' Creates a new instance of the firewall class for the provided server and
   credentials.
     ''' </summary>
     Public Sub New(ByVal server As String, ByVal login As String, ByVal password
   As String)
       Me.ServerName = server
       Me._login = login
       Me._password = password

       Dim builder As New SqlConnectionStringBuilder
       builder.DataSource = String.Format("tcp:{0}.database.windows.net,1433",
   Me.ServerName)
       builder.InitialCatalog = "master"
       builder.UserID = Me._login
       builder.Password = Me._password
       builder.Pooling = True
       Me._masterConnectionString = builder.ToString()
     End Sub

   End Class



     Note: Here you define a constructor for the Firewall class that 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.



11. Insert the following method immediately after the code defined in the previous step.
   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Firewall GetRules method – C#)
C#
class Firewall
{
  ...
  /// <summary>
  /// Retrieves the list of firewall rules for the server.
  /// </summary>
  public List<FirewallRule> GetRules()
  {
    List<FirewallRule> rules = new List<FirewallRule>();

    using (SqlConnection conn = new
SqlConnection(this._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 - Ex1 Firewall GetRules method – VB)
Visual Basic
Public Class Firewall
  ...
  ''' <summary>
  ''' Retrieves the list of firewall rules for the server.
  ''' </summary>
  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

   End Class



     Note: Each of the firewall rules that you specified via the SQL Azure portal are stored in a
     system table called “sys.firewall_rules” that resides in the master database where you
     connected. Here, you execute a T-SQL script to select out the name, start_ip_address and
     end_ip_address columns.



12. Press CTRL + SHIFT + S to save all the changes.
13. Open the Program.cs file (for C# projects) or Module1.vb file (for Visual Basic projects).
14. Add the following method to the Program class.
   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Firewall PrintAllRules method – C#)
   C#
   class Program
   {
     ...
     /// <summary>
     /// Gets all firewall rules defined for a given firewall and outputs them to
   the console.
     /// </summary>
     static void PrintAllRules(Firewall firewall)
     {
       Console.WriteLine("Current firewall rules for SQL Azure server: {0}",
   firewall.ServerName);

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

           Console.WriteLine();
       }
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Firewall PrintAllRules method – VB)
   Visual Basic
   Module Module1
     ...
     ''' <summary>
     ''' Gets all firewall rules defined for a given firewall and outputs them to
   the console.
     ''' </summary>
     Private Sub PrintAllRules(ByVal firewall As Firewall)

       Dim rules As List(Of FirewallRule) = firewall.GetRules
       Console.WriteLine("Current firewall rules for SQL Azure server: {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

   End Module



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



15. Insert the following member declarations at the top of the class.
    (Code Snippet – SQL Azure Tips and Tricks - Ex1 Initialization – C#)
   C#
   class Program
   {
       /// <summary>SQL Azure server name.</summary>
       static string serverName = "REPLACE_WITH_SERVERNAME";

       /// <summary>Username used to connect to SQL Azure server.</summary>
       static string userName = "REPLACE_WITH_USERNAME";

       /// <summary>Password used to connect to SQL Azure Server.</summary>
       static string password = "REPLACE_WITH_PASSWORD";

       /// <summary>Firewall instance for SQL Azure Server.</summary>
       static Firewall firewall = new Firewall(serverName, userName, password);
       ...
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Initialization – VB)
   Visual Basic
   Module Module1

       ''' <summary>SQL Azure server name.</summary>
       Private serverName As String = "REPLACE_WITH_SERVERNAME"

       ''' <summary>Username used to connect to SQL Azure server.</summary>
       Private userName As String = "REPLACE_WITH_USERNAME"

       ''' <summary>Password used to connect to SQL Azure Server.</summary>
       Private password As String = "REPLACE_WITH_PASSWORD"

     ''' <summary>Firewall instance for SQL Azure Server.</summary>
     Private firewall As Firewall = New Firewall(serverName, userName, password)
     ...
   End Module



       Note: Here you initialize the parameters serverName, userName and password. Make sure to
       replace them with your own SQL Azure account details. Next, you initialize a firewall instance
       based on these parameters. This static firewall member will be used throughout this exercise.
       The userName field refers to the user name shown in the Server Administration page under
       Administrator Username.



16. Update method Main with the following code:
   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Main method – C#)
   C#
   class Program
   {
       ...
       static void Main(string[] args)
       {
         // Print firewall rules
         PrintAllRules(firewall);
         Console.WriteLine("Press any key to continue...");
         Console.ReadKey(true);
       ...
   }



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

          ' Print Firewall rules
          PrintAllRules(firewall)
          Console.WriteLine("Press any key to continue...")
          Console.ReadKey(True)

     End Sub
     ...
   End Module



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



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




   Figure 12
   Running the program under the debugger
    18. You should see a console window open with the following output:




        Figure 13
        Program output showing the firewall rules currently defined



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




Task 3 – Adding Firewall Rules Programmatically
Previously, you defined several firewall rules via the SQL Azure portal. In this task, you create an
additional firewall rule programmatically.
    1. In Solution Explorer, double-click Firewall.cs (for C# projects) or Firewall.cs (for Visual Basic
       projects) to open this file.
    2. Insert the following code snippet directly under the GetRules method.
        (Code Snippet – SQL Azure Tips and Tricks - Ex1 SetFirewallRule method – C#)
        C#
        class Firewall
        {
          ...
          /// <summary>
          /// Creates a new firewall rule or updates an existing rule with the
        provided name to
          /// have the new start and ip addresses.
          /// </summary>
          public void SetFirewallRule(FirewallRule rule)
          {
            using (SqlConnection conn = new
        SqlConnection(this._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 - Ex1 SetFirewallRule method – VB)
   Visual Basic
   Public Class Firewall
     ...

     ''' <summary>
     ''' Creates a new firewall rule or updates an existing rule with the
   provided name to have the new start and ip
     ''' addresses.
     ''' </summary>
     Public Sub SetFirewallRule(ByVal rule As FirewallRule)
       Using conn As SqlConnection = New
   SqlConnection(Me._masterConnectionString)
         Using cmd As SqlCommand = 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()
         End Using
       End Using
     End Sub

   End Class



     Note: There is a stored procedure called ‘sp_set_firewall_rule’ in the master database. In this
     method, you establish a connection to the master database and execute this stored procedure
     to add an additional firewall rule to the SQL Azure Server programmatically.



3. In Solution Explorer, double-click Program.cs (for C# Projects) or Module1.vb (for Visual Basic
   projects) to open this file.
4. Replace the body of method Main with the following code snippet.
   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Call SetFirewallRule – C#)
   C#
   class Program
   {
     ...
     static void Main(string[] args)
     {
       // Create a new firewall rule
       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);
       Console.WriteLine("Press any key to continue...");
       Console.ReadKey(true);
     }
     ...
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Call SetFirewallRule – VB)
   Visual Basic
   Module Module1
     ...
     Sub Main()

        ' Create a new firewall rule
        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.WriteLine("Press any key to continue...")
        Console.ReadKey(True)

     End Sub
     ...
   End Module
     Note: Here, you initialize a FirewallRule instance with a startIp, endIp and a name. You then
     pass this FirewallRule to the SetFirewallRule method in the Firewall class that you defined in
     the previous step. Finally, you output all the firewall rules to the console to confirm 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.
6. You should see a console window open with the following output.




   Figure 14
   Program output showing the newly added firewall rule



     Note: Notice that the output of the program includes the newly created firewall rule.



7. You can also confirm that the firewall rule has been added successfully via the SQL Azure portal.
   If the rule is not displayed, just refresh the page.




   Figure 15
   SQL Azure portal UI showing the updated firewall settings
Task 4 – Deleting Firewall Rules Programmatically
SQL Azure server firewall rules can be deleted via the SQL Azure Portal. In this task, you are going to look
at how to do this programmatically.
    1. In Solution Explorer, double-click Firewall.cs (for C# projects) or Firewall.vb (for Visual Basic
       projects) to open this file.
    2. Insert the following code snippet immediately after the SetFirewallRule method.
        (Code Snippet – SQL Azure Tips and Tricks - Ex1 DeleteFirewallRule method – C#)
        C#
        class Firewall
        {
          ...
          /// <summary>
          /// Deletes a firewall rule with the provided name.
          /// </summary>
          public void DeleteFirewallRule(string name)
          {
            using (SqlConnection conn = new
        SqlConnection(this._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 - Ex1 DeleteFirewallRule method – VB)
        Visual Basic
        Public Class Firewall
          ...
          ''' <summary>
          ''' Deletes a firewall rule with the provided name.
          ''' </summary>
          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

   End Class



     Note: There is a stored procedure named ‘sp_delete_firewall_rule’ in the master database. In
     this method, you establish a connection to the master database and execute this stored
     procedure to delete the firewall rule from SQL Azure Server programmatically. The name of
     the firewall rule to delete is passed in as a parameter to this method.



3. In Solution Explorer, double-click Program.cs (for C# projects) or Module1.vb (for Visual Basic
   projects) to open this file.
4. Replace the content of method Main with the following code snippet.
   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Call DeleteFirewallRule – C#)
   C#
   class Program
   {
     ...
     static void Main(string[] args)
     {
       // Delete a firewall rule
       firewall.DeleteFirewallRule("IP Example 1");
       Console.WriteLine("After deleting firewall rule 'IP Example 1'...");
       PrintAllRules(firewall);
       Console.WriteLine("Press any key to continue...");
       Console.ReadKey(true);
     }
     ...
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex1 Call DeleteFirewallRule – VB)
   Visual Basic
   Module Module1
     ...
     Sub Main()

        ' Delete a firewall rule
        firewall.DeleteFirewallRule("IP Example 1")
        Console.WriteLine("After deleting firewall rule 'IP Example 1'...")
        PrintAllRules(firewall)
        Console.WriteLine("Press any key to continue...")
        Console.ReadKey(True)

     End Sub
     ...
   End Module



     Note: Here you execute the DeleteFirewallRule method with the name of the firewall rule that
     you created in the previous task, namely “IP Example 1”.



5. To start debugging, select Debug | Start Debugging, or alternatively, just press F5 on your
   keyboard. You should see a console window open with the following output:




   Figure 16
   Program output showing the current rules after “IP Example 1” is deleted



     Note: Notice that the “IP Example 1” rule that you created in the previous task is no longer in
     the list.



6. You can also confirm that the firewall rule was deleted correctly via the SQL Azure portal. If the
   rule is not displayed, just refresh the page
Figure 17
SQL Azure Portal showing that the IP Example 1 rule was deleted
Exercise 2: Managing Connections –
Logging SessionIds
Much like on-premise SQL Servers, SQL Azure supports the function CONTEXT_INFO() as well. It contains
the session ID value assigned when a connection to SQL Azure is established. Session IDs are critical to
debugging errors, especially when dealing with Microsoft support. Therefore, developers should get in
the habit of logging them.
In a scenario where a front-end website is hosted in Windows Azure, developers can very easily leverage
Windows Azure Diagnostics to log messages and raise alerts.
In this exercise, you will walk through the simple steps required to retrieve a session ID
programmatically.

Note: To learn more about context information in SQL Server and Windows Azure Diagnostics, 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
Windows Azure Diagnostics:
Implementing Windows Azure Diagnostics




Task 1 – Implementing a SessionId Class
When a client application connects to SQL Azure, a CONTEXT_INFO (Transact-SQL) is set with a unique
session specific GUID value automatically. In this task, you implement a class that will create a SQL
connection to the master database in SQL Azure and then retrieve the session ID for this connection.
    1. Open Visual Studio from Start | All Programs | Microsoft Visual Studio 2010.
    2. In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse
       to Ex2-LoggingConnectionSessionId\begin in the Source folder of this lab, select
       ConnectionLoggingSessionId.sln in the folder for the language of your preference (Visual C# or
       Visual Basic) and click Open.
    3. In Visual Studio, open Solution Explorer from View | Solution Explorer.
   Figure 18
   Solution Explorer showing the ConnectionLogginSessionId project on C#




   Figure 19
   Solution Explorer showing the ConnectionLogginSessionId project on VB



     Note: ConnectionLoggingSessionId is a standard console application.



4. Add a new class to the project. To do this, right-click ConnectionLoggingSessionId in Solution
   Explorer, point to Add and then select Class. In the Add New Item dialog, set the name to
   SessionId.cs (for Visual C# projects) or SessionId.vb (for Visual Basic projects) and then click
   Add.
5. In the SessionId.cs file (for Visual C# projects) or SessionId.vb file (for Visual Basic projects),
   insert the following namespace directive at the top of the file.
   (Code Snippet – SQL Azure Tips and Tricks - Ex2 Namespace directives – C#)
   C#
   using System.Data.SqlClient;



   (Code Snippet – SQL Azure Tips and Tricks - Ex2 Namespace directives – VB)
   Visual Basic
   Imports System.Data.SqlClient
6. Replace the SessionId class with the following (highlighted) code snippet.
   (Code Snippet – SQL Azure Tips and Tricks - Ex2 SessionId class – C#)
   C#
   namespace SqlAzureTipsAndTricks.ConnectionLoggingSessionId
   {
     /// <summary>
     /// A simple class that is used to illustrate how session IDs can be stored
   and later retrieved
     /// in SQL Azure.
     /// </summary>
     class SessionId
     {
       // Domain base address of the SQL Azure service endpoint
       private const string DomainBaseAddress = "database.windows.net";

         // Server to which a connection is established.
         private string _server;

         // Login that has access to the master database on the server
         private string _login;

         // Password for server login
         private string _password;

       /// <summary>
       /// Creates a new instance of the class for the provided server and
   credentials.
       /// </summary>
       public SessionId(string server, string login, string password)
       {
         this._server = server;
         this._login = login;
         this._password = password;
       }
     }
   }



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

   '''   <summary>
   '''   A simple class that is used to illustrate how session ids can be stored
   and   later retrieved in SQL Azure.
   '''   </summary>
   Public Class SessionId

     ''' <summary>Domain base address of the SQL Azure service
   endpoint.</summary>
     Private Const DomainBaseAddress As String = "database.windows.net"

     ''' <summary>Login that has access to the master database on the
   server.</summary>
     Private _login As String

     ''' <summary>Password for server login.</summary>
     Private _password As String

     ''' <summary>Creates a new instance of the class for the provided server and
   credentials.</summary>
     Private _server As String

     ''' <summary>
     ''' Creates a new instance of the class for the provided server and
   credentials.
     ''' </summary>
     Public Sub New(ByVal server As String, ByVal login As String, ByVal password
   As String)
       Me._server = server
       Me._login = login
       Me._password = password
     End Sub

   End Class



     Note: Here, you define a class named SessionId with class member variables that are later
     used to build a connection string to SQL Azure. You also define a simple constructor that takes
     the SQL Azure server name, login and password for the server.



7. Insert the following method immediately after the SessionId class constructor:
   (Code Snippet – SQL Azure Tips and Tricks - Ex2 CreateSqlConnectionToMaster method – C#)
   C#
   class SessionId
   {
     ...
     /// <summary>
     /// Establishes a SQL connection to the master database and retrieves a
   session ID for the connection.
     /// </summary>
    public SqlConnection CreateSqlConnectionToMaster(out Guid sessionId)
    {
      sessionId = Guid.Empty;

    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder.DataSource = string.Format("tcp:{0}.{1},1433", this._server,
SessionId.DomainBaseAddress);
    builder.InitialCatalog = "master";
    builder.UserID = this._login;
    builder.Password = this._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 - Ex2 CreateSqlConnectionToMaster method – VB)
Visual Basic
Public Class SessionId
  ...
  ''' <summary>
  ''' Establishes a SQL connection to the master database and retrieves a
session ID for the connection.
  ''' </summary>
  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._server,
DomainBaseAddress)
    builder.InitialCatalog = "master"
    builder.UserID = Me._login
    builder.Password = Me._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

        End Class



         Note: Here you define a method named CreateSqlConnectionToMaster that constructs a SQL
         connection string with the correct DataSource, InitialCatalog, UserID, Password and Pooling
         values set.
         Note that just like a default SQL Server on-premise deployment, SQL Azure server runs on TCP
         port 1433.
         Next, the method establishes a connection to the SQL Azure server with this connection string,
         executes the T-SQL script to retrieve a CONTEXT_INFO() and then returns it as a session ID via
         an output parameter on the method.




Task 2 – Printing the Session ID to the Console
In your applications that use a SQL Azure server backend, you should record session IDs with your
application specific logging mechanism; for instance, in the Windows Azure scenario, you can use the
System.Diagnostics tracing features to log a session ID. This is important when raising a support call
with the Microsoft SQL Azure support team.
In this task, you simply output the session ID to the console for demonstration purposes.
    1. In Solution Explorer, double-click Program.cs (for C# projects) or Module1.vb (for Visual Basic
       projects) to open this file.
    2. Insert the following (highlighted) code snippet inside the body of method Main and replace the
       value of the variables serverName, username, and password values with your own SQL Azure
       server settings.
        (Code Snippet – SQL Azure Tips and Tricks - Ex2 Main method– C#)
        C#
        class Program
        {
          static void Main(string[] args)
       {
           var   serverName = "REPLACE_WITH_SERVERNAME";
           var   userName = "REPLACE_WITH_USERNAME";
           var   password = "REPLACE_WITH_PASSWORD";
           var   sessionId = new SessionId(serverName, userName, password);

           Guid sqlSessionId;
           sessionId.CreateSqlConnectionToMaster(out sqlSessionId);
           Console.WriteLine("SQL Server connection session ID: {0}", sqlSessionId);

           Console.ReadKey(true);
       }
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex2 Main method – VB)
   Visual Basic
   Sub Main()

       Dim   serverName As String = "REPLACE_WITH_SERVERNAME"
       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 Server connection session ID: {0}", sqlSessionId)

       Console.ReadKey(True)

   End Sub



       Note: Here, you initialize the variables serverName, username and password and then create
       a session ID instance based on these values. Please remember to replace these values with
       your own SQL Azure server settings.
       Next, you use the CreateSqlConnectionToMaster method defined in the previous task to
       establish a connection to the SQL Azure server and return a session ID.
       Finally, you 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 20
Program output showing the current session ID
Exercise 3: Supportability – Usage
Metrics
For the commercial offering of Windows Azure, the following simple consumption-based pricing model
applies:
There are two basic 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/
 Accounts and Billing in SQL Azure:
 http://msdn.microsoft.com/en-us/library/ee621788.aspx

In this exercise, you explore mechanisms for programmatically calculating bandwidth and database
costs.



Task 1 – Establishing a Connection to SQL Azure
In this task, you implement a class named Metrics and create a SQL connection to a SQL Azure database.
    1. Open Visual Studio from Start | All Programs | Microsoft Visual Studio 2010.
    2. In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse
       to Ex3-Metrics\begin in the Source folder of this lab, select Metrics.sln in the folder for the
       language of your preference (Visual C# or Visual Basic) and click Open.
    3. In Visual Studio, open Solution Explorer from View | Solution Explorer.
   Figure 21
   Solution Explorer showing the Metrics project



     Note: Metrics is a standard console application.



4. Add a new class to the project. To do this, right-click Metrics in Solution Explorer, point to Add
   and then select Class. In the Add New Item dialog, set the name to Metrics.cs (for Visual C#
   projects) or Metrics.vb (for Visual Basic projects) and then click Add.
5. In the Metrics.cs file (for Visual C# projects) or Metrics.vb file (for Visual Basic projects), insert
   the following namespace directive at the top of the file.
   (Code Snippet – SQL Azure Tips and Tricks - Ex3 Namespace directives – C#)
   C#
   using System.Data;
   using System.Data.SqlClient;



   (Code Snippet – SQL Azure Tips and Tricks - Ex3 Namespace directives – VB)
   Visual Basic
   Imports System.Data.SqlClient



6. Replace the Metrics class with the following (highlighted) code snippet.
   (Code Snippet – SQL Azure Tips and Tricks - Ex3 Metrics class – C#)
   C#
   namespace SqlAzureTipsAndTricks.Metrics
   {
     /// <summary>
     /// Simple class that provides methods to interact with metrics data in SQL
   Azure.
     /// </summary>
     public class Metrics
     {
       /// <summary>Cost per GB of Peak Egress traffic in dollars.</summary>
       public const decimal IngressCost = 0.10M;

         /// <summary>Cost per GB of Peak Egress traffic in dollars.</summary>
         public const decimal EgressCost = 0.15M;

         /// <summary>Monthly cost of Business SKU.</summary>
         public const decimal BusinessSkuCost = 99.99M;
        /// <summary>Monthly cost of Web SKU.</summary>
        public const decimal WebSkuCost = 9.99M;

        // Server that is being manipulated
        private string _server;

        // Login that has access to the master database on the server
        private string _login;

        // Password for server login
        private string _password;

        // Connection string that is used to connect to master database on server
        private string _masterConnectionString;
    }
}



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

''' <summary>
''' Simple class that provides methods to interact with metrics data in SQL
Azure.
''' </summary>
Public Class Metrics

    ''' <summary>Cost per GB of Peak Egress traffic in dollars.</summary>
    Public Const IngressCost As Decimal = 0.1D

    ''' <summary>Cost per GB of Peak Egress traffic in dollars.</summary>
    Public Const EgressCost As Decimal = 0.15D

    ''' <summary>Monthly cost of Business SKU.</summary>
    Public Const BusinessSkuCost As Decimal = 99.99D

    ''' <summary>Monthly cost of Web SKU.</summary>
    Public Shared ReadOnly WebSkuCost As Decimal = 9.99D

    ' Server that is being manipulated
    Private _server As String

    ' Login that has access to the master database on the server.
    Private _login As String

    ' Password for server login.
     Private _password As String

     ' Connection string that is used to connect to master database on server.
     Private _masterConnectionString As String

   End Class



     Note: Here, you define a few parameters that represent pricings for SQL Azure.
     IngressCost is the incoming bandwidth cost per GB in US dollars.
     EgressCost is the outgoing bandwidth cost per GB in US dollars.
     BusinessSkuCost is the cost for the SQL Azure Business SKU per month in US dollars.
     WebSkuCost is the cost for the SQL Azure Web SKU per month in US dollars.
     _server, _login, _password are connection settings that you will use to establish a connection
     to SQL Azure.
     Note that the cost of data transfers for certain regions differs and you may need to adjust
     some of these parameters accordingly.



7. Insert the following constructor immediately after the _masterConnectionString variable
   definition.
    (Code Snippet – SQL Azure Tips and Tricks - Ex3 Metrics constructor – C#)
   C#
   public class Metrics
   {
     ...
     /// <summary>
     /// Creates a new instance of the metrics class for the provided server and
   credentials.
     /// </summary>
     public Metrics(string server, string login, string password)
     {
       this._server = server;
       this._login = login;
       this._password = password;

       SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
       builder.DataSource = string.Format("tcp:{0}.database.windows.net,1433",
   this._server);
       builder.InitialCatalog = "master";
       builder.UserID = this._login;
       builder.Password = this._password;
               builder.Pooling = true;
               this._masterConnectionString = builder.ToString();
           }
       }



       (Code Snippet – SQL Azure Tips and Tricks - Ex3 Metrics constructor – VB)
       Visual Basic
       Public Class Metrics
         ...
         ''' <summary>
         ''' Creates a new instance of the metrics class for the provided server and
       credentials.
         ''' </summary>
         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

       End Class



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




Task 2 – Calculating 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 bandwidth used in KB.



In this task, you calculate the bandwidth usage cost programmatically for the designated SQL Azure
server during the current month.
    1. Directly under the code you inserted in the last step, insert the following (highlighted) code
       snippet.
       (Code Snippet – SQL Azure Tips and Tricks - Ex3 GetBandwidthCost method – C#)
       C#
       public class Metrics
       {
         ...
         /// <summary>
         /// Computes the accumulated cost of bandwidth for the current month.
         /// </summary>
         public decimal GetBandwidthCost()
         {
           decimal cost = 0;

           using (SqlConnection conn = new
       SqlConnection(this._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 - Ex3 GetBandwidthCost method – VB)
Visual Basic
Public Class Metrics
  ...
  ''' <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._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")) * IngressCost))
                    Else
                      cost = (cost + (CDec(reader.Item("quantity")) * EgressCost))
                    End If
              Loop
            End Using
          End Using
        End Using

        Return cost

      End Function

   End Class



     Note: Here you define a method that calculates the bandwidth costs for a SQL Azure server.
     Because Ingress and Egress data have different charge rates, you need to group quantity data
     by direction.
     First, open a connection to the master database and then execute a SQL command to retrieve
     the sum of quantity data in GB, grouped by direction. Since you are only interested in the
     current month’s data charge, you need to filter the result set by passing a DateTime parameter
     representing the start of the current month.
     Lastly, you apply the respective charge rates to Ingress and Egress data retrieved, aggregate
     these costs and return the total.



2. Open the Program.cs file (for C# projects) or the Module1.vb file (for Visual Basic projects) by
   double clicking the file in Solution Explorer.

3. Replace the body of method Main with the following code snippet. Remember to replace the
   value of the serverName, logon and password variables with your SQL Azure account details.

   (Code Snippet – SQL Azure Tips and Tricks - Ex3 Main method – C#)
   C#
   class Program
   {
     static void Main(string[] args)
     {
       var serverName = "REPLACE_WITH_SERVERNAME";
       var userName = "REPLACE_WITH_USERNAME";
       var password = "REPLACE_WITH_PASSWORD";

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

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

        Console.ReadKey(true);
       }
   }



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

       Sub Main()

           Dim serverName As String = "REPLACE_WITH_SERVERNAME"
           Dim userName As String = "REPLACE_WITH_USERNAME"
           Dim password As String = "REPLACE_WITH_PASSWORD"

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

       Console.WriteLine("Total bandwidth cost USD$: {0:c4}",
   metrics.GetBandwidthCost)

           Console.ReadKey(True)

       End Sub

   End Module



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



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

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




   Figure 22
   Program output showing the total bandwidth cost in USD$
         Note: The program outputs the cost in USD$ on the designated SQL Azure server.




Task 3 – Calculating 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.
Every time a new database is provisioned or an existing database is deleted, an entry is created in this
table. The 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 maximum number of databases of an SKU type that existed during that day



In this task, you calculate the database costs for SQL Azure server programmatically.
    1. Double-click Metrics.cs (for C# projects) or Metrics.vb (for Visual Basic projects) file in Solution
       Explorer to open this file.

    2. Insert the following (highlighted) code snippet immediately after the GetBandwidthCost
       method.

        (Code Snippet – SQL Azure Tips and Tricks - Ex3 GetDatabaseCost – C#)
        C#
        public class Metrics
        {
          ...
          /// <summary>
          /// Computes the accumulated cost of databases for the current month.
          /// </summary>
          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(this._masterConnectionString))
            {
            conn.Open();
            decimal lastKnownQuantity = 0;

      using (SqlCommand cmd = conn.CreateCommand())
      {
        // 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 the system. 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.
        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 - Ex3 GetDatabaseCost – VB)
Visual Basic
Public Class Metrics
  ...
  ''' <summary>
  ''' Computes the accumulated cost of databases for the current month.
  ''' </summary>
  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._masterConnectionString)
      conn.Open()
      Dim lastKnownQuantity As Decimal = 0

       Using cmd As SqlCommand = conn.CreateCommand()

        ' 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 the system. 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.
        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
     End Using
       Return cost
     End Function

   End Class



     Note: Here, you define a method that takes a SKU as a parameter and then calculates the
     database cost for the current month for the given SKU.
     To do this, it creates 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. Next, insert the following (highlighted) code snippet inside the body of the GetDatabaseCost
   method. Place the code 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 - Ex3 GetBandwidthCost 2– C#)
   C#
   public class Metrics
   {
     ...
     public decimal GetDatabaseCost(string sku)
     {
       ...
       using (SqlConnection conn = new
   SqlConnection(this._masterConnectionString))
       {
         conn.Open();
         decimal lastKnownQuantity = 0;

          using (SqlCommand cmd = conn.CreateCommand())
          {
            ...
          }

          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("            {0:yyyy-MM-dd} - ${1:c4}", currentDate,
costForDay);
          cost += costForDay;

                    currentDate = currentDate.AddDays(1);
                }
            }
        }

        return cost;
    }
}



(Code Snippet – SQL Azure Tips and Tricks - Ex3 GetBandwidthCost 2– VB)
Visual Basic
Public Class Metrics
  ...
  Public Function GetDatabaseCost(ByVal sku As String) As Decimal
    ...
    Using conn As SqlConnection = New
SqlConnection(Me._masterConnectionString)
      conn.Open()
      Dim lastKnownQuantity As Decimal = 0

      Using cmd As SqlCommand = conn.CreateCommand()
        ...
      End Using

      If (lastKnownQuantity = 0) Then
        ' There is no work to be done
        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
            lastKnownQuantity = CDec(quantity)
            costForDay = lastKnownQuantity
          Else
            costForDay = lastKnownQuantity
          End If

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

          Console.WriteLine(" {0:yyyy-MM-dd} - ${1:c4}", currentDate,
costForDay)
          cost = (cost + costForDay)

           currentDate = currentDate.AddDays(1)
        End Using
      Loop
    End Using

    Return cost
       End Function

   End Class



       Note: From the lastKnownQuantity and the respective date derived from the last code
       snippet, you iterate through each day forward to today’s date, calculating the charges for each
       database using the respective SKU rates, and then return the aggregated charges.



4. Open the Program.cs file (for C# projects) or the Module1.vb file (for Visual Basic projects) by
   double clicking the file in Solution Explorer.

5. Insert the following code snippet inside method Main directly above the line Console.ReadKey.

   (Code Snippet – SQL Azure Tips and Tricks - Ex3 Call GetDatabaseCost – C#)
   C#
   static void Main(string[] args)
   {
     var serverName = "REPLACE_WITH_SERVERNAME";
     var userName = "REPLACE_WITH_USERNAME";
     var password = "REPLACE_WITH_PASSWORD";

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

     Console.WriteLine("Total bandwidth cost USD$: {0:c4}",
   metrics.GetBandwidthCost());
     Console.WriteLine();
     Console.WriteLine("'Web Edition' SKU database daily cost");
     Console.WriteLine("'Web Edition' total database cost for current month USD$:
   {0:c2}", metrics.GetDatabaseCost("Web"));
     Console.WriteLine();
     Console.WriteLine("'Business Edition' SKU database daily cost");
     Console.WriteLine("'Business Edition' total database cost for current month
   USD$: {0:c2}", metrics.GetDatabaseCost("Business"));

       Console.ReadKey(true);
   }



   (Code Snippet – SQL Azure Tips and Tricks - Ex3 Call GetDatabaseCost – VB)
   Visual Basic
   Sub Main()

       Dim serverName As String = "REPLACE_WITH_SERVERNAME"
       Dim userName As String = "REPLACE_WITH_USERNAME"
      Dim password As String = "REPLACE_WITH_PASSWORD"

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

     Console.WriteLine("Total bandwidth cost USD$: {0:c4}",
   metrics.GetBandwidthCost())
     Console.WriteLine()
     Console.WriteLine("'Web Edition' SKU database daily cost")
     Console.WriteLine("'Web Edition' total database cost for current month USD$:
   {0:c2}", metrics.GetDatabaseCost("Web"))
     Console.WriteLine()
     Console.WriteLine("'Business Edition' SKU database daily cost")
     Console.WriteLine("'Business Edition' total database cost for current month
   USD$: {0:c2}", metrics.GetDatabaseCost("Business"))

      Console.ReadKey(True)

   End Sub



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

7. You should see a console window open with output similar to that shown below:




   Figure 23
   Program output showing daily database costs and monthly aggregate
          Note: You should see the cost for each database under each SKU as well as an aggregated cost
          value as well.




Summary
In this lab you looked at:
       How to create, retrieve, and delete firewall rules in SQL Azure programmatically.
       How to retrieve the session ID for a SQL Azure connection programmatically.

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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:10/25/2011
language:English
pages:54