Chapter 3. Differences between SQL Server and Microsoft Azure SQL Database - Microsoft Azure SQL Database Step by Step (2014)

Microsoft Azure SQL Database Step by Step

Chapter 3. Differences between SQL Server and Microsoft Azure SQL Database

—Leonard Lobel

One of the most attractive aspects of Microsoft Azure SQL Database is that it shares virtually the same codebase and exposes the same tabular data stream (TDS) as on-premises Microsoft SQL Server. Thus, to a great extent, the same tools and applications that work with SQL Server work just the same and just as well with SQL Database. Notice that we said to a great extent, because despite their commonality, there are quite a few SQL Server features that SQL Database does not support. In this brief chapter, we discuss how and why these two platforms differ from one another, and we explain the SQL Database constraints you need to be aware of if you have previous experience with SQL Server.

SQL Server and SQL Database differ in several ways—most notably, in terms of size limitations, feature support, and T-SQL compatibility. In many cases, these constraints are simply the price you pay for enjoying a hassle-free, self-managing, self-healing, always-available database in the cloud. That is, Microsoft cannot responsibly support features that impair its ability to quickly replicate, relocate, and scale a SQL Database instance. This is why SQL Database places limits on database size and doesn’t support certain specialized features, such as FILESTREAM.

Another common reason why a particular feature or T-SQL syntax might not be supported in SQL Database is that it’s simply not applicable. With SQL Database, administrative responsibilities are split between Microsoft and you. Microsoft handles all the physical administration (such as disk drives and servers), while you manage only the logical administration (such as database design and security). This is why any and all T-SQL syntax that relates to physical resources (such as path names) are not supported in SQL Database. For example, you don’t control the location for primary and log file groups. This is why you can’t include an ON PRIMARY clause with a CREATE DATABASE statement, and indeed, why SQL Database does not permit a file group reference in any T-SQL statement. Plainly stated, everything pertaining to physical resources (that is, infrastructure) is abstracted away from you with SQL Database

Yet still, in some cases, a certain SQL Server feature or behavior might be unsupported merely because Microsoft has just not gotten around to properly testing and porting it to SQL Database. Azure is constantly evolving, so you need to keep watch for updates and announcements. This small chapter is a great starting point, but the best way to stay current is by reviewing the “Guidelines and Limitations” section of the SQL Database documentation on the MSDN website. (See http://msdn.microsoft.com/en-us/library/ff394102.aspx.)

Size limitations

With the exception of the free, lightweight Express edition of SQL Server, there is no practical upper limit on database size in any edition of SQL Server. A SQL Server database can grow as large as 524,272 terabytes. (For SQL Server Express edition, the limit is 10 gigabytes.)

In contrast, SQL Database has very particular size limitations. As explained in Chapter 2, “Configuration and pricing,” you can set the maximum size by choosing between the Web and Business editions. With a Web edition database, you can set the maximum database size to either 1 or 5 gigabytes (GB). With a Business edition database, the maximum database size can range from 10 to 150 GB. The absolute largest supported database size is 150 GB, although partitioning strategies can be leveraged for scenarios that require databases larger than 150 GB (as explained inChapter 8, “Designing and tuning for scalability and high performance”).

Image Note

At the time this book went to press, Microsoft announced the Preview availability of SQL Database Premium. This is a more costly option than the standard Web and Business editions of SQL Database (which have been rebranded as Basic and Standard), and it supports a maximum database size of 500 GB. As we explain in Chapter 8, SQL Database Premium also lets you scale up for performance using dedicated CPU and memory.

Connection limitations

SQL Database is far less flexible than SQL Server when it comes to establishing and maintaining connections. Keep the following in mind when you connect to SQL Database:

Image SQL Server supports a variety of client protocols, such as TCP/IP, Shared Memory, and Named Pipes. Conversely, SQL Database allows connections only over TCP/IP.

Image SQL Database does not support Windows authentication. Every connection string sent to SQL Database must always include a login user name and password.

Image SQL Database often requires that @<server> is appended to the login user name in connection strings. SQL Server has no such requirement.

Image SQL Database communicates only through port 1433, and it does not support static or dynamic port allocation like SQL Server does.

Image SQL Database does fully support Multiple Active Result Sets (MARS), which allows multiple pending requests on a single connection.

Image Because of the unpredictable nature of the Internet, SQL Database connections can drop unexpectedly, and you need to account for this condition in your applications. Fortunately, several options are available to cope with this:

• The latest version of the Entity Framework (EF6, Microsoft’s recommended data access API for .NET) has a new Connection Resiliency feature, which automatically handles the retry logic for dropped connections.

• The Microsoft Enterprise Library Transient Fault Handling Application Block, covered in Chapter 4, lets you define and implement retry strategies to deal with dropped connections.

• The ADO.NET SqlConnection class has an OpenWithRetry extension method that handles the retry logic based on the default retry policy (which must be defined using the Microsoft Enterprise Library Transient Fault Handling Application Block).

Unsupported features

This section lists many SQL Server capabilities that are not supported in SQL Database, and here we suggest workarounds where possible. Again, because this content is subject to change, we recommend you check the MSDN website for the latest information. (Seehttp://msdn.microsoft.com/en-us/library/ff394102.aspx.)

Image Agent Service You cannot use the SQL Server Agent service to schedule and run jobs on SQL Database.

Image Audit The SQL Server auditing feature records server and database events to either the Windows event log or the file system, and it is not supported in SQL Database.

Image Backup/Restore Conventional backups with the BACKUP and RESTORE commands are not supported with SQL Database. However, SQL Database supports an automated backup schedule that creates transactionally consistent backups in the form of BACPAC files created in Azure storage. You can also create BACPAC files manually; however, this does not provide transactional consistency for changes made during the export operation. To ensure transactional consistency for a manual backup, you can either set the database as read-only before exporting it to a BACPAC, use the Database Copy feature to create a copy of the database with transactional consistency and then export that copy to a BACPAC file. See Chapter 5, “Security and backup,” for more information.

Image Browser Service SQL Database listens only on port 1433. Therefore, the SQL Server Browser Service, which listens on various other ports, is unsupported.

Image Change Data Capture (CDC) This SQL Server feature monitors changes to a database, and it captures all activity related to change tables. CDC relies on a SQL Server Agent job to function and is unsupported in SQL Database.

Image Common Language Runtime (CLR) The SQL Server CLR features (often referred to simply as SQL CLR) allow you to write stored procedures, triggers, functions, and user-defined types in any .NET language (such as Microsoft C# or Visual Basic) as an alternative to using traditional T-SQL. In SQL Database, only T-SQL can be used; SQL CLR is not supported. Note, however, that this limitation does not apply to SQL Server data types implemented internally using the CLR (such as xml, geography, and geometry, all of which are supported in SQL Database).

Image Compression SQL Database does not support the data-compression features found in SQL Server, which you use to compress tables and indexes.

Image Database object naming convention In SQL Server, multipart names can be used to reference a database object in another schema (with the two-part name syntax schema.object), in another database (with the three-part name syntax database.schema.object), and (if you configure a linked server) on another server (with the four-part name syntax server.database.schema.object). In SQL Database, two-part names can also be used to reference objects in different schemas. However, three-part names are limited to reference only temporary objects in tempdb (that is, where the database name is tempdb and the object name starts with a # symbol); you cannot access other databases on the server. And you cannot reference other servers at all, so four-part names can never be used.

Image Extended events In SQL Server, you can create extended event sessions that help to troubleshoot a variety of problems, such as excessive CPU usage, memory pressure, and deadlocks. This feature is not supported in SQL Database.

Image Extended stored procedures You cannot execute your own extended stored procedures (which are typically custom-coded procedures written in C or C++) with SQL Database. Only conventional T-SQL stored procedures are supported.

Image File streaming SQL Server native file-streaming features, including FILESTREAM and FileTable, are not supported in SQL Database. Instead, you can consider using Azure Blob Storage containers for unstructured data files, but it will be your job at the application level to establish and maintain references between SQL Database and the files in blob storage, though note that there will be no transactional integrity between them using this approach.

Image Full-Text Searching (FTS) The FTS service in SQL Server that enables proximity searching and querying of unstructured documents is not supported in SQL Database. However, there is a third-party text search engine library available from Lucene that does work with SQL Database. For more information, visit http://www.lucene.net.

Image Mirroring SQL Database does not support database mirroring, which is generally a non-issue because Microsoft is ensuring data redundancy with SQL Database, so you don’t need to worry about disaster recovery. This does also mean that you cannot use SQL Database as a location for mirroring a principal SQL Server database running on-premises. However, if you want to consider the cloud for this purpose, you can host SQL Server inside an Azure virtual machine (VM) against which you can mirror an on-premises principal database. This solution requires that you also implement a virtual private network (VPN) connection between your local network and the Azure VM, although it will work even without the VPN if you use server certificates.

Image Partitioning With SQL Server, you can partition tables and indexes horizontally (by groups of rows) across multiple file groups within a database, which greatly improves the performance of very large databases. SQL Database has a maximum database size of 150 GB (or 500 GB, for the newly announced Premium edition) and gives you no control over file groups, thus it does not support table and index partitioning.

Image Replication SQL Server offers robust replication features for distributing and synchronizing data, including merge replication, snapshot replication, and transactional replication. None of these features are supported by SQL Database; however, SQL Data Sync can be used to effectively implement merge replication between a SQL Database and any number of other SQL Databases on Microsoft Azure and on-premises SQL Server databases. See Chapter 7, “Microsoft Azure SQL Data Sync,” for more information.

Image Resource Governor The Resource Governor feature in SQL Server lets you manage workloads and resources by specifying limits on the amount of CPU and memory that can be used to satisfy client requests. These are hardware concepts that do not apply to SQL Database, so the Resource Governor is unsupported.

Image Service Broker SQL Server Service Broker provides messaging and queuing features, and it is not supported in SQL Database.

Image System stored procedures SQL Database supports only a few of the system stored procedures provided by SQL Server. The unsupported ones are typically related to SQL Server features and behaviors not supported by SQL Database. At the same time, SQL Database provides a few new system stored procedures not found in SQL Server that are specific to SQL Database (for example, sp_set_firewall_rule).

Image Tables without a clustered index Every table in a SQL Database must define a clustered index. By default, SQL Database will create a clustered index over the table’s primary key column, but it won’t do so if you don’t define a primary key. Interestingly enough, SQL Database will actually let you create a table with no clustered index, but it will not allow any rows to be inserted until and unless a clustered index is defined for the table. This limitation does not exist in SQL Server.

Image Transparent Data Encryption (TDE) You cannot use TDE to encrypt a SQL Database like you can with SQL Server.

Image USE In SQL Database, the USE statement can refer only to the current database; it cannot be used to switch between databases as it can with SQL Server. Each SQL Database connection is tied to a single database, so to change databases, you must connect directly to the database.

Image XSD and XML indexing SQL Database fully supports the xml data type, as well as most of the rich XML support that SQL Server provides, including XML Query (XQuery), XML Path (XPath), and the FOR XML clause. However, XML schema definitions (XSD) and XML indexes are not supported in SQL Database.

Summary

In this brief chapter, you learned about the important differences between on-premises SQL Server and SQL Database on Microsoft Azure. We explained the SQL Database limitations on size, as compared to a virtually unlimited database size supported by SQL Server. We also discussed connection limitations, and important considerations to keep in mind with respect to dropped connections, which occur with relative frequency in SQL Database. The chapter concluded by enumerating the many SQL Server features that are either unsupported or have limited support in SQL Database, and offered workarounds where possible.

The information in this chapter will help you decide whether or not SQL Database is suitable for your particular scenario. Of course, if you determine that it is not, always remember that you can run on-premises SQL Server in an Azure VM (we show you how in Chapter 6). This IaaS approach provides you with full SQL Server functionality in the cloud, compared to the PaaS approach of going with SQL Database.