Welcome to my blog, in this post I want to discuss Azure SQLDB.

There's a fair level of misunderstanding around this PaaS Azure feature, questions around this have recently appeared on the forums, so this post will seek to clarify what the feature is and what it can do.

What is SQLDB?

Azure SQLDB is a single instance of a SQL Server database deployed within a subscription in the Azure Cloud.
The platform has different tier levels and different redundancy levels to suit different performance and redundancy requirements. The Tier levels are based on either vCore or DTU based provisioning, the tier levels are;

Service Tier HA model Locally Redundant Zone Redundant
General Purpose (vCore) Remote Storage Yes Yes
Business Critical (vCore) Local Storage Yes Yes
Hyperscale (vCore) Hyperscale Yes Yes
Basic (DTU) Remote Storage Yes No
Standard (DTU) Remote Storage Yes No
Premium (DTU) Local Storage Yes Yes

 

vCore

This tier level represents virtual CPU core provisioning much like a Virtual Machine. Logical CPUs are defined and in turn dictate the amount of memory available and the maximum storage size. Sizing details are shown below;

Use caseGeneral PurposeBusiness CriticalHyperscale
Best for Most business workloads. Offers budget-oriented, balanced, and scalable compute and storage options. Offers business applications the highest resilience to failures by using several high availability secondary replicas, and provides the highest I/O performance. The widest variety of workloads, including those workloads with highly scalable storage and read-scale requirements. Offers higher resilience to failures by allowing configuration of more than one high availability secondary replica.
Compute size 2 to 128 vCores 2 to 128 vCores 2 to 128 vCores
Storage type Premium remote storage (per instance) Super-fast local SSD storage (per instance) Decoupled storage with local SSD cache (per compute replica)
Storage size 1 GB – 4 TB 1 GB – 4 TB 10 GB – 100 TB
IOPS 320 IOPS per vCore with 16,000 maximum IOPS 4,000 IOPS per vCore with 327,680 maximum IOPS 327,680 IOPS with max local SSD
Hyperscale is a multi-tiered architecture with caching at multiple levels. Effective IOPS depend on the workload.
Memory/vCore 5.1 GB 5.1 GB 5.1 GB or 10.2 GB
Backups A choice of geo-redundant, zone-redundant, or locally redundant backup storage, 1-35 day retention (default 7 days)
Long term retention available up to 10 years
A choice of geo-redundant, zone-redundant, or locally redundant backup storage, 1-35 day retention (default 7 days)
Long term retention available up to 10 years
A choice of locally redundant (LRS), zone-redundant (ZRS), or geo-redundant (GRS) storage
1-35 days (7 days by default) retention, with up to 10 years of long-term retention available
Availability One replica, no read-scale replicas,
zone-redundant high availability (HA)
Three replicas, one read-scale replica,
zone-redundant high availability (HA)
zone-redundant high availability (HA)
Pricing/billing vCore, reserved storage, and backup storage are charged.
IOPS aren't charged.
vCore, reserved storage, and backup storage are charged.
IOPS aren't charged.
vCore for each replica and used storage are charged.
IOPS aren't charged.
Discount models Reserved instances
Azure Hybrid Benefit (not available on dev/test subscriptions)
Enterprise and Pay-As-You-Go Dev/Test subscriptions
Reserved instances
Azure Hybrid Benefit (not available on dev/test subscriptions)
and Pay-As-You-Go Dev/Test subscriptions
Azure Hybrid Benefit (not available on dev/test subscriptions) 1
Enterprise and Pay-As-You-Go Dev/Test subscriptions

 

DTU

The Database Transaction Unit tier level represents a unit of work grouped by memory, CPU and I\O requirements. The DTU model has a maximum storage size available. The maximum size available is generally less than the vCore options. 

 BasicStandardPremium
Target workload Development and production Development and production Development and production
Uptime SLA 99.99% 99.99% 99.99%
Backup A choice of geo-redundant, zone-redundant, or locally redundant backup storage, 1-7 day retention (default 7 days)
Long term retention available up to 10 years
A choice of geo-redundant, zone-redundant, or locally redundant backup storage, 1-35 day retention (default 7 days)
Long term retention available up to 10 years
A choice of locally-redundant (LRS), zone-redundant (ZRS), or geo-redundant (GRS) storage
1-35 days (7 days by default) retention, with up to 10 years of long-term retention available
CPU Low Low, Medium, High Medium, High
IOPS (approximate)* 1-4 IOPS per DTU 1-4 IOPS per DTU >25 IOPS per DTU
IO latency (approximate) 5 ms (read), 10 ms (write) 5 ms (read), 10 ms (write) 2 ms (read/write)
Columnstore indexing N/A Standard S3 and higher Supported
In-memory OLTP N/A N/A Supported
Maximum storage size 2 GB 1 TB 4 TB
Maximum DTUs 5 3000 4000



Elastic Pools and Limits

You can also configure a feature called "Elastic Pools", these are pools configured with a set amount of compute and storage and the databases within the pool use the provisioned resource as and when they require.

To learn more, review Resource limits for pooled databases.

 BasicStandardPremium
Maximum storage size per database 2 GB 1 TB 1 TB
Maximum storage size per pool 156 GB 4 TB 4 TB
Maximum eDTUs per database 5 3000 4000
Maximum eDTUs per pool 1600 3000 4000
Maximum number of databases per pool 500 500 100

 

Logical Server

The logical server is merely a connection management point for the Azure SQL databases that it manages. There is no need to configure server principals or server roles\permissions at the virtual master database level. The server name is generated during the deployment process and is a unique combination of the following;

yourservername.database.windows.net


The network connection and policy will also be specified during the deployment process. We can see on the create database blade below the options that are chosen.

 

 

Of note from the above screenshot is the connection policy. The default uses either Proxy or Redirect, based upon where the traffic is initiated.

A connection from the public internet to the Public Endpoint would be proxied through one of the Azure SQLDB gateways for that region. This has the potential for high latency and is generally not recommended.

A connection from a private endpoint (which could be an extension of your on-premise network) would be redirected directly to the host servicing the database.
There are also firewall rules that are configured firstly at the logical server level and finally at the database level, the 2 stored procedures used for these are "sp_set_firewall_rule" and "sp_set_database_firewall_rule". Of course, you can also set these rules on the blade for the logical server and via TSQL in the database(s).

For connections that come in;

  • via the internet, the database rules for the incoming connection are first checked, if a match for the range is found the connection is granted.
  • If no database rule is matched, the server rules are enumerated, if a server level rule is found the connection is granted.
  • If there is no server rule match the connection fails.


Just remember that server level rules cover all databases so for security reasons it all depends which databases the connections require access to.

For Azure based connections an open rule is configured and this is the only time this is recommended, the start and end ip for this rule are 0.0.0.0 - 0.0.0.0 

Logical Server Permissions

Logical server permissions and roles have changed quite a bit for Azure SQLDB. There is no need to (and indeed you shouldn't) create logins at the virtual server level, administrators are defined during the logical server deployment and typically consist of a SQL server admin account and a Microsoft Entra user or group. The only requirement for server level and database level administrator rights would be to configure the firewall rules mentioned above, apart from that no users should hold any elevated roles or permissions.
The old server roles we've all been used to are gone, along with most of the server level securables. In fact, the system catalogs that tracked these are now gone in the virtual master database, any attempt to grant a legacy role or permission fails with the errors shown below.

 

There is now, a new set of fixed server roles and database roles. The server roles consist of the following;

Fixed server-level roleDescription
##MS_DatabaseConnector## Members of the ##MS_DatabaseConnector## fixed server role can connect to any database without requiring a User-account in the database to connect to.

To deny the CONNECT permission to a specific database, users can create a matching user account for this login in the database and then DENY the CONNECT permission to the database-user. This DENY permission overrules the GRANT CONNECT permission coming from this role.
##MS_DatabaseManager## Members of the ##MS_DatabaseManager## fixed server role can create and delete databases. A member of the ##MS_DatabaseManager## role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the ##MS_DatabaseManager## role don't necessarily have permission to access databases that they don't own. You should use this server role over the dbmanager database level role that exists in master.
##MS_DefinitionReader## Members of the ##MS_DefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY DEFINITION, respectively VIEW DEFINITION on any database on which the member of this role has a user account.
##MS_LoginManager## Members of the ##MS_LoginManager## fixed server role can create and delete logins. You should use this server role over the loginmanager database level role that exists in master.
##MS_SecurityDefinitionReader## Members of the ##MS_SecurityDefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY SECURITY DEFINITION, and respectively has VIEW SECURITY DEFINITION permission on any database on which the member of this role has a user account. This is a small subset of what the ##MS_DefinitionReader## server role has access to.
##MS_ServerStateManager## Members of the ##MS_ServerStateManager## fixed server role have the same permissions as the ##MS_ServerStateReader## role. Also, it holds the ALTER SERVER STATE permission, which allows access to several management operations, such as: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF();
##MS_ServerStateReader## Members of the ##MS_ServerStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER STATE, respectively VIEW DATABASE STATE on any database on which the member of this role has a user account.

 

The database roles are now listed as;

Role nameDescription
dbmanager Can create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the dbmanager role don't necessarily have permission to access databases that they don't own.
db_exporter Applies only to Azure Synapse Analytics dedicated SQL pools (formerly SQL DW).
Members of the db_exporter fixed database role can perform all data export activities. Permissions granted via this role are CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL DATA SOURCE, ALTER ANY EXTERNAL FILE FORMAT.
loginmanager Can create and delete logins in the virtual master database.

 

Again, you should only use these roles when a specific need arises and they should not be used freely just because they exist.

Authorisation is handled directly by the database the users connect to, much in the same way Contained databases worked in on-premise environments. No server level interaction is required and security best practices should be followed. Role Based Access Control should be employed to ensure only the required permissions are granted in order to carry out business tasks.
Microsoft Defender for Cloud brings rich configuration checking for Azure SQL DB and can alert when conditions are breached, for example a user is added to an elevated role or granted elevated permissions outside of a known role\permission set.
That's it for this basic guide, it should help clear up some misunderstandings around the platform and i would urge to read up in detail on the topics discussed on this blog post.