9 out of 12 rated this helpful - Rate this topic

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 Database 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 Database exposes a tabular data stream (TDS) interface for Transact-SQL-based database access. This allows your database applications to use SQL Azure Database in the same way that they use SQL Server. Because SQL Azure Database is a service, administration in SQL Azure Database is slightly different.

Unlike administration for an on-premise instance of SQL Server, SQL Azure Database 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 Database 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 Database and an on-premise instance of SQL Server in terms of administration, provisioning, Transact-SQL support, programming model, and features. For more information, see Transact-SQL Support (SQL Azure Database) and Tools and Utilities Support (SQL Azure Database).

Logical Administration vs. Physical Administration

Although SQL Azure Database 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 Database, DBAs manage schema creation, statistics management, index tuning, query optimization, and security administration (logins, users, roles, and so on). For more information about security administration in SQL Azure Database, see Managing Databases and Logins in SQL Azure.

Database administration in SQL Azure Database differs most from SQL Server in terms of physical administration. SQL Azure Database automatically replicates all data to provide high availably. SQL Azure Database 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 Database. 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 Database.

Note
SQL Azure allows you to back up your database by copying it to a new database in SQL Azure. For more information, see Copying Databases in SQL Azure. Although backup and restore commands are not available, you can also use SQL Server Integration Services and the SQLCMD utility to bulk copy data. For more information about using SQLCMD with SQL Azure Database, see How to: Connect to SQL Azure Using sqlcmd.

Note

SQL Azure allows you to back up your database by copying it to a new database in SQL Azure. For more information, see Copying Databases in SQL Azure.

Although backup and restore commands are not available, you can also use SQL Server Integration Services and the SQLCMD utility to bulk copy data. For more information about using SQLCMD with SQL Azure Database, see How to: Connect to SQL Azure Using sqlcmd.

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 Database, these tasks are performed by the SQL Azure provisioning process.

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

Each SQL Azure subscription may be bound to one or more SQL Azure servers 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.

For more information about provisioning, see SQL Azure Provisioning Model.

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 Database because they have dependencies on the physical configuration. In such cases, the command is considered partially supported. For more information about Transact-SQL support, see Transact-SQL Support (SQL Azure Database).

Features and Types

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

Because SQL Azure Database 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 Database supports many SQL Server 2008 data types; it does not support data types that have been deprecated from SQL Server 2008. For more information about data type support in SQL Azure Database, see Data Types (SQL Azure Database). For more information about SQL Server 2008 deprecated types, see Deprecated Database Engine Features in SQL Server 2008.