As everything seems to migrate to the cloud, establishing an Azure SQL database sounds promising, but it has limits over an on-premise SQL Enterprise installation.
Azure SQL database is a relational database as a service on cloud based on Microsoft’s SQL server engine. Companies using this will have their database on standardized hardware and software that is owned, hosted, and maintained by Microsoft. So you won’t have to manage any virtual machines, OS or database software, nor will you have to handle the upgrades.
Companies can develop directly on the service using built-in features and functionality. Microsoft set up SQL Database as pay-as-you-go with options to scale up or out for greater power with no interruption.
Taking advantage of Microsoft’s Azure SQL (PaaS) database does reduce the administration required to set up an on-premise instance. But it only offers a subset of the functionality found in SQL Enterprise.
Even the latest update, SQL Database V12, supports some but not all of the arguments that exist in the SQL Server 2016 Transact-SQL statements. The CREATE PROCEDURE statement is available, but not the WITH ENCRYPTION option.
For a list of all features not supported in SQL Database, visit Microsoft’s Azure SQL Database Transact-SQL differences documentation.
- Cross-database queries using three or four part names. (Read-only cross-database queries are supported by using elastic database query.)
- Cross database ownership chaining, TRUSTWORTHY setting
- Data Collector
- EXECUTE AS logins
- Encryption: extensible key management
- Eventing: events, event notifications, query notifications
- Features that rely on the SQL Server Agent or the MSDB database: jobs, alerts, operators, Policy-Based Management, database mail, central management servers.
- Functions: fn_get_sql, fn_virtualfilestats, fn_virtualservernodes
- Global temporary tables
- .NET Framework CLR integration with SQL Server
- Resource governor
- Semantic search
- Server credentials
- Sever-level items: Server roles, IS_SRVROLEMEMBER, sys.login_token. Server level permissions are not available though some are replaced by database-level permissions. Some server-level DMV’s are not available though some are replaced by database-level DMV’s.
- SQL Server audit (use SQL Database auditing instead)
- SQL Server Profiler
- SQL Server trace
- Trace flags
- Transact-SQL debugging
- USE statement: To change the database context to a different database you must make a new connection to the new database.
You can also check out Sarabpreet Singh Anand’s list, which he put into categories such as troubleshooting & audit related; backup & recover; connectivity, security, server level and SQLAgent.
SQL Server Enterprise
Microsoft describes SQL Server 2016 Enterprise as comprehensive, mission-critical in-memory performance with unparalleled security, an end-to-end enterprise business intelligence solution with mobile BI built-in, and in-database advanced analytics at scale.
It features an unlimited number of cores and a maximum size of 524 PB. In addition, it offers advanced high availability, advanced security and data integration. Programmability and developer tools include T-SQL, CLR, Data Types, FileTable and JSON.
Which Option is Cheaper?
Most of the decisions on an organization’s choice of SQL database services put a major emphasis on the costs. What’s cheaper in the long term?
For Azure SQL, Microsoft charges monthly based on services and processing power. That can be expensive over a long period of time.
For SQL Server Enterprise on premise, an organization buys hardware that might be used for up to four years before replacement. The company recoups the license cost over time. With Azure SQL, the cost has to be justified monthly.
Checklist to Guide Your Choice
Ask yourself these questions before migrating to the cloud. (And even then, you’ll have to consider what level of service to use.)
- How does a mature data model in Azure SQL compare to what’s possible on premise?
- What functionality does your application require?
- How does Azure SQL handle multiple database queries and data warehousing?
- How does it handle multi-database queries, data warehousing?
Create and Learn About Azure SQL Limitations
Also, read Daniel Calbimonte‘s article on SQLServerCentral.com. In his article, he shows how to create an Azure SQL Database, and then details its limitations compared with a SQL Server Enterprise Edition.