DOCX

SQL Azure Tips and Tricks

By Ricky Howard,2014-08-09 18:24
6 views 0
SQL Azure Tips and Tricks

Hands-On Lab

    SQL Azure: SQL Azure Tips and Tricks

Lab version: 0.0.1

    Last updated: 8/9/2011

Contents

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

    EXERCISE 1: MANIPULATING THE SQL AZURE FIREWALL VIA API’S .............................................. 5

    Task 1 Create sample firewall rules via the SQL Azure portal. ........................................................ 5

    Task 2 Programmatically retrieve firewall rules ............................................................................. 9

    Task 3 Programmatically add firewall rules .................................................................................. 20

    Task 4 Programmatically delete firewall rules .............................................................................. 24 EXERCISE 2: MANAGING CONNECTIONS LOGGING SESSIONIDS ............................................... 28

    Task 1 Implement a SessionId class ............................................................................................. 28

    Task 2 Output SessionId to the console ....................................................................................... 35 EXERCISE 3: MANAGING CONNECTIONS THROTTLING, LATENCY AND TRANSACTIONS........ 37

    EXERCISE 4: SUPPORTABILITY USAGE METRICS ........................................................................ 37

    Task 1 Establish a connection to SQL Azure ................................................................................. 38

    Task 2 Calculate SQL Azure bandwidth costs ............................................................................... 43

    Task 3 Calculate SQL Azure Database costs.................................................................................. 48 SUMMARY ............................................................................................................................................ 53

Overview

    SQL Azure makes the power of Microsoft SQL Server available in a Cloud Hosted offering. Working with SQL Azure should be a familiar experience for most developers because, for the most part, it supports the same tooling and development practices currently used for on premise SQL Server applications. However, there are some small differences between working with SQL Azure and working with on-premise SQL Server. Some of these differences are inherent in the way that SQL Azure has been architected and some will only apply during the Community Technical Preview phase. This Hands-On-Lab will walk through a series of tips and tricks, which are important to working with SQL Azure, such as managing your connection in the event of throttling and querying the metrics views. Objectives

    In this Hands-On Lab, you will learn about:

    ; Manipulating the SQL Azure firewall via API’s

    ; Managing connections

    ? Logging sessionId’s

    ? Throttling and latency

    ? Transactions

    ; Supportability

Prerequisites

    To ensure that you can successfully complete all exercises of this lab. Please refer to the Setup

    document for this lab. This can be located in this labs directory Setup folder. This document will guide you through ensuring all the dependencies for this lab are installed on your machine.

Figure 1

    Setup Directory

Exercises

    This Hands-On Lab comprises the following exercises:

    1. Manipulating the SQL Azure firewall via API’s

    2. Managing Connections Logging SessionId’s

    3. Managing Connections Throttling, Latency and Transactions

    4. Supportability Usage Metrics

Estimated time to complete this lab: 60 minutes.

    Exercise 1: Manipulating the SQL Azure firewall via API’s

    The new firewall feature will deny all connections by default, thereby addressing a major security concern of storing data in the cloud. The new firewall features allows you to specify an allow list of IP addresses that can access your SQL Azure Server. New connections can be added via the SQL Azure portal as well as programmatically.

    In this exercise, you will learn how to programmatically retrieve, create and delete firewall rules for your SQL Azure database.

    Note: This exercise requires you to have a SQL Azure database. If you do not have one, please refer to the Introduction to SQL Azure lab to set one up.

    To learn more about SQL Azure Firewall refer to the MSDN article:

    SQL Azure Firewall:

    http://msdn.microsoft.com/en-us/library/ee621782.aspx

    How to: Configure the SQL Azure Firewall

    http://msdn.microsoft.com/en-us/library/ee621783.aspx

Task 1 Create sample firewall rules via the SQL Azure portal

    SQL Azure allows users to add firewall rules via SQL Azure portal. In this task, we are going to add some sample firewall rules.

    1. Go to the website https://sql.azure.com

    2. Login with your Windows Live account.

    Figure 2

    Logging into the Azure Services Portal

3. Select the SQL Azure project from your project list that you wish to work with (if you have more

    than one SQL Azure project).

    Figure 3

    The SQL Azure portal

4. Select Firewall Settings tab.

    Figure 4

    Firewall settings tab

5. Select Add Record.

    Figure 5

    Adding an exception to the firewall

6. Enter “Allowed Host 1” and your IP address shown in the Custom Firewall Settings dialog, into

    IP Range, and click on Submit.

Figure 6

    Adding an exception to the firewall

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

    7. Repeat step 5 and 6 for the following values.

    Name Start IP Address End IP Address

    Allowed Range 1 192.168.0.0 192.168.0.255

    Allowed Range 2 192.168.1.0 192.168.1.255

    Note: These IP addresses are for demonstration purposes only. We will later retrieve these firewall rules programmatically. They have no real effect on your SQL Azure server, as they are private IP Addresses.

    Your firewall settings should look like the following:

Figure 7

    Firewall settings

Task 2 Programmatically retrieve firewall rules

    SQL Azure allows us to programmatically retrieve firewall rules; in this task we are going to retrieve the

    firewall rules that we entered via SQL Azure portal in task 1. 1. Open Microsoft Visual Studio 2008 as Administrator, from Start | All Programs | Microsoft

    Visual Studio 2008 right-click Microsoft Visual Studio 2008 and choose Run as Administrator.

    2. If the User Account Control dialog appears, click Continue.

    Figure 8

    Running Visual Studio 2008 as administrator

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

    Figure 9

    Opening a project or solution

4. In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse

    to C:\PDC09AzureTrainingKit\Labs\SqlAzureTipsAndTricks\Source\Ex01-

    ManipulatingFirewallviaAPI\begin, select FirewallAPI.sln in the folder for the language of your

    preference (Visual C# or Visual Basic) and click Open.

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

    Figure 10

    Opening the Solution Explorer

    Figure 11

    Firewall API solution

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

6. Add a new item to the project by right clicking on the FirewallAPI project

    7. In the context menu select Add | New Item…

Report this document

For any questions or suggestions please email
cust-service@docsford.com