Updated on the 10th of February after being pointed out by Dmitri Furman and Greg Low that as a matter of a fact a whole group of AAD users can be set as a Azure SQL Server administrators. The current documentations wording are extremely misleading to me.
It is incredibly easy to fall in a trap of using the cheapest solution available. I have seen small and even very big organisations falling into this trap, by saying “We do not need a lot of processing power”, we expect it to scale down accordingly and if in production we are using Premium P2 tier for our workload, than a rather small Standard tiered based S1 database or for the sake of playing safe, a S2 tiered Standard Azure SQL Database will suffice.
Well, suffice for what ?
Consider the following arguments and not that those are definitely not the exhausting list of the reasons to avoid basic and Basic and Standard (S0 & S1) Azure SQL Databases:
- The slowness of the lower tiers, that is proportional on all DTU fronts – including the log writes, which is the most common throttle mechanism. Do not treat lowering tier as an expectation of getting just less CPU processing power, but consider that if you are doing writing operations, those might go seriously into the wild, potentially causing locking or even deadlocking in the places where you would swift by without troubles
- Permissions. Yes, permissions! Imagine that you want to run something trivial like Adam Machanic’s wondrous sp_whoIsActive procedure. Well … Besides that you need to choose the edition that is compatible (11.30, for example), you need to ensure … THE PERMISSIONS ! Sounds easy, right ?
Well, for the SQL Server on-premises or the SQL Managed Instance you will need a requires VIEW SERVER STATE permission, which obviously does not exists on Azure SQL Database, since it is a database. There you have the VIEW DATABASE STATE permission that you can assign.
Riiiiight ….
Here is a short reference for your pleasure, from the official Microsoft Documentation:
“On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the Server admin or an Azure Active Directory admin account is required. On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.”
In plain English that means that either you are Administrator of your Azure SQL Server (there can be 1 user or 1 group). The documentation should have been clear on this item.
If you are using a regular SQLLogin this means that even if you give a dbmanager role to your user, you are pretty much screwed. - You can scale up and down your tiers without any issues … unless you need In-Memory or Columnstore Indexes – as explained in Columnstore Indexes – part 121 (“Columnstore Indexes on Standard Tier of Azure SQL DB”), you will need at least S3 for running. Yes, that means that scaling up and down has its limits, as obvious!
Note: if you are running any vCPU core based Azure SQL Databases, you should be fine, since those do not have these restrictions. Only the old DTU-based tiers are in question.
This raises a rather obvious question – what should one use for the Development & Testing purposes ?
– Use Azure Elastic Pools for Azure SQL Databases grouping your development databases together, thus trying to scale them up.
– No, I do not want to remember RS Premium Tiers that gave us blazing speed until Microsoft removed them …
– Yes, I can imagine a Developer Licensed Azure Arc enabled SQL Managed Instance can provide some answers, but mostly in the future as it is still not in GA (General Availability)
– Your own SQL Server 2019.
– [unclaimed_space] – yeah, there is a development tier missing.
Don’t forget for development tiers to use Dev/Test subscriptions as these can save a lot of money
> RS Premium Tiers
> yeah, there is a development tier missing
The problem with (re-)introducing anything like that (from MS’s PoV) is people just end up using them for production workloads to be cheap. Not only does this cost MS some revenue, when something goes wrong and data is lost (because the redundancy & recovery guarantees do not hold for non-production tiers) it looks bad for MS/Azure because those people scream publicly and loudly that Azure lost their data.
(I’m not sure how they work around and enforce things differently with the dev/test subs)
> Your own SQL Server 2019.
Though you will almost certainly want to test anything significant in Azure before a release with AzureSQL as a/the target (preferably not just _immediately_ before release or you have no time to carefully rework anything that needs it), as performance metrics can be quite different between Azure and on-prem no matter how you configure the latter to try emulate the former.
Great Blog. Now current update is
On Azure SQ L Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Azure Active Directory admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.