General Guidelines and Limitations (Windows Azure SQL Database)
This topic describes the Microsoft Windows Azure SQL Database general guidelines and limitations. The general guidelines and limitations details are covered in the following headings:
Driver, library, and protocol support
Visual Studio support
Tools and technology support
Data migration support
SQL Server Agent/Jobs
Row Versioning-Based Isolation Levels
SQL Server collation support
Clustered index requirement
Database count and size limit
For information about security-related guidelines and limitations, see Security Guidelines and Limitations (Windows Azure SQL Database).
Driver, Library, and Protocol Support
When writing applications for Windows Azure SQL Database, 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.
An updated version of SQL Server JDBC Driver 3.0 that supports SQL Database.
Windows Azure SQL Database supports tabular data stream (TDS) protocol client version 7.3 or later. Earlier versions of TDS protocol are not supported.
Connecting to Windows Azure SQL Database by using OLE DB or ADO is not supported.
Visual Studio Support
When you write applications for Windows Azure SQL Database, you can use one of the .NET Framework programming languages, which come with Visual Studio: 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 Database. Previous versions of Server Explorer are not supported. For more information, see Visual Studio documentation on MSDN.
Tools and Technology Support
Consider the following points when using tools to connect to Windows Azure SQL 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 SQL Database server name to the login portion of the connection string using the
<login>@<server>notation. See Managing Databases and Logins in Windows Azure SQL Database for more information.
The SQL Server 2008 SQL Server browser is not supported because Windows Azure SQL Database does not have dynamic ports, only port 1433.
For more information about tools support, see Tools and Utilities Support (Windows Azure SQL Database).
You can use Configuring ODBC Data Sources to define user and system data sources for Windows Azure SQL Database. 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 Windows Azure SQL Database, 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 Database 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 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, Windows Azure SQL Database supports only English, so select English as a language. Windows Azure SQL Database 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
masterwhile 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.
Data Migration Support
You can transfer data to Windows Azure SQL Database by using the following:
SQL Server 2008 Integration Services (SSIS)
The bulk copy utility (BCP.exe)
Scripts that use
INSERTstatements to load data into the database
Windows Azure SQL Database does not support:
Attaching a database to the SQL Database server.
For more information about SSIS, see SQL Server Integration Services.
SQL Server Agent/Jobs
Windows Azure SQL 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 Windows Azure SQL Database.
Windows Azure SQL Database does not support distributed transactions, which are transactions that affect several resources. For more information, see Distributed Transactions (ADO.NET).
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. For more information about transactions and avoiding transaction promotion, see System.Transactions Integration with SQL Server (ADO.NET).
|Windows Azure SQL Database may not preserve the uncommitted timestamp values of the current database (DBTS) across failovers.|
Row Versioning-Based Isolation Levels
Both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options are set to ON in Windows Azure SQL Database. Because SET <snapshot_option> in the ALTER DATABASE Transact-SQL statement is not supported, these database options cannot be changed. For more information on row versioning-based isolation levels, see Understanding Row Versioning-Based Isolation Levels.
SQL Server Collation Support
The default database collation used by Windows Azure SQL 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. Windows Azure SQL Database does not allow setting the collation at the server level. To use the non-default collation with Windows Azure SQL Database, set the collation with the Create Database Collate option, or at the column level or the expression level. SQL Database does not support the Collate option with the Alter Database command. By default, in SQL Database, temporary data will have the same collation as the database. For more information about how to set the collation, see COLLATE (Transact-SQL) in SQL Server Books Online.
Clustered Index Requirement
Windows Azure SQL 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.
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')
Database Count and Size Limits
By default, Windows Azure SQL Database supports up to 150 databases in each SQL Database server, including the master database. You can create up to 149 databases in each SQL Database server. An extension of this limit may be available for your SQL Database server. For more information, contact a customer support representative at the Microsoft Online Services Customer Portal.
Windows Azure SQL Database 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 150 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.
For information about how to specify the size of the database, see CREATE DATABASE (Windows Azure SQL Database).
Certain user names are not allowed for security reasons. You cannot use the following names:
Names for all new objects must comply with the SQL Server rules for identifiers. For more information, see Identifiers.
Additionally, login and user names cannot contain the
\ character (Windows Authentication is not supported).
Windows Azure SQL Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all Windows Azure SQL Database 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
|Maximum allowable durations are subject to change depending on the resource usage. A logged-in session that has been idle for 30 minutes will be terminated automatically. We strongly recommend that you use the connection pooling and always close the connection when you are finished using it so that the unused connection will be returned to the pool. For more information about connection pooling, see Connection Pooling.|
When your connection to the service is closed, you will receive an error. For more information on the error, see Connection-Loss Errors.
A SQL Database application should determine if a closed connection is caused by a transient error. If a transient error causes a closed connection, an application should be able to re-establish the connection and execute the failed commands or the query. For more information about retrying closed connections, see: