SQL Azure Overview

Microsoft SQL Azure Database is a cloud-based relational database service that is built on SQL Server technologies and runs in Microsoft data centers on hardware that is owned, hosted, and maintained by Microsoft. This topic provides an overview of SQL Azure and describes some ways in which it is different from SQL Server.

Similarities and Differences

Similar to an instance of SQL Server on your premises. SQL Azure exposes a tabular data stream (TDS) interface for Transact-SQL-based database access. This allows your database applications to use SQL Azure in the same way that they use SQL Server. Because SQL Azure is a service, administration in SQL Azure is slightly different.

Unlike administration for an on-premise instance of SQL Server, SQL Azure abstracts the logical administration from the physical administration; you continue to administer databases, logins, users, and roles, but Microsoft administers the physical hardware such as hard drives, servers, and storage. This approach helps SQL Azure provide a large-scale multi-tenant database service that offers enterprise-class availability, scalability, security, and self-healing.

Because Microsoft handles all of the physical administration, there are some differences between SQL Azure and an on-premise instance of SQL Server in terms of administration, provisioning, Transact-SQL support, programming model, and features.

Logical Administration and Physical Administration

Although SQL Azure plays an active role in managing the physical resources of the database, the DBA plays a very important role in administering SQL Azure-based database applications. Using SQL Azure, DBAs manage schema creation, statistics management, index tuning, query optimization, and security administration (logins, users, roles, etc.).

Database administration in SQL Azure differs most from SQL Server in terms of physical administration. SQL Azure automatically replicates all data to provide high availably. SQL Azure also manages load balancing and, in case of a server failure, transparent fail-over.

To provide this level of physical administration, you cannot control the physical resources of SQL Azure. For example, you cannot specify the physical hard drive or file group where a database or index will reside. Because the computer file system is not accessible and all data is automatically replicated, SQL Server backup and restore commands are not applicable to SQL Azure.

Provisioning

When preparing an on-premises SQL Server deployment, it may be the role of the DBA or IT department to prepare and configure the required hardware and software. When using SQL Azure, these tasks are performed by the SQL Azure provisioning process.

You can begin provisioning your SQL Azure database after you create a Windows Azure platform account. This account allows you to access all the services, such as Windows Azure, .Net Services, and SQL Azure, and is used to set up and manage your subscriptions.

Each SQL Azure subscription is bound to one SQL Azure server at the Microsoft data center. Your SQL Azure server is an abstraction that defines a grouping of databases. To enable load-balancing and high availability, databases associated with your SQL Azure server may reside on separate physical computers at the Microsoft data center.

Transact-SQL Support

Many SQL Server Transact-SQL statements have parameters that allow you to specify file groups or physical file paths. These types of parameters are not supported in SQL Azure because they have dependencies on the physical configuration. In such cases, the command is considered partially supported.

Features and Types

SQL Azure does not support all of the features and data types found in SQL Server. Analysis Services, Replication, Reporting Services, and Service Broker are not currently provided as services on the Windows Azure Platform.

Because SQL Azure performs the physical administration, any statements and options that attempt to directly manipulate physical resources will be blocked, such as Resource Governor, file group references, and some physical server DDL statements. It is also not possible to set server options and SQL trace flags or use the SQL Server Profiler or the Database Tuning Advisor utilities.

SQL Azure supports many SQL Server 2008 data types; it does not support data types that have been deprecated from SQL Server 2008.