In my last post, Azure SQL Database vs. SQL Server: Rent or Own, I explained the high-level differences between Azure SQL Database, SQL Server on an Azure Virtual Machine, and traditional SQL Server. In this article, I’ll provide an overview of what you can and cannot do with Azure SQL Database.
SQL Database in the Azure Cloud
Azure SQL Database is a cloud-based service that currently provides relational database functionality to more than one million databases. The Azure SQL Database “server” is actually a logical grouping of databases that may reside on different physical machines; the hardware and software infrastructure is owned, hosted and maintained by Microsoft. This arrangement frees the customer from the administrative overhead required to maintain and administer hardware/software infrastructure, network connectivity and security. High availability features are also included with Azure SQL Database: built-in backups and geo-replication ensure that your data is recoverable, even to a point in time or in event of a regional outage. In addition, most of the functionality of traditional SQL Server is included with Azure SQL Database so developers can quickly get to work. From customer interviews, Azure SQL Database was cited in an IDC Business Value study as, “more than 53% less expensive than on-premise/hosted databases and driving almost a 47% increase in IT staff efficiencies.” So with reduced overhead, increased efficiency, high data availability and familiar functionality, Azure SQL Database offers solid reasons to choose this platform for your database needs.
Azure SQL Database does, however, have some limitations. Because it is a virtual database instance with shared physical resources, Azure SQL Database must impose some restrictions on what users can do. Server-level functionality is generally restricted or unsupported. Server-centric features such as Service Broker or SQL Agent are not available. Some Transact-SQL commands are partially supported, with options related to database management or server configuration unavailable. The latest version of Azure SQL Database, v12, expands some functionality and removes some limitations of previous versions. Below is an overview of issues to consider when using Azure SQL Database.
Disclaimer: Some of these limitations I’ve encountered myself; others are based on research I’ve done to prepare myself for future projects.
Service Tiers and Performance Levels
Azure SQL Database comes in three tiers (Basic, Standard, and Premium) that offer different performance levels at varying costs. In the past, Microsoft offered Web and Business service tiers, but those will be retired later this year. Each tier determines not only performance level, but also database size. All tiers limit the number of databases to 150. For more information, see Azure SQL Database Service Tiers and Performance Levels
If the database reaches its size limit as defined in its service tier, no INSERTs or UPDATEs allowed. SELECTS are permitted, as is DELETE, TRUNCATE and DROP.
Commands related to the msdb database and objects associated with SQL Agent are not supported. This includes jobs, alerts, database mail, operators, and policy-based management. It is possible to schedule jobs on an on-premise SQL Server that connects to your Azure SQL Database instance using bcp or sqlcmd.
High availability options such as AlwaysOn, database mirroring and log shipping are not supported. Attach/Detach database is not supported. Backup/Restore commands are not available, but Azure SQL Database does provide automatic backups and geo-replication for Service Tiers Basic, Standard and Premium. Point-in-time recovery is possible. Backup retention periods are determined by Service Tier. If you need to retain data past the retention period, an alternative archive strategy is necessary. The recommended method is to use the Azure SQL Database Copy function to create a transactionally consistent copy of the database, then use Data Export to create a BACPAC file. For more information, see Azure SQL Database Backup and Restore.
Windows authentication is not supported in Azure SQL Database. SQL Authentication is required with strong passwords. This means that credentials must be supplied for each connection.
All incoming connections to the Azure SQL Database are blocked unless the IP address is explicitly specified in the Azure Management Portal. Firewall rules can be created on both a server and a database level. Return traffic from the client to the SQL Database may also be blocked on some customer networks unless explicitly defined. For easier management, you can allow connections from the client to a range of Microsoft public network addresses instead of a single specific Azure SQL Database address.
An administrative login, also referred to as the server-level principal, is created during provisioning of the database instance; it is analogous to the sa login. It cannot be dropped or altered, but its password can be reset from the Azure Portal.
Two database roles in the master database control who can manage security in the Azure SQL Database: loginmanager and dbmanager. Loginmanager members can create logins and users; dbmanager members can create databases. They are similar to the securityadmin and dbcreator roles in an on-premise SQL Server. The administrative login has the permissions associated with these two roles, although it is not listed as a member.
Version 12 allows contained database users. These users authenticate at the database level and do not require an associated login. This offers the advantage of more portable databases, but also reduces the ability of the administrative login to manage the database.
For more information, see Azure SQL Database Security Guidelines and Limitations, .Security Guidelines for Azure SQL Database and Azure SQL Database Firewall.
Applications can connect to Azure SQL Database with several connection libraries, including ADO.NET, the Entity Framework, WCF Data Services, SQL Server 2008 Driver for PHP version 1.1, and ODBC.
Before you can connect to Azure SQL Database, you must configure the firewall using the Azure Portal to allow specific IP addresses to connect to the instance. Dynamic ports are not supported; outbound traffic from Azure SQL Database requires port TCP/1433 to be open. Make sure the firewall on the customer network and local computer allows outgoing requests on TCP port 1433.
Version SQL Server 2008 R2 and greater of Analysis Services and Reporting Services can connect to an Azure SQL Database instance as a data source.
The USE statement is not supported; connect directly to required database.
Linked servers are not supported, along with OPENROWSET and OPENQUERY.
Server-level default collation is SQL_Latin1_GENERAL_CP1_AS (code-insensitive, accent-sensitive). This cannot be changed. However, you can set collation on a database, column or expression level.
Transactions are supported, but not distributed transactions. From Azure Wiki: “SQL Database does not support distributed transactions, which are transactions that multiple transaction managers (multiple resources)…This means that SQL Database doesn’t allow Microsoft Distributed Transaction Coordinator (MS DTC) to delegate distributed transaction handling. Because of this you can’t use ADO.NET or MSDTC to commit or rollback a single transaction that spans across multiple SQL Databases or a combination of SQL Database and an on-premise SQL Server.”
Most Transact-SQL statements are available in Azure SQL Database. When an option is not supported, it is generally because it is related to server-specific functionality. For example, CREATE or ALTER DATABASE options related to database file placement or growth management are not allowed since Azure SQL Database manages that for you. Configuration commands that set hardware related options like memory, cpu and worker threads are not allowed.
Other Unsupported Features
- Replication – use SQL Data Sync, bcp or SSIS to move data to another database.
- Change Data Capture – CDC is not available since it relies on the log reader. However, Change Tracking is available with v12.
- Global temporary tables
- Trace flags
- Transact-SQL debugging
- SSIS instance. Can connect via on-premise SSIS.
- Database diagrams
- Triggers: Server-scoped or logon triggers
- Service Broker
- SQL Server error log. Operation logs are available from the Azure Portal.
Because Azure SQL Database is a logical grouping of databases in a shared physical environment, restrictions are enforced in order to ensure optimal performance to all Azure SQL Database users. Therefore, connections may be terminated or throttled due to the following conditions:
- Excessive resource usage: memory, number of transactions per minute, CPU
- Long-running queries
- Long-running single transactions
- Idle connections
These restrictions force developers to follow best practices in coding: keeping transactions short, using appropriate indexes, executing large DML operations in batches, using connection pooling, keeping statistics up to date.For more information see Azure SQL Database Resource Management and Azure SQL Database Best Practices to Prevent Request Denials or Connection Termination.
In version 11, heap tables are not supported. You can create a table without a clustered index, but you would be unable to insert, update or delete data in the heap table. This has changed in version 12. In the latest version, you can create and modify data in a table without a clustered index. SELECT INTO functionality is also supported in v12, so that you can create a table from a result set.
Version 11 does allow creation and data population of a local temporary table without a clustered index. Global temporary tables are not supported in any version.
Azure SQL Database does not provide monitoring utilities such as SQL Profiler, SQL Trace, Activity Monitor or PerfMon. However, visibility into performance issues such as blocking, query plans, and connections is possible with dynamic management views. VIEW DATABASE STATE permission is required to query DMVs. The Azure Portal also provides some monitoring capabilities; deadlocks, DTU percentage, successful connections and failed connections are displayed in a graph format on the Monitor tab.
Monitoring of illegal or attempted connections is not available. Third-party cloud-based applications can provide this service. For more information, see Monitoring Azure SQL Database Using Dynamic Management Views
Version 12 Enhancements
Version 12 of Azure SQL Database expands some functionality and removes some limitations of previous versions. Here’s a few highlights:
- More support for cryptographic functions
- DBCC SQLPERF, allows visibility into transaction log space used.
- Contained database users – associated login is not required.
- Larger transactions – 2G limit per single transaction is eliminated.
- Larger Indexes – prior to v12, indexes were limited to 16 columns, 900 bytes. Now it is 32 columns and 900 bytes for a clustered index, 1700 bytes for a non-clustered index.
- Full-text Indexing – in preview status, not yet generally available.
- .Net CLR integration
- Application roles
- More DMVs available
- Change Tracking
- Indexes on XML columns
- Table partitioning – all partitions are mapped to Primary filegroup.
For more information, see What’s new in SQL Database V12.
Azure SQL Database is a Good Choice
In conclusion, Azure SQL Database offers many advantages over your traditional SQL Server implementation. It reduces administrative overhead, increases staff efficiency, offers familiar functionality, includes high availability, and accelerates development time. But since it is a service in the cloud, there are some limitations to be aware of, mainly due to the multi-tenant, abstracted nature of the Azure SQL Database instance. With version 12, however, it is close to full compatibility with traditional SQL Server. For most applications, Azure SQL Database is a viable choice for hosting your data.