Limitations of SQL Azure

Below are the limitations in the SQL Azure CTP1:

When writing applications for SQL Azure, you can use the following drivers and libraries:

• .NET Framework Data Provider for SQL Server (System.Data.SqlClient) from the .NET Framework 3.5 Service Pack 1 or later.
• Entity Framework from the .NET Framework 3.5 Service Pack 1 or later.
• SQL Server 2008 R2 Native Client ODBC driver. SQL Server 2008 Native Client ODBC driver is also supported, but has less functionality.
• SQL Server 2008 Driver for PHP version 1.1 or later.
SQL Azure supports tabular data stream (TDS) protocol client version 7.3 or later. Earlier versions of TDS
protocol are not supported.

Connecting to SQL Azure by using OLE DB is not supported.

When you write applications for SQL Azure, you can use one of the .NET Framework programming languages, which come with Visual Studio 2008 or later: Microsoft Visual Basic, Microsoft Visual C#, or Microsoft Visual C++. Visual Studio provides a server management console, Server Explorer, to open data connections and to log on to servers and explore databases. Starting with Visual Studio 2010, you can use the Server Explorer to connect to and to explore your databases in SQL Azure. Previous versions of Server Explorer are not supported. Visual Studio 2010 is fully supported.

Consider the following points when using tools to connect to SQL Azure Database:• Only TCP/IP connections are allowed.
• Multiple Active Result Sets (MARS) is supported.
• Because some tools implement tabular data stream (TDS) differently, you may need to append the server name to the login portion of the connection string using the @ notation
The SQL Server 2008 SQL Server browser is not supported because SQL Azure does not have dynamic ports, only port 1433.

You can use Configuring ODBC Data Sources to define user and system data sources for SQL Azure. To see the list of the user and system data sources, check the User DSN or System DSN tabs of the ODBC Data Source Administrator dialog box.

When using the data source name (DSN) wizard to define a data source for SQL Azure, click the With SQL Server Authentication using a login ID and password entered by the user option and select the Connect to SQL Server to obtain default settings for the additional configuration options. Enter your user name and password to connect to your SQL Azure server as Login ID and Password. When using the SQL Server 2008 Native Client, clear the Connect to SQL Server to obtain default settings… checkbox. Starting with the SQL Server 2008 R2 Native Client ODBC driver, it is not necessary to clear the Connect to SQL Server to obtain default settings checkbox. Click Change the default database to: and enter the name of your SQL Azure database even if it does not show up in the list. Note that the wizard lists several languages in the Change the language of SQL Server system messages to: list.

In this release, SQL Azure supports only English, so select English as a language. SQL Azure does not support Mirror Server or Attach Database, so leave those items empty. Click Test Connection.
• 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.

• 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.
You can transfer data to SQL Azure by using the following:
• SQL Server 2008 Integration Services (SSIS)
• The bulk copy utility (BCP.exe)
• System.Data.SqlClient.SqlBulkCopy class
• Scripts that use INSERT statements to load data into the database

SQL Azure does not support:
• The RESTORE statement.
• Attaching a database to the SQL Azure server.

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.
SQL Azure Database does not support distributed transactions, which are transactions that affect several resources

Starting with the .NET Framework version 2.0, application transactions may be automatically promoted to distributed transactions. This applies to applications that use the System.Data.SqlClient class to perform database operations in the context of a System.Transactions transaction.
Transaction promotion occurs when you open multiple connections to different servers or databases within a TransactionScope, or when you enlist multiple connections in a System.Transactions object by using the EnlistTransaction method. Transaction promotion also occurs when you open multiple concurrent connections to the same server and database either within the same TransactionScope or by using the EnlistTransaction method.

Starting with the .NET Framework version 3.5, the transaction will not be promoted if the connection strings for the concurrent connections are exactly the same

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.

The default database collation used by SQL Azure Database is SQL_LATIN1_GENERAL_CP1_CI_AS, where LATIN1_GENERAL is English (United States), CP1 is code page 1252, CI is case-insensitive, and AS is accent-sensitive.

When using an on-premise SQL Server, you can set collations at server, database, column, and expression levels. SQL Azure does not allow setting the collation at the server or database level. To use the non-default collation with SQL Azure, set the collation at the column level or the expression level.
SQL Azure 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.

For example, the following Transact-SQL creates a new table, creates a clustered index on the table, and then inserts data into the table. Performing the insert without creating the clustered index will result in an error.

CREATE TABLE Table1 (Col1 int, Col2 char(20))
CREATE CLUSTERED INDEX Table1_Index ON Table1 (Col1)
INSERT INTO Table1 VALUES (1, 'string1')

By default, SQL Azure 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.

SQL Azure provides two database editions: Web Edition and Business Edition. Web Edition databases can grow up to a size of 5 GB and Business Edition databases can grow up to a size of 50 GB. The MAXSIZE is specified when the database is first created and can later be changed using ALTER DATABASE. MAXSIZE provides the ability to limit the size of the database. If the size of the database reaches its MAXSIZE, you will receive an error code 40544. When this occurs, you cannot insert or update data, or create new objects, such as tables, stored procedures, views, and functions. However, you can still read and delete data, truncate tables, drop tables and indexes, and rebuild indexes. If you remove some data to free storage space, there can be as much as a fifteen-minute delay before you can insert new data.

Certain user names are not allowed for security reasons. You cannot use the following names:
• admin
• administrator
• guest
• root
• sa
Names for all new objects must comply with the SQL Server rules for identifiers.

Additionally, login and user names cannot contain the \ character (Windows Authentication is not supported).

SQL Azure provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all SQL Azure customers, your connection to the service may be closed due to the following conditions:
• Excessive resource usage
• Connections that have been idle for 30 minutes or longer
• Failover because of server failures

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.

We recommend that you implement the retry logic in your SQL Azure applications to catch these errors.
When an error occurs, re-establish the connection and then re-execute the failed commands or the query.