Windows Azure SQL Database Overview
Microsoft Windows Azure SQL 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 Windows Azure SQL 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, Windows Azure SQL Database exposes a tabular data stream (TDS) interface for Transact-SQL-based database access. This allows your database applications to use Windows Azure SQL Database in the same way that they use SQL Server. Because Windows Azure SQL Database is a service, administration in Windows Azure SQL Database is slightly different.
Unlike administration for an on-premise instance of SQL Server, Windows Azure SQL 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 Windows Azure SQL 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 Windows Azure SQL 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 (Windows Azure SQL Database) and Tools and Utilities Support (Windows Azure SQL Database).
Logical Administration and Physical Administration
Although Windows Azure SQL Database plays an active role in managing the physical resources of the database, the DBA plays a very important role in administering SQL Database-based database applications. Using Windows Azure SQL 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 Windows Azure SQL Database, see Managing Databases and Logins in Windows Azure SQL Database.
Database administration in Windows Azure SQL Database differs most from SQL Server in terms of physical administration. Windows Azure SQL Database automatically replicates all data to provide high availably. Windows Azure SQL 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 Windows Azure SQL 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 Windows Azure SQL Database.
|SQL Database allows you to back up your database by copying it to a new database in SQL Database. For more information, see Copying Databases in Windows Azure SQL Database. 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 Windows Azure SQL Database, see How to: Connect to Windows Azure SQL Database Using sqlcmd.|
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 Windows Azure SQL Database, these tasks are performed by the SQL Database provisioning process.
You can begin provisioning your SQL 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 Windows Azure SQL Database, and is used to set up and manage your subscriptions.
Each SQL Database subscription may be bound to one or more SQL Database servers at the Microsoft data center. Your SQL Database server is an abstraction that defines a grouping of databases. To enable load balancing and high availability, databases associated with your SQL Database server may reside on separate physical computers at the Microsoft data center.
For more information about provisioning, see Windows Azure SQL Database Provisioning Model.
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 Windows Azure SQL 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 (Windows Azure SQL Database).
Features and Types
Windows Azure SQL 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 Windows Azure SQL 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.
Windows Azure SQL 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 Windows Azure SQL Database, see Data Types (Windows Azure SQL Database). For more information about SQL Server 2008 deprecated types, see Deprecated Database Engine Features in SQL Server 2008.