Azure SQL Database General Guidelines and Limitations
Updated: July 27, 2015
Azure SQL Database guidelines and limitations details in this topic are organized under the following headings:
Database Throughput Unit (DTU) Quotas
Database count limits
Driver, Library, and Protocol Support
Visual Studio Support
Tools and Technology Support
Data Migration Support
SQL Server Agent/Jobs
SQL Server Collation Support
Azure SQL Database Security Guidelines and Limitations are covered in a separate topic.
DTUs provide a way to describe the relative capacity of a performance level based on a blended measure of CPU, memory, reads, and writes. See Azure SQL Database Service Tiers and Performance Levels for more information. Each SQL Database logical server has a maximum limit of 2000 DTUs it can host, based on the sum of the maximum DTUs each database on the server may consume for its performance level. For example, a server with 5 Basic databases (5 X 5 DTUs maximum), 2 Standard S1 databases (2 X 20 DTUs maximum), and 3 Premium P1 databases (3 X 100 DTUs maximum) has consumed 365 DTUs of its 2000 DTU quota. An extension of this DTU quota may be available by contacting Azure Support.
Each logical server can host up to 150 databases of any service tier, including the master database. An extension of this limit may be available by contacting Azure Support.
The information on Microsoft and third-party drivers has been consolidated in the Connection Libraries topic on azure.microsoft.com.
Microsoft Azure SQL Database supports tabular data stream (TDS) protocol client version 7.3 or later. Earlier versions of TDS protocol are not supported. Because some tools implement tabular data stream (TDS) differently, you may need to append the logical server name to the login portion of the connection string using the
<login>@<servername> notation. See Managing Databases, Logins, and Users in Azure SQL Database for more information.
When working with Azure SQL Database, using the latest version of Visual Studio and SQL Server Data Tools (SSDT) is recommended. See Download Latest SQL Server Data Tools.
Consider the following points when using tools to connect to Microsoft Azure SQL Database:
Only TCP/IP connections are allowed.
Multiple Active Result Sets (MARS) is supported.
The SQL Server 2008 SQL Server browser is not supported because Microsoft Azure SQL Database does not have dynamic ports, only port 1433.
You can use Configuring ODBC Data Sources to define user and system data sources for Microsoft 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 Microsoft 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 Azure 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 Azure 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, Microsoft Azure SQL Database supports only English, so select English as a language. Microsoft 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.
Microsoft Azure SQL Database does not support SQL Server Agent or jobs. You can, however, run SQL Server Agent on your on-premises SQL Server and connect to Microsoft Azure SQL Database.
Microsoft 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 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 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).
|Microsoft Azure SQL Database may not preserve the uncommitted timestamp values of the current database (DBTS) across failovers.|
The default database collation used by Microsoft 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. Microsoft Azure SQL Database does not allow setting the collation at the server level. To use the non-default collation with Microsoft 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.
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).
Connection to the Azure SQL Database service may be closed due to:
Excessive resource usage
Connections and logged-in sessions that have been idle for 30 minutes or longer, which are terminated automatically
Failover because of server failures
If a connection to the service is closed, you will receive an error. For more information on errors that can be returned, see the transient errors and connection loss errors in Error Messages. If a transient error causes a closed connection, you can use retry logic to re-establish the connection and execute the failed commands or the query. For more information about retrying closed connections, see the guidelines and code samples in the Client quick-start code sample for Azure SQL Database.
See Idle Connection Resiliency for information about connection resiliency features introduced in .NET Framework 4.5.1. Use connection pooling and always close a connection when you are finished using it so that the unused connection is returned to the pool. For more information about connection pooling, see Connection Pooling.