Microsoft SQL Azure Enterprise Application Development: Moving business applications and data to the cloud can be a smooth operation when you use this practical guide. Learn to make the most of SQL Azure and acquire the knowledge to build enterprise-ready applications.

SQL Azure is a database service in the cloud on Microsoft's Windows Azure platform well-suited for web facing database applications as well as a relational database in the cloud.

SQL Azure Services was briefly introduced in Chapter 1, Cloud Computing and Microsoft Azure Services Platform. The present version mostly deals with the component analogous to a database engine in a local, on-site SQL Server. Future enhancements will host the other services such as Integration Services, Reporting Services, Service Broker, and any other yet-to-be defined services. Although these services are not hosted in the cloud, they can leverage data on SQL Azure to provide support. SQL Server Integration Services can be used to a great advantage with SQL Azure for data movement, and highly interactive boardroom quality reports can be generated using SQL Azure as a backend server (Chapter 6, SSIS and SSRS Applications Using SQL Azure).

In Chapter 1, Cloud Computing and Microsoft Azure Services Platform, the procedure to access the Windows Azure Services was described, which allowed you to host applications on the Windows Azure platform. In a similar manner, you can also access the SQL Azure Services that allow you to create databases and firewall rules. Firewall rules provide the security blanket for your cloud-based resources. This allows you and only you, or users designated by you, from IP addresses specified by you, to access your cloud-based SQL Azure database from an external location.

Now, you will create a user database in SQL Azure and set up firewall rules so that you can access SQL Azure from your ground-based, on-site location. While creating a user database makes use of the SQL Azure portal, these can be done from your ground-based SSMS, or other utilities that support T-SQL. Database and firewall rules can also be programmatically created from Visual Studio 2008 SP1 or Visual Studio 2010 (Beta 2 as well as RC).

The SQL Azure Portal can manage servers, databases, and firewall settings as seen earlier. However, if you want to work with the databases and/or create objects you need an external utility. SQL Server Management Studio R2 provides the ability to work with databases by providing T-SQL support. There are other tools to connect to SQL Azure. SQL Azure supports only a limited set of features that SQL Server 2008 is capable of as described earlier in this chapter and all these features can be used in SSMS once you are connected to SQL Azure Server.

When you are connected to the SQL Azure Server from SSMS, you can work with the server objects as you would with local servers except that SQL Azure supports (presently) only a subset of the features supported by SQL Server 2008 R2 (November CTP). We will now review a number of features that are supported fully or partially.

While the administration of SQL Azure is similar to SQL Server 2008 R2, there are some differences as the strategy used for server management has changed. The security folder in the Object Explorer is your starting point for creating logins when you access SQL Server 2008. In SQL Azure, the server node in Object Explorer is mapped to several (one or more) physical computers at the datacenter(s). The object that is available for you in SQL Azure is the master. It is in the master that you create objects, which include databases, logins, and so on.

The securityadmin role in SQL Server 2008 R2 is replaced by the loginmanager (Administrative User) that you created in SQL Azure Portal (also known as the Security Principal) and he/she will be responsible for server level security for creating logins.

The dbmanager role in SQL Azure is akin to dbcreator role in an on-site SQL Server and this role can create a database when connected to the master and...

Diagnosing SQL Server database-related performance problems whether they are related to databases, execution, or transaction, is best done using the related Dynamic Management Views (DMV), a subset of those available in an on-site server. A user must have the permission to monitor or review these DMVs. Here, you will grant the permission to the user DmvMonitor who has a login001 to Bluesky.

In this chapter, we covered several aspects of SQL Azure Services beginning with provisioning of SQL Azure Server in the portal and later with related features such as setting up databases and firewalls; creating and administering SQL Azure databases; migration, summarizing synchronization, and monitoring of SQL Azure; troubleshooting and monitoring of databases.

Additionally, we focused on SQL Azure access using SQL Server Management, provisioning a server after accessing the portal for the first time; setting up firewall rules to allow on-site users access; creating user databases; running general to specific queries; creating users, logins and permissions; and monitoring SQL Azure usage, and so on.

In the next chapter, we will describe with examples, methods of accessing SQL Azure to create, modify, and manage database objects using both Client APIs and Server APIs. We will also describe the secure way of connecting to SQL Azure.