Search code examples
sql-serverazure-sql-databasesql-server-2014sql-server-2016

How does SQL Azure differ from SQL Server?

I am completely new to SQL Azure, I use an on-premise SQL Server and aware of cloud services like Amazon AWS RDS which host SQL Server in cloud. I understand SQL Azure is built on top of SQL server and hosted in cloud. But is "hosted in cloud" the only difference? But then I get confuse because SQL MSDN pages call out certain topics apply to SQL Azure vs. SQL Server.

  • Does SQL Azure has versions like SQL 2014, 2016 or is it a totally different SQL-variation product line from Microsoft?
  • What additional features/benefits does SQL Azure offer(besides those that naturally come from hosting in cloud - scalability, availability, low startup pricing etc)?
  • What major areas/features SQL Azure differ from SQL Server? Are there any TSQL Syntax variations, SQL Engine optimizations?
  • When is a SQL Server (let's say, also hosted in cloud, like AWS RDS) is preferred over SQL Azure?

For this discussion, lets say the SQL server is 2014 or 2016 version.

Solution

  • Azure SQL database is the latest version of SQL Server. Unlike SQL Server that is released periodically (e.g. 2014, 2016, etc.), Azure SQL Database always has the latest version of stable features. Every new feature in SQL Server is pushed to Azure SQL as soon as it passes tests, while in SQL Server you need to wait for the next version and explicitly install it (e.g. 2016). There is a constraint in Azure SQL compared to SQL Server because all features that require file system access or some specific resources are not enabled (e.g. FileStream, Service Broker, etc.)

    Another important feature is advisor mechanism - if you have Azure SQL Database, you will get some features as notifications for threat detection, automatic indexing/tuning. If you host SQL Server in your own environment you will need to monitor it your self.

    Also, Azure SQL database is a service that you can easily tune and scale up/down. You can choose some performance tier than easily move to lower/higher tiers. In on-prem SQL Server, you would need to buy more hardware with stronger CPU.