AzureAzure SQL Database Limitations April 21, 201616462 views0Share By IG ShareThis post is about the Azure SQL Database Limitations. Azure SQL Database is a robust, one of a kind “Database as a Service” offering from Microsoft. It has some major advantages like Built-in High Availability, Point in time database backups, Geo-Availability etc. You can set these up with a click of a button, within minutes, without the help of the IT Department or a DBA. While these are major advantages, there are some Azure SQL Database limitations which one need to look into before jumping in. Let’s look at the limitations.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.Table of Contents ToggleAzure SQL Database Limitations (DB and T-SQL Related)However, your On-Premises SQL Server must have the following build:Azure SQL Database Limitations: Database InternalsOther Azure SQL Database LimitationsAzure SQL Database Limitations (DB and T-SQL Related)You cannot change the Collation settings of system objects in the Database.You will not be able to use Endpoint statements or ORIGINAL_DB_NAME.You cannot use Windows authentication on SQL Azure. You can only use SQL Logins or Azure Active Directory tokens.You will not be able to do distributed database queries using three or four part names. However, you can do read-only queries across databases by using elastic database query.Cross database ownership chaining or TRUSTWORTHY setting is not supported.You do not have Performance Data Collector sets available to capture events using tools like Perfmon.exe.Database Diagrams are not available.Database Mail is not supported Out of the box. However, you can use custom Message-Queue tables and monitor using custom agents.Instead of DATABASEPROPERTY, you need to use DATABASEPROPERTYEX.You cannot use EXECUTE AS command for logins.Encryption is supported. Extensible key management functionality is not available. See my earlier post on Azure SQL security features.Any kind of event tracking, notifications or query notifications is not supported.You cannot create custom file groups or select MDF or LDF database file placement, size, or other best practices that you would follow for SQL On-Premises or SQL in a VM on Azure. Database files are automatically managed by Microsoft Azure.You cannot manage any high availability features. Features like backup & restore, Always On, database mirroring, log shipping, recovery modes is managed through the Microsoft Azure account.Replication features are not supported which rely upon the log reader agent service for On-Premise deployments. Features like Replication, Change Data Capture are not available. However, you can use Azure Database as a subscriber to an on-premise Database or SQL on VM in Azure.However, your On-Premises SQL Server must have the following build:SQL Server 2016 Editions supporting Replication.SQL Server 2014 Service Pack 1 Cumulative Update 3SQL Server 2014 RTM Cumulative Update 10SQL Server 2012 Service Pack 2 Cumulative Update 8Azure SQL Database Limitations: Database InternalsYou only have access to the Master Database. Any features that rely upon the SQL Server Agent or the MSDB database like backup jobs, alerts, operators, Policy-Based Management, database mail, central management servers are not supported.FILESTREAM pointing to an external file location is not supported.Global temporary tablesYou cannot manage any hardware related server settings like memory, worker threads, CPU affinity or NUMA Nodes, trace flags etc. You need to use the appropriate service tiers to meet your performance goals.HAS_DBACCESS, KILL STATS JOB, Linked servers, OPENQUERY, OPENROWSET, OPENDATASOURCE, BULK INSERT or SQL bulk insert statement to import data is not supported. Use API instead.3 and 4 part names Master/target servers etc. are not supported..NET Framework CLR integration with SQL Serversp_xml_prepare document ,OPENXML, sp_xml_removedocument are not currently supportedNo support for MDS, DQS or SSIS support. Use Azure SQL VM instead.Resource governor is managed by Azure. You cannot create custom pools or assign server resources.Full-text search is present with the following limitations:No support for installation or use of third party filters, including Office and .pdf.Customers cannot manage service settings for the host. All configurations are being managed by the service.Semantic search, thesaurus and search property lists syntax is not yet enabled.Server credentials and Sever-level items like Server roles, IS_SRVROLEMEMBER, sys.login_token. Server level permissions are not available. You need to use database-level permissions. Server-level DMV’s are not available and instead you need to use database-level DMV’s.Serverless express edition of SQL-like local DB, user instances are not supported.Service brokerExporting and importing a database that has external data source or external tables is not supportedThe following System Stored Procedures are not supported:SET REMOTE_PROC_TRANSACTIONSSHUTDOWNsp_addmessagesp_configure options and RECONFIGUREsp_helpusersp_migrate_user_to_containedOther Azure SQL Database LimitationsSQL Server audit is not present, you need to use SQL Database auditing instead.You cannot use SQL Server Profiler to take a SQL Server traces for analysis like Deadlocks etc.Trace flagsTransact-SQL debuggingTriggers: Server-scoped or logon triggersUSE statement database context switching is not supported. Instead, separate connections need to be used to connect to different databases.Hard limit of Databases using Automated export per subscription is 10.You can have up to 5000 databases per server on the newer V12 servers.You can have up to a max of 45000 DTUs per server on V12 servers for provisioning databases, creating elastic pools and Azure SQL Data Warehouses. See references for additional information.Please be advised that this is my personal collated list. Please refer to the official documentation for further reference:Full-Text Search is now available in Azure SQL Database (GA)SQL DatabaseAzure SQL Database Transact-SQL differencesTransactional Replication to Azure SQL DB now in public previewSQL Database Resource Limits Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 2 In Love 0 Not Sure 0 Silly 0
AzureMove & Load Data to Azure SQL DataWarehouse using Data Platform StudioThere are multiple ways to populate data into Azure SQL DataWarehouse PAAS (Platform as a ...
SQLHow to Upgrade to SQL Server 2016 from Earlier and Older VersionsHow to Upgrade to SQL Server 2016 from Earlier and Older Versions SQL Server 2016 ...
Enterprise TechAuditing Features in SQL Server 2016 and Azure SQL DatabaseAuditing Features in SQL Server 2016 and Azure SQL Database SQL Server auditing features allow ...
Enterprise TechHow to Replicate your Data to an Azure VM with SQL StandardHow to Replicate your Data to an Azure VM with SQL Standard SQL Server Standard ...