Getting Started with SQL Azure Database

Follow the steps to install and connect to Microsoft's cloud database and access it from an application

SQL Azure Database is Microsoft's cloud-based version of SQL Server. SQL Azure is much like an on-premises SQL Server, providing a cloud-based relational database that you can connect to and many familiar SQL Server management and development tools. I'll explain how to get started using SQL Azure Database and how to connect to SQL Azure and use it as database back end for your applications.

At Your Service
The first step to using SQL Azure Database is to set up a SQL Azure account. The initial CTPs for SQL Azure allowed free access. The CTP expired in November 2009, when Microsoft replaced it with the live version of SQL Azure. There are two different levels of SQL Azure accounts. SQL Azure Web Edition supports a maximum of 1GB of data and costs $9.99 per month. SQL Azure Business Edition includes up to 10GB of data and costs $99.99 per month. You can obtain a SQL Azure account by going to sql.azure.com, then completing the registration process.

After signing up for the SQL Azure service, you log in to manage the SQL Azure database by going to http://sql.azure.com, then logging in using your Windows Live ID. When you initially set up your SQL Azure service, you're prompted to enter an administrative login and password that must meet strong password requirements. After you provide the administrative account information, you'll see the SQL Azure management interface that Figure 1 shows.

The SQL Azure Management window enables you to perform two essential tasks: Create databases and open client connections to the SQL Azure server. When you initially connect, you need to pay special attention to the SQL Azure server name, as you'll need that to establish client connections to the database.

The first task in getting started with SQL Azure is to create a database. You do so by selecting the Databases tab, then clicking the Create Database button. You'll then see a dialog box that lets you name the database and specify a maximum size. I was able to choose either 1GB or 10GB. For my initial testing I created a 1GB pubs database.

After creating the database, you can get the connection string for the database by checking the radio box in front of the data, then clicking the Connection Strings button that you can see near the bottom of Figure 1. This is important because unlike an on-premises SQL Server system, you cannot change databases after connecting to SQL Azure. You need to establish a new connection to connect to a different database. Listing 1 shows an example of the SQL Azure connection strings from ADO.NET and ODBC.

Next you need to enable the firewall. You can't connect to your SQL Azure database until you explicitly enable a range of IP addresses that the client systems will use to connect. To add new firewall entries, select the Firewall Settings tab, then click the Add Record button. Doing so will display the Custom Firewall Settings dialog box, in which you enter a range of IP addresses that may connect to your SQL Azure database. One point to note: If you're using Network Address Translation (NAT), you'll need to use your system's public IP address—not the internal IP address of your client system. SQL Azure helps you with this by displaying your current external (public) IP address on the Custom Firewall Settings dialog box.

Connecting to the Cloud
SQL Azure supports client connection using the native SQL Server Tabular Data Stream (TDS) protocol, and you can connect to SQL Azure using SQL Server Management Studio (SSMS). However, it's important to know that Object Explorer doesn't work because it requires access to the sys.configurations table, which isn't accessible in SQL Azure. Instead you need to perform SQL Azure management using Query Editor. To connect to SQL Azure, open SSMS, then on the Connect dialog box that runs automatically, click Cancel to connect Object Explorer to the database. This will open a blank SSMS window. Click New Query, then fill out the connection dialog box, as Figure 2 shows.

In Figure 2, you can see that I supplied the server name that I retrieved from the SQL Azure management window. I selected SQL Server Authentication and entered the administrative login and password that I used when I created the SQL Azure service. Then I clicked Options, entered the database name pubs in the Connect to database prompt, and clicked Connect. This opened a Query Editor window that SQL Azure connected to. At this point, you can create your database schema and object by executing T-SQL commands from the Query Editor.

Seeding the Cloud
I performed my testing with the SQL Azure CTP; however, using that release level, there was no good way to get data from my on-premises SQL Server systems into SQL Azure. For my initial testing I thought the easiest way to create the pubs database would be to run the insrpubs.sql script, which you can download as a part of the SQL Server 2000 sample from Microsoft.

However, I ran into problems right away, and the script would not run. Although SQL Azure is much like an on-premises SQL Server system, there are also key differences. The first issue I ran into was that the USE command wasn't supported. That makes sense considering you're limited to one database connection. Next, I was a bit surprised to see that all SQL Azure tables need a primary clustered index. These issues quickly sent me to the SQL Azure online documentation.

With the CTP release, Microsoft's recommended method of populating SQL Azure is to run the Database Script Wizard, then manually modify the generated script. To convert the sample pubs database, I right-clicked pubs, then selected the Tasks, Generate Scripts option. Clicking past the Welcome dialog box, on the Select Database dialog I then choose the pubs database, as Figure 3 shows.

Next, on the Choose Script Options dialog box, I changed the script options shown in Table 1. These options will set up most of the script requirements, as SQL Azure does not support user-defined data types, extended properties, or the USE statement. Since the Script Data option causes data as well as the schema to be scripted out, this technique isn't suitable for large databases.

Clicking Next on the Choose Script Options dialog box displayed the Choose Object Types dialog, where I selected the Stored procedures, Tables, and Views options, as Figure 4 shows. Then I clicked Next through the remaining dialog boxes, where I selected all the objects on the Choose Stored Procedures, Choose Tables, and Choose Views dialogs. At the end of the wizard, I clicked Finish to generate the pubs database-creation script in a new Query Editor window.

Next, I edited the script to remove the things that SQL Azure doesn't like. First, I removed all occurrences of the following statement:

SET ANSI_NULLS ON

Then I edited all the CREATE TABLE statements and removed the following clause:

Finally, I made sure that all the CREATE TABLE statements had a clustered primary key constraint. Most of the tables in the pubs database already do, but I found I needed to modify the employee, discounts, and roysched tables. After I made the required changes, running the script created the objects in the pubs database and populated the tables. Then I was able to query the SQL Azure pubs database and get results, as Figure 5 shows.

As you'd expect, SQL Azure supports all the standard T-SQL select queries, including multi-table joins and subselects. For an idea of the kinds of features not supported in SQL Azure, see the sidebar "Key SQL Azure Database Differences."

Reach for the Sky
After populating the database, I tested connecting to SQL Azure from an ADO.NET application. No additional tools are needed to make the connection. Microsoft provides a Visual Studio add-on called Windows Azure Tools for Microsoft Visual Studio. However, this add-on is not required to create database applications that you run under SQL Azure.

Connecting my Visual Studio 2008 project to SQL Azure using data binding was a snap. First I opened Visual Studio 2008 and selected the File, New Project option. Then, from the New Project dialog I selected Windows Forms application and named the project SQLAzure. Next, I changed the Form's Text property to SQL Azure Demo, then resized the form as a horizontal rectangle, dragged the DataGridView control onto the form, and anchored it all four sides.

To add a SQL Azure data source, I clicked the DataGridView tasks arrow and, on the Choose Data Source drop-down, selected the Add Project Data Source link. This action started the Data Source Configuration wizard. On the Choose a Data Source Type dialog, I selected Database and clicked Next. Next, on the Choose Your Data Connection dialog, I clicked New Connection to display the Add Connection dialog. I initially attempted to add a Microsoft SQL Server (SqlClient) connection. Although the test connection worked, I encountered an error while attempting to add the data source to the project. Instead, I needed to select the Microsoft ODBC Data Source, which displayed the ODBC Add Connection dialog box that Figure 6 shows.

I selected the Use connection string option and entered the connection string that I retrieved earlier from the SQL Azure manager. The connection string that I used was

Next I saved the connection as pubsConnectionString. Then on the Choose Your Database Objects dialog, I selected the authors table. This action built a DataSet that was capable of adding, editing, and deleting data from the SQL Azure pubs authors table. The total process of building the application took only a couple of minutes and was much like working with a normal SQL Server system. You can see the sample application running in Figure 7.

Occasionally, I experienced issues where I needed to retry connecting to the SQL Azure database. Otherwise, my experience of developing an application on SQL Azure was very much like using an on-premises SQL Server system. Although this demo application was very simple, I found the response time to be subsecond—comparable to an application running locally.

Up in the Air?
Before writing this article, I was unsure about what SQL Azure actually was and how one might use it. After using SQL Azure for a little while, though, I saw its close resemblance to SQL Server and how that made SQL Azure easy to adopt and use. However, differences in schema requirements and limited data types would make it difficult for businesses to port their applications to SQL Azure. Even if they did so, businesses might receive limited benefits from running on SQL Azure, as on-premises SQL Server offers very high scalability and has many options for high availability.

Although admittedly cloud computing is still in its early stages, SQL Azure seems to make the most sense for ISVs looking for a globally accessible database. SQL Azure's biggest strength is that it can be accessed anywhere that you can get the Internet. This could make it a compelling offering for new ISV application development. You can find more information about SQL Azure at www.microsoft.com/windowsazure/sqlazure and from the SQL Azure Team Blog.