As we already discussed SQLAzure is an offering under Microsoft Azure umbrella as PaaS (Platform as a service) & there are limitations of using SQLAzure (SQLDatabase) over SQLServer in Azure VM (or on-premise SQLServer) I am listing down all the limitations under specific headings which will help you prepare accordingly and decide before shooting the arrow (Making the switch).
Make sure you read the detailed difference between both offerings so that you understand it better. The Limitations of SQLAzure are as follows:
Troubleshooting & Audit related Limitations
- Eventing: events, event notifications, query notifications
- Functions: fn_get_sql, fn_virtualfilestats, fn_virtualservernodes
- SQL Server audit (use SQL Database auditing instead)
- SQL Server Profiler
- SQL Server trace
- Trace flags
Backup & Recovery Limitations
- Attach and detach operation doesn’t work.
- Recovery Model
- You can’t take normal DB or Log backups and restore operations.
- Endpoint statements, ORIGINAL_DB_NAME.
- Microsoft Azure SQL Database supports tabular data stream (TDS) protocol client version 7.3 or later.
- Only TCP/IP connections are allowed that too on Port 1433, it is not possible to change the port.
- The SQL Server 2008 SQL Server browser is not supported because Microsoft Azure SQL Database does not have dynamic ports, only port 1433.
- Windows Authentication is not supported. However, Azure Active Directory Authentication is supported with certain limitations.
- Encryption: extensible key management
- EXECUTE AS logins
- Server credentials
- 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.
- Server roles, IS_SRVROLEMEMBER, sys.login_token.
- These user names are not allowed for security reasons: admin, administrator, guest, root, sa
- USE statement: The USE command doesn’t work against SQL Database to switch database contexts. Because a database can be physically located on any server, the only practical way to switch databases is to reconnect.
- Transact-SQL debugging
- Cross database ownership chaining, TRUSTWORTHY setting
- Cross database queries using three or four part names. (Read-only cross-database queries are supported by using elastic database query.)
- DATABASEPROPERTY (use DATABASEPROPERTYEX instead)
- Every table must have a clustered index in order to insert records.
- Features related to database file placement, size, and database files which are automatically managed by Microsoft Azure.
- Global temporary tables
- KILL STATS JOB
- Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT, 3 and 4 part names
- SET REMOTE_PROC_TRANSACTIONS
Server Level Limitations
- .NET Framework CLR integration with SQL Server
- Collation of system objects
- Data Collector
- Database Diagrams
- Features that rely upon the log reader: Replication, Change Data Capture.
- HA & DR: AlwaysOn, database mirroring, log shipping, recovery modes.
- Hardware related server settings: memory, worker threads, CPU affinity, trace flags, etc. Use service levels instead.
- Resource governor
- Semantic search
- Serverless express: localdb, user instances
- Service broker
- sp_configure options and RECONFIGURE
- Triggers: Server-scoped or logon triggers
- Central management servers.
- database mail
- Master/target servers
- Microsoft Azure SQL Database does not support SQL Server Agent or jobs. You can, however, run SQL Server Agent on your on-premises SQL Server and connect to Microsoft Azure SQL Database.
- Policy-Based Management
I hope this post will help you to decide whether SQLAzure is best fit for your case or not.
Hope you enjoyed the post, feel free to leave a comment. 🙂