SQL Server Reference Guide

SQL Azure is a cloud-based Relational Database Management System (RDBMS) from Microsoft. Before I cover how it works and where you can use it, I’ll explain how it differs from an “on-premise” or locally-owned copy of SQL Server.

I think of technology within an organization in three big circles, which can overlap.

Figure 1

The first circle, there at the bottom and in the front, is “Infrastructure.” This includes power, cooling, networking, and even security appliances and software. You can think of it as the “road” a computing device needs to get from one location to another.

The second circle is the “Platform.” This includes the server hardware, printers, SANs, firmware, Software (Operating Systems and SQL Server, among others) and other components you need to provide an end-result to the users.

The final circle is “Data.” This includes structured data, like what is stored in SQL Server databases, and un-structured data such as Microsoft Office documents and text files. In this conceptual diagram, I’m not talking about the databases themselves — I’m referring to the data they hold.

These circles overlap because many of the components share parts with each other, and of course they are all dependent on each other to provide a path to a result. For instance — when a user clicks on a button in an application, the signal rides across the network using electricity (Infrastructure) to a server which holds the code for the application (Platform), makes a change to some data (Data) and returns the result to the user. With only Infrastructure, the user can’t do any meaningful work. Without Infrastructure, the application has no way to transmit the data back to the user. Without a server, the application code can’t exist anywhere.

As a technical professional, the tasks you perform each day depend largely on the circle you’re in at the time. I’ll ignore for the moment that we all work a little in each circle — for this discussion, let’s assume that you’re a “pure” Data Professional — you only write code, for instance, or manage and monitor a database Instance. Of course, that’s not all we do, but it will simplify this diagram a bit.

From our standpoint, these three circles can’t be broken up. We see an on-premise need for all three functions to provide data to our users. But from the standpoint of the developer, this isn’t always true. They can write code on almost any platform. They still need Infrastructure to move things around, and of course the “data” in this case is conceptual — they don’t care where it lives. This is also true of the organization’s owners and managers. A CEO, for instance, views the three circles as a cost, from hardware to software licenses to staff to run them all. But in fact he or she only cares that the business needs are met — not caring necessarily how that happens.

This is where, I think, the big divide comes for Data Professionals and Business People. The technical professional thinks of all of the effort for transport, optimization and security of a data path for an application; and the business people tend not to focus on that as often. Business managers are very attracted to the fact that they can quickly provision a SQL Azure system, without having to buy a server or wait for it to be configured. And they like that it is patched and upgraded in the background, all the time, with no downtime. They also want to buy more space and processing power as they need it, and stop paying for it when they don’t. This makes the platform different than an “on-premise” system that they have to buy, staff, license and maintain.

So with that explanation, I’ll explain what SQL Azure is, and then I’ll explain some places where it seems to work well within the “three circles”. It’s up to us as data professionals to help the business see where a “cloud” solution fits, and where it makes more sense to build an infrastructure and platform locally.

SQL Azure Architecture

SQL Azure is not a traditional Instance of SQL Server that runs on another system somewhere. In fact, you don’t think of an Instance at all when you talk about SQL Azure — you focus on the database level.

SQL Azure is a variant of SQL Server. However, it is a specialized Instance, so you’re not able to use your “normal” processes of managing the server. For instance, if you connect to the Azure database using SQL Server Management Studio (more on that process in a moment) you don’t need to right-click on the Instance name and make changes to memory, file locations and so on. You also don’t need to specify database parameters such as the recovery model or FileGroups using Properties or the sp_configure system stored procedure.

This can cause a bit of confusion for the data professional. Since these settings impact performance, security and so on, we often find that not having them makes us a bit nervous about using it, but I’ll explain where it fits shortly — even with these constraints.

The architecture, then, starts at the database level. You don’t connect to an Instance, you create databases. I’ll show you how to do all that in a moment. Once inside the database, you can create a more limited set of objects, but rich enough for many projects. You have tables, views, stored procedures, functions, triggers and data types available, along with users, database roles, and schemas. You also have Synonyms available.

Databases come in two sizes and types: As of this writing, a “Web” database is 1GB in size, and a “Business” database can be from 10-50GB in size, in increments of 10GB each. These databases are automatically created with the proper recovery levels, file layouts and are mirrored three times for disaster recovery. The difference is the cost for each type of database.

Speaking of disaster recovery, as of this writing there is no BACKUP DATABASE command. You can copy the data out using the bulk-copy program(bcp) utility or SQL Server Integration Services (SSIS), and you can also use “Sync Services” to perform a “trickle backup” to another location — even locally if you with. I’ll give examples of that in another tutorial.

Installing and Configuring a SQL Azure Database

Technically, you don’t “install” SQL Azure. Using a credit-card and a Windows LiveID, you connect to the Azure developer’s portal located here: https://sql.azure.com/ProjectViewer.aspx. Once you’ve laid your money down and signed in with your LiveID, you’ll be asked to create a new “Project” — which will hold one or more databases. Here is my current Projects screen:

Figure 2

Once you click your project name, you’re brought to the database administration panel. I’ve created the UniversalDB from a previous tutorial, and in the graphic below you can see I’ve blotted out my server and administrator’s name. You will see those on your own screen once you create a database.

Figure 3

To create a new database, you simply click the Create Database button and make the selection for Web or Business Databases, along with the size:

Figure 4

You won’t have access to the recovery level, size and placement for the data files. You don’t need to set those, since the SQL Azure system will handle that for you.

Connecting to a SQL Azure Database for Coding and Applications

With the database settings created, you have two more steps to complete to connect in to your system and begin using it. The first step is to open the TCP/IP range on SQL Azure to allow your systems to connect in. During development, I recommend you only allow the developers and administrators and testers the right to connect, and then open it further once you want to allow connections from your middle-tier or direct-connected applications.

Figure 5

The next step is to figure out your connection strings for the applications and your management tools. This is actually quite simple — you’ll set the administrator name and password right from the main Projects panel, and to generate a string for the database, click on the database and then click the “Connection Strings” button.

Figure 6

You can use those strings right in your application, and to manage and program the database.

You can connect to the database using various tools, including SQL Server 2008 R2 SQL Server Management Studio (SSMS) and a graphical interface called “Project Houston,” which you can find here: https://www.sqlazurelabs.com/houston.aspx. Here’s a shot of that running on my system:

Figure 7

Using SSMS 2008 R2, you connect to the database just like any other Instance of SQL Server, but you need to use the full name of the server, SQL Server Authentication, and you need to specify the name of the database to connect to. Once you do, the only differences are the icon for the server, and how many items show up on a right-click and in the object list:

Figure 8

You can also use the SQLCMD command-line tool from SQL Server 2008 R2 to connect to a SQL Azure database. You’ll need to supply the full server name, SQL Server authentication, and database name just as in SSMS.

As I mentioned earlier, you’ll also need to ensure you set the Firewall extensions in your SQL Azure developer’s portal to allow your workstation to connect. All of your tools will use the same TCP/IP address, so you only have to open the workstation’s IP on SQL Azure. When you’re ready to allow users into the server, you’ll need to find out how they are getting to the Internet to enter their ranges.

Use-Cases for SQL Azure

To be very clear, SQL Azure isn’t intended as a complete replacement for an on-premise system. The size limitations alone preclude that. Also, if your application requires some of the advanced features in SQL Server and Transact-SQL, they obviously won’t fit for SQL Azure. If you have huge Extract, Transform and Load (ETL) or other processes that move massive amounts of data across the network, they will also not be appropriate for SQL Azure.

SQL Azure is best evaluated in two cases: Applications with location-independence needs, and in conjunction with the Windows Azure platform.

If you have an application that needs to serve users in multiple locations, you may not want or need to provide them access to your corporate network. You can code the application to use SQL Azure and locate the data outside of your organization.

SQL Azure really shines when it is paired with Windows Azure. Windows Azure has three parts: one which serves up applications like any other server, a service-bus called the Application Fabric, and a storage offering for data. There are two types of storage in Windows Azure — a Binary Large store (called blob storage), and a key-value-pair, No-SQL — like table storage. Adding SQL Azure to that mix gives you a lot of options for mixing and matching structured and non-structured data, and as an added benefit you don’t have to pay for network bandwidth between the Windows Azure application and SQL Azure. Not only that, but since they share a datacenter and network backbone, that transfer is very fast.

To be sure, these use-cases will grow as time moves on and the product matures. It’s worth taking a few minutes and running through some of the tutorials I’ll reference at the end of this article.

InformIT Articles and Sample Chapters

Michael Miller asks the question, Are You Ready for Computing in the Cloud?

Books and eBooks

David Linthicum has an interesting take on the cloud by combining the discussion with a Service Oriented Architecture, or SOA, in his book Cloud Computing and SOA Convergence in Your Enterprise: A Step-by-Step Guide.

Online Resources

Lots of whitepapers, videos and other introduction training is here.