Cloud Computing: An Introduction to SQL Azure

With SQL Azure, you can realize the benefits of a familiar relational database platform, plus the benefits of cloud computing.

Joshua Hoffman

Data is the lifeblood of business. Ensuring that it’s secure, available and easily accessible are fundamental requirements of any IT department. More importantly, ensuring that data is used well—to drive processes, inform decision making and react intelligently to changing circumstances—is what differentiates successful businesses from those left behind.

The manner in which businesses ensure the availability of data is rapidly changing. Hosted services—and the very idea of software as a service—for everything from core datacenter functions like e-mail and business intelligence, to personal applications like photo-sharing and file synchronization, have become an everyday part of how we interact with our information. Cloud computing has enjoyed a meteoric rise over the past few years, both as a concept and as a practical component of IT infrastructure.

One particularly compelling cloud-computing solution is Microsoft SQL Azure. SQL Azure is a powerful, familiar infrastructure for storing, managing and analyzing data. It also provides the benefits of cloud computing. Shared, hosted infrastructure helps reduce both direct and indirect costs. A pay-as-you-go model helps achieve greater efficiency. And high availability (HA) and fault tolerance are built in.

Still, certain aspects of this new model pose challenges for the IT professional. Making the right choices around using hosted services or in-house infrastructure is a profound challenge. You must ensure smooth integration between those approaches and maintain strong security and business-continuity plans. These are just some of the issues IT professionals need to consider when evaluating cloud-computing solutions for their organization.

Here, I’ll introduce you to SQL Azure from the IT professional’s perspective. I’ll discuss how SQL Azure works, where it would fit best in your environment, how to get started and how to ensure a smooth integration with your existing data infrastructure.

What Is SQL Azure?

SQL Azure is part of the Windows Azure platform: a suite of services providing hosted computing, infrastructure, Web services and data services. The SQL Azure component provides the full relational database functionality of SQL Server, but it also provides functionality as a cloud-computing service, hosted in Microsoft datacenters around the globe.

SQL Azure is generally a good fit any time you need database services. There are a few aspects where SQL Azure would be a particularly good fit:

• Collaboration: By moving your data into the cloud, SQL Azure helps you build a collaboration hub. When you need to share access to information across organizational boundaries, between branch offices and so on, hosted services let you lower network roadblocks while ensuring data security.
• Scale: One of the inherent benefits of cloud computing is the ability to add infrastructure on-demand, which lets you dynamically scale out application capacity as the need arises. This is particularly helpful when peak usage of hosted applications changes dramatically over time (such as retail applications during holiday periods and so on).
• Consolidation: As the need for relational database functionality becomes more granular and trickles down to individual departments and workgroups, internal management of “database sprawl” can become a challenge. SQL Azure lets you consolidate departmental and workgroup databases, turn-key provisioning and simplified management so administrators can more easily meet departmental needs.
• Hosted Applications: If you’re already hosting other workloads on the Windows Azure platform (such as Web applications), integrating hosted relational database workloads on SQL Azure is a snap. It also helps to maximize application performance by reducing network traffic.
• Cost Efficacy: Depending on the workloads you’re looking to install, the pay-as-you-go model that cloud computing offers may provide the most cost-effective approach.

Generally speaking, the essential functionality of SQL Azure and the traditional SQL is interchangeable, with the exception of some database size limitations on the Windows Azure platform (read about more about that at SQL Azure FAQ). Each approach offers different benefits and costs in terms of infrastructure, so it’s simply a matter of selecting the best fit based on the individual business case. You can also spread workloads across both platforms in a tightly integrated fashion.

Getting Started with SQL Azure

Getting started with SQL Azure is simple, especially if you’re familiar with traditional SQL Server management tools like the SQL Server Management Studio (which you can use to manage SQL Azure instances).

The first step is to set up an account for the Windows Azure platform, which gives you access to Windows Azure services including Windows Azure, SQL Azure and Windows Azure AppFabric (which provides a means for developers to connect applications and services across organizational and network boundaries). Once you’ve established your account, you can access the SQL Azure control panel (see Figure 1).

Figure 1 A close look at the SQL Azure control panel

The SQL Azure control panel lets you create new databases within your account. You can define each database as either a Web Edition or Business Edition database. A single Web Edition database can support up to 5GB of data, and a single Business Edition database will support up to 50GB. (The business edition uses 10GB billing increments: 10GB, 20GB, 30GB, 40GB and 50GB.)

The control panel also gives you a server name for your SQL Azure instance, as well as ADO.NET or ODBC connection strings you can use to connect to your database (based on SQL Authentication; Windows Authentication is not supported in SQL Azure). From there, you can use either the connection strings within an application, create an ODBC object or plug in the server information to a management tool like SQL Server Management Studio (see Figure 2).

Figure 2 SQL Server Management Studio connected to a SQL Azure database

As an added measure of security, you’ll notice the Firewall Settings window within the SQL Azure control panel. This lets you specify the IP addresses from which your databases are accessible. You can input any combination of an individual address, a range of addresses or an allowance for other Microsoft services (so that your own Windows Azure instances and hosted applications have access to your databases).

From here, interacting with the data you store in SQL Azure is identical to working with your own SQL Server. Using SQL Server Management Studio, you can perform all of the traditional SQL Server tasks: creating tables, views and stored procedures; importing data; executing T-SQL statements and so on.

A cloud-based database should offer some cloud-based management tools, as well. As a lightweight alternative to SQL Server Management Studio, SQL Azure Labs has created a Web-based management tool for SQL Azure databases, called Microsoft Project Code-Named “Houston.” It gives you a simple way to quickly develop, deploy and manage databases in the cloud.

Created with Microsoft Silverlight, Project Houston provides a cross-platform, Web-based database-management tool for basic database-management tasks such as authoring and executing queries, designing and editing a database schema, and editing table data. It’s worth noting, though, that Project Houston is currently available only as a community technology preview (CTP), so it’s not officially supported by standard Microsoft support services.

Integration with SQL Server

There are many ways to integrate SQL Azure with your existing SQL Server infrastructure. Most of the traditional means for integrating and migrating data across servers are available for SQL Azure as well, including SQL Server Integration Services, which allows for data import/export, data transformation, data warehousing and so on. SQL Azure also supports the SQL Server Import and Export Wizard included with the SQL Server Management Studio toolset for copying databases, and migrating data to and from SQL Azure (see Figure 3).

Figure 3 Working with the SQL Server Import and Export Wizard

There’s another option for integrating SQL Azure into your existing infrastructure in the community-developed toolset called the SQL Azure Migration Wizard. This wizard will walk you through the selection of existing SQL Server 2005/2008 objects and create corresponding SQL scripts to migrate those objects to SQL Azure. Though not officially supported by Microsoft, there’s broad community support for the SQL Azure Migration Wizard, including videos to help walk you through the process of using the tools. 

Finally, SQL Azure Data Sync (currently available as a CTP) is a cloud-based synchronization service designed to provide bidirectional data synchronization between on-premises SQL Servers and SQL Azure, as well as between SQL Azure databases in different locations (providing geo-replication functionality).

SQL Azure Data Sync is based on the Microsoft Sync Framework, so it provides a familiar approach for synchronizing data to and from multiple locations. Once again, though, it’s worth noting that SQL Azure Data Sync is a CTP, and is not currently supported by Microsoft support services.

The benefits of cloud computing are undeniable. The cost-efficacy, server consolidation, on-demand provisioning and geographic diversity that cloud computing offers represent just the beginning of the advantages that we’ll come to realize by moving data into the cloud. SQL Azure combines the powerful performance and familiar environment of SQL Server with the benefits of cloud computing. It should fit in well as a solution for any organization looking to build a more dynamic, cost-effective data-management infrastructure.

Joshua Hoffman* is the former editor in chief of TechNet Magazine. He’s now an independent author and consultant, advising clients on technology and audience-oriented marketing. Hoffman also serves as editor in chief of ResearchAccess.com, a site devoted to growing and enriching the market research community. He lives in New York City..*

Related Content

• Windows Azure: Getting to Know the Runtime Environment
• Windows Azure: Cost Architecting for Windows Azure
• SQL Q&A: Shrinking, Growing and Redesigning Databases