Windows Azure SQL Database is a cloud-based relational database service from Microsoft. SQL Database provides relational database functionality as a utility service. Cloud-based database solutions such as SQL Database can provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead. This paper provides an architectural overview of SQL Database, and describes how you can use SQL Database to augment your existing on-premises data infrastructure or as your complete database solution.
Last Reviewed: 8/26/2011
Similar to an instance of SQL Server on your premises, SQL Database exposes a tabular data stream (TDS) interface for Transact-SQL-based database access. This allows your database applications to use SQL Database in the same way that they use SQL Server. Since SQL Database is a service, administration in SQL Database is slightly different.
Unlike administration for an on-premise instance of SQL Server, SQL Database abstracts the logical administration from the physical administration; you continue to administer databases, logins, users, and roles, but Microsoft administers and configures the physical hardware such as hard drives, servers, and storage. This approach helps SQL Database provide a large-scale multi-tenant database service that offers enterprise-class availability, scalability, security, and self-healing.
Since Microsoft handles all of the physical administration, there are some differences between 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 Guidelines and Limitations (Windows Azure SQL Database).
Although 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 SQL Database, DBAs manage schema creation, statistics management, index tuning, query optimization, and security administration (logins, users, roles, etc.). For more information about security administration in SQL Database, see Managing Databases and Logins in Windows Azure SQL Database.
Database administration in SQL Database differs most from SQL Server in terms of physical administration. SQL Database automatically replicates all data to provide high availability. SQL Database also manages load balancing and, in case of a server failure, transparent fail-over to a healthy machine hosting one of the backup copies of your database.
To provide this level of physical administration, you cannot control the physical resources of 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 SQL Database. The SQL Database service still backs up all databases; however they are not accessible to regular users. This is a feature that may be offered in future.
Starting with SQL Database Service Update 4, 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.
For more information on the available options to transfer data to SQL Database, see Migrating Databases to Windows Azure SQL Database.
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 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, AppFabric, and SQL Database, and is used to set up and manage your subscriptions.
Each SQL Database subscription is bound to one SQL Database server within one of the Microsoft data centers. 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 within the Microsoft data center. For more information about provisioning, see Windows Azure SQL Database Provisioning Model.
Transact-SQL is a language that contains commands used to administer instances of SQL Server including creating and managing all objects in an instance of SQL Server, and inserting, retrieving, modifying, and deleting all data in tables. Applications can communicate with an instance of SQL Server by sending Transact-SQL statements to the server. Windows Azure SQL Database supports a subset of Transact-SQL for SQL Server. For more information about Transact-SQL support, see Transact-SQL Support (Windows Azure SQL Database).
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 SQL Database. You can connect from on-premises Analysis Server to SQL Database and SQL Database can be used either as a data source or destination. When this article is being updated, the Customer Technology Preview of Windows Azure SQL Reporting is also available. SQL Reporting is a cloud-based reporting service built on SQL Database, SQL Server, and SQL Server Reporting Services technologies. You can publish, view, and manage reports that display data from SQL Database data sources.
Because 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.
The benefits of using SQL Database include manageability, high availability, scalability, a familiar development model, and a relational data model.
SQL Database offers the scale and functionality of an enterprise data center without the administrative overhead that is associated with on-premise instances of SQL Server. This self-managing capability enables organizations to provision data services for applications throughout the enterprise without adding to the support burden of the central IT department or distracting technology-savvy employees from their core tasks in order to maintain a departmental database application.
With SQL Database, you can provision your data storage in minutes. This reduces the initial costs of data services by enabling you to provision only what you need. When your needs change, you can easily extend your cloud-based data storage to meet those needs.
SQL Database is built on proven Windows Server and SQL Server technologies, and is flexible enough to cope with any variations in usage and load. The service replicates multiple redundant copies of your data to multiple physical servers to maintain data availability and business continuity. In the case of a hardware failure, SQL Database provides automatic failover to ensure availability for your application.
A key advantage of SQL Database is the ease with which you can scale your solution. As data grows, databases need to either scale up or scale out. Scale up always has a ceiling whereas scale out has no virtual limits. A common scale out technique is data-partitioning. After partitioning your data, the service scales as your data grows. A pay-as-you-grow pricing model makes sure that you only pay for the storage that you use, so that you can also scale down the service when you do not need it.
When developers create on-premise applications that use SQL Server, they use client libraries like ADO.NET, ODBC that use the tabular data stream (TDS) protocol to communicate between client and server. SQL Database provides the same TDS interface as SQL Server so that you can use the same tools and libraries to build client applications for data that is stored in SQL Database. For more about TDS, see Network Protocols and TDS Endpoints.
SQL Database will seem very familiar to developers and administrators because data is stored in SQL Database just like it is stored in SQL Server, by using Transact-SQL. Conceptually similar to an on-premise instance of SQL Server, a SQL Database server is logical group of databases that acts as an authorization boundary.
Within each SQL Database server, you can create multiple databases that have tables, views, stored procedures, indices, and other familiar database objects. This data model makes good use of your existing relational database design and Transact-SQL programming skills, and simplifies the process of migrating existing on-premise database applications to SQL Database. For more about Transact-SQL and its relationship to SQL Database, see Transact-SQL Support (Windows Azure SQL Database).
SQL Database servers and databases are virtual objects that do not correspond to physical servers and databases. By insulating you from the physical implementation, SQL Database enables you to spend time on your database design and adding additional value to the business.
The following table provides a high-level comparison between SQL Database and SQL Server.
Data Storage
No size limits as such
* The Web Edition Database is best suited for small Web applications and workgroup or departmental applications. This edition supports a database with a maximum size of 1 or 5 GB of data.
* The Business Edition Database is best suited for independent software vendors (ISVs), line-of-business (LOB) applications, and enterprise applications. This edition supports a database of up to 150 GB of data, in increments of 10 GB.
Exact size and pricing information can be obtained at Pricing Overview.
· An archival process can be created where older data can be migrated to another database in SQL Database or on premise.
· Because of above size constraints, one of the recommendations is to partition the data across databases. Creating multiple databases will allow you take maximum advantage of the computing power of multiple nodes. The biggest value in the Azure model is the elasticity of being able to create as many databases as you need, when your demand peaks and delete/drop the databases as your demand subsides. The biggest challenge is writing the application to scale across multiple databases. Once this is achieved, the logic can be extended to scale across N number of databases.
Edition
· Express
· Workgroup
· Standard
· Enterprise
* Web Edition
* Business Edition
For more information, see Accounts and Billing in Windows Azure SQL Database.
Connectivity
· SQL Server Management Studio
· SQLCMD
* The SQL Server Management Studio from SQL Server 2008 R2 and SQL Server 2008 R2 Express can be used to access, configure, manage and administer SQL Database. Previous versions of SQL Server Management Studio are not supported.
*The Management portal for Windows Azure SQL Database
* SQLCMD For more information, see Tools and Utilities Support.
Data Migration
For more information, see Migrating Databases to Windows Azure SQL Database.
Authentication
* SQL Authentication
* Windows Authentication
SQL Server Authentication only
Use SQL Server authentication
Schema
No such limitation
SQL Database does not support heaps. ALL tables must have a clustered index before data can be inserted.
Check all scripts to make sure all table creation scripts include a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.
TSQL Supportability
Certain Transact-SQL commands are fully supported; some are partially supported while others are unsupported.
* Supported Transact-SQL: http://msdn.microsoft.com/en-us/library/ee336270.aspx
* Partially Supported Transact-SQL: http://msdn.microsoft.com/en-us/library/ee336267.aspx
* Unsupported Transact-SQL: http://msdn.microsoft.com/en-us/library/ee336253.aspx
“USE” command
Supported
In SQL Database, the USE statement does not switch between databases. To change databases, you must directly connect to the database.
In SQL Database, each of the databases created by the user may not be on the same physical server. So the application has to retrieve data separately from multiple databases and consolidate at the application level.
Transactional Replication
Not supported
You can use BCP or SSIS to get the data out on-demand into an on premise SQL Server. When this article is being updated, the Customer Technology Preview of SQL Data Sync is also available. You can use it to keep on-premise SQL Server and SQL Database in sync, as well as two or more SQL Database servers. For more information on available migration options, see Migrating Databases to Windows Azure SQL Database.
Log Shipping
Database Mirroring
SQL Agent
Cannot run SQL agent or jobs on SQL Database
You can run SQL Server Agent on your on-premise SQL Server and connect to SQL Database.
Server options
Some system views are supported For more information, see System Views (Windows Azure SQL Database) on MSDN.
The idea is most system level metadata is disabled as it does not make sense in a cloud model to expose server level information
Connection Limitations
N/A
In order to provide a good experience to all SQL Database customers, your connection to the service may be closed. For more information, see General Guidelines and Limitations on MSDN and SQL Database Connection Management.
SQL Server Integration Services (SSIS)
Can run SSIS on-premise
SSIS service not available on Azure platform
Run SSIS on site and connect to SQL Database with ADO.NET provider
This article was originally written by Dinakar Nethi, Niraj Nagrani, and reviewed by Michael Thomassy, David Robinson.
This article is also available in other languages, including Russian. Wiki: Сравнение Windows Azure SQL Database и локального SQL Server