SQL Azure is designed for peak workloads by failover clustering, load balancing, replication, and scaling out, which are all automatically managed at the data center. SQL Azure's infrastructure architecture is fashioned to implement all of these features.

High availability is made possible by replicating multiple redundant copies to multiple physical servers thus, ensuring the business process can continue without interruption. At least three replicas are created; a replica can replace an active copy facing any kind of fault condition so that service is assured. At present, the replicated copies are all on the same data center, but in the future, geo-replication of data may become available so that performance for global enterprises may be improved. Hardware failures are addressed by automatic failover.

Enterprise data centers addressed the scaling out data storage needs, but incurred administrative overheads in maintaining the on-site SQL Servers. SQL Azure offers the same or even better functionality without incurring administrative costs.

SQL Azure (version 10.25) may be viewed as a subset of an on-site SQL Server 2008 (version 10.5) both exposing Tabular Data Stream (TDS) for data access using T-SQL. As a subset, SQL Azure supports only some of the features of SQL Server and the T-SQL feature set. However, more of T-SQL features are being added in the continuous upgrades from SU1 to SU5. Since it is hosted on computers in the Microsoft Data Centers, its administration — in some aspects — is different from that of an on-site SQL Server.

SQL Azure is administered as a service, unlike on-site servers. The SQL Azure server is not a SQL Server instance and is therefore administered as a logical server rather than as a physical server. The database objects such as tables, views, users, and so on are administered by SQL Azure database administrator but the physical side of it is administered by Microsoft on its data centers. This abstraction of infrastructure away from the user confers most of its availability, elasticity, price, and extensibility features. To get started with SQL Azure, you must provision a SQL Azure Server on Windows Azure platform as explained in the After accessing the portal subsection, later in the chapter.

Provisioning a SQL Azure Server at the portal is done by a mere click of the mouse and will be ready in a few minutes. You may provision the storage that you need, and when the need changes, you can add or remove storage. This is an extremely attractive feature especially for those whose needs start with low storage requirements and grow with time. It is also attractive to those who may experience increased load at certain times only.

SQL Azure databases lie within the operational boundary of the customer-defined SQL Azure Server; it is a container of logical groupings of databases enclosed in a security firewall fence. While the databases are accessible to the user, the files that store the relational data are not; they are managed by the SQL Azure services.

A single SQL Azure Server that you get when you subscribe, can house a large number (150) of databases, presently limited to the 1 GB and 10 GB types within the scope of the licensing arrangement.

Once you are in the portal, you will be able to create your server for which you can provide a username and password. You will also be able to drop the server and change the password. You can also designate in which of the data centers you want your server to be located. With the credentials created in the portal, you will become the server-level principal; the equivalent of sa of your server. In the portal, you can also create databases and firewall fences that will only allow users from the location(s) you specify here. The user databases that you create here are in addition to the master database that is created by SQL Azure Services; a repository of information about other databases. The master database also keeps track of logins and their permissions. You could get this information by querying the master for sys.sql_logins and sys.database views.

If you are planning to create applications, you may also copy the connection strings that you would need for your applications, which are available in the portal. You would be typically using the Visual Studio IDE to create applications. However, SQL Azure can be used standalone without having to use the Windows Azure service. Indeed some users may just move their data to SQL Azure for archive.

Once you have provisioned a server, you are ready to create other objects that are needed besides creating the databases. At the portal, you can create a database and set up a firewall fence, but you will need another tool to create other objects in the database.

SQL Azure database administration is best done from SSMS. You connect to the Database Engine in SSMS, which displays a user interface where you enter the credentials that you established in the portal. You also have other options to connect to SQL Azure (Chapter 3, Working with SQL Azure Databases from Visual Studio 2010 and Chapter 4, SQL Azure Tools). In SSMS, you have the option to connect to either of the databases, the system-created master or the database(s) that you create in the portal. The Object Explorer displays the server with all objects that are contained in the chosen database. What is displayed in the Object Explorer is contextual and the use of the USE statement to change the database context does not work. Make sure you understand this, whether you are working with Object Explorer or query windows. The server-level administrator is the 'top' administrator and he or she can create other users and assign them to different roles just like in the on-site SQL Server. The one thing that an administrator cannot do is undertake any activity that would require access to the hardware or the file system.

CREATE DATABASE database_name
[ON
[ PRIMARY ] [ <filespec> [ ,...n ]
[ , <filegroup> [ ,...n ] ]
[ LOG ON { <filespec> [ ,...n ] } ]
]
[ COLLATE collation_name ]
[ WITH <external_access_option> ]
]
[;]
To attach a database
CREATE DATABASE database_name
ON <filespec> [ ,...n ]
FOR { ATTACH [ WITH <service_broker_option> ]
| ATTACH_REBUILD_LOG }
[;]
<filespec> ::=
{
(
NAME = logical_file_name ,
FILENAME = { 'os_file_name' | 'filestream_path' }
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
) [ ,...n ]
}
<filegroup> ::=
{
FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ]
<filespec> [ ,...n ]
}
<external_access_option> ::=
{
[ DB_CHAINING { ON | OFF } ]
[ , TRUSTWORTHY { ON | OFF } ]
}
<service_broker_option> ::=
{
ENABLE_BROKER
| NEW_BROKER
| ERROR_BROKER_CONVERSATIONS
}
Create a database snapshot
CREATE DATABASE database_snapshot_name
ON
(
NAME = logical_file_name,
FILENAME = 'os_file_name'
) [ ,...n ]
AS SNAPSHOT OF source_database_name
[;]

CREATE DATABASE database_name
[(MAXSIZE = {1 | 10} GB )]
[;]

SELECT * from sys.databases

CREATE LOGIN xfiles WITH PASSWORD = '@#$jAyRa1'

CREATE USER User1 FROM LOGIN 'login1';
CREATE USER User2 FROM LOGIN 'login1';
EXEC sp_addrolemember 'dbmanager', 'User1';
EXEC sp_addrolemember 'loginmanager', 'User2';

As most web applications are data-centric, SQL Azure's databases need to be populated with data before the applications can access the data. More often, if you are trying to push all of your data to SQL Azure, you need tools. You have several options, such as using scripts, migration wizard, bulk copy (bcp.exe), SQL Server Integration Services, and so on. More recently (April 19, 2010 update) Data-tier applications were implemented for SQL Azure providing yet another option for migrating databases using both SSMS as well as Visual Studio. We will look at these in Chapter 5, Populating SQL Azure Databases.

SQL Azure partially supports six categories of Dynamic Management Views (DMV) that help diagnose performance problems caused by:

The following are the dynamic management views that you can query (see http://www.sys-con.com/node/1291144):

A user must have a View Database State permission granted to him in order to view these DMVs. A later section shows how this may be carried out, for a user of a database, with two examples of the previous views.

Microsoft Sync Framework is leveraged to provide synchronization between on-site applications with SQL Azure. The notion of a data hub in the cloud allows on-site clients to synchronize with each other using the data hub. Synchronization will be described in Chapter 9, Synchronizing SQL Azure.

Ground-based client applications access the SQL Azure Services (databases) on the cloud using standard client libraries such as ODBC, ADO.NET (using TDS protocol), PHP, and so on. What this means, is that all of these technologies are familiar to application developers and do not require learning new developmental techniques, a feature Microsoft calls Developer Agility. In addition to Microsoft technologies, open source programming languages can also be used to develop Azure applications. We will look at the details of how client libraries access SQL Azure in the next chapter. Web applications accessing SQL Azure will be described in Chapter 8, Database Applications on Windows Azure Platform Accessing SQL Server Databases.

The third component described in Chapter 1, Cloud Computing and Microsoft Azure Services Platform that is soon to be commercialized, the AppFabric, would support Windows Azure platform to integrate with on-site application despite the security fences that isolate the on-site application. This would make it more appealing for enterprise applications. This will be considered in Chapter 8.

There may be any number of reasons why interacting with SQL Azure may not always be successful. For example, there may just be a possibility that the service level agreement that assures 99.99 percent may not actually be possible, there may be a problem of time-out that is set for executing a command, and so on. In these cases, troubleshooting to find out what might have happened becomes important. Herein, we will see some of the cases that prevent interacting with SQL Azure and the ways and means of troubleshooting the causes.

Transact-SQL is used to administer SQL Azure. You can create and manage objects as you will see later in this chapter. CRUD (create, read, update, delete) operations on the table are supported. Applications can insert, retrieve, modify, and delete data by interacting with SQL Azure using T-SQL statements.

As a subset of SQL Server 2008, SQL Azure supports only a subset of T-SQL that you find in SQL Server 2008.

The supported and partially supported features from Microsoft documentation are reproduced here for easy reference.

The support for Transact-SQL reference in SQL Azure can be described in three main categories:

The following Transact-SQL features are supported or partially supported by SQL Azure:

The following Transact-SQL features are not supported by SQL Azure:

T-SQL grammar details are found here: http://msdn.microsoft.com/en-us/library/ee336281.aspx.