Applies to: Azure SQL Database
This article provides answers to frequently asked questions for customers considering a database in the Azure SQL Database Hyperscale service tier, referred to as just Hyperscale in the remainder of this FAQ. This article describes the scenarios that Hyperscale supports and the features that are compatible with Hyperscale.
- This FAQ is intended for readers who have a brief understanding of the Hyperscale service tier and are looking to have their specific questions and concerns answered.
- This FAQ isn’t meant to be a guidebook or answer questions on how to use a Hyperscale database. For an introduction to Hyperscale, we recommend you refer to the Azure SQL Database Hyperscale documentation.
What is a Hyperscale database?
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, etc. work like any other database in Azure SQL Database.
What resource types and purchasing models support Hyperscale?
The Hyperscale service tier is only available for single databases using the vCore-based purchasing model in Azure SQL Database.
How does the Hyperscale service tier differ from the General Purpose and Business Critical service tiers?
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.
Who should use the Hyperscale service tier?
The Hyperscale service tier is for all customers who require higher performance and availability, fast backup and restore, and/or fast storage and compute scalability. This includes customers 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 up to 100 TB
- 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 read offloading and as hot standbys.
- Rapid scaling up of compute, in constant time, to be more powerful to accommodate the heavy workload and then scale down, in constant time. This is similar to scaling up and down between a 4-core and a 32-core database, for example, but is much faster as this is not a size of data operation.
What regions currently support Hyperscale?
The Hyperscale service tier is available in all regions where Azure SQL Database is available.
Can I create multiple Hyperscale databases per server?
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.
What are the performance characteristics of a Hyperscale database?
The Hyperscale architecture provides high performance and throughput while supporting large database sizes.
What is the scalability of a Hyperscale database?
Hyperscale provides rapid scalability based on your workload demand.
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 is not a size of data operation.
With Hyperscale, you can use three kinds of secondary replicas to cater for 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-standbys 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 same or different compute size than primary, to cater to a variety of read scale-out scenarios.
- A geo-replica in a different Azure region to protect against regional outages and to enable geographic read scale-out.
Deep dive questions
Can I mix Hyperscale and single databases in a single server?
Yes, you can.
Does Hyperscale require my application programming model to change?
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 using Hyperscale, your application can take advantage of features such as secondary replicas.
What transaction isolation level is the default in a Hyperscale database?
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 DB database.
Can I bring my on-premises or IaaS SQL Server license to Hyperscale?
Yes, Azure Hybrid Benefit is available for Hyperscale. Every SQL Server Standard core can map to 1 Hyperscale vCores. Every SQL Server Enterprise core can map to 4 Hyperscale vCores. You don’t need a SQL license for secondary replicas. The Azure Hybrid Benefit price will be automatically applied to Read Scale-out (secondary) replicas.
What kind of workloads is Hyperscale designed for?
Hyperscale works well for all workload types, including OLTP, Hybrid (HTAP), and Analytical (data mart) workloads.
How can I choose between Azure Synapse Analytics and Azure SQL Database Hyperscale?
If you are 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 are 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 may be the best choice.
Hyperscale compute questions
Can I pause my compute at any time?
Not at this time, however you can scale your compute and the number of replicas down to reduce cost during non-peak times.
Can I provision a compute replica with extra RAM for my memory-intensive workload?
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.
Can I provision multiple compute replicas of different sizes?
For read workloads, this can be achieved using named replicas.
How many Read Scale-out replicas are supported?
For high availability, do I need to provision additional compute replicas?
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 may take a minute or two to create a new replica after failover, vs. seconds in case when an HA secondary replica is available. The new replica will have cold caches initially, which may 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. That way there is a hot-standby replica available that serves as a failover target.
Data size and storage questions
What is the maximum database size supported with Hyperscale?
What is the size of the transaction log with Hyperscale?
The transaction log in Hyperscale is practically infinite, with the restriction that a single transaction cannot generate more than 1 TB of log. Additionally, if using Change Data Capture, at most 1 TB of log can be generated since the start of the oldest active transaction. It is recommended to avoid unnecessarily large transactions to stay below this limit. Other than the restrictions stated, you do not 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.
Does my `tempdb` scale as my database grows?
tempdb database is located on local SSD storage and is sized proportionally to the compute size (the number of cores) that you provision.
tempdb size is not configurable and is managed for you. To determine maximum
tempdb size for your database, see Hyperscale storage and compute sizes.
Does my database size automatically grow, or do I have to manage the size of data files?
Your database size automatically grows as you insert/ingest more data.
What is the smallest database size that Hyperscale supports?
10 GB. A Hyperscale database is created with a starting size of 10 GB and grows as needed in 10GB chunks.
In what increments does my database size grow?
Each data file grows by 10 GB. Multiple data files may grow at the same time.
Is the storage in Hyperscale local or remote?
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.
Can I manage or define files or filegroups with Hyperscale?
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.
Can I provision a hard cap on the data growth for my database?
Is database shrink supported?
Not at this time.
Is data compression supported?
Yes, just like in any other Azure SQL DB database. This includes row, page, and columnstore compression.
If I have a huge table, is table data spread out across multiple data files?
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.
Data migration questions
Can I move my existing databases in Azure SQL Database to the Hyperscale service tier?
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 will be 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 will be faster if done during a period of low write activity. Learn the limitations for reverse migration.
Can I move my Hyperscale databases to other service tiers?
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 cannot be moved to other service tiers.
Do I lose any functionality or capabilities after migration to the Hyperscale service tier?
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 may be blocked, or these features will stop working after migration. We expect these limitations to be temporary. For details, see Known limitations.
Can I move my on-premises SQL Server database, or my SQL Server database in a cloud virtual machine to Hyperscale?
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.
What is my downtime during migration from an on-premises or virtual machine environment to Hyperscale, and how can I minimize it?
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.
How much time would it take to bring in X amount of data to Hyperscale?
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.
Can I read data from blob storage and do fast load (like Polybase in Azure Synapse Analytics)?
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.
Does Hyperscale allow provisioning multiple nodes for parallel ingesting of large amounts of data?
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.
Does Hyperscale support migration from other data sources such as Amazon Aurora, MySQL, PostgreSQL, Oracle, DB2, and other database platforms?
Yes. Azure Database Migration Service supports many migration scenarios.
Business continuity and disaster recovery questions
What SLAs are provided for a Hyperscale database?
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.
Are the database backups managed for me by Azure SQL Database?
Does Hyperscale support Availability Zones?
Yes, Hyperscale supports zone redundant configuration. At least 1 HA secondary replica and the use of zone-redundant or geo-zone-redundant storage is required for enabling the zone redundant configuration for Hyperscale.
How often are database backups taken?
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. 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.
Does Hyperscale support point-in-time restore?
What is the Recovery Point Objective (RPO)/Recovery Time Objective (RTO) for database restore in Hyperscale?
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 may be longer for larger databases, and if the database had experienced significant write activity before and up to the restore point in time.
Does database backup affect compute performance on my primary or secondary replicas?
No. Backups are managed by the storage subsystem, and leverage storage snapshots. They do not impact user workloads.
Can I perform geo-restore with a Hyperscale database?
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.
Can I set up geo-replication with a Hyperscale database?
Yes. Geo-replication can be set up for Hyperscale databases.
Can I take a Hyperscale database backup and restore it to my on-premises server, or on SQL Server in a VM?
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, i.e. Azure Data Factory, Azure Databricks, SSIS, etc.
Will I be charged for backup storage costs in Hyperscale?
Yes. Effective May 4th 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 4th May 2022, backups will be charged only if backup retention is set to be greater than 7 days. To learn more, see Hyperscale backups and storage redundancy.
How can I measure backup storage size in my Hyperscale database?
Details on how to measure backup storage size are captured in Automated Backups.
How do I know what my backup bill will be?
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 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.
How will my workload influence my backup storage costs?
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 may have smaller backup costs.
How can I minimize backup storage costs?
Details on how to minimize the backup storage costs are captured in Automated Backups.
How much write throughput can I push in a Hyperscale database?
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 at this rate.
How many IOPS do I get on the largest compute?
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.
Does my throughput get affected by backups?
No. Compute is decoupled from the storage layer. This eliminates performance impact of backup.
Does my throughput get affected as I provision additional compute replicas?
Because the storage is shared and there is no direct physical replication happening between primary and secondary compute replicas, the throughput on primary replica will not be directly affected by adding secondary replicas. However, we may throttle continuous aggressively writing workloads 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.
Is Hyperscale well suited for resource-intensive, long-running queries and transactions?
Yes. However, just like in other Azure SQL DB databases, connections might be terminated by very infrequent transient errors, which may 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.
How do I diagnose and troubleshoot performance problems in a Hyperscale database?
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.
How long would it take to scale up and down a compute replica?
Scaling compute up or down typically takes up to 2 minutes regardless of data size.
Is my database offline while the scaling up/down operation is in progress?
No. The scaling up and down will be online.
Should I expect connection drop when the scaling operations are in progress?
Scaling existing compute up or down results in connections being dropped when a failover happens at the end of the scaling operation. Adding or removing secondary replicas does not result in connection drops on the primary.
Is the scaling up and down of compute replicas automatic or end-user triggered operation?
End-user. Not automatic.
Does the size of my `tempdb` database and RBPEX cache also grow as the compute is scaled up?
Can I provision multiple primary compute replicas, such as a multi-master system, where multiple primary compute heads can drive a higher level of concurrency?
No. Only the primary compute replica accepts read/write requests. Secondary compute replicas only accept read-only requests.
Read scale-out questions
What kinds of secondary (read scale-out) replicas are available in Hyperscale?
Hyperscale supports High Availability (HA) replicas, named replicas, and geo-replicas. See Hyperscale secondary replicas for details.
How many HA secondary replicas can I provision?
How do I connect to an HA secondary replica?
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 they were added for your database. For details, see Use read-only replicas to offload read-only query workloads.
How do I validate if I have successfully connected to secondary compute replica using SQL Server Management Studio (SSMS) or other client tools?
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. Note that the database context must be set to the name of your database, not to the
Can I create a dedicated endpoint for an HA secondary replica?
No. You can only connect to HA secondary replicas by specifying
ApplicationIntent=ReadOnly. However, you can use dedicated endpoints for named replicas.
Does the system do intelligent load balancing of the read workload on HA secondary replicas?
No. A new connection with read-only intent is redirected to an arbitrary HA secondary replica.
Can I scale up/down HA secondary replicas independently of the primary replica?
No. 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. Named replicas provide the ability to scale each replica independently.
Do I get different `tempdb` sizing for my primary compute and my HA secondary replicas?
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.
Can I add indexes and views on my secondary compute replicas?
No. Hyperscale databases have shared 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.
How much delay is there going to be between the primary and secondary compute replicas?
Data latency from the time a transaction is committed on the primary to the time it is readable on a secondary depends on 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 is 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 may be different for different secondary replicas. Workloads that need to read committed data immediately should run on the primary replica.
Can a named replica be used as a failover target?
No, named replicas cannot be used as failover targets for the primary replica. Add HA replicas for that purpose.
How can I distribute a read-only workload across my named replicas?
Since every named replica may have a different service level objective and thus be used for different use cases, there is no built-in way to direct read-only traffic sent to the primary to a set of named replicas. For example, you may have eight named replicas, and you may want to direct OLTP workload only to named replicas 1 to 4, while all the Power BI analytical workloads will use named replicas 5 and 6 and the data science workload will use replicas 7 and 8. Depending on which tool or programming language you use, strategies to distribute such workload may vary. One example of creating a workload routing solution to allow a REST backend to scale out is here: OLTP scale-out sample.
Can a named replica be in a region different from the region of the primary replica?
No, as named replicas use the same page servers of the primary replica, they must be in the same region.
Can a named replica impact availability or performance of the primary replica?
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 log 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 will not impact the primary's availability, it may 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.
What happens to named replicas if the primary replica is unavailable, for example because of planned maintenance?
Named replicas will still be available for read-only access, as usual.
How can I improve availability of named replicas?
By default, named replicas do not 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.
For more information about the Hyperscale service tier, see Hyperscale service tier.