Azure SQL or SQL server: Which one is right for you?

Over the last few years, Microsoft has been focusing more on cloud computing and storage, in a bid to take a substantial market share in this competitive and lucrative market. To this end, it has come up with many products designed for the cloud, and one such product is Azure SQL.

Azure SQL is a cloud-based service built along the lines of SQL Server, and so shares many features with on-premises SQL Server.

Let’s take a detailed look into how these two database systems compare with each other, and which of the two is best in different situations.

Differences between SQL Server and Azure SQL

azure sql database
Since Azure SQL is based on SQL Server, they share many similarities in functionality and compatibility. But that doesn’t mean they are the same. In fact, contrary to what many think, Azure SQL is not exactly the cloud version of SQL Server because there are many fundamental differences between the two.

Before moving on to the exact differences, it is imperative to understand a fundamental aspect. In SQL server, databases are the only entity on the database server, but in SQL Azure, a single database can host databases from different customers. In other words, Azure SQL is multitenant and shares its physical resources with all clients who use that service.

Now that we have understood this fundamental difference, let’s move on to others.

Architecture

Azure SQL is designed so SQL developers can use it easily for communication and authentication. This is why there isn’t much of a difference in terms of programming. In many cases, you’ll have to simply change the value of a parameter in the connection string to access Azure SQL. It’s really that simple.

That said, there is a big difference in communication. In SQL Server, your application talks directly to the server over your LAN and uses Tabular Data Stream (TDS) protocol over TCP/IP or HTTP. Also, it can communicate on any configurable port and does not really require transport security.

But in SQL Azure, your application should make an explicit call, preferably through your network’s firewall, and this call can reach the Azure Gateway only through the Internet. So, Azure SQL uses only TCP/IP protocol for communication.

At the Gateway, there is another firewall called SQL Azure firewall that allows only those IP addresses that you’ve explicitly defined to get access. After getting past this firewall, your call makes the connection to the backend data node using Gateway as the proxy.

To top it, all communications happen only through SSL and it goes only through port 1433.

If you compare the two, you’ll know that Azure SQL has a more complicated architecture, and that’s because it accepts connects through the Internet, and also caters to multiple clients.

Data definition language

nosql
Data definition language, or DDL in short, is a syntax used for creating and changing the structure of database objects.

Azure SQL does not provide support for certain features because of its architecture and the fact that it does not have access to an underlying hardware platform.

Both these databases differ in the following ways:

Common language runtime

SQL Azure does not support common language runtime (CLR). This means system views, stored procedures, triggers, and user-defined functions are not available in Azure. This limitation is because of the multitenant nature of Azure. It aims to protect one user from accidentally or intentionally using others’ CLR objects on the same server.

Extended stored procedures and table partitioning

Extended stored procedures are routines stored in DLLs. Written in C or C++ languages, these stored procedures use Open Data Services API to get parameters and to return results.

Table partitioning is another feature in SQL server that spreads table or index data across multiple file groups within the same database to improve performance.

SQL Azure doesn’t support both these features.

Clustered indexes

Clustered indexes change the order in which records are stored in a physical database. This means one table can have only one clustered index. In SQL server, having a clustered index is optional.

In SQL Azure, all tables should have a clustered index. If not, INSERT operations will fail. Again, this boils down to Azure’s architecture and its lack of support for heap tables.

If you ever have to migrate your data from SQL Server to SQL Azure, plan to build clustered indexes for tables that don’t have them.

Data types

SQL Azure supports all data types supported by SQL Server including specialized ones such as spatial data types. However, Azure does not support XML indexing and typed XML

Database mirroring and failover clustering

SQL Azure doesn’t support database mirroring and failover clustering. If you’re wondering why, it’s because SQL Azure is built on the Windows Azure platform that provides high availability! So, every user database is replicated two times on two different nodes. When the primary node goes down, one of the two replicas will take over. This is why it doesn’t need an explicit failover clustering like SQL Server.

Data manipulation language

Data manipulation language (DML) is a language used for performing basic CRUD (create, update and delete) operations in any application.

SQL Azure does not support certain CRUD commands that are available in SQL server, and they are:

  • MAXDOP is always 1
  • PAGLOCK
  • REMOTE
  • No support for full-text search using CONTAINS and FREETEXT
  • Does not support BULK INSERT
  • No support for ROW set functions such as CONTAINSTABLE, FREETEXTTABLE, OPENQUERY, and OPENXML .

Deployment

Since SQL Azure is a logical rather than a physical entity, the deployment architecture is different when compared to SQL server. In this cloud database, there are three entities — subscription, server, and database.

  • Subscription is your Windows Azure platform and the services that are a part of it. This entity determines the context for billing.
  • Server means a logical grouping of databases that share a common datacenter.
  • Database is a logical representation of a database, which exists as a partition within a SQL server database.

Unsupported features

There are also many features of SQL server that are not supported by SQL Azure, and they are:

  • Master data services
  • Attaching a database
  • Change data capture
  • Data auditing
  • Resource governor
  • Data quality service (DQS)
  • Database snapshots
  • Data compression
  • SQL server browser
  • Distributed transactions
  • Extended stored procedures
  • Filestream
  • Minimal logging in bulk import
  • Polybase
  • Policy-based management
  • Semantic search
  • SQL server reporting services
  • Trace flags

From the above discussion, it is clear that SQL Azure is a subset of SQL Server, and comes with many limitations because of its architecture.

So why in the world would someone use it instead of the fully featured SQL Server?

Much more than a database

Azure SQL
Microsoft has big ambitions for Azure in general and for SQL Azure in particular. They want to create not just a cloud database but an infrastructure for petabyte-scale data. This means, SQL Azure is not just for today but is more geared for the future when the cloud will be all-pervasive.

So, if you’re planning an infrastructure for the long-term, SQL Azure is a good choice.

Management and administration is easy

If you’ve used SQL Server, you already know how difficult it is to set it up and administer. Apportioning databases and working around physical systems is a hassle, to say the least.

When this job is done by someone else, you can focus on other productive work and it is so much less headache for you.

High availability

Another important reason to use SQL Azure is its high availability. The Azure platform, in general, is renowned for this aspect. Since SQL Azure is built on it, high availability is assured.

Business continuity

Azure SQL automatically backs up database and transaction log periodically, as a part of its business continuity strategy. It uses read-access geo-redundant (RA-GRS) to provide geo-redundancy. This backup option gives you cross-geographical durability, so there is no question of data loss.

In addition, you can restore database any point in time in case of accidental deletions. During regional outages too, you can restore database from a different region. This ease of backup and restoration is yet another reasons to use Azure SQL.

Easy scalability

Microsoft has taken considerable efforts to make SQL Azure appealing for both small and big businesses. They have created it in such a way that the architecture can be scaled to meet varying business needs.

Also, the pricing structure is based on usage, so businesses can choose their usage level and pay accordingly.

In addition, applications written for any database can be scaled with SQL Azure as Microsoft provides an infrastructure of federations to make this scaling process less impactful on users.

Now, this doesn’t mean SQL Azure is ideal in all situations. Many features of SQL server are not implemented in this cloud version, and for good reason. So, if those features are important to you, then SQL Server is your best choice.

So now you know: SQL Azure is a cloud-based database service that is a subset of SQL server. The differences in the architecture of both these databases, make them ideal for different scenarios and setups.

As a user, go for SQL Azure if you want a highly available and scalable database and you don’t plan to use all the extensive features of SQL server. On the other hand, if the massive set of features of SQL server are necessary for your organization, go for the full version by all means.

Featured image: Shutterstock

4 thoughts on “Azure SQL or SQL server: Which one is right for you?”

  1. Hi Lavanya,

    As an essential part of business continuity and disaster recovery strategy, Azure SQL Database automatically backs up the database and transaction log periodically and uses read-access geo-redundant (RA-GRS) Azure storage to provide geo-redundancy. In the event of accidental corruption or deletion, users can restore the database to any point in time during the retention. During regional outages, users can also restore the database to a different region. You can find more information from the Azure SQL Database documentation: https://docs.microsoft.com/azure/sql-database/sql-database-automated-backups.

    Can you please update your article to reflect this? There are other Azure SQL DB details mentioned here that should be revisited too – but that was one of the bigger areas that needs correction.

    Thanks,
    Joe Sack, Principal PM, Microsoft

Leave a Comment

Your email address will not be published.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Scroll to Top