SQL Azure: Tips and Tricks - Johns Hopkins University



Hands-On LabSQL Azure: SQL Azure Tips and TricksLab version:0.0.1Last updated: DATE \@ "M/d/yyyy" 12/15/2009Contents TOC \h \z \t "Heading 3,2,pp Topic,1,PP Procedure start,3" Overview PAGEREF _Toc248656440 \h 3Exercise 1: Manipulating the SQL Azure firewall via API’s PAGEREF _Toc248656441 \h 7Task 1 – Create Sample Firewall Rules Via the SQL Azure Portal PAGEREF _Toc248656442 \h 7Task 2 – Programmatically Retrieve Firewall Rules PAGEREF _Toc248656443 \h 11Task 3 – Programmatically Add Firewall Rules PAGEREF _Toc248656444 \h 21Task 4 – Programmatically Delete Firewall Rules PAGEREF _Toc248656445 \h 24Exercise 2: Managing Connections – Logging SessionIds PAGEREF _Toc248656446 \h 28Task 1 – Implement a SessionId Class PAGEREF _Toc248656447 \h 28Task 2 – Output SessionId to the Console PAGEREF _Toc248656448 \h 33Exercise 3: Managing Connections – Throttling, latency and transactions PAGEREF _Toc248656449 \h 36Exercise 4: Supportability – Usage Metrics PAGEREF _Toc248656450 \h 36Task 1 – Establish a Connection to SQL Azure PAGEREF _Toc248656451 \h 37Task 2 – Calculate SQL Azure Bandwidth Costs PAGEREF _Toc248656452 \h 41Task 3 – Calculate SQL Azure Database Costs PAGEREF _Toc248656453 \h 46Summary PAGEREF _Toc248656454 \h 51OverviewSQL 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.ObjectivesIn this Hands-On Lab, you will learn about:Manipulating the SQL Azure firewall via API’sManaging connections Logging sessionId’sThrottling and latencyTransactionsSupportabilityPrerequisitesThe following is required to complete this hands-on lab:IIS 7 (with , WCF HTTP Activation)Microsoft .NET Framework 3.5 SP1Microsoft Visual Studio 2008 SP1 (or above)SQL Server 2005 Express Edition (or above)Windows Azure Tools for Microsoft Visual Studio (November 2009)SetupFor 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:Run the SetupLab.cmd script located in the lab's Source\Setup folder to check dependencies and install any missing prerequisites.Once you have verified every prerequisite, follow the instructions to install the code snippets. Using the Code SnippetsWith code snippets, you have all the code you need at your fingertips. The lab document will tell you exactly when you can use them. For example, To add this code snippet in Visual Studio, you simply place the cursor where you would like the code to be inserted, start typing the snippet name (without spaces or hyphens), in this case LabNameEx01RunmethodCS, watch as Intellisense picks up the snippet name, and then hit the TAB key twice once the snippet you want is selected. The code will be inserted at the cursor location. Figure 1Hit TAB to select the highlighted snippet.Figure 2Hit TAB again and the snippet will expandTo insert a code snippet using the mouse rather than the keyboard, right-click where you want the code snippet to be inserted, select Insert Snippet followed by My Code Snippets and then pick the relevant snippet from the list.To learn more about Visual Studio IntelliSense Code Snippets, including how to create your own, please see Hands-On Lab comprises the following exercises:Manipulating the SQL Azure firewall via API’sManaging Connections – Logging SessionId’sManaging Connections – Throttling, Latency and TransactionsSupportability – Usage MetricsEstimated time to complete this lab: 60 minutes.Exercise 1: Manipulating the SQL Azure firewall via API’sThe 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: to: Configure the SQL Azure Firewall Task 1 – Create Sample Firewall Rules Via the SQL Azure PortalSQL Azure allows users to add firewall rules via SQL Azure portal. In this task, we are going to add some sample firewall rules.Go to the website Login with your Windows Live account. Figure 1Logging into the Azure Services PortalSelect the SQL Azure project from your project list that you wish to work with (if you have more than one SQL Azure project).Figure 2The SQL Azure portalSelect Firewall Settings tab. Figure 3Firewall settings tabSelect Add Rule.Figure 4Adding an exception to the firewallEnter “Allowed Host 1” and your IP address shown in the Custom Firewall Settings dialog, into IP Range, and click on Submit.Figure 5Adding an exception to the firewallNote: 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.Repeat step 5 and 6 for the following values. NameStart IP AddressEnd IP AddressAllowed Range 1192.168.0.0192.168.0.255Allowed Range 2192.168.1.0192.168.1.255Note: 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 6Firewall settingsTask 2 – Programmatically Retrieve Firewall RulesSQL 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.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.In Visual Studio 2008, open the begin solution from File | Open | Project/Solution… Figure 7Opening a project or solution In the Open Project dialog, browse to Ex01-ManipulatingFirewallviaAPI\begin inside the Source folder of this lab, select the FirewallAPI.sln file in the folder for the language of your preference (Visual C #or Visual Basic) and click Open.In Visual Studio 2008, open the Solution Explorer from View | Solution Explorer.Figure 8Opening the Solution Explorer Figure 9Firewall API solutionNote: FirewallAPI is a plain .NET Framework 3.5 Console Application.Add a new item to the project by right clicking on the FirewallAPI project and selecting Add | New Item… in the context menu:Figure 10Adding a new item 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.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 ;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 BasicImports 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 PropertyEnd ClassNote: 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.Add another class to the FirewallAPI project by right clicking on it and selecting Add | New Item… in the context menu:Figure 11Adding a new itemIn 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.Open the Firewall.cs file (for Visual C# projects) or Firewall.vb file (for Visual Basic projects) and replace the contents of the Firewall class file with the appropriate code snippet.(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall class – C#)C#using System.Collections.Generic;using System.Data;using System.Data.SqlClient;namespace SqlAzureTipsAndTricks.FirewallAPI{ public class Firewall { public string ServerName { get; set; } private string Login { get; set; } private string Password { get; set; } private string MasterConnectionString { get; set; } }}(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall class – VB)Visual BasicImports System.Data.SqlClientPublic 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 PropertyEnd ClassNote: 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.Insert the following class constructor at the bottom of the Firewall class definition: (Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall Constructor – C#)C#public Firewall(string server, string login, string password){ ServerName = server; Login = login; Password = password; SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = string.Format("tcp:{0}.database.,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.,1433", Me._server) builder.InitialCatalog = "master" builder.UserID = Me._login builder.Password = Me._password builder.Pooling = True Me._masterConnectionString = builder.ToString End SubNote: 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.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(); mandText = "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() mandText = "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 FunctionNote: 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.Press Ctrl + Shift + S to save all the changes.Open the Program.cs file (for C# projects) or Module1.vb file (for Visual Basic projects).Add the following to the file open in the previous step.(Code Snippet – SQL Azure Tips and Tricks - Ex01 Firewall PrintAllRules method – C#)C#static void PrintAllRules(Firewall firewall){ 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 SubNote: 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.Insert the following code snippet in the above of the file: (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.Update the Main method with the following code: (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 BasicConsole.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.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 12Expected 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 RulesIn Task 1, we added firewall rules via the SQL Azure portal. In this task, we will add firewall rules programmatically.In Solution Explorer, double click on Firewall.cs file (for C# projects) or Firewall.cs file (for Visual Basic projects) to open it.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(); mandText = "sp_set_firewall_rule"; mandType = 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 BasicPublic Sub SetFirewallRule(ByVal rule As FirewallRule) Using conn As SqlConnection = New SqlConnection(Me._masterConnectionString) Using cmd As SqlCommand = conn.CreateCommand conn.Open() mandText = "sp_set_firewall_rule" mandType = 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 UsingEnd SubNote: There is a stored procedure called ‘sp_set_firewall_rule’ in the master database since 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.In Solution Explorer, double click the Program.cs file (for C# Projects) or Module1.vb file (for Visual Basic projects) to open it.Replace the content of the 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 BasicDim 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 classWe 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.To start Debugging select, Debug | Start Debugging, or alternatively just press F5 on your keyboard.Figure 13DebuggingYou should see a console window open with the following output;Figure 14Expected output Note: Notice that the newly created firewall rule is also listed in there.You can also confirm that this firewall rule has been added correctly via the SQL Azure portal.Figure 15Updated firewall settingsTask 4 – Programmatically Delete Firewall RulesSQL 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.In the Solution Explorer, double click the Firewall.cs file (for C# projects) or Firewall.vb file (for Visual Basic projects) to open it.Insert the appropriate following code snippet directly under the SetFirewallRule method.(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(); mandText = "sp_delete_firewall_rule"; mandType = CommandType.StoredProcedure; cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name; cmd.ExecuteNonQuery(); }}(Code Snippet – SQL Azure Tips and Tricks - Ex01 DeleteFirewallRule method – VB)Visual BasicPublic Sub DeleteFirewallRule(ByVal name As String) Using conn As SqlConnection = New SqlConnection(Me._masterConnectionString) Using cmd As SqlCommand = conn.CreateCommand conn.Open() mandText = "sp_delete_firewall_rule" mandType = CommandType.StoredProcedure cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name cmd.ExecuteNonQuery() End Using End UsingEnd SubNote: There is a stored procedure called ‘sp_delete_firewall_rule’ in the master database since 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.In Solution Explorer, double click the Program.cs file (for C# projects) or the Module1.vb file (for Visual Basic projects) to open it.Replace the content of 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 Basicfirewall.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”.To start Debugging select, Debug | Start Debugging, or alternatively just press F5 on your keyboard.Figure 16Start Debugging You should see a console window open with the following output:Figure 17Expected OutputNote: Notice that the firewall-rule “IP Example 1” that we created in the last task is no longer in the list.You can also confirm that this firewall rule has been deleted correctly via the SQL Azure portal.Figure 18Firewall settingsExercise 2: Managing Connections – Logging SessionIdsJust 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: (Transact-SQL): class: RoleManager.WriteToLog method: Task 1 – Implement a SessionId ClassIn 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.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.In Visual Studio 2008, open the beginning solution from File | Open | Project/Solution… Figure 19Opening a project or a solution In the Open Project dialog, browse to Ex02-LoggingConnectionSessionId\begin inside the Source folder of this lab, select the ConnectionLoggingSessionId.sln file in the folder for the language of your preference (Visual C #or Visual Basic) and click Open.In Visual Studio 2008, open Solution Explorer from View | Solution Explorer.Figure 20Opening the Solution ExplorerFigure 21The ConnectionLogginSessionId solution Note: ConnectionLoggingSessionId is a plain .NET Framework 3.5 Console Application.Add a new item to the project by right clicking on the ConnectionLoggingSessionId project and selecting Add | New Item… in the context menu.Figure 22Adding a New ItemIn the Add New Item – ConnectionLogginSessionId dialog, select Code in Categories window, and Class in the Templates window. Enter SessionId for the class name and click Add.Figure 23Adding a New SessionIdOpen the SessionId class by double clicking on the SessionId.cs file (for C# projects) or the SessionId.vb file (for Visual Basic projects) in Solution Explorer. Figure 24The SessionId ClassReplace 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."; 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 BasicImports SystemImports System.Data.SqlClientPublic Class SessionId Public Const SessionIdInfoMessageErrorNumber As Integer = &H9EA0 Private Const DomainBaseAddress As String = "database." 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 SubEnd ClassNote: 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.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()) { mandText = "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 BasicPublic 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.") 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 mandText = "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())" Dim contextInfo As String = CStr(cmd.ExecuteScalar) sessionId = New Guid(contextInfo) End Using Return connEnd FunctionNote: 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 ConsoleIn 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.In Solution Explorer, double click on Program.cs (for C# projects) or Module1.vb (for Visual Basic projects).Figure 25ConnectionLoggingSessionId projectInsert the following appropriate code snippet directly inside the Main 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 AzureGuid 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 BasicDim 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 GuidsessionId.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.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 26Expected OutputExercise 3: Managing Connections – Throttling, latency and transactionsWhen 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 MetricsUpon 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: Commercial Availability and Announcing Business Model: this exercise, we will go through the mechanisms on how to programmatically calculate bandwidth and database costs.Task 1 – Establish a Connection to SQL AzureIn this simple task, we will implement a class called Metrics and create a SQL connection to SQL Azure database.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.In Visual Studio 2008, open the beginning solution from File | Open | Project/Solution… In the Open Project dialog, browse to Ex04-Metrics\begin inside the Source folder of this lab, select the Metrics.sln file in the folder for the language of your preference (Visual C #or Visual Basic) and click Open.In Visual Studio 2008, open Solution Explorer from View | Solution Explorer.Figure 27Opening the Solution Explorer Figure 28The Metrics solutionNote: Metrics is a plain .NET Framework 3.5 Console Application.Add a new item to the project by right clicking on the Metrics project and selecting Add | New Item… in the context menu.Figure 29Adding a new itemIn the Add New Item – Metrics dialog, select Code in Categories window, and Class in the Templates window. Enter Metrics for the Name and click Add.Figure 30Adding a new Metrics classOpen the Metrics class by double clicking on the Metrics.cs file (for C# projects) or the Metrics.vb file (for Visual Basic projects) in Solution Explorer.Figure 31The Metrics classReplace 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 BasicImports System.Data.SqlClientPublic 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 StringEnd ClassNote: 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.Insert the following code snippet directly under the masterConnectionString variable definition. (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.,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 BasicPublic 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.,1433", Me.m_server) builder.InitialCatalog = "master" builder.UserID = Me.m_login builder.Password = Me.m_password builder.Pooling = True Me.m_masterConnectionString = builder.ToStringEnd SubNote: 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 CostsSQL 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 nameDetailstimeThe time stamp when the bandwidth usage took place.database_nameThe name of the database the bandwidth is used ondirectionOnly takes two values; Ingress or Egress meaning incoming or outgoing trafficclassOnly takes two values; External or Internal; meaning external or internal traffic. quantityThe amount of data clocked in KB.In this task, we will programmatically calculate the bandwidth usage cost for the designated SQL Azure server.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(); mandText = "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() mandText = "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 UsingEnd FunctionNote: 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.Open the Program.cs file (for C# projects) or the Module1.vb file (for Visual Basic projects) by double clicking on the file in Solution Explorer.Replace the Main method 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 BasicModule 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 SubEnd ModuleNote: 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.Start debugging, from Visual Studio, Debugging | Start Debugging. Or just press F5 on your keyboard.Figure 32DebuggingYou should see a console window open with the following output;Figure 33Expected OutputNote: It outputs the cost in USD$ on the designated SQL Azure server.Task 3 – Calculate SQL Azure Database CostsThe 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 nameDetailsTimeThe date on which an existing database was deleted or new database is provisioned.SKUBusiness or WebQuantityThe number of databases for the respective SKU after the change.In this task we programmatically calculate the Database costs for SQL Azure server.Open the Metrics class by double clicking on the Metrics.cs (for C# projects) or Metrics.vb (for Visual Basic projects) file in Solution Explorer.Figure 34The Metrics classDirectly 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()) { mandText = "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 BasicPublic 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 mandText = "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 costEnd FunctionNote: 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.Insert the following code snippet directly under:C#: closing bracket of the using statement using (SqlCommand cmd = conn.CreateCommand())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 costDateTime currentDate = startTime;while (currentDate <= cutoffTime){ using (SqlCommand cmd = conn.CreateCommand()) { mandText = "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 BasicIf (lastKnownQuantity = 0) Then Return 0End If' Query for database usage on each day to compute costDim currentDate As DateTime = startTimeDo While (currentDate <= cutoffTime) Using cmd = conn.CreateCommand mandText = "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 UsingLoopNote: 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.Open Program.cs (for C# projects) or Module1.vb (for Visual Basic projects) by double clicking on the file in Solution Explorer.Insert the following code snippet on the Main method 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 Call GetDatabaseCost – VB)Visual BasicConsole.WriteLine("Database cost USD$: {0}", metrics.GetDatabaseCost("Web"))Console.WriteLine("Database cost USD$: {0}", metrics.GetDatabaseCost("Business"))Console.WriteLine()Start debugging, from Visual Studio, Debugging | Start Debugging. Or just press F5 on your keyboard.You should see a console window open with the following output;Figure 35Expected outputNote: We should see the cost for each database under each SKU as well as an aggregated cost value as well.SummaryIn 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. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download