Friday, June 3, 2011

Limitations of SQL AZURE

1. SQL Azure Database supports tabular data stream (TDS) protocol client version 7.3 or later. Earlier versions of TDS protocol are not supported.
2. Connecting to SQL Azure Database by using OLE DB is not supported.
3. Only TCP/IP connections are allowed.
4. The SQL Server 2008 SQL Server browser is not supported because SQL Azure Database does not have dynamic ports, only port 1433.
5. When using the SQL Server 2008 Native Client ODBC driver, the Test Connection button may result in an error that master.dbo.syscharsets is not supported. Ignore this error, save the DSN, and use it. In addition, when you choose to connect to a different database other than master while configuring the DSN, the TESTS COMPLETED SUCCESSFULLY message may not show up even when there is no error.
6. With the SQL Server 2008 R2 Native Client ODBC driver, you will not receive the error stating that the master.dbo.syscharsets is not supported even when connected to a different database.
7. It does not support The RESTORE statement.
8. It doesn’t support Attaching a database to the SQL Azure server.
9. SQL Azure Database does not support SQL Server Agent or jobs. You can, however, run SQL Server Agent on your on-premise SQL Server and connect to SQL Azure Database.
10. SQL Azure Database does not support distributed transactions, which are transactions that affect several resources.
11. Both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options are set to ON in SQL Azure Database. Because SET in the ALTER DATABASE Transact-SQL statement is not supported, these database options cannot be changed.
12. When using an on-premise SQL Server, you can set collations at server, database, column, and expression levels. SQL Azure Database does not allow setting the collation at the server or database level. To use the non-default collation with SQL Azure Database, set the collation at the column level or the expression level.
13. SQL Azure Database does not support tables without clustered indexes. A table must have 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.
14. By default, SQL Azure Database supports up to 150 databases in each SQL Azure server, including the master database. You can create up to 149 databases in each SQL Azure server. An extension of this limit may be available for your SQL Azure server.
15. Certain user names are not allowed for security reasons. You cannot use the following names:
a. admin
b. administrator
c. guest
d. root
e. sa
16. Additionally, login and user names cannot contain the \ character (Windows Authentication is not supported).
17. SQL Azure Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all SQL Azure Database customers, your connection to the service may be closed due to the following conditions:
a. Excessive resource usage
b. Connections that have been idle for 30 minutes or longer
c. Failover because of server failures
18. When your connection to the service is closed, you will receive one of the following errors: 40197, 40501, 40544, 40549, 40550, 40551, 40552, 40553, and 40613. For more information on these errors, see Connection-Loss Errors (http://msdn.microsoft.com/en-us/library/4cff491e-9359-4454-bd7c-fb72c4c452ca#bkmk_throt_errors)
19. The following Transact-SQL features are not supported by SQL Azure Database:
a. Common Language Runtime (CLR)
b. Database file placement
c. Database mirroring
d. Distributed queries
e. Distributed transactions
f. Filegroup management
g. Global temporary tables
h. SQL Server configuration options
i. SQL Server Service Broker
j. System tables
k. Trace Flags
20. The SQL Azure Database service is only available through TCP port 1433. To access a SQL Azure database from your computer, ensure that your firewall allows outgoing TCP communication on TCP port 1433. Before you can connect to the SQL Azure server for the first time, you must use the Windows Azure Platform Management Portal (http://go.microsoft.com/fwlink/?LinkID=161793)
to configure the SQL Azure firewall. You will need to create a firewall setting that enables connection attempts from your computer or Windows Azure.
21. SQL Server Management Studio does not support SQL Azure Database in versions prior to SQL Server 2008 R2.
22. For the server-level principal login, the following restrictions apply:
a. The database user in the master database corresponding to the server-level principal login cannot be altered or dropped.
b. Although the server-level principal login is not a member of the two server roles dbmanager and loginmanager, it has all permissions granted with these two roles.
23. To access the master database, every login must be mapped to a user account in the master database.
24. You must be connected to the master database when executing the CREATE/ALTER/DROP LOGIN and CREATE/ALTER/DROP DATABASE statements.
25. When executing the CREATE/ALTER/DROP DATABASE and CREATE/ALTER/DROP LOGIN statements, each of these statements must be the only statement in a Transact-SQL batch. Otherwise, an error occurs. For example, the following Transact-SQL checks whether the database exists. If it exists, a DROP DATABASE statement is called to remove the database. Because the DROP DATABASE statement is not the only statement in the batch, executing this Transact-SQL will result in an error.
26. IF EXISTS (SELECT [name]
FROM [sys].[databases]
WHERE [name] = N'database_name')
DROP DATABASE [database_name];
Go
27. When executing the CREATE USER statement with the FOR/FROM LOGIN option, it must be the only statement in a Transact-SQL batch.
28. When executing the ALTER USER statement with the WITH LOGIN option, it must be the only statement in a Transact-SQL batch.
29. Only the server-level principal login and the members of the dbmanager role have permission to execute the CREATE DATABASE and DROP DATABASE statements.
30. Only the server-level principal login and the members of the loginmanager role have permission to execute the CREATE LOGIN, ALTER LOGIN, and DROP LOGIN statements.
31. When the owner of a database role tries to add/remove another database user to/from that database role, the following error may occur: User or role 'Name' does not exist in this database. This error occurs because the user is not visible to the owner. To resolve this issue, grant the owner with VIEW DEFINITION permission on the user.
32. The following features that were new to SQL Server 2008 R2 are not supported by SQL Azure Database:
a. SQL Server Utility
b. SQL Server PowerShell Provider. PowerShell scripts can be run on an on-premise computer, however, and connect to SQL Azure Database using supported objects (such as System Management Objects or Data-tier Applications Framework).
c. Master Data Services
The following features that were new to SQL Server 2008 are not supported by SQL Azure Database:
a. Change Data Capture
b. Data Auditing
c. Data Compression
d. Extended Events
e. Extension of spatial types and methods through Common Language Runtime (CLR)
f. External Key Management / Extensible Key Management
g. FILESTREAM Data
h. Integrated Full-Text Search
i. Large User-Defined Aggregates (UDAs)
j. Large User-Defined Types (UDTs)
k. Performance Data Collection (Data Collector)
l. Policy-Based Management
m. Resource Governor
n. Sparse Columns
o. SQL Server Replication
p. Transparent Data Encryption
The following features that were new to SQL Server 2005 are not supported by SQL Azure Database:
a. Common Language Runtime (CLR) and CLR User-Defined Types
b. Database Mirroring
c. Service Broker
d. Table Partitioning
e. Typed XML and XML indexing is not supported. The XML data type is supported by SQL Azure Database.
The following features from earlier versions of SQL Server are not supported by SQL Azure Database:
a. Backup and Restore
b. Replication
c. Extended Stored Procedures
d. SQL Server Agent/Jobs