Sunday, January 3, 2010

Main differences between SQL Azure and SQL Server 2008


SQL Azure is a service which makes the administration slightly different.  Unlike administration for an on-premise instance of SQL Server, SQL Azure abstracts the logical administration from the physical administration; As an database administrator you continue to administer databases, logins, users, and roles, but Microsoft administers the physical hardware such as hard drives, servers, and storage. 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 vs. Physical Administration
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. 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.

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 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 SQL Azure.

Microsoft SQL Azure Database supports a subset of Transact-SQL for SQL Server 2008.

Transact-SQL Features Supported on SQL Azure
• Constants
• Constraints
• Cursors
• Index management and rebuilding indexes
• Local temporary tables
• Reserved keywords
• Stored procedures
• Statistics management
• Transactions
• Triggers
• Tables, joins, and table variables
• Transact-SQL language elements such as
o Create/drop databases
o Create/alter/drop tables
o Create/alter/drop users and logins
o and so on.
• User-defined functions
• Views, including sys.synonyms view

Transact-SQL Features Unsupported on SQL Azure
• Common Language Runtime (CLR)
• Database file placement
• Database mirroring
• Distributed queries
• Distributed transactions
• Filegroup management
• Global temporary tables
• Spatial data and indexes
• SQL Server configuration options
• SQL Server Service Broker
• System tables
• Trace Flags

5 comments:

Kasi said...

Hi,
Nice article about differences between SQL Azure an SQL Server 2008. As SQL Azure don't support trace flags and profiler, I want record all the TSQL's running in a particular time interval as we are ta=racing in sql profiler is there any way to sort this. Thanks in advance.

Kasi said...
This comment has been removed by the author.
André van de Graaf said...

Kasi,
Unfortunately this is not possible at this moment. SQL profiler is high on the wish list of the SQL Azure community. I think MS is working hard on it.

Gr. André

Hoyacoder said...

According to Microsoft -- http://msdn.microsoft.com/en-us/library/windowsazure/ee336248 -- SQL Azure does not support RowSet functions, i.e. those that "Return an object that can be used like table references in an SQL statement." Does this mean that SQL Azure does not support user-defined table-valued functions?

André van de Graaf said...

Hoyacoder,

I think this blog post will answer your question:
http://msdn.microsoft.com/en-us/library/windowsazure/ee336283.aspx