The Server Information box is at the top of the Summary tab. Figure 3-3 shows that box in context, and Figure 3-4 focuses in so you can read the details.

Figure 3.4. Server Information

The Server Information box displays the following:

Microsoft recommends that when creating your logical servers, you should put them in the same geo location. If you don't, you incur data charges. For example, if you put your Windows Azure web application in the North Central US location and your SQL Azure database in the South Central US location, you incur data-transfer and other transactional charges. If your application and database are located in the same geo location, you don't incur these charges. Another reason to put your application and database in the same geo location is performance. However, spreading your services across geo locations helps with redundancy.

A quick note about the Drop Server button in the Server Information box: dropping a SQL Azure server deletes all databases associated with the server and removes the server from your account. When you drop a server, you're informed that dropping the server can't be undone and asked whether you want to continue. If you choose to continue, no databases can be recovered, and you must start the server-creation process all over again, including creating a new administrator and selecting the location.

Because your databases are located in places you physically can't get to or control, Microsoft has implemented a mechanism to help protect your data. This protection mechanism is the SQL Azure firewall, which prevents restricted access to all your databases until you physically specify which computers have permission. Access to your SQL Azure databases is based on the originating IP address that makes a request.

To grant access to your SQL Azure databases, you specify a range of acceptable IP addresses in the Server Administration portal on the Firewall Settings tab, shown in Figure 3-5. IP address ranges are defined by rules that allow you to specify multiple firewall setting entries. For example, you can define an IP address range for your home as well as one for your office. If you attend a conference, you can define an additional rule and IP address that let you connect from that specific location. Also notice the "Allow Microsoft Services access to this server" check box on the Firewall Settings tab; it allows internal communication between Windows Azure services and SQL Azure databases.

Figure 3.5. Firewall Settings tab

To add a firewall rule, click the Add Rule button on the Firewall Settings tab to display the Add Firewall Rule dialog, shown in Figure 3-6. You're asked to specify the rule name and the IP address range. Firewall rule names must be unique, and there is no limit to the number of rules you can have.

Entering the IP Range is simple because the dialog tells you what your IP address is. Figure 3-6 uses the same value for the range's start and ending values to indicate that only the single specified address should be allowed to access the database. You can enter a range of IP address if you want multiple computers to access SQL Azure.

After you've entered your firewall rule information, click the Submit button. Your new rule is added to the Firewall Settings tab, as shown in Figure 3-7. As the note in Figure 3-6 states, it may be up to 5 minutes before your rule takes effect and you can connect to SQL Azure (although I have never had it take more than 1 minute to take affect). After that, you can edit and delete the rule.

Figure 3.6. Adding a firewall rule

Figure 3.7. New firewall rule

The Databases tab is first in the Server Administration portal, but the chapter discussed the Firewall Settings tab before it because even though you can create databases, you can't connect to them without defining a firewall rule and an IP address range. Now that you've defined your firewall rules, let's create a database and connect to it.

The Databases tab makes it very easy to create and manage SQL Azure databases. Click the Create Database button on the Databases tab to bring up the dialog shown in Figure 3-8. This dialog asks you to enter the name of the database, the database edition (Web or Business), and the database's maximum size. Web database editions are available in 1 GB and 5 GB; the Business edition provides sizes ranging from 10 GB to 50 GB, in increments of 10.

Figure 3.8. Create Database dialog

After you've entered the database name, edition, and maximum size, click the Create button. Your database is now be listed on the Databases tab, along with the database's current size, type (or maximum size), and availability. (See Figure 3-9.)

From the SQL Azure Server Administration portal and the Databases tab, you can also drop existing databases and test connectivity to databases. To test connectivity, select a database from the list, and click the Test Connectivity button. Doing so opens the Database Connectivity Test dialog, which asks you to specify your username and password to connect to the selected database. Enter the appropriate username and password, and click Connect. If the connection is successful, the dialog lets you know, as shown in figure 3-10.

Figure 3.9. Database list

Figure 3.10. Successful database connectivity test

At this point, you've created a database and walked through how to create and manage your databases. Let's move on to how to connect to your databases through SQL Server Management Studio (SSMS) and how to create databases and users via T-SQL.

Now that you've defined your firewall rules and settings, you can create and access SQL Azure databases. Let's do that now.

When you create databases in a non-Azure environment, you have to know and specify a lot in the CREATE statement. For example, you must provide the path and physical object names of the database and log files, as well as the database size and optionally the maximum size and file growth. A CREATE DATABASE statement in a non-Azure environment might look like the following:

CREATE DATABASE [MyDatabase] ON  PRIMARY
( NAME = N'MyDatabase', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL10.MSSQLSERVERMSSQLDATAMyDatabase.mdf' , SIZE = 32768KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyDatabase_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL10.MSSQLSERVERMSSQLDATAMyDatabase_log.ldf' , SIZE = 92864KB , MAXSIZE = 2048GB
, FILEGROWTH = 10%)
GO

As you've learned in the past few chapters, with Azure you don't have to worry about hardware and where objects are located and how hardware is provisioned, which makes statements such CREATE DATABASE much simpler. In Chapter 1, you learned that you must connect to the master database first; then, all you need to do is issue the following statement:

CREATE DATABASE MyDatabase

You can optionally supply two parameters with the CREATE DATABASE statement: MAXSIZE, which specifies the maximum size of the database; and EDITION, which specifies the edition of the database (Web or Business). Here's an example:

CREATE DATABASE MyDatabase (MAXSIZE= 10 GB, EDITION= 'Business')

In the current release of SQL Azure, two database sizes are available for the Web edition: 1GB and 5GB. For the Business edition, available sizes range from 10GB to 50GB, in increments of 10GB. If MAXSIZE is set to a value of 1GB or 5GB and EDITION isn't specified, the database edition is automatically set to Web. If MAXSIZE is set to a value between 10BG and 50GB and EDITION isn't specified, the database edition is automatically set to Business. If neither MAXSIZE nor EDITION is specified, a Web database of 1GB is created.

SQL Azure doesn't support additional arguments that you're used to using when working with SQL in a local environment. For example, SQL Azure doesn't support parameters that relate to working with physical files, such as filespec and filegroup. You also can't attach or detach a database. Other unsupported arguments are covered in depth in Appendix B.

Now that you've created a database, you need to grant access to other users. This section discusses creating logins and users, and allowing others to access the SQL Azure environment. Creating logins and users is similar to doing so locally; but as you found out when creating databases, there are T-SQL limitations.

When granting access to another user, you must first create the login for that user. Then, you create the user based on that login. A login is used for authentication; a user account is used for database access and permission validation.

CREATE LOGIN loginname WITH PASSWORD = 'password'

CREATE USER username

CREATE LOGIN myuser WITH PASSWORD = 'T3stPwd001'
CREATE USER myuser

CREATE LOGIN myuser WITH PASSWORD = 'T3stPwd001'
CREATE USER myotheruser

CREATE USER username FROM LOGIN loginname

CREATE LOGIN myuser WITH PASSWORD = 'T3stPwd001'
CREATE USER myotheruser FROM LOGIN myuser

In this first example, you build a simple Windows Forms application and use ADO.NET to query the EFAzure SQL Azure database. Follow these steps:

When the code is in place, run the application. When Form1 appears, click the button; doing so should quickly display in the list box the first name and last name of the contacts from the Contact table. As you can see, the code is the same as if you were querying a local SQL Server database, except for the connection string.

This example uses the ADO.NET Entity Framework and lets it handle your connection. Follow these steps:

Again, this book isn't focused on the Entity Framework, but here's a quick explanation of what this code does (you use the Entity Framework in a couple more places in this book). The first line creates an instance of the EFAzureEntities class. This class lets you work with database objects in terms of .NET object-oriented objects. You then use the Language Integrated Query (LINQ) language technology (in this case, LINQ to Entities) to query the Contact table and fill the list box with the first names of all the contacts, as shown in Figure 3-18.

Figure 3.18. Completed Form With Data From SQL Azure.

And there you have it; you've successfully queried the cloud. Yes, this is a simple example, but its purpose is to illustrate how easy it is to connect to SQL Azure, create and populate a database, and create an application that queries the database.