Part V

Programming Azure

• Chapter 11: SQL Azure
• Chapter 12: An Azure Datamarket Overview
• Chapter 13: Service Bus
• Chapter 14: AppFabric: Access Control Service
• Chapter 15: Azure Connect
• Chapter 16: Azure Diagnostics and Debugging
• Chapter 17: When to Use Azure Versus Office 365

Chapter 11

SQL Azure

What’s In This Chapter

• Comparing SQL Azure to SQL Server
• Managing SQL Azure Servers and Databases with the Azure Portal
• Troubleshooting connectivity issues

In this chapter you learn the fundamental differences between SQL Azure and SQL Server 2012, and how to create, manage, and use SQL Azure Databases from your applications.

SQL Azure Overview

SQL Azure is a highly available, distributed relational Database-as-a-service built on SQL Server technologies. SQL Azure focuses on a scale-out approach of adding more small physical machines rather than a scale-up approach of adding larger and more powerful physical machines, as is typically done with SQL Server.

The Difference Between SQL Server and SQL Azure

Unlike SQL Server where your Databases are the only ones on your Database server, SQL Azure may use a single physical server to host Databases from many different customers. This difference in approach is fundamental—SQL Azure is inherently multitenant, and it needs to share physical resources among all clients of the service. This fact underlies many of the feature differences between SQL Server and SQL Azure; although, a tremendous overlap exists in functionality and compatibility between the two.

Comparing Architectures

SQL Azure is specifically intended to feel familiar to developers using SQL Server, because it takes a recognizable approach to communication, authentication, and development. In many cases, the difference between programming against SQL Azure and SQL Server is the value placed in the connection string. Using the Tabular Data Stream (TDS) protocol for communication, using SQL logins for authentication, and programming with Transact SQL (T-SQL) are familiar to any SQL Server developer.

It is helpful to understand the differences between the on-premises SQL Server and the cloud-based SQL Azure by comparing the communication architecture of each one side-by-side, as shown in Figure 11-1. For an on-premises SQL Server, you typically have your applications talking directly to SQL Server across your local area network using the TDS protocol over TCP/IP or via HTTP endpoints. This is different from how your on-premises applications communicate with SQL Azure. As you can see, your applications must now call out, possibly through your own network’s firewalls, and reach the SQL Azure Gateway via the Internet, and they can use only TCP/IP.

Figure 11-1

In addition, unlike for SQL Server, which can communicate on a configurable port without transport security, communication with SQL Azure must happen using SSL, and it must occur via port 1433. Furthermore, it is already at the Gateway that validation of your login occurs. SQL Azure, unlike SQL Server, supports only SQL Server Authentication (using a login and password, not Windows Integrated Security).

The Gateway is also where another firewall enters the picture—the SQL Azure Firewall. This firewall enforces IP-level security, catering only for IP addresses and address ranges that you explicitly define to be allowed access to your SQL Azure Server.

Once past the Gateway, a connection to the backend data node hosting your SQL Azure Database is made, using the Gateway as a proxy. Each backend data node runs a single instance of SQL Server containing its own Database, where this database is divided into partitions. Each partition contains a SQL Azure user Database (e.g., your database).

Aside from this single instance, each SQL Azure user Database is replicated a further two times, on two different backend data nodes. These replicas provide no load-balancing services to handle user queries—they are solely to provide SQL Azure’s very high availability by enabling failover to one of the two replicas should the node hosting the primary node go down.

However, an element of load balancing does exist in this picture. If one backend node becomes overloaded by the workload of its shared tenants, then the underlying infrastructure (called the SQL Azure Fabric) may change the primary replicas of some users’ Databases to one of the backup replicas found on a different backend node, converting this primary into a secondary replica for those Databases.

Beyond providing relational Database capabilities to your on-premises applications, observe how SQL Azure is well positioned to provide support for off-premises applications (like partners) and, more important, applications hosted within Windows Azure.

Aside from the architectural differences, numerous feature differences become apparent during development on, administration of, and licensing for SQL Azure that you should be aware of. The following sections explore the notable differences for these.

Development

In the spirit of maintaining a familiar feel, SQL Azure has a high degree of feature parity to SQL Server with respect to the needs of the developer. In terms of support for T-SQL grammar for Data Definition Language (DDL), Data Manipulation Language (DML), and general programmability, you can find a mixture of levels of support that vary between full parity with the SQL Server; to partial parity (where some options are omitted, or added uniquely to support SQL Azure); to data types, functions, operators, statements, procedures, and system tables/views that are flat-out not supported at all.

DDL Differences

DDL encompasses the features you need to define your Database schema and objects. For DDL, you can find near full-feature parity for DDL statements like Create/Alter/Drop affecting Database objects such as Tables, Views, Stored Procedures, Functions, Triggers, User Defined Types, and Indexes. There are some notable differences though, in that they generally lack support for some specific features that exist only in the on-premises versions of SQL Server. The following are a couple of stand-out differences:

• The Common Language Runtime (CLR) integration is not supported within SQL Azure, which means stored procedures, triggers, and user-defined functions written in a .NET language are not supported in SQL Azure. This limitation has its roots in the multitenant nature of SQL Azure, aiming to protect tenants from the accidental or intentional misuse of CLR objects by other tenants on the same server.
• Extended Stored Procedures, such as xp_sendmail, are not supported. This, too, is a limitation designed to ensure tenant isolation.
• Table partitioning (horizontal partitioning that spreads table/index data across multiple file groups within a single Database to improve performance on large datasets) is not supported.
• A clustered index is required on all SQL Azure tables. SQL Azure does not support heap tables, and INSERT operations will fail until a clustered index is created.
• Snapshot isolation is enabled and cannot be turned off at the Database level.

SQL Azure supports almost all the system data types found in SQL Server from numeric types such as int and real, date and time such as datetime, character strings such as char and nvarchar, and binary strings such as varbinary and image. It even supports some of the more specialized data types such as the spatial data types geography and geometry, hierarchyid, and xml. Although the XML data type is supported, typed XML and XML indexing are not.

For a complete list of supported data types in SQL Azure, you should read this document on MSDN:http://msdn.microsoft.com/en-us/library/windowsazure/ee336233.aspx

Data types that are not supported include: CLR user-defined types and user-defined aggregates, and the FILESTREAM attribute on varbinary(max) typed columns is not supported.

DML Differences

Data Manipulation Language (DML) encompasses the features for performing CRUD (create, read, update, delete) operations against your Database data. The standard querying of tables and views accomplished using the SELECT clause, TOP, and the FROM statement, and optionally WHERE, ORDER BY, GROUP BY, or HAVING statements function exactly as they do in SQL Server. In addition, you can find support for the following:

• EXCEPT, INTERSECT, and UNION
• TOP
• Cursors
• Common Table Expressions
• FOR XML and FOR BROWSEs
• MARS (multiple active result sets)

SQL Azure supports all the same Query, Table, and Join Hints as the on-premises SQL Server, with the exceptions of MAXDOP (which it always treats as 1) and PAGLOCK and REMOTE (neither of which it supports).

The largest difference to query support is that Full-Text search (for example, using CONTAINS, FREETEXT, and so on) is not supported by SQL Azure. Character-based searches with LIKE are supported.

For data modification, UPDATE, INSERT, DELETE, and MERGE are all fully supported, but BULK INSERT is not supported.

For more details on statements that are partially supported, and to see specifically which options are missing, check out http://msdn.microsoft.com/en-us/library/ee336267.aspx.

Programmability

In terms of programmability, consider the T-SQL statements you typically use to define logic within stored procedures and functions. The following are all supported:

• IF. . .ELSE, BEGIN. . .END, DECLARE
• Exception handling with TRY. . .CATCH, as well as THROW, is fully supported.
• RAISERROR
• CAST and CONVERT
• Use of tempdb and creation of temporary tables

In addition, SQL Azure provides full support for all aggregate functions (AVG, MAX, and so on), all ranking functions (RANK, ROW_NUMBER, and so on) and all ODBC scalar functions, as well as most scalar functions found in SQL Server. Only the row set functions CONTAINSTABLE, FREETEXTTABLE, OPENDATASOURCE, OPENQUERY, OPENROWSET, and OPENXML are not supported.

For an exhaustive list of supported/unsupported functions, you should check out http://msdn.microsoft.com/en-us/library/ee336248.aspx.

The feature most likely to catch you unaware as a developer is SQL Azure’s transaction support. Local transactions that work with only the current Database (such as those using BEGIN_TRANSACTION. . .END, COMMIT, and ROLLBACK) are fully supported, but distributed transactions that span multiple Databases are not.

Naming Conventions

Administration

Aside from the lack of physical server and application management and patching, hosting a relational Database in the cloud naturally introduces changes to how Database administration is performed. SQL Azure has quite a few changes from SQL Server that you should be aware of.

Deployment

The architecture of a SQL Azure deployment is logical, rather than physical. When you create a SQL Azure Database, what you actually manage are three logical entities: Subscription, Server, and Database (see Figure 11-2).

Figure 11-2

A Subscription encompasses all your Windows Azure platform services, such as hosted services, storage, caching, and SQL Azure, and provides the context under which use of these services is metered and billed.

Unlike a SQL Server, an Azure Server does not represent a physical or virtual machine running the Database services. In Azure, a Server is a logical grouping of Databases sharing a common datacenter region and firewall configuration. Databases grouped beneath a single Azure Server may run on different physical data nodes in the SQL Azure infrastructure.

A Database is a logical representation of the familiar SQL Server user Database. Recall from the introduction that in the SQL Azure topology your Database actually exists as a partition within a SQL Server Database instance. Each Database is created with a specific maximum size and edition. Currently, sizes and editions are available, as shown in Table 11-1, but the maximum database size is 150GB.

Table 11-1: SQL Azure Database Size Options

It used to be that the edition selected would restrict which sizes you could select, or switch between. For example, if you selected Web, then you could switch between only 1GB and 5GB Databases; switching a Web edition Database to a 10GB Business edition Database was not possible. This originally was designed to encourage you to commit to a certain maximum size (which presumably helped behind the scenes with resource allocation). Thankfully, as of the latest versions of SQL Azure, you can switch between sizes without restriction. You must ensure you never reach the configured maximum size because at that point you will receive errors when trying to create objects or insert or update data.

When a Server is created, a master Database is created along with it that is used to store metadata about the Server and the Databases it contains. This master Database cannot be deleted.

A single Server can manage 149 user-created Databases plus the required master Database for a total of 150 Databases. A Server can manage any mixture of Database editions and sizes. By default, a single Subscription can create up to six Servers. In addition, a Subscription defines a global quota across all Servers of 15 Business Edition Databases or 150 Web edition Databases.

You can increase many of the quotas mentioned is this chapter by contacting Microsoft Windows Azure Platform Support. You can find the contact information for Microsoft Azure Support by visiting https://mocp.microsoftonline.com/site/Support.aspx.

Security and Access Control

Access to SQL Azure is controlled by two devices: a firewall and SQL logins. A SQL Azure Server enables you to specify Firewall rules allowing access from ranges of IP addresses, as well as from Windows Azure platform resources. When a SQL Azure Server is created, an administrative login referred to as the Server-level principal is also created. This login is conceptually equivalent to the “sa” account on SQL Server. SQL Azure does not support Windows Integrated authentication, so all logins are SQL Logins having a username and password.

At the Database level, SQL Azure security has a great deal of parity to an on-premises SQL Server. You create additional logins that enable connection to the Server, and associate those with Database users who have permissions within a specific Database. The T-SQL for access control–related statements, such as Create/Alter/Drop Login; Role, User or Grant/Revoke Database Permissions; Database Principal Permissions; and Type Permissions or changing entity ownership with Alter Authorization, is supported.

SQL Azure requires that all connections use Transport Layer Security (TLS) by allowing only connections that happen across SSL. Clients of SQL Azure should validate the Certificate provided by SQL Azure to prevent man-in-the-middle attacks when using SSL.

When it comes to encryption of data at rest, SQL Azure lacks a few of the features found in SQL Server. For example, SQL Azure does not support Transparent Data Encryption nor the Extensible/External Encryption Key Management features of SQL Server. If you need to encrypt portions of your data, you must implement your own solution for key management and for encrypting/decrypting data.

Availability

SQL Azure provides for high availability with an infrastructure delivering automatic replication across three replicas and automatic failover from the primary replica to one of the two backup replicas. However, this replication is specific to SQL Azure and is not to be confused with SQL Server Replication. In addition, SQL Azure does not provide support for Database mirroring or log shipping.

Backup and Restore

Backup and restore of production Databases is vital to any Database. Although SQL Azure provides the aforementioned replication to provide high availability, this protects you only against failures within the datacenter (such as hardware problems, data corruption, and so on). It does not protect your data against user errors.

With SQL Server, you would typically create a backup to the filesystem and restore from the same. With SQL Azure, this is not allowed because that filesystem for an SQL Azure data node represents a potentially shared resource across all the tenants who have Databases on that node. This translates to there being no support for BACKUP/RESTORE statements or attaching/detaching Databases; instead you must take an alternative approach, such as the following:

• Database copy: With SQL Azure you can run a CREATE DATABASE statement that creates a copy of a SQL Azure Database in another Database within the same or a different Server.
• Data Tier Application Import/Export: You can export entire SQL Azure Databases, schema, and data, as Data Tier Applications (BACPAC) and import from them as well via the Azure Portal.
• Scripts: You can use the Generate Scripts Wizard within SQL Server Management Studio to export schema and data from your SQL Azure Database to SQL script files, which you can run to re-create your Database.
• Data Sync Services: Enables you to run regular synchronizations between SQL Azure Databases or SQL Azure and SQL Server Databases.
• SQL Server Integration Services: You can use an instance of SSIS running on-premises against a SQL Azure Database.
• Bulk copy: Using the bcp utility or SqlBulkCopy class to bulk load data.
• SQL Azure Migration Wizard: Among other features, enables you to create backups of SQL Azure Database schema and data, or migrate between SQL Server and SQL Azure.

Backup to Azure Blob Storage via BACPAC is currently available in CTP form within the Azure Portal and is planned for a future release. That said, there is also a small community of third-party tools to help you with your SQL Azure Backup needs, such as SQL Azure Backup from Red Gate Software.

Diagnostics and Monitoring

Because SQL Azure instances are multitenant, many of the diagnostic and monitoring features you may have been accustomed to accessing as a system administrator really no longer apply and are not available. These include the SQL Server Utility (for holistic monitoring of resource utilization across servers), SQL Trace & Profiler, Extended Events (for example, ETW traces written to Event Log), the Data Collector, and many system tables. Instead, most diagnostic and health monitoring is performed by querying a small set of dynamic management views.

For a complete list of the supported system views, visit http://msdn.microsoft.com/en-us/library/ee336238.aspx.

Administration Automation

Many of the features for automating administration are still available with SQL Azure, with the caveat that there is no direct remote access to the server. As a result, any automation tools must be run from on-premises or, for instance, from a Windows Azure-hosted role that targets SQL Azure. SQL Server PowerShell and SQL Agent (and Jobs), for example, do not run within SQL Azure but can run from remote machines that target your SQL Azure instance. Furthermore, SQL Azure does not support the use of Service Broker (which is often used in the absence of SQL Agent) or the Policy-Based Management available since SQL Server 2008.

Other Unsupported Features

Depending on your specific requirements of SQL Server, you may find additional features missing in the current release of SQL Azure, including the following:

• Master Data Services
• Change Data Capture and Data Auditing
• Resource Governor
• Data compression
• SQL Server Browser

Microsoft, as a cloud vendor, is continually improving the feature set of SQL Azure, often with the result of increasing feature parity, or at least offering multitenant-friendly alternatives, so it is worth keeping abreast of the latest release notes:

http://msdn.microsoft.com/en-us/library/windowsazure/ff602419.aspx

Unique to SQL Azure—Support for Federations

As previously mentioned, table partitioning is not supported. However, similar performance gains for handling large data can be achieved with SQL Azure Federations, which effectively spreads your data across multiple SQL Azure Databases. This is a powerful feature update to SQL Azure that makes it easier to leverage SQL Azure’s scalability.

Tooling

In general, working with SQL Azure enables you to leverage the tools familiar to you from working with SQL Server, such as SQL Server Management Studio (this includes the Express Edition, but requires 2008 R2 with Service Pack 1 and upward and Visual Studio 2010 with Service Pack 1).

Beyond these, SQL Azure provides some of its own additional web-based tooling for development and management in the Azure Management Portal and the Database Manager.

In addition, a preview of a cloud-based version of Reporting Services, called SQL Azure Reporting, provides report hosting and management as a service. Reports published here can be created with SQL Server Business Intelligence Development Studio or Report Designer and, when published, viewed directly within the web browser or via other formats such as Excel and PDF. These reports can report only on data sourced from SQL Azure Databases.

Licensing

Because SQL Azure is provided as a service, its licensing model is naturally quite different from SQL Server. In the latter, you buy licenses for servers or clients, whereas with SQL Azure you pay a monthly fee for the Databases that you use by the maximum size used, prorated by their usage in the month. For example, in the current pricing scheme, a single Database using 10GB costs $45.954 per month, and you would be charged that much if you had such a Database created for the whole month (and less than that for each day the Database was not created). In addition to size, you are also billed for the amount of data transferred out of the Azure datacenter.

Observe that there is a special pricing tier for web edition databases created with a MAXSIZE of 1 GB, when the actual space used is between 0 and 100 MB. In this case, you still create a 1GB database, but assuming you use less than 100 MB over the month, you pay half of the rate charged for a 1 GB database per month (for example, $4.995 instead of $9.99). In a similar fashion, databases with an actual space used in the 1-10 GB range, are billed for the first GB, with a lower cost per additional GB. Databases with actual space used between 10-50 GB are billed for the first 10 GB, with an even lower cost per additional GB. Finally, Databases with an actual space used between 50-150 GB are billed for the first 50 GB, with the lowest cost per additional GB.

Another substantial difference is that SQL Azure provides a service-level agreement (SLA), which defines a baseline 99.9 percent availability for your Databases over the course of a month, below which Microsoft issues credits applied to your next month’s bill.

With the differences between SQL Azure and SQL Server under your belt, it is time to dive into creating and working with your first SQL Azure Database.

Getting Started with Your First SQL Azure Database

To create a SQL Azure Database, you need a Subscription for the Windows Azure platform. If you do not already have one, visit www.microsoft.com/windowsazure/ and follow the instructions to set up one. At the time of this writing, there are offers that can help you start using SQL Azure for free.

The easiest way to create your first Database is by using the Azure Management Portal. Go to http://windows.azure.com and log in to the Azure Management Portal with the Windows Live ID associated with your Subscription. Now you are ready to begin by creating your first SQL Azure Server.

Creating Your First Server

Recall that a Server provides a logical grouping of Databases, the point to define the region in which Databases are hosted, and the administrative account used to access those Databases.

A Server is not a physical entity; it does not represent a VM or machine hosting your Databases. Databases defined under a common Server are often hosted by different physical machines.

To create your first Server, follow these steps:

Figure 11-3

Figure 11-4

Figure 11-5

Figure 11-6

Your newly created server appears in the list. You are now ready to create your first Database. For the server you just created, you already have one Database: the master Database. This Database is required per Server and cannot be dropped.

Figure 11-7

Figure 11-8

Creating Your SQL Azure Database

With a Server in place, you are now ready to create a new SQL Azure Database with the following steps:

Figure 11-9

Figure 11-10

Your Database now appears beneath your Server in the tree-view. Congratulations, you have just created your first SQL Azure Database!

Understanding the Administrator Login

When you create a SQL Azure Server, you also create an administrator login. This account is a server-level principal that is a login to the Server. Whenever you create a Database beneath that server, a Database user is created and mapped to this login. The result is that your administrator user has permissions to manage all Databases, including the master, within the Server. In addition, this login has server-level permissions for creating and dropping Databases, as well as creating, altering, and dropping logins.

Naturally, this highly privileged login is not one you want to use for everyday access to your Database, because it is effectively the keys to your Database kingdom! Just as for SQL Server, SQL Azure enables you to create additional logins and new Database users with more appropriate permissions. The following section shows you how to accomplish this, as you explore how to program against SQL Azure.

Programming Databases

In this section, you learn how to program against SQL Azure by using Transact-SQL run directly from your local SQL Server Management Studio, how to connect to it from the Excel client on your desktop, and how to access it from .Net applications (perhaps even hosted within Windows Azure) using the ADO.Net classes in the System.Data.SqlClient namespace or the Entity Framework.

Before we begin though, let’s start with a quick note on what this section does not cover. This section is not intended to provide a complete treatment of T-SQL, System.Data.SqlClient, or the Entity Framework, as each of these topics easily fills books on its own. Instead, the focus is on the aspects of using these technologies that are specific to SQL Azure and to point you in the right direction to start. If you need a primer on any of the technologies mentioned, see the Additional Resources section at the end of the chapter.

Using SQL Server Management Studio

When you have created your initial Server in the Azure Portal, and have your administrator login at hand, you can use SQL Server Management Studio to author and execute T-SQL queries against the master Database or any of your user Databases grouped under the same Server. You can also use Object Explorer within SSMS to navigate your existing Database objects.

The easiest way to connect SSMS to SQL Azure is to first click the Connect button on the Object Explorer toolbar. From there you can create new SQL scripts using that connection. To begin:

You can also use other logins you’ve created, in addition to your administrator login, when connecting via SSMS. See the following section titled “Working with Logins and Users.”

Figure 11-11

Figure 11-12

The following sections examine the T-SQL syntax you can execute from SSMS or Database Manager for creating Database objects in SQL Azure. It also covers querying your SQL Azure Database from various clients beyond SQL Server Management Studio.

Figure 11-13

The Database Manager Alternative

If you do not have SSMS available to you, you can use the Silverlight-based Database Manager available from the Azure Portal. This tool enables management functionality empowering you to create new Databases (Figure 11-14), deploy data-tier applications, extract a SQL Azure Database to a data-tier application, and drop a Database. It also enables you to see summary statistics about a selected Database; query tables and views; update records; execute stored procedures; and create and modify tables (Figure 11-15), views, and stored procedures.

Figure 11-14

Figure 11-15

To get to Database Manager, click the Databases tab in the Azure Portal; then select a Server in the Subscriptions tree view. On the Ribbon, click the Manage button found within the Server group. In the prompt, log in with your Server Administrator credentials.

To use the Database Manager, the Firewall Rules configured for your SQL Azure Server must have the Allow Other Windows Azure Services to Access This Server check box checked.

Creating Databases

After you create a Server, you can create a new Database using either the Azure Portal or SSMS. In this section, we will assume you use SSMS. You must connect to the master Database, and then use the CREATE DATABASE statement. This statement amounts to specifying the Database name, and optionally the EDITION (which can have the values web or business) and MAXSIZE (which specifies the max size as 1, 5, 10, 20, 30, 40, 50, 100, or 150 GB) options.

Collectively these two options define the maximum size of your new Database. You do not need to specify either option, in which case a 1GB web edition Database results, and in practice you specify only the MAXSIZE because the EDITION is set automatically from this value. The following shows how you can explicitly create a 1GB web edition Database called MyFirstDatabase.

CREATE DATABASE MyFirstDatabase 
(MAXSIZE = 1 GB, EDITION = 'web'),

You must be connected to the master Database using either the administrator login or a login that is a member of the dbmanager role to execute this statement.

You can always change your Database’s MAXSIZE or EDITION at a later date simply by executing the ALTER DATABASE statement. For example, to change MyFirstDatabase to a 10GB business edition, you would run the following:

ALTER DATABASE MyFirstDatabase
MODIFY (MAXSIZE = 10 GB);

When your Database has reached MAXSIZE, you are prevented from inserting/updating data or creating objects. Although you can still read and delete data/objects, it can take up to 15 minutes before creation or update operations are allowed following these deletions.

Working with Logins and Users

After creating your Database, one of the first things to consider doing is to create a login associated with just that Database. Why? Your administrator login is basically the keys to the kingdom; it grants access not just to a single Database, but also to every other Database including the master, as well as all-powerful permissions that, among other things, enable the creation, deletion, and management of Databases within the Server.

Creating a new login with access limited to only a single Database is a two-step process:

Creating Logins

To create a login and give it a password, you need to execute the CREATE LOGIN statement from the context of a connection to the master Database. The following snippet shows how to create the login foobar with the password ‘abc!1234’.

CREATE LOGIN foobar WITH PASSWORD = 'abc!1234'

Unlike SQL Server, which has a lot of options that could follow the WITH keyword, SQL Azure supports only specifying a string password.

Creating a User to Grant Database Access

Database access is granted to a login by creating a user from that login. A Database user must be created from within the context of a connection to the Database to which the user requires access. Therefore, when connecting via SSMS, you need to connect to the Database to which you want to grant access (and not master). Then you can use the CREATE USER statement. The syntax for creating the foobaruser from the previously created foobar login appears as follows.

CREATE USER foobaruser FROM LOGIN foobar

When a Database user is in place, you can configure permissions just as for an on-premises SQL Server, such as:

• Manage user membership in the built-in Database roles using the system stored procedures sp_addrolemember and sp_droprolemember.
• Grant/deny/revoke schema permissions and define schema ownership.
• Grant/deny/revoke permissions on Database objects.

Granting Database-Level Permissions

With both the login and user created, you can now connect with this login from SSMS or any other SQL Azure client application (for example, your own ASP.NET website, or WPF application). However, if you try to connect you’ll find that this user has no access to Database objects—for example, no Database objects will be listed in Object Explorer, and simple T-SQL queries will indicate that the user does not have sufficient permissions. To enable access, you need to provide additional permissions, and the easiest way to accomplish that is by adding the Database user to the appropriate built-in Database role.

SQL Azure includes the following ten built-in Database roles:

• db_accessadmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_ddladmin
• db_denydatareader
• db_denydatawriter
• db_owner
• db_securityadmin
• public

For a detailed description of all the permissions assigned to the built-in Database roles, see http://msdn.microsoft.com/en-us/library/ms189612.aspx.

To add a Database user to a role, use the sp_addrolemember system stored procedure when you are connected to the master Database with an administrator account. For example, to grant a user the highest level of permissions at the Database level, you add the user to the db_owner Database role using the sp_addrolemember stored procedure. For the foobar Database user, this would appear as follows:

EXEC sp_addrolemember 'db_owner', 'foobaruser';

Naturally, you want to choose the role that most accurately restricts the actions the user needs to perform. Each role describes one or more Database permissions assigned to members of that role. If you want to assign permissions in a more granular fashion, you can use the GRANT statement. For example, to apply permissions equivalent to those assigned by adding the foobaruser to the db_owner role, you can give the user the CONTROL permission directly as follows:

GRANT CONTROL TO foobaruser WITH GRANT OPTION;

Granting Server-Level Permissions

If you want to grant a login the ability to create, alter, or drop logins, or if you want it to create or drop Databases, you need to grant server-level permissions to the user in the master Database associated with the login. Similar to Database-level permissions, you can assign server-level permissions by adding the Database user to the appropriate security roles:

• loginmanager: Required to create/alter/drop logins
• dbmanager: Required to create/drop Databases

To add a user to either of these roles, connect to the master Database and create in it a user for foobar, as shown previously. Then use the sp_addrolemember system stored procedure and specify the role and login name. For example, to add the foobaruser to the dbmanager role, you need to execute the following snippet.

EXEC sp_addrolemember 'dbmanager', 'foobaruser'

Creating Tables

Tables ultimately store the data managed by your Database in the form of rows and columns. To create a table in SQL Azure, you can follow syntax as simple as the following that defines the table name and name/datatype pair for each column.

CREATE TABLE table_name (column_name data_type, …)

However, for SQL Azure this is not enough to create a useable table. All tables in SQL Azure require a clustered index, which governs the physical sort order of rows in the table. You can define a clustered index in two ways within the CREATE TABLE statement: either by using the UNIQUE CLUSTERED keywords or by the PRIMARY KEY keyword. (By default SQL Azure creates a unique Clustered Index for primary key constraints.) In both cases, you need to indicate which columns participate in the clustered index for the row.

To create a table called Products having ID, Name, and Description columns, with a primary key consisting of just the ID column, you would execute the following T-SQL:

CREATE TABLE Products (ID int, [Name] varchar(255), [Description] varchar(max)
CONSTRAINT PK_Products_ID PRIMARY KEY (id))

Alternatively, to create the Products table with a unique clustered index constraint, you can execute the following:

CREATE TABLE Products (ID int, [Name] varchar(255),
[Description] varchar(max)
CONSTRAINT CI_Products_ID UNIQUE CLUSTERED (id))

Although SQL Azure enables you to create a table without a clustered index, it throws an exception as soon as you attempt to insert data. This is why the previous example includes creating the table with a primary key or clustered index constraint.

The CREATE TABLE statement includes a host of other options that enable you to specify column options (such as collation, nullability, default constraints, and identity) and column constraints (such as unique constraints or nonclustered indexes), as well as to define computed columns.

For the complete syntax for creating a table, showing all supported and unsupported options, visit http://msdn.microsoft.com/en-us/library/ee336258.aspx.

One interesting column option is SPARSE columns, which starts to address the gap between the rigid table schemas required by SQL Azure, and the features offered by NO-SQL style storage such as Windows Azure Tables, which flexibly support jagged rows. Jagged rows do not necessarily have the same columns present across all rows. One benefit of jagged rows is that it minimizes the storage required for rows that do not have values for some columns. Columns marked as SPARSE have optimized storage for NULL values present in unused columns for a given row. For large or wide tables where the majority of columns tend to have null values, this can result in significantly decreased storage requirements. A SPARSE column for storing color can be defined on the Products table shown previously using the following statement.

CREATE TABLE Products (ID int, [Name] varchar(255), 
[Description] varchar(max), [Color] varchar(100) SPARSE
CONSTRAINT PK_Products_ID PRIMARY KEY (id))

Creating Indexes

In the previous section on creating a table, you saw that tables in SQL Azure require a clustered index. You may wonder what exactly a clustered index is or why it is necessary to have one. This section briefly introduces the two major index types and shows how to use them in SQL Azure.

Why create indexes? In short, for improved query performance. Without any index on a table, finding the rows that satisfy a query requires a complete row by row search of all rows in the table. Like the index for this book, looking for a key value in an index involves significantly less work than scanning every page in the book, aka the entire table. In addition, you can use indexes to ensure that certain columns with rows have values unique across all rows in the table.

Every table created in SQL Azure can have two physical types of indexes:

• Clustered index: This type of index controls the physical sort order of rows in the table because it defines the order in which the rows are actually written to disk. There can be only one clustered index on a table because the data can be stored only in one order.
• Nonclustered index: This type of index is like an index that points to the clustered index. It builds an index of column values that point to entries in the clustered index. SQL Azure supports creating up to 999 nonclustered indexes on a table.

You can create a Unique constraint with either a nonclustered or clustered index to ensure that the key values are unique across all rows in the table. When you define a Primary Key on a table, you are in effect defining a unique constraint.

Creating a Clustered Index

You have already seen how to create a clustered index at table creation time, but what if you want to create a clustered index for a table that already exists (and does not already have a clustered index)? You can do this using the CREATE CLUSTERED INDEX statement. For example, assume you had not created the clustered index ID on the Products table shown previously. To create the clustered index, you would execute the following T-SQL, providing a name for the index (CI_Products_ID), the name of the table (Products), and the column on which to build the index and sort the table (ID).

CREATE CLUSTERED INDEX CI_Products_ID ON Products (ID)

Creating a Nonclustered Index

You can create additional nonclustered indexes to speed query performance on key values besides those used for the clustered index, at the expense of additional storage. To create a nonclustered index, you use the CREATE NONCLUSTERED INDEX statement. Assume, in the case of the Product’s table, that you often need to query by the name of the product (in addition to the product ID already covered by the clustered index).

CREATE NONCLUSTERED INDEX NCI_Products_Name ON Products ([Name])

Creating a Unique Index

To create a unique index either in a clustered or non-clustered index, you specify the UNIQUE option within the CREATE...INDEX statement; you do this by including the UNIQUE keyword after CREATE. For example, assume you wanted to ensure that product names were also unique across all rows in the table. You could create a nonclustered index as follows that enforces this uniqueness.

CREATE UNIQUE NONCLUSTERED INDEX UNCI_Products_Name
ON Products ([Name])

Accessing SQL Azure from Applications

SQL Azure Databases are not typically created for use solely by SQL Server Management Studio, but rather to power applications. SQL Azure, because it is provided as a cloud-hosted service, enables access from two categories of client applications:

• Access from on-premises applications: Applications that exist on your local network, such as thick client Windows Presentation Foundation apps, intranet websites, or even those that feed into existing desktop productivity applications such as Excel. This category could also include access by other on-premises applications located at partner organizations or at branch offices outside of headquarters.
• Access from Azure-hosted applications: Websites/web services hosted in an Azure Web Role, computational services running in an Azure Worker Role, or even applications running within an Azure VM Role.

Supported Client Libraries

Applications communicate with SQL Azure using one of the many client libraries available. This communication with SQL Azure occurs via the Tabular Data Stream protocol version 7.3 or later. Clients utilizing earlier versions are not supported, nor are connections to SQL Azure via OLE DB. That said, many client libraries are supported:

• .NET Framework Data Provider for SQL Server (in other words, the classes in the System.Data.SqlClient namespace) included with the .NET 3.5 Service Pack 1 or later
• Entity Framework included with the .NET Framework 3.5 Service Pack 1 or later
• SQL Server 2008 or 2008 R2 Native Client ODBC driver
• SQL Server 2008 Driver for PHP, version 1.1 or later
• SQL Server JDBC Driver 3.0, with support for the SQL Azure Database

The following sections show how to use both System.Data.SqlClient classes and Entity Framework.

Accessing SQL Azure from Azure-Hosted Applications

In the following sections, you will see how to create a simple Web Role and use it to access a SQL Azure Database using either SQL Client or Entity Framework. After which you will probably agree, the experience is almost identical to using SQL Server and often amounts to simply a difference in the connection string used.

Creating the Sample Database

For the samples in the following sections, use the AdventureWorks LT for SQL Azure sample Database available from CodePlex. Follow these steps to create a sample database:

buildawltaz.cmd tcp:<server>.Database.windows.net <login>@<server> <password>

After a minute or two, you will have a new Database called AdventureWorksLTAZ2008R2 created within your SQL Azure Server. This Database contains a relatively simple schema and sample data that makes it easy to experiment with building clients, which you will do next.

Ensure that the SQL Azure Firewall is configured with rules allowing access both from Windows Azure platform services (if you deploy a client built in the following to Azure), and for the external IP address of your development machine (when testing it locally).

Creating the Azure Host Project

Recall from above that you can create Windows Azure hosted services that communicate with SQL Azure, which can be Web Roles, Worker Roles, or VM Roles. All of these roles are written in standard .NET, so irrespective of which type of application you are building, the steps required to interact with SQL Azure are:

Create the Web Role

In the following steps, you construct an MVC 3 Web Role hosted service project and configure it to use System.Data.SqlClient classes first and then, later, Entity Framework. Simply follow these steps:

Figure 11-16

This creates a new solution containing two projects: a cloud project that describes the configuration of the Web Role and an MVC Web Project where all your content goes. In addition, the assembly references needed for using either the System.Data.SqlClient classes (namely System.Data.dll) and Entity Framework (namely EntityFramework.dll and System.Data.Entity.dll) are also added.

Figure 11-17

Access Using the System.Data.SqlClient Classes

The classes in the System.Data.SqlClient namespace provide the lowest level API for communicating with SQL Server and SQL Azure by using SqlConnection to connect to the server, SqlDataReader to process result rows in a forward-only fashion, SqlCommand to execute queries, and DataSet to retrieve data in batches. In the following steps, you will use these classes to query the Product table to provide a basic listing of products.

Figure 11-18

string connectionString =
    System.Configuration.ConfigurationManager.
    ConnectionStrings["AdventureWorks"].ConnectionString;

//
// GET: /Products/
public ActionResult Index()
{
    List<Tuple<string, decimal>> products = 
new List<Tuple<string, decimal>>();

    using (SqlConnection sqlConnection =
        new SqlConnection(connectionString))
    {

        SqlCommand command =
          new SqlCommand("SELECT Name, ListPrice FROM SalesLT.Product");

        command.Connection = sqlConnection;

        sqlConnection.Open();

        SqlDataReader dataReader = command.ExecuteReader();
                
        while (dataReader.Read())
        {
            products.Add(new Tuple<string, decimal>(
              dataReader.GetString( dataReader.GetOrdinal("Name") ),
              dataReader.GetDecimal( dataReader.GetOrdinal("ListPrice") )
              ));
        }
    }

    return View(products);
}

@model List<Tuple<string, decimal>>

<h2>Products</h2>

<table>
<tr>
    <th>Product</th>
    <th>List Price</th>
</tr>

@foreach (var product in Model){
    <tr>
        <td>
            @product.Item1
        </td>
        <td>
            @product.Item2
        </td>
    </tr>    
}
</table>

<add name="AdventureWorks" 
connectionString="data source=[ServerName].Database.windows.net;
initial catalog=AdventureWorksLTAZ2008R2;
user id=[Login];
password=[Password];
multipleactiveresultsets=True;
TrustServerCertificate=False;"/>

You should always validate the server certificate of SQL Azure to prevent man-in-the-middle attacks. Doing this is easy with ADO.NET applications because it is just a matter of appending the following to the connection string used in web.config:

TrustServerCertificate=False

Figure 11-19

The steps shown in this section apply equally well to modifying data in SQL Azure using the System.Data.SqlClient classes, either with inline SQL or via Stored Procedure calls. For examples of this, see http://msdn.microsoft.com/en-us/library/3btz0xwf.aspx.

Access Using Entity Framework

If you prefer to work with SQL Azure Databases with strongly typed entities, rather than the more weakly typed approach taken by the System.Data.SqlClient classes, you can use the Entity Framework. If you are already familiar with the Entity Framework, working with SQL Azure is no different than working with SQL Server; the primary difference is the data source, which points to SQL Azure instead.

In the following steps, you will see how you can quickly use the Entity Framework combined with MVC 3 scaffolding to query the Products table.

Figure 11-20

Figure 11-21

Figure 11-22

Figure 11-23

public ViewResult Index()
{
    return View(db.Products.ToList());
}

Connections to SQL Azure may be closed due to excessive resource usage when idle for 30 minutes or when a failover occurs.

Accessing SQL Azure from On-Premises Applications

You can use many existing on-premises applications to communicate with SQL Azure, provided your Firewall configuration is correct. Microsoft Excel 2010 is a great example of this capability. In this section you see how to query the Product table from the AdventureWorks Database and view the results in an Excel spreadsheet.

Figure 11-24

Figure 11-25

Figure 11-26

Figure 11-27

Figure 11-28

After a moment, the product list appears within the spreadsheet (see Figure 11-29) and you can perform whatever analysis you choose.

Figure 11-29

Working with Transactions

Transactions provide consistency when multiple operations (queries or modifications) happen on data at the same time, such as ensuring that all operations succeed or fail together as a unit.

Transactions in SQL Azure

Traditionally consistency is brought about by placing locks on the rows or tables involved in a query that prevent other concurrent operations from making changes until the operation completes—referred to as pessimistic concurrency.

SQL Azure takes a different approach; concurrent operations are allowed, and no locking takes place. Instead, if one operation makes a change to the data used by another operation, when the latter operation completes, it gets an error indicating that it was operating on stale data. This is the optimistic approach to concurrency used by SQL Azure. Grouping the individual steps of the operation into an atomic unit of work (that can fail or complete as a whole) is the transaction.

SQL Azure provides support for local transactions only. These are transactions created in T-SQL with BEGIN TRANSACTION, followed by ROLLBACK TRANSACTION (in the case of an error), and finally COMMIT TRANSACTION (in the case of a successful completion).

In other words, SQL Azure does not support distributed transactions. Distributed transactions are those that, for example, span multiple SQL Azure Databases or include other transaction-aware resources beyond the SQL Azure Database you connect to. If you are familiar with distributed transactions with SQL Server, this also means that SQL Azure does not provide support for resource managers such as the Microsoft Distributed Transaction Coordinator (MS DTC).

Transactions in SQL Azure default to running with READ_COMMITTED_SNAPSHOT as the isolation level. Because this setting cannot be changed at the Database level, it represents a fundamental shift in concurrency behavior. Your transactions must run with optimistic concurrency behavior (for example, an error is thrown when attempting an update to a stale record) instead of the pessimistic concurrency (for example, where records are locked) that was standard for SQL Server.

For a background or refresher on the purpose and application of isolation levels, be sure to read:

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Consider that before starting a transaction with BEGIN TRANSACTION, you can set the isolation level to REPEATABLE READ as follows:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Even in this case, you are still running with optimistic concurrency. You can re-create the pessimistic concurrency behavior of SQL Server on SQL Azure by using the WITH (READCOMMITTEDLOCK) hint for every table queried in every transaction.

Choosing SqlTransaction over TransactionScope

When programming against a SQL Azure Database using SQL Azure, you have the option to use a TransactionScope to wrap a block of code in a transaction. Be careful of this! The TransactionScope class starts with a local transaction so long as you are working with only one connection to SQL Azure. However, if within this scope you add connections to another Database, if you nest connections to the same Database, if the ambient transaction is already a distributed transaction, or if you invoke another resource manager with a Database connection, you’ll be working with a distributed transaction, and this will get you a runtime exception.

The safest way to avoid the problem of unexpected runtime exceptions caused by accidentally creating a distributed transaction is to use SqlTransaction explicitly instead of TransactionScope. The following snippet shows the pattern you should follow to use SqlTransaction. Even though you switched from TransactionScope, you still get the benefit of exceptions automatically causing the transaction to roll back and undo the work done by both commands. The difference is that commands do not automatically join an ambient transaction, even if there is one, and so you avoid accidentally enlisting in a distributed transaction.

using (SqlConnection sqlConnection =
  new SqlConnection(connectionString))
{
  sqlConnection.Open();

  using (SqlTransaction sqlTransaction =
    sqlConnection.BeginTransaction())
  {
  // Create your first command and execute it
    SqlCommand firstCommand = new SqlCommand("firstCommmand", 
      sqlConnection, sqlTransaction);

    firstCommand.ExecuteNonQuery();

    // Create your second command and execute it
    SqlCommand secondCommand = new SqlCommand("secondCommmand", 
      sqlConnection, sqlTransaction);
    secondCommand.ExecuteNonQuery();

    sqlTransaction.Commit();
  }
}

Note that if you are doing simple, discreet operations, like in the sample MVC web application, TransactionScope is going to be fine. You should, however, always keep an eye out for situations that implicitly enlist in a transaction and cause unexpected behavior.

Troubleshooting

In this section, you will take a pragmatic approach to solving the issues typically faced when using SQL Azure, such as working with firewalls, dealing with connection loss, reducing latency, and estimating usage costs.

Working with Firewalls

If you have difficulty connecting to SQL Azure, you may want to check firewall configuration on both ends of the pipe in order to:

• Ensure local firewalls are configured to allow outbound TCP on port 1433.
• Ensure your external IP address is configured with access within a SQL Azure Firewall Rule. In some cases, your local IP address may not be the external address that presents in communications with SQL Azure. One way to find your external IP is to visit a public website such as http://whatismyipaddress.com that displays it. The SQL Azure Firewall dialog shows you your current address; albeit in rare cases this may not give you the correct address (in which case try a third party site as mentioned previously).
• If you only want to allow your IP address, specify the same IP address values in the “from” and “to” boxes.
• Remember to create a rule for each IP address or range you will use. For instance, if you create a rule allowing you access from your laptop at work, do not forget to add another rule containing your home IP address if you use your laptop from home.

When a Firewall Rule is not configured properly for your client, you should expect errors of the following form:

Cannot open server 'servername' requested by the login. 
Client with IP address 'XXX.XXX.XXX.XXX' is not allowed to access the server.  
To enable access, use the SQL Azure Portal or 
run sp_set_firewall_rule on the master 
Database to create a firewall rule for this 
IP address or address range.  
It may take up to five minutes for this change to 
take effect.

Login failed for user 'ZoinerTejada'.

This typically means your IP address has changed since you last configured access in the SQL Azure Firewall.

The easiest way to correct this is to follow these steps:

If you have examined all the preceding items and still have trouble connecting, consider that it can take up to 5 minutes before a SQL Azure Firewall configuration takes effect.

Failing that, you may want to verify that your login and password are both correct and that your login has permission to access the target SQL Azure Server.

Troubleshooting Connection Loss

The resources for SQL Azure are shared by multiple tenants. To provide a generally balanced experience for all tenants, you may experience intermittent or transient errors that disconnect you from your Database.

Various causes for disconnection relate to the way SQL Azure throttles usage, particularly when the system resources are limited. Limits include:

• Network problems
• Large number of locks
• Too many uncommitted transactions
• Locks blocking system calls
• Excessive transaction log storage consumption by a transaction
• Transactions that require too much space in TempDB
• Excessive memory consumption
• MAXSIZE of Database reached
• Connections idle for 30+ minutes
• Large number of login failures from a single IP
• When the primary Database replica has failed over to a backup replica
• Overloaded physical server

To address connection-loss issues, you should know how to diagnose the cause and take preventative measures in your application design.

Diagnosing the Cause of a Connection Loss

In many cases, the reason for connection loss is present in the exception text returned to your application (such as “the Database has reached its size quota”) and may point you directly to the cause of the connection loss for which a solution is obvious (such as increase your MAXSIZE, or clean up your Database).

In other cases, you may need to understand more about the problem (such as when you have excessive locks or long-running queries). Here you have two options.

• You can examine the System Views for examining query performance:
  • Identify time-consuming queries: sys.dm_exec_query_stats and sys.dm_exec_sql_text
  • Monitor for excessive locking: sys.dm_tran_locks
  • Pinpoint inefficient query plans: sys.dm_exec_query_stats

For examples of querying these views to troubleshoot query performance, visit http://msdn.microsoft.com/en-us/library/ff394114.aspx.

• You can try to stress test your application by gradually increasing the workload until you trigger the connection loss (which should provide you with the reason in the error text).

Taking Preventative Measures

The measures taken in the previous section are great tools after a problem has occurred, but ideally you should build your application to work around the connection loss when it happens.

The best approach to this is to build retry logic into your application that responds to these transient errors automatically. This can be quite an undertaking, when you consider you must first identify which errors are transient (for which a retry will ultimately repair the problem) and which are permanent.

In addition, your retry approach needs to factor in a back-off strategy. In many cases, the system just needs some additional time to be available again, and trying again directly after the initial transient error only results in the same error. Your solution instead needs to retry after waiting for a small period of time (say a few seconds) and gradually increase that wait (say to 10 seconds) if the transient error persists.

Fortunately, the Windows Azure AppFabric Customer Advisory Team has put together a library that encapsulates this robust retry logic for SQL Azure. You should check out the tutorial available at http://social.technet.microsoft.com/wiki/contents/articles/retry-logic-for-transient-failures-in-sql-azure.aspx that walks you through the setup and basic use of the retry library.

In rare cases, SQL Azure may be unavailable. You can always check the status of SQL Azure by visiting the Azure Health Status page at http://www.microsoft.com/windowsazure/support/status/servicedashboard.aspx.

Reducing Latency

If you move your SQL Server Database out of your local network to SQL Azure, your applications will experience greater network latency owing to the geographical distance introduced between your on-premises application and the cloud-hosted SQL Azure. This distance is the result of various factors including network congestion and available bandwidth (which is likely far less over the Internet than on your local network).

It may be obvious, but the best way to reduce latency of network requests between your application and SQL Azure is to minimize the distance between them. Practically speaking, this means you should host your application in Windows Azure (such as within a Web or Worker Role), and when you create your application’s hosted service on Windows Azure, make sure the region you select is the same as the region for your SQL Azure Server. This reduces latency because communication happens across the much faster internal networks rather than across the Internet. In addition, this can result in a cost-savings on bandwidth because data transfer within a datacenter is not charged. You also have the added benefits that Windows Azure hosted services provide, for instance high availability of your website.

Calculating Usage

Although you can view metrics of your usage and accrued costs in web-page form by visiting the Microsoft Online Customer Portal (http://mocp.microsoftonline.com), you can also get a reasonably accurate estimate programmatically. You might want to do this to ensure that you have adequate capacity (for example, have selected the appropriate MAXSIZE for your Database) or to estimate accrued costs. You can calculate storage space use, billed edition/size use, and bandwidth use by querying specific system views.

Calculating Storage Usage

The following query provides the current amount of space used by your SQL Azure Database. You must be logged in to the Database you want to report on—and it cannot be the master Database.

SELECT SUM(reserved_page_count)*8.0/1024 as 'Size (MB)'
FROM sys.dm_db_partition_stats;

Calculating Database Edition Usage

To examine the cumulative usage for all the Databases within your SQL Azure Server by day, query the sys.Database_usage system view, as shown in the following snippet. Note that you must connect to the master Database with the administrator login.

SELECT * 
FROM sys.Database_usage

Rows are returned by day (note the time zone is UTC), and as Table 11-2 shows, if you incurred charges for multiple editions (SKU), more than one row is returned for that day.

Table 11-2: Sample Database Usage Output

Given this, you can calculate your approximate usage costs for the month, for all Databases within the Server, using the following query.

SELECT sku,
  avg_size = AVG(quantity), 
  estimate = CASE 
      WHEN AVG(quantity) > 50 
            THEN (125.874 + (AVG(quantity) - 50) * 0.999)    
      WHEN AVG(quantity) > 10 
            THEN (45.954 + (AVG(quantity) - 10) * 1.998) 
      WHEN AVG(quantity) > 1 
            THEN (9.99 + (AVG(quantity) - 1) * 3.996) 
      WHEN AVG(quantity) > 0.5 
            THEN (9.99)  
      ELSE 4.995    
      END  
FROM sys.Database_usage     
WHERE 
      datepart(yy,[time]) = datepart(yy, getutcdate()) AND     
      datepart(mm,[time]) = (datepart(mm, getutcdate()))  
GROUP BY sku

The key point to the previous query is how Database sizes are encoded in the quantity. If you have a single 1GB web edition Database using 1GB of space, its quantity shows up as 1. However, for a single 100 MB Database, the quantity has a value of 0.5. The query shown includes the prices available at the time of this writing: you may have to update these values as new pricing is announced.

You may notice that this estimate may underestimate costs in cases where you have a mixture of database sizes across the different price tiers, because the quantity field rolls up the total space used by day per SKU and provides no way to figure out the correct cost per additional gigabyte. This happens because it cannot be certain whether, for example, a quantity of 15 means:

• A single 15 GB database billed as 10 GB at $45.954 plus 5 additional GBs at $1.998 each, for a total of $55.944
• An 11 GB database ($45.954 + 1 x $1.998) and a 4 GB database ($9.99 + 3 x $3.996) billed for total of $69.93

The query shown would provide the estimate of $55.944 for a quantity of 15, so just be wary of this limitation.

Calculating Bandwidth Usage

You can view your bandwidth usage in KBs for an hourly time period by querying the sys.bandwidth_usage system view from the context of the master Database with the administrator login.

SELECT * 
FROM sys.bandwidth_usage

This returns data similar to Table 11-3, with four rows for each Database in use for any single hour.

Table 11-3: Viewing Bandwidth Usage

For the purposes of estimating your bill, you need to be concerned only with rows having the class ‘External’ (traffic leaving the Windows Azure datacenter) and Direction Egress (out from SQL Azure) because ingress is free of charge (regardless of whether internal or external), and data egress within the same subregion (as indicated by a Direction of Egress and Class of Internal) is also free of charge. Therefore, to approximate your bandwidth costs for the month to date by Database, you could use the following query.

SELECT Database_name, 
  SUM (quantity/1048576) as 'GB Out',
  SUM (0.12 * (quantity/1048576)) as 'Bandwidth Costs'
FROM sys.bandwidth_usage
WHERE direction = 'Egress' and Class = 'External' AND
datepart(yy,[time]) = datepart(yy, getutcdate()) AND     
datepart(mm,[time]) = datepart(mm, getutcdate())   
GROUP BY Database_name

The previous query assumes your data transfer is $0.12 per GB (currently the fee for Zone 1). You should adjust this amount to reflect the fees associated with the region in which your SQL Azure server is hosted.

Summary

In this chapter, you saw how the cloud-based SQL Azure differs fundamentally from on-premises SQL Server, yet ultimately provides a familiar experience for developing against a SQL Server-based relational Database.

You saw how SQL Azure Databases are logically grouped underneath a SQL Azure Server for management reasons, as well as how to create SQL Azure Servers using the Windows Azure portal. You also saw how to create SQL Azure Databases through the portal, as well as how to manage them using T-SQL executed from SQL Server Management Studio or the Database Manager.

In addition, you were exposed to some of the myriad ways applications can be built to target SQL Azure, and how you can troubleshoot connection issues, reduce latency, and calculate usage for your SQL Azure powered applications.

Additional Resources