Export (0) Print
Expand All

Azure SQL Database General Guidelines and Limitations

Updated: January 20, 2015

This topic describes the Microsoft Azure SQL Database general guidelines and limitations. The general guidelines and limitations details are covered in the following headings:

For information about security-related guidelines and limitations, see Azure SQL Database Security Guidelines and Limitations.

DTUs provide a way to describe the relative capacity of a performance level based on a blended measure of CPU, memory, reads, and writes. Each server has a maximum of 2000 DTUs for Basic, Standard, and Premium databases, in addition to the overall limit for databases per server and maximum size per database. DTUs are consumed based on the DTU rating for that performance level. For example, a server with 5 Basic databases, 2 Standard S1 databases, and 3 Premium P1 databases consumes 365 DTUs. For more information on the DTU rating associated with each performance level, see Azure SQL Database Service Tiers and Performance Levels. An extension of this DTU quota may be available for your Azure SQL Database server. For more information, contact Azure Support.

Each server supports up to 150 databases, including the master database. This limit applies to all service tiers. An extension of this limit may be available for your Azure SQL Database server. For more information, contact Azure Support

In addition to limiting the number of databases per server, each service tier (edition) limits the maximum size of each 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 creating databases, see CREATE DATABASE (Azure SQL Database).

When writing applications for Microsoft 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 (and later) 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.

Microsoft 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 Microsoft Azure SQL Database by using OLE DB or ADO is not supported.

When you write applications for Microsoft 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 Azure SQL Database. Previous versions of Server Explorer are not supported. For more information, see Visual Studio documentation on MSDN.

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.

  • Because some tools implement tabular data stream (TDS) differently, you may need to append the Azure SQL Database server name to the login portion of the connection string using the <login>@<server> notation. See Managing Databases, Logins, and Users in Azure SQL Database for more information.

  • The SQL Server 2008 SQL Server browser is not supported because Microsoft Azure SQL Database does not have dynamic ports, only port 1433.

For more information about tools support, see Azure SQL Database Tools and Utilities Support.

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 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 Microsoft Azure SQL Database 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

Microsoft Azure SQL Database does not support:

  • The RESTORE statement.

  • Attaching a database to the Azure SQL Database server.

For more information about SSIS, see SQL Server Integration Services.

Microsoft 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 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.

Both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options are set to ON in Microsoft 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.

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.

Tables in Microsoft Azure SQL Database versions prior to V12 must have a clustered index before insert operations are allowed on the table.

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. For more information, see Identifiers.

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

Microsoft Azure SQL Database provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all Microsoft 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 (for more information, see Idle Connection Resiliency)

  • 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.

An Azure 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:

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft