Thursday, April 25, 2024
Latest:

SQLChamp

Let's learn together!

Azure Data PlatformSQLAzure

Know these restrictions\limitations before moving to SQLAzure

Sarabpreet Singh Anand

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

  1. Eventing: events, event notifications, query notifications
  2. Functions: fn_get_sql, fn_virtualfilestats, fn_virtualservernodes
  3. SQL Server audit (use SQL Database auditing instead)
  4. SQL Server Profiler
  5. SQL Server trace
  6. Trace flags

 

Backup & Recovery Limitations

  1. Attach and detach operation doesn’t work.
  2. Recovery Model
  3. You can’t take normal DB or Log backups and restore operations.

 

Connectivity Limitations

  1. Endpoint statements, ORIGINAL_DB_NAME.
  2. Microsoft Azure SQL Database supports tabular data stream (TDS) protocol client version 7.3 or later.
  3. Only TCP/IP connections are allowed that too on Port 1433, it is not possible to change the port.
  4. The SQL Server 2008 SQL Server browser is not supported because Microsoft Azure SQL Database does not have dynamic ports, only port 1433.
  5. Windows Authentication is not supported. However, Azure Active Directory Authentication is supported with certain limitations.

 

Security Limitations

  1. Encryption: extensible key management
  2. EXECUTE AS logins
  3. HAS_DBACCESS
  4. Server credentials
  5. 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.
  6. Server roles, IS_SRVROLEMEMBER, sys.login_token.
  7. These user names are not allowed for security reasons: admin, administrator, guest, root, sa

 

Other Limitations

  1. 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.
  2. Transact-SQL debugging
  3. Cross database ownership chaining, TRUSTWORTHY setting
  4. Cross database queries using three or four part names. (Read-only cross-database queries are supported by using elastic database query.)
  5. DATABASEPROPERTY (use DATABASEPROPERTYEX instead)
  6. Every table must have a clustered index in order to insert records.
  7. Features related to database file placement, size, and database files which are automatically managed by Microsoft Azure.
  8. FILESTREAM
  9. Global temporary tables
  10. KILL STATS JOB
  11. Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT, 3 and 4 part names
  12. SET REMOTE_PROC_TRANSACTIONS
  13. SHUTDOWN
  14. sp_addmessage
  15. sp_helpuser
  16. sp_migrate_user_to_contained

 

Server Level Limitations

  1. .NET Framework CLR integration with SQL Server
  2. Collation of system objects
  3. Data Collector
  4. Database Diagrams
  5. Features that rely upon the log reader: Replication, Change Data Capture.
  6. HA & DR: AlwaysOn, database mirroring, log shipping, recovery modes.
  7. Hardware related server settings: memory, worker threads, CPU affinity, trace flags, etc. Use service levels instead.
  8. Resource governor
  9. Semantic search
  10. Serverless express: localdb, user instances
  11. Service broker
  12. sp_configure options and RECONFIGURE
  13. Triggers: Server-scoped or logon triggers

 

SQLAgent Limitations

  1. Alerts
  2. Central management servers.
  3. database mail
  4. Jobs
  5. Master/target servers
  6. 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.
  7. Operators
  8. 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. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox