Azure SQL Database Hyperscale FAQ

A Hyperscale database is a database in SQL Database that is backed by the Hyperscale scale-out storage technology. A Hyperscale database supports up to 100 TB of data and provides high throughput and performance, as well as rapid scaling to adapt to the workload requirements. Connectivity, query processing, database engine features, and so on, work like any other database in Azure SQL Database.

The Hyperscale service tier is only available for single databases using the vCore-based purchasing model in Azure SQL Database. It is not available in the DTU-based purchasing model.

The vCore-based service tiers are differentiated based on database availability and storage type, performance, and maximum storage size as described in resource limit comparison.

The Hyperscale service tier is for all customers looking for higher performance and availability, fast backup and restores, fast storage, and compute scalability. This includes customers who are starting out small and growing, those running large mission-critical databases, those who are moving to the cloud to modernize their applications and customers who are already using other service tiers in Azure SQL Database. With Hyperscale, you get:

• Database size that can grow from 10 GB up to 100 TB.
• Compute vCore resources from 2 vCores up to 128 vCores
• Fast database backups regardless of database size (backups are based on storage snapshots).
• Fast database restores regardless of database size (restores are from storage snapshots).
• Higher log throughput regardless of database size and the number of vCores.
• Read Scale-out using one or more read-only replicas, used for offloading read-only workloads or as hot standby databases.
• Rapid scaling up of compute, in constant time, to be more powerful to accommodate the heavy workload and then scale down, in constant time. Scaling operations take single-digit minutes for provisioned compute, and less than a second for serverless compute, regardless of database size.
• The option to pay for what you use with serverless compute, where compute is billed based on usage.

The Hyperscale service tier is available in all regions where Azure SQL Database is available.

Yes. For more information and limits on the number of databases per server, see SQL Database resource limits for single and pooled databases on a server.

The Hyperscale architecture provides high performance and throughput while supporting large database sizes.

Hyperscale provides rapid scalability based on your workload demand.

• Scaling Up/Down

  With Hyperscale, you can scale up the primary compute size in terms of resources like CPU and memory, and then scale down, in constant time. Because the storage is remote, scaling up and scaling down isn't a size of data operation.

  Support for serverless compute provides automatic scale-up and scale-down, and compute is billed based on usage.

• Scaling In/Out

  With Hyperscale, you can use three kinds of secondary replicas to cater to read scale-out, high availability, and geo-replication requirements. This includes:

  • Up to four high-availability replicas having the same compute size as primary. These serve as hot standby replicas to quickly fail over from the primary. You can also use them to offload read workloads from the primary.
  • Up to 30 named replicas having the same or different compute size than the primary, to cater to various read scale-out scenarios.
  • A geo-replica in a different Azure region to protect against regional outages and to enable geographic read scale-out.

Yes, you can.

No, your application programming model stays the same as for any other MSSQL database. You use your connection string as usual and the other regular ways to interact with your Hyperscale database. Once your application is using the Hyperscale database, your application can take advantage of features such as secondary replicas.

On the primary replica, the default transaction isolation level is RCSI (Read Committed Snapshot Isolation). On the Read Scale-out secondary replicas, the default isolation level is Snapshot. This is the same as in any other Azure SQL database.

With the new, simplified pricing in effect since December 15, 2023, the price of compute has been reduced for newly created Hyperscale databases, all serverless Hyperscale databases, and all Hyperscale elastic pools. With the new, simplified pricing, there is no need to apply Azure Hybrid Benefit (AHB) to obtain equivalent savings. Azure Hybrid Benefit (AHB) can only be applied to older (created before December 15, 2023) Hyperscale single databases with provisioned compute. For those older databases, AHB is only applicable until December 2026, after which those databases will also be billed as per the new, simplified pricing. For more information, see Hyperscale pricing blog and Azure SQL Database Hyperscale – lower, simplified pricing!.

Hyperscale works well for all workload types, including OLTP, Hybrid (HTAP), and Analytical (data mart) workloads.

If you're currently running interactive analytics queries using SQL Server as a data warehouse, Hyperscale is a great option because you can host small and mid-size data warehouses (such as a few TB up to 100 TB) at a lower cost, and you can migrate your SQL Server data warehouse workloads to Hyperscale with minimal T-SQL code changes.

If you're running data analytics on a large scale with complex queries and sustained ingestion rates higher than 100 MB/s or using Parallel Data Warehouse (PDW), Teradata, or other Massively Parallel Processing (MPP) data warehouses, Azure Synapse Analytics or Microsoft Fabric could be the best choice.

Not at this time. However you can scale your compute and the number of replicas down to reduce cost during nonpeak times, or use serverless to automatically scale compute based on usage.

For read workloads, you can create a named replica with a higher compute size (more cores and memory) than the primary. For more information on available compute sizes, see Hyperscale storage and compute sizes.

For read workloads, this can be achieved using named replicas.

You can scale the number of HA secondary replicas between 0 and 4 using Azure portal or REST API. Additionally, you can create up to 30 named replicas for many read scale-out scenarios.

In Hyperscale databases, data resiliency is provided at the storage level. You only need one replica (the primary) to provide resiliency. When the compute replica is down, a new replica is created automatically with no data loss.

However, if there's only the primary replica, it can take a minute or two to create a new replica after failover, vs. seconds in the case when an HA secondary replica is available. The new replica will have cold caches initially, which can result in higher storage latency and reduced query performance immediately after failover.

For mission-critical apps that require high availability with minimal failover impact, you should provision at least one HA secondary replica to ensure a hot standby replica is available to serve as a failover target.

100 TB.

In Hyperscale, the transaction log is practically infinite, with a restriction that the active portion of the log cannot exceed 1 TB. The active portion of the log can grow because of long-running transactions, or because of Change Data Capture processing not keeping up with the rate of data change. Avoid unnecessarily long and large transactions to stay below this limit. Other than this restriction, you don't need to worry about running out of log space on a system that has high log throughput. However, log generation rate might be throttled for continuous aggressively writing workloads. The peak sustained log generation rate is 100 MB/s.

Your tempdb database is located on local SSD storage and is sized proportionally to the compute size (the number of cores) that you provision. The size of tempdb is not configurable and is managed for you. To determine maximum tempdb size for your database, see Hyperscale storage and compute sizes.

Your database size automatically grows as you insert/ingest more data.

10 GB. A Hyperscale database is created with a starting size of 10 GB and grows as needed in 10-GB chunks.

Each data file grows by 10 GB. Multiple data files can grow at the same time.

In Hyperscale, data files are stored in Azure standard storage. Data is fully cached on local SSD storage, on page servers that are remote to compute replicas. In addition, compute replicas have data caches on local SSD and in memory, to reduce the frequency of fetching data from remote page servers.

No. Data files are added automatically to the PRIMARY filegroup. The common reasons for creating additional filegroups do not apply in the Hyperscale storage architecture, or in Azure SQL Database more broadly.

No.

Yes, database and file shrink operations are currently in preview. For more information on the preview, see Shrink for Azure SQL Database Hyperscale.

Yes, just like in any other Azure SQL DB database. This includes row, page, and columnstore compression.

Yes. The data pages associated with a given table can end up in multiple data files, which are all part of the same filegroup. The MSSQL database engine uses proportional fill strategy to distribute data over data files.

Yes. You can move your existing databases in Azure SQL Database to Hyperscale. For proofs of concept (POCs), we recommend you make a copy of your database and migrate the copy to Hyperscale.

The time required to move an existing database to Hyperscale consists of the time to copy data, and the time to replay the changes made in the source database while copying data. The data copy time is proportional to data size. The time to replay changes is shorter if the move is done during a period of low write activity.

Get sample code to migrate existing Azure SQL Databases to Hyperscale in the Azure portal, Azure CLI, PowerShell, and Transact-SQL in Migrate an existing database to Hyperscale.

Reverse migration to the General Purpose service tier allows customers who have recently migrated an existing database in Azure SQL Database to the Hyperscale service tier to move back, should Hyperscale not meet their needs. While reverse migration is initiated by a service tier change, it's essentially a size-of-data operation between different architectures. Similarly to migration to Hyperscale, reverse migration is faster if done during a period of low write activity. Learn the limitations for reverse migration.

If you have previously migrated an existing Azure SQL Database to the Hyperscale service tier, you can reverse migrate it to the General Purpose service tier within 45 days of the original migration to Hyperscale. If you wish to migrate the database to another service tier, such as Business Critical, first reverse migrate to the General Purpose service tier, then modify the service tier. Reverse migration is a size of data operation.

Databases created in the Hyperscale service tier can't be moved to other service tiers.

Learn how to reverse migrate from Hyperscale, including the limitations for reverse migration and impacted backup policies.

Yes. Some Azure SQL Database features are not supported in Hyperscale yet. If some of these features are enabled for your database, migration to Hyperscale could be blocked, or these features will stop working after migration. We expect these limitations to be temporary. For details, see Known limitations.

Yes. You can use many existing migration technologies to migrate to Hyperscale, including transactional replication, and any other data movement technologies (Bulk Copy, Azure Data Factory, Azure Databricks, SSIS). See also the Azure Database Migration Service, which supports many migration scenarios.

Downtime for migration to Hyperscale is the same as the downtime when you migrate your databases to other Azure SQL Database service tiers. You can use transactional replication to minimize downtime migration for databases up to a few TB in size. For very large databases (10+ TB), you can consider implementing the migration process using ADF, Spark, or other bulk data movement technologies.

Hyperscale is capable of consuming 100 MB/s of new/changed data, but the time needed to move data into databases in Azure SQL Database is also affected by available network throughput, source read speed and the target database service level objective.

You can have a client application read data from Azure Storage and load data load into a Hyperscale database (just like you can with any other database in Azure SQL Database). Polybase is currently not supported in Azure SQL Database. As an alternative to provide fast load, you can use Azure Data Factory, or use a Spark job in Azure Databricks with the Spark connector for SQL. The Spark connector to SQL supports bulk insert.

It is also possible to bulk read data from Azure Blob store using BULK INSERT or OPENROWSET: Examples of Bulk Access to Data in Azure Blob Storage.

Simple recovery or bulk logging model is not supported in Hyperscale. Full recovery model is required to provide high availability and point-in-time recovery. However, Hyperscale log architecture provides better data ingest rate compared to other Azure SQL Database service tiers.

No. Hyperscale is a symmetric multi-processing (SMP) architecture and is not a massively parallel processing (MPP) or a multi-master architecture. You can only create multiple replicas to scale out read-only workloads.

Yes. Azure Database Migration Service supports many migration scenarios.

See SLA for Azure SQL Database. We recommend adding HA secondary replicas for critical workloads. This provides faster failover, and reduces potential performance impact immediately after failover.

Yes.

Yes, Hyperscale supports zone redundant configuration. At least one HA secondary replica and the use of zone-redundant or geo-zone-redundant storage is required for enabling the zone redundant configuration for Hyperscale.

Yes. For more information, see Hyperscale elastic pools and Blog: Hyperscale Elastic Pools are now generally available.

There are no traditional full, differential, and transaction log backups for Hyperscale databases. Instead, there are regular storage snapshots of data files, with a separate snapshot cadence for each file. The generated transaction log is retained as-is for the configured retention period. At restore time, relevant transaction log records are applied to restored storage snapshots. Regardless of snapshot cadence, this results in a transactionally consistent database without any data loss as of the specified point in time within the retention period. In effect, database backup in Hyperscale is continuous.

Yes.

The RPO for point-in-time restore is 0 min. Most point-in-time restore operations complete within 60 minutes regardless of database size. Restore time can be longer for larger databases, and if the database experienced significant write activity before and up to the restore point in time. Changing the storage redundancy when issuing a restore can result in longer restore times as the restore is size of data and hence the time will be proportional to the database size.

No. Backups are managed by the storage subsystem, and use storage snapshots. They do not impact user workloads.

Yes. Geo-restore is fully supported if geo-redundant storage is used. This is the default for new databases. Unlike point-in-time restore, geo-restore requires a size-of-data operation. Data files are copied in parallel, so the duration of this operation depends primarily on the size of the largest file in the database, rather than on total database size. Geo-restore time will be significantly shorter if the database is restored in the Azure region that is paired with the region of the source database.

Yes. Geo-replication can be set up for Hyperscale databases.

No. The storage format for Hyperscale databases is different from any released version of SQL Server, and you don't control backups or have access to them. To take your data out of a Hyperscale database, you can extract data using any data movement technologies, that is, Azure Data Factory, Azure Databricks, SSIS, etc.

Yes. Effective May 4, 2022, backups for all new databases are charged based on the backup storage consumed and selected storage redundancy at rates captured in Azure SQL Database pricing page. For Hyperscale databases created before May 4, 2022, backups will be charged only if backup retention is set to be greater than seven days. To learn more, see Hyperscale backups and storage redundancy.

Details on how to measure backup storage size are captured in Automated Backups.

To determine your backup storage bill, backup storage size is calculated periodically, and multiplied by the backup storage rate and the number of hours since the last calculation. To estimate your backup bill for a time period, multiply the billable backup storage size for every hour of the period by the backup storage rate, and add up all hourly amounts. To query relevant Azure Monitor metrics for multiple hourly intervals programmatically, use Azure Monitor REST API. Backup billing in the serverless compute tier is the same as in the provisioned compute tier.

Backup costs will be higher for workloads that add, modify, or delete large volumes of data in the database. Conversely, workloads that are mostly read-only might have smaller backup costs.

Details on how to minimize the backup storage costs are captured in Automated Backups.

Transaction log throughput cap is set to 100 MB/s for any Hyperscale compute size. The ability to achieve this rate depends on multiple factors, including but not limited to workload type, client configuration and performance, and having sufficient compute capacity on the primary compute replica to produce log records at this rate.

IOPS and IO latency will vary depending on the workload patterns. If the data being accessed is cached in RBPEX on the compute replica, you will see similar IO performance as in Business Critical or Premium service tiers.

No. Compute is decoupled from the storage layer. This eliminates the performance impact of backup.

Because the storage is shared and there is no direct physical replication happening between primary and secondary compute replicas, the throughput on the primary replica will not be directly affected by adding secondary replicas. However, continuous and aggressive write workloads might be throttled on the primary to allow log apply on secondary replicas and page servers to catch up. This avoids poor read performance on secondary replicas and long recovery after failover to an HA secondary replica.

Yes. However, just like in other Azure SQL DB databases, connections might be terminated by very infrequent transient errors, which can abort long-running queries and roll back transactions. One cause of transient errors is when the system quickly shifts the database to a different compute node to ensure continued compute and storage resource availability, or to perform planned maintenance. Most of these reconfiguration events finish in less than 10 seconds. Applications that connect to your database should be built to expect and tolerate these infrequent transient errors by implementing retry logic. Additionally, consider configuring a maintenance window that matches your workload schedule to avoid transient errors due to planned maintenance.

For most performance problems, particularly those not rooted in storage performance, common SQL diagnostic and troubleshooting steps apply. For Hyperscale-specific storage diagnostics, see SQL Hyperscale performance troubleshooting diagnostics.

The maximum amount of memory that a serverless database can scale up is 3 GB/vCore times the maximum number of vCores configured as compared to more than 5 GB/vCore times the same number of vCores in provisioned compute. Review serverless Hyperscale resource limits for details.

Scaling up or down in the provisioned compute tier typically takes up to 2 minutes, regardless of data size. In the serverless compute tier, where compute is automatically scaled based on workload demand, the scaling time is typically subsecond, but can occasionally take as long as when scaling provisioned compute.

No. A database remains online during scale up or scale down operations.

Scaling provisioned compute up or down results in connections being dropped when a failover happens at the end of the scaling operation. In serverless compute, automatic scaling typically does not result in dropping a connection, but it can occur occasionally. Adding or removing secondary replicas does not result in connection drops on the primary.

Scaling in provisioned compute is performed by the end user. Automatic scaling in serverless compute is performed by the service.

Yes. The tempdb database and RBPEX cache size on compute nodes scale up automatically as the number of cores is increased. For details, see Hyperscale storage and compute sizes.

No. Only the primary compute replica accepts read/write requests. Secondary compute replicas only accept read-only requests.

Hyperscale supports High Availability (HA) replicas, named replicas, and geo-replicas. See Hyperscale secondary replicas for details.

Between 0 and 4. If you want to adjust the number of replicas, you can do so using Azure portal or REST API.

You can connect to these additional read-only compute replicas by setting the ApplicationIntent property in your connection string to ReadOnly. Any connections marked with ReadOnly are automatically routed to one of the HA secondary replicas, if present. For details, see Use read-only replicas to offload read-only query workloads.

You can execute the following T-SQL query: SELECT DATABASEPROPERTYEX ('<database_name>', 'Updateability'). The result is READ_ONLY if you are connected to a read-only secondary replica, and READ_WRITE if you are connected to the primary replica. The database context must be set to the name of your database, not to the master database.

No. You can only connect to HA secondary replicas by specifying ApplicationIntent=ReadOnly. However, you can use dedicated endpoints for named replicas.

No. A new connection with read-only intent is redirected to an arbitrary HA secondary replica.

Not in the provisioned compute tier. HA secondary replicas are used as high availability failover targets, so they need to have the same configuration as the primary to provide expected performance after failover. In serverless, the compute is scaled automatically for each HA replica based on its individual workload demand. Each HA secondary can still autoscale to the configured max cores to accommodate its post-failover role. Named replicas provide the ability to scale each replica independently.

No. Your tempdb database is configured based on the provisioned compute size; your HA secondary replicas are the same size, including tempdb, as the primary compute. On named replicas, tempdb is sized according to the compute size of the replica, thus it can be smaller or larger than tempdb on the primary.

No. Hyperscale database compute replicas share storage, meaning that all compute replicas see the same tables, indexes, and other database objects. If you want additional indexes optimized for reads on secondary, you must add them on the primary. You can still create temporary tables (table names prefixed with # or ##) on each secondary replica to store temporary data. Temporary tables are read-write.

Data latency from the time a transaction is committed on the primary to the time it is readable on a secondary depends on the current log generation rate, transaction size, load on the replica, and other factors. Typical data latency for small transactions is in tens of milliseconds, however there's no upper bound on data latency. Data on a given secondary replica is always transactionally consistent, thus larger transactions take longer to propagate. However, at a given point in time data latency and database state might be different for different secondary replicas. Workloads that need to read committed data immediately should run on the primary replica.

No, named replicas cannot be used as failover targets for the primary replica. Add HA replicas for that purpose.

Since every named replica can have a different service level objective and thus be used for different use cases, there's no built-in way to direct read-only traffic sent to the primary to a set of named replicas. For example, you can have eight named replicas, and you might want to direct OLTP workload only to named replicas 1 to 4, while Power BI analytical workloads use named replicas 5 and 6, and the data science workload uses replicas 7 and 8. Depending on which tool or programming language you use, strategies to distribute such workload could vary. For an example of creating a workload routing solution to allow a REST backend to scale out, review the OLTP scale-out sample.

No, as named replicas use the same page servers of the primary replica, they must be in the same region.

A named replica cannot impact the availability of the primary replica. Named replicas, under normal circumstances, are unlikely to impact the primary's performance, but it can happen if there are intensive workloads running. Just like an HA replica, a named replica is kept in sync with the primary via the transaction log service. If a named replica, for any reason, is not able to consume the transaction logs fast enough, it will start asking the primary replica to slow down (throttle) its log generation, so that it can catch up. While this behavior won't impact the primary's availability, it can impact performance of write workloads on the primary. To avoid this situation, make sure that your named replicas have enough resource headroom – mainly CPU – to process transaction log without delay. For example, if the primary is processing numerous data changes, it is recommended to have named replicas with at least the same Service Level Objective as the primary to avoid saturating CPU on the replicas, and thus forcing the primary to slow down.

Named replicas will still be available for read-only access, as usual.

By default, named replicas don't have any HA replicas of their own. A failover of a named replica requires creating a new replica first, which typically takes about 1-2 minutes. However, named replicas can also benefit from higher availability and shorter failovers provided by HA replicas. To add HA replicas for a named replica, you can use the parameter ha-replicas with AZ CLI, or the parameter HighAvailabilityReplicaCount with PowerShell, or the highAvailabilityReplicaCount property with REST API. The number of HA replicas can be set during the creation of a named replica and can be changed – only via AZ CLI, PowerShell, or REST API – anytime after the named replica has been created. Pricing of HA replicas for named replicas is the same of HA replicas for regular Hyperscale databases.

Yes. The Column Master Key is stored in the user database and can be validated by executing the query SELECT * FROM sys.column_master_keys. Named replicas and high availability secondary replicas read data from the same page servers/storage layer as the primary Hyperscale database. Both types of replicas are synchronized with the primary Hyperscale database via the log service. A key change is considered a transaction and is automatically replicated to the named replica and high availability secondary replica.

The DATABASEPROPERTYEX() function, when executed within the context of a named replica, can map the replica_id to its corresponding named replica. However, it’s currently not feasible to link the replica_id to its respective named replica for each record in the sys.dm_database_replica_states dynamic management view when queried from the primary replica. The following example query can be run within a named replica’s context to identify the replica name, replica ID, and the synchronization details.

  SELECT replica_id, DB_NAME() AS 'Database/Replica name', 
  synchronization_state_desc, log_send_queue_size/1024.0/1024.0 AS log_send_queue_size_gb,
  last_sent_time, last_received_time, last_commit_time, secondary_lag_seconds
  FROM sys.dm_database_replica_states
  WHERE replica_id = DATABASEPROPERTYEX(DB_NAME(),'REPLICAID');

Related content

For more information about the Hyperscale service tier, see Hyperscale service tier.