Book Image

Microsoft SQL Azure Enterprise Application Development

By : Jayaram Krishnaswamy
Book Image

Microsoft SQL Azure Enterprise Application Development

By: Jayaram Krishnaswamy

Overview of this book

SQL Azure is a database service in the cloud. Based on Microsoft’s Windows Azure platform, SQL Azure is well suited for web facing database applications. Are you interested in moving your business applications and data to the cloud but are not sure how to go about it? Look no further; this book covers all you ever wanted to know about taking your relational enterprise data to the cloud using SQL Azure. This book will show you how to manage SQL Azure using various tools and also guide you in developing enterprise applications and business intelligence solutions. It will take you through migration and synchronization scenarios with a variety of tools, help you in working with Microsoft technology still in incubation, and in leveraging hybrid applications that exist partially in the cloud and partially on the ground.This step-by-step tutorial begins by providing an overview of Cloud Computing, introducing you to the most significant Cloud Computing implementations. You will then learn the mechanics of signing up and obtaining an account on Microsoft Windows Azure and logging into the portal.The book then dives deep into SQL Azure, showing you how to provision a SQL Azure Server, and how to create/delete databases as well as set up Firewall rules so that you can access SQL Azure from tools/Client programs. It is then followed by details of how to access SQL Azure using Client and Server APIs.Other content includes, a comprehensive description of tools required to access SQL Azure and how to use them and how to populate and migrate SQL Azure databases using a variety of tools.Finally, the book will detail with examples, data-centric applications that leverage a mix of on-site data and Cloud based data, how to synchronize data and extend the applicability of SQL Azure data by disconnected applications on mobile media, and synchronize services for globally distributed data. After covering the topics of services which are in production, the book will then cover future developments as well as a complete update to SQL Azure at the time of writing this book.
Table of Contents (17 chapters)
Microsoft SQL Azure: Enterprise Application Development
Credits
About the Author
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface

Windows Azure


In the following sections, a summary of the Windows Azure Platform details are described. The hardware system at the Microsoft Data Centers provides the physical support for all the programs and activities, and the software architecture is built on this resource.

Azure platform details

The Azure platform is a collection of services shown in the following image that operate in the cloud and orchestrate to provide businesses with an integrated set of programs that improve and enhance their business. The following image shows the three components of the Windows Azure Platform:

Businesses develop their application on their site in a simulated cloud environment, which may be compared with how you develop web applications on the development web server and then deploy it over an internet connection. End users access these applications on the cloud using familiar internet protocols such as SOAP, HTTP, REST, and more. Prior to February 2010, when the Windows Azure Platform was open to the public, the operating system changed over from CTP to Guest Operating System 1.0. Since then many updates to the operating system have been made as shown in the following table taken from Microsoft documentation at this URL http://msdn.microsoft.com/en-us/library/ee924680.aspx.

Guest OS

Release date

Windows Azure Guest OS 1.6 (Release 201008-01)

September 1, 2010

Windows Azure Guest OS 1.5 (Release 201006-01)

July 20, 2010

Windows Azure Guest OS 1.4 (Release 201005-01)

June 17, 2010

Windows Azure Guest OS 1.3 (Release 201004-01)

April 30, 2010

Windows Azure Guest OS 1.2 (Release 201003-01)

April 5, 2010

Windows Azure Guest OS 1.1 (Release 201001-01)

January 30, 2010

Windows Azure Guest OS 1.0 (Release 200912-01)

December 7, 2009

However, in order to develop for the cloud you also need the appropriate software development kit (SDK). Not every release of OS has an associated SDK. The SDKs released as of this writing and the appropriate OS for which the SDK can be used is shown in this table from the same documentation. Windows Azure SDKs provide the software support for running the development fabric (a mini version with most features of the application running on the cloud platform) on the local (on-site) machine and the development server.

Windows Azure SDK version

Compatible Windows Azure operating system releases

Windows Azure SDK version 1.2 (June 2010)

Guest OS 1.3 or newer

Windows Azure SDK version 1.1 (February 2010)

Guest OS 1.1 or newer

Windows Azure SDK version 1.0 (November 2009)

Guest OS 1.0 or newer

The Azure platform is accessible from programs in the cloud as well as from on-premises applications. In this sense, it works seamlessly between what the businesses have on their premises and those they have implemented, or propose to implement, in the cloud. In order to maximize the ROI and commensurate with their security needs, the businesses may move their data and applications, whole, or in part to the cloud. The present trend appears to be that the businesses move their less serious data initially when they buy into the cloud platform; and as they get more comfortable and gain confidence with the platform, move their applications and data to the cloud.

While security and reliability are of utmost concern to most businesses the most alluring features of "Cloud Computing" is the ease and rapidity with which a business on the internet can be installed. These concerns (outages, hacking, malware, and data loss) are making businesses refrain from large scale adoption according to several polls. Another preoccupation of business is to do with how customer data is stored. When the business removes data from its cloud application for whatever reason, what surety do they have that all of their data is completely removed without a trace? In order to satisfy the round the clock availability, the data is replicated to many locations in the data center, sometimes even to geographically separated locations. For small businesses however it appears to be an excellent fit where economic returns are tangible.

While all security concerns have not been addressed to the satisfaction of the users several major measures are implemented in the form of forms authentication for both Azure Table Storage and SQL Azure and enabling SSL with a self-signed certificate on Windows Azure. Also Microsoft has implemented comprehensive security measures for all physical security-related issues at its data centers.

The Platform hardware is housed in the various Microsoft Global Hosting Data Centers (see for example: http://www.datacenterknowledge.com/archives/category/microsoft/, also http://www.computerworld.com/s/article/9118190/Ozzie_details_Azure_Microsoft_s_cloud_version_of_Windows) with two in USA and one each in East Asia and Europe. The data centers are large installations (tens to hundreds of thousands of square foot area) with containerized hardware comprising of more than 2000 servers and other needed network interfaces. The infrastructure consists of Virtual Machines running Windows Servers 2008 provided by a Microsoft modified Hyper-V-based hypervisor. When a developer desires to host an application, the VMs get provisioned. User code gets into these VMs and users interact with the VMs using the internet. An excellent reference to some of the hardware details are found in this reference: http://whitepapers.techrepublic.com.com/thankyou.aspx?&tag=content;leftCol&docid=972383&view=972383&load=1&regSrc=wp&authId=ynwzchQWj6So7PJTwBg6rhE0o0VIkE8N2rj+2Ss6Rdz47retSuySSlC+kCljTZba.

Platform components

As previously mentioned, the Microsoft Azure platform URL accessible through its portal, using Windows Live Login, consists of the three components, which will be discussed later in this chapter. The details of accessing the portal are described later in the chapter.

In the next section, an overview of each of the three components is described. For the purposes of this book, the Windows Azure Service, which consists of the Compute and Storage items and the AppFabric are only tangential, as the emphasis of this book is on SQL Azure. However, these components will be addressed in the later chapters with some more detail so far as they are used for creating web-facing applications using SQL Azure, or on-premises SQL Servers.

Windows Azure

As described previously, Microsoft Data Centers contain the clusters of Windows Server 2008 (64 bit), which are virtualized to provide the Virtual Machine Images (Cloud Virtual Machines), which in turn hosts the User Code and Runtime APIs. These are accessed through the portal using the Service Management Service (a REST-based Service). In addition to compute VM clusters, the data center also hosts Storage Clusters for the storage service. These are also accessible through Representational State Transfer (REST), a new client-server software architecture built on transfer of resource representations. The developers using Visual Studio 2008 SP1 and Visual Studio 2010, presently in Beta 2 (VS 2010 will be available during 2010), and the Windows Azure SDKs as mentioned previously need Windows Azure Visual Tools: http://www.microsoft.com/downloads/details.aspx?familyid=6967FF37-813E-47C7-B987-889124B43ABD&displaylang=en. The servers hosting SQL Azure Services are separate from the ones used for the Compute and Storage Services.

Compute: Windows Azure Hosting Service

The Windows Azure Service, as you will be seeing later in Exercise 1.2, consists of components called roles enclosed within a service boundary built with managed code. This is where your executable program is hosted using this role-based model.

By design, it is mandated that the service should have at least one role. There are two kinds of roles; the Web Role, supported by IIS7 and ASP.NET, is well suited for web-applications defining a single HTTP/HTTPS endpoint for external clients and a Worker Role for running background programs. The storage service, the other component, can be accessed by the worker role to feed data to the web role. An application hosted on Windows Azure can have more than one role of each kind and a service hosted on Windows Azure can run multiple instances of these roles. The Azure Platform can replicate these across multiple virtual machine images. From an external location the service is reachable by a single Virtual IP Address (VIP) for load-balanced traffic to multiple end points.

The web role to worker role communication takes place through internal endpoints over TCP and HTTP shown schematically in the following figure:

While the previous figure shows the constituent parts of the compute service, the application is developed using Visual Studio at the user's premises. Visual Studio is the main programming IDE used. The SDKs provide the necessary hook ups to the Cloud Simulation and a number of different kinds of application templates (ASP.NET Web Application, WCF Service, Web Service, and more). The tools are needed for the application to be run in the development fabric (the program that simulates the Cloud environment locally on the premises) with the support of a locally installed IIS 7 server.

Typically, applications are developed in Visual Studio and tested in the simulated development environment followed by deployment. In the development process the development fabric comes into play and this is where 'debugging' of the application can be done effectively. The user will also have access to development user interface and a development local storage. The deployment can be a two-step process with an initial deployment to a Staging Slot (a client-specific staging URL) followed by a Public or web-facing URL, the Production Slot. After deployment, logging the messages is the only way available for developers to track the application.

The billing for the Windows Azure-hosted service starts whenever an application is hosted irrespective of whether it is in Production or Staging, irrespective of whether it is stopped or active. If you don't want to be billed you delete the application. In order to delete you must first stop it. In fact the Windows Azure Portal posts a warning to this effect.

Windows Azure storage

You need to get a storage account to work with Windows Azure Storage. Cloud-hosted applications use client data stored in Storage Accounts, and/or relational data in the SQL Azure. The Storage Account accessed by the Storage Service API, which as mentioned previously, is REST-based. You manipulate data in the Storage Service using POST, PUT, and DELETE requests and retrieve using GET. SQL. Azure data, on the other hand, is accessed by both applications as well as on-premises SQL Servers using T-SQL and ADO.NET. Users can access this Azure Storage Service from within an application running in Windows Azure, or directly from the internet by sending HTTP/HTTPS (version 1.0 for some operations and HTTP/HTTPS 1.1) requests and processing the returned responses.

Users should make sure of the storage service version they will be using by looking it up here: http://msdn.microsoft.com/en-us/library/dd894041.aspx. Just as in the case of hosted services, a development storage service is provided for local testing to simulate cloud storage service using local SQL Express servers (default 2005 or 2008 versions) or any local instance of SQL Server.

The following figure shows the components of the Windows Azure Storage. The Windows Azure Drive was added on Feb 1, 2009 which writes to Page blobs. The Windows Azure Drive makes it easier to migrate applications to the cloud as it appears as another durable disk drive. For applications that use file I/0 the disk availability provides a high degree of flexibility using the standard Windows NTFS APIs.

The Storage Account you hold is the top element in the namespace, which is accessed with authentication. The REST API exposes the storage account as a resource consisting of:

  • BLOBS — Stores Text and Binary data

    • Block blobs for streaming

    • Page Blobs for random read/write operations organized in containers

  • Queues — Stores unlimited 8KB sized messages

    • Messages accessible to storage account holder

    • Messages added to the end of the queue and retrieved from the front of the queue

  • Tables — Provides structured tables (not relational)

    • Store data as a collection of entities (similar to rows) with a primary key and a set of properties unlike relational databases, which are schema bound

    • Every entity has PartitionKey and RowKey properties handled by the developer and a Timestamp property provided by the server

    • REST API is ADO.NET Data Services Compliant

    • Any number of tables with uniquely identifiable names can be created

  • Windows Azure Drive — NTFS Virtual Hard Drive volumes for applications

    • Random read/write to Page Blob up to Page Blob limit of 1 TB

    • Supported by Windows Azure OS version 1.1 or later. Data to drives uploaded/downloaded through Page Blob

SQL Azure

SQL Azure is a relational database built leveraging SQL Server Technology in its latest embodiment — the SQL Server 2008 R2 (version 10.5). SQL Azure services offer a platform for hosting SQL Azure databases in the Windows Azure platform. Users can build web-facing database applications using SQL Azure as the 'cloud end'. In mid-February 2010, SQL Azure was updated with SU1.

Being built on SQL Server technologies and the agile, extensible, scalable data center infrastructure, SQL Azure provides a highly scalable, highly available, multi-tenant database service in the cloud. The ease with which you can create a database is truly amazing as the provisioning is extremely fast as we see later in the book.

The provisioning and management offered by this cloud service abstracts away an important part of how the databases are traditionally installed and managed. In fact many businesses do not need to employ IT professionals who work on the physical (hardware) side of database installation and management. This will be one of the selling points where the ROI is highly perceptible. As the databases are replicated in the cloud with a high degree of redundancy, users need not concern themselves regarding replication issues to the extent they were concerned traditionally. Also with the SQL Server being a mature technology there is a very little learning path to move user data to the cloud. The very familiar T-SQL is supported and the databases created in the cloud can be accessed through familiar tools such as Microsoft SQL Server Management Studio, SQLCMD, and so on.

The SQL Azure service architecture follows the schematic shown in the following figure:

The hardware is installed in the Microsoft Data Centers. SQL Server instances are installed on these machines, which provide the SQL Server and SQL Server Management Services. The SQL Azure hardware is separate from hardware used for Azure Compute and Storage but is shared across all clients. The SQL Azure fabric controller works in the platform layer. The SQL Azure Fabric is a part of the computing system that handles automatic failover, load balancing, and automatic replication between the servers in the data centers. It is, therefore, tightly integrated with the data center infrastructure such as servers, network, and storage. The services layer does most of the provisioning, billing and metering and connection routing. The Services layer is accessed by the Client through the portal using TDS (Tabular Data Stream). Tabular data stream is also accessible through the familiar ODBC and ADO.NET data services. SQL Server applications and tools can access the services layer using TDS but customer applications written in any of the familiar languages such as PHP, Java, and .NET languages can access through ODBC and ADO.NET and through HTTP/REST. These are the same languages that access on-premises SQL Servers; therefore, it makes it very easy to work with SQL Azure.

At present, in addition to SQL Azure, the SQL Azure Data Sync application — based on Microsoft Sync Framework 2.0 — provides a useful tool to synchronize SQL Azure data with on-the-premises SQL Server, thus enabling remote access to data on the cloud to mobile clients connecting through remote sites.

Although SQL Server Integration Services, the SQL Server Reporting Services, are not on the cloud, these applications available in the local servers can be used with the cloud-based SQL Azure databases. It is envisaged that these Business Intelligence (BI) applications will also be available in a future version of Microsoft Azure Platform.

Azure AppFabric

The AppFabric addresses the needs of securely accessing widely distributed resources. What used to be called .NET Services in the earlier versions of Windows Azure is now called AppFabric. As you will see later in this chapter, you can access the AppFabric from the Windows Azure Portal (http://windows.azure.com) after logging in with your Windows Live ID. It is a service just like the Windows Azure discussed previously.

The AppFabric consists of the Service Bus and Access control. These provide building blocks to .NET Applications to function reliably in the Microsoft Windows Azure platform. The necessity arises because one needs authentication and access to widely distributed resources that programs need, which reside behind machine firewalls and network firewalls. Furthermore, the stateless nature of web applications together with the changeable nature of the hosting environment makes for a loosely connected system requiring a way to manage connections. AppFabric provides these very important functionalities, which are needed for taking applications to the cloud:

  • Bidirectional application connectivity through firewalls

  • Claims-based access control

The Service Bus "relays" messages from clients to applications running on the premises sidestepping obstacles such as firewalls, NATs, and other objects as shown in the next figure. In this respect, it may be compared to a "relay phone" in the USA, which acts as a relaying point to connect your phone to a phone in another country, for example, a procedure used by Skype. Many Internet chat services work on the same principle of relayed connectivity.

The "Service" opens a bidirectional connection to the "Relay" by an outbound connection and a "Relay Address". The client (Application) connects to this address through an outbound connection. This way the Client can connect to the Service bypassing the barriers. The client application need not know where the service resides. The Service Bus provides a network infrastructure for interconnecting applications using a variety of messaging patterns.

The Service Bus supports the following forms of communication:

  • One way messaging

  • Request-Response

  • Publish-Subscribe (multicast)

  • Asynchronous

The security provided by the barriers is in no way compromised. Additionally, the Service Bus can also negotiate for direct connection between applications under some circumstances as in the following image:

The Service Bus works with Access Control through claims and a trust relationship. Both the client and the service must provide credentials to the Access Control to obtain a security token. Access Control provides a generic way to implement the authentication and authorization (using claims) program separate from applications. This relieves the developers from writing custom authentication and authorization routines and rules. The Access Control provides a claims transformation program that can leverage federated identity (delegated identity) providers facilitating enterprise clients to use their authentication in Active Directory. It is envisaged that other Identity Providers, such as Facebook connect, Google Accounts, and Windows Live ID will be available in the future.

Both of these services are accessible through open protocols and standards such as SOAP, REST, ATOM, and ATOMPUB, which allows more flexible program integration.

The AppFabric SDK, which may be downloaded from here: (http://www.microsoft.com/downloads/details.aspx?FamilyID=0bd0b14f-d112-4f11-94bf-90b489622edd&displaylang=en; Version 1.0 in Beta 1, November 2009) provides a great deal of help in understanding and working with these services by way of codes and samples. Windows Communication Foundation WCF is the primary programming model for working with the Service Bus. The SDK simplifies the task of integrating these services into your .NET Applications on the premises by introducing new concepts like WCF Relay Bindings. Similar SDKs are also available for Java and Ruby developers.

Most recently, Microsoft has released (http://blogs.msdn.com/b/windowsazureappfabric/archive/2010/09/19/windows-azure-appfabric-sdk-september-release-available-for-download.aspx) Windows Azure AppFabric SDK (Version V 1.0) for both x32 and x64 bit computers. If you want to use this September release follow this link:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=39856a03-1490-4283-908f-c8bf0bfad8a5&displaylang=en.

However, note that the name of the installer has now changed to WindowsAzureAppFabricSDK-x86.msi.

System requirements

These requirements are for the tools and SDKs used by developers (clients) to create, host, and modify their applications and services on the Windows Azure Platform.

Note

The Windows Azure provides for backward compatibility with Version 1.0 by specifying the same in the configuration files.

Only one version of the tool is to be installed on a computer.

  • Supported Operating Systems:

  • Windows 7 (Hotfixes for Visual Studio stability and Fast CGI support not required)

  • Windows Server 2008

  • Windows Server 2008 R2 (Hotfixes for Visual Studio stability and Fast CGI support not required)

  • Windows Vista SP1

  • Windows Vista SP2

    IIS version

  • IIS 7.0 [with ASP.NET and WCF HTTP Activation and optionally CGI]

  • Use guidance here: http://learn.iis.net/page.aspx/28/install-iis-7-on-windows-vista-and-windows-7/ to install IIS 7.0

    Development IDEs

  • Microsoft Visual Studio 2008 SP1

  • Microsoft Visual Studio 2010 Beta 2 — Native debugging Hotfix not required

  • Microsoft Visual Web Developer 2008 Express Edition SP1

    SQL Servers

  • SQL Server 2005 Express Edition

  • SQL Server 2008 R2 or later

  • Hardware and software required

  • Getting started with Azure Platform and accessing services

In addition, the hands-on exercise in this chapter will teach you how to acquire the needed materials and permissions to work with this platform.

Hardware and software required

For working with the practical elements in this book, the following hardware and software components were used:

  • Aspire 4810TZ notebook computer

Intel Pentium SU2700/1.3 GHZ, 3GB DDR3 SDRAM-1066MHz, 24x(CD)/8x(DVD), 14in TFT Active Matrix, Ethernet/Wireless LAN, 3 x Hi-Speed USB Interfaces with Vista Home Premium:

  • Windows 7 Ultimate (installed after removing Vista Home Premium)

  • Wired and Wireless LAN

  • SQL Server 2008 R2 Nov-CTP Enterprise (Evaluation)

  • Visual Studio 2008, SP1

  • Visual Studio 2010 Beta 2