Hyperscale elastic pools overview - Azure SQL Database (2023)

  • Article

Applies to: Hyperscale elastic pools overview - Azure SQL Database (1) Azure SQL Database

This article provides an overview of Hyperscale elastic pools in Azure SQL Database.

(Video) Azure SQL Database Hyperscale

Overview

Deploy your Hyperscale database to an elastic pool to share resources between databases within the pool and optimize the cost of having multiple databases with different usage patterns.

(Video) What is Azure SQL Database Hyperscale?

Scenarios to use an elastic pool with your Hyperscale databases:

  • When the sum of allocated database storage exceeds the 4 TB limit of the other service tiers.
  • When you need to scale the compute resources allocated to the elastic pool up or down in a predictable amount of time, independent of the amount of allocated storage.
  • When you want to scale out the compute resources allocated to the elastic pool by adding one or more read-scale replicas.
  • If you want to use high transaction log throughput for write-intensive workloads, even with lower compute resources.

Migrating non-Hyperscale databases to a Hyperscale elastic pool upgrades the databases to the Hyperscale service tier.

Architecture

Traditionally, the architecture of a standalone Hyperscale database consists of three main independent components: Compute, Storage ("Page Servers"), and the log ("Log Service"). When you create an elastic pool for your Hyperscale databases, the databases within the pool share compute and log resources. Additionally, if you choose to configure high availability, then each high availability pool is created with an equivalent and independent set of compute and log resources.

The following describes the architecture of an elastic pool for Hyperscale databases:

(Video) Azure SQL DB Single Databases and Elastic pools | Azure SQL single databases vs elastic pools

  • A Hyperscale elastic pool consists of a primary pool that hosts primary Hyperscale databases, and, if configured, up to four additional high availability pools.
  • Primary Hyperscale databases hosted in the primary elastic pool share the SQL Server database engine (sqlservr.exe) compute process, vCores, memory, and SSD cache.
  • Configuring high availability for the primary pool creates additional high availability pools that contain read-only database replicas for the databases in the primary pool. Each primary pool can have a maximum of four high availability replica pools. Each high availability pool shares compute, SSD cache, and memory resources for all the secondary read-only databases in the pool.
  • Hyperscale databases in the primary elastic pool all share the same log service. Since databases in the high availability pools don't have write workload, they don't utilize the log service.
  • Each Hyperscale database has its own set of page servers, and these page servers are shared between the primary database in the primary pool, and all secondary replica databases in the high availability pool.
  • Geo-replicated secondary Hyperscale databases can be placed inside another elastic pool.
  • Specifying ApplicationIntent=ReadOnly in your database connection string routes you to a read-only replica database in one of the high availability pools.

The following diagram shows the architecture of an elastic pool for Hyperscale databases:

Hyperscale elastic pools overview - Azure SQL Database (2)

Manage Hyperscale elastic pool databases

You can use the same commands to manage your pooled Hyperscale databases as pooled databases in the other service tiers. Just be sure to specify Hyperscale for the edition when creating your Hyperscale elastic pool.

The only difference is the ability to modify the number of high availability (H/A) replicas for an existing Hyperscale elastic pool. To do so:

(Video) Azure SQL Hyperscale Database

  • Use the HighAvailabilityReplicaCount parameter of the Azure PowerShell Set-AzSqlElasticPool command.
  • Use the --ha-replicas parameter of the Azure CLI az sql elastic-pool update command.

You can use the following client tools to manage your Hyperscale databases in an elastic pool:

  • Azure PowerShell: Az.Sql.3.11.0 or higher. PowerShell AzureRM.Sql isn't supported.
  • The Azure CLI: Az version 2.40.0 or higher.
  • Transact-SQL (T-SQL) starting with: SQL Server Management Studio (SSMS) v18.12.1 or Azure Data Studio v1.39.1.

Resource limits

The following lists the supported limits for working with Hyperscale databases within elastic pools:

  • Each Hyperscale elastic pool can have up to 25 databases.
  • Supported hardware generation: Standard-series (Gen5) only.
  • vCore maximum per pool: 80 vCores.
  • Maximum supported data size per database: 100 TB.
  • Maximum supported total data size across DBs in the pool: 100 TB.
  • Maximum supported transaction log throughput per database: 100 MB.
  • Maximum supported total transaction log throughput across databases in the pool: 130 MB/second.

For greater detail, see the Hyperscale elastic pool resource limits.

Note

Performance profiles, supported capabilities, and published limits are subject to change while the feature is in preview. As such, it's best to validate your use case with regular functional, performance, and scale testing of workloads.

(Video) Introducing Azure SQL Database Hyperscale Service Tier

Limitations

During preview, consider the following limitations:

  • Changing an existing non-Hyperscale elastic pool to the Hyperscale edition isn't supported. Individual databases need to be moved out of their respective existing pool before they can be added to the Hyperscale elastic pool.
  • Changing the edition of a Hyperscale elastic pool to a non-Hyperscale edition isn't supported.
  • In order to reverse migrate an eligible database which is in a Hyperscale elastic pool, it must first be removed from the Hyperscale pool. The standalone Hyperscale database can then be reverse migrated to a General Purpose standalone database.
  • Maintenance of databases in a pool is performed, and maintenance windows are configured, at the pool level. It isn't currently possible to configure a maintenance window for Hyperscale elastic pools.
  • Zone redundancy isn't currently available for Hyperscale elastic pools. Attempting to add a zone-redundant Hyperscale database to a Hyperscale elastic pool results in an error.
  • Named replicas aren't currently supported with Hyperscale elastic pools. Attempting to add a Hyperscale database with a named replica to a Hyperscale elastic pool results in an error.

Known issues

  • In rare cases, you may get the error 45122 - This Hyperscale database cannot be added into an elastic pool at this time. In case of any questions, please contact Microsoft support, when trying to move, restore or copy a Hyperscale database into an elastic pool. This limitation is due to implementation-specific details. If this error is blocking you, raise a support incident and request help.
  • Using PowerShell or the Azure CLI to create a geo-replica with Geo-Zone backup storage redundancy inside a Hyperscale pool will fail with the error (UnsupportedBackupStorageRedundancyForEdition) The requested backup storage redundancy of GeoZone is not supported for edition. To work around this issue, use the T-SQL ALTER DATABASE command to add the geo-secondary database.
  • Attempting to create a new Hyperscale elastic pool with the -ZoneRedundant parameter specified results in a vague One or more errors occurred. When you re-run the PowerShell or Azure CLI command with the respective -Verbose and -Debug parameters specified, you will see the actual error: Provisioning of zone redundant database/pool is not supported for your current request. At this time, creating Hyperscale elastic pools with zone redundancy specified is unsupported.
  • If you try to copy a Hyperscale database to a Hyperscale elastic pool on a server in another region, it will fail with an error The server encountered an unexpected exception. To work around this issue, please copy the Hyperscale database as a standalone database on the remote server, and then add the database (copy) into the elastic pool.
  • If you try to use the Azure portal to add an existing Hyperscale database which has Geo Zone specified as the backup storage redundancy level, into a Hyperscale elastic pool, you will receive an error Changing the backup storage account type value for Hyperscale is not supported. To work around this issue, please use the ALTER DATABASE command, or the equivalent Set-AzSqlDatabase, or az sql db update commands to add the database to the elastic pool.

Next steps

  • For examples on creating, scaling, and moving databases into a Hyperscale elastic pool, see Working with Hyperscale elastic pools using command-line tools.
  • For pricing information, see Elastic pool pricing.
  • To scale elastic pools, see Scale elastic pools and Scale an elastic pool - sample code.
  • To learn more about design patterns for SaaS applications by using elastic pools, see Design patterns for multitenant SaaS applications with SQL Database.
  • For a SaaS tutorial by using elastic pools, see Introduction to the Wingtip SaaS application.
  • To learn about resource management in elastic pools with many databases, see Resource management in dense elastic pools.

FAQs

What is elastic pool in Azure SQL Database? ›

Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price.

What is Azure SQL Database Hyperscale? ›

What is Azure SQL Database Hyperscale? Azure SQL Database Hyperscale is a SQL-based and highly scalable cloud service tier for single databases. With its revolutionary cloud-born architecture which decouples compute, log and storage, Azure SQL Database Hyperscale provides you with up to 100 TB of storage.

What is the elastic pool size of Azure SQL Database? ›

Valid values for the max data size for a Standard or Premium tier elastic pool can be one of these values: 50 GB, 100 GB, 150 GB, 200 GB, 250 GB, 300 GB, 400 GB, 500 GB, 750 GB, 800 GB, 1024 GB, 1200 GB, 1280 GB, 1536 GB, 1600 GB, 1792 GB, 2000 GB, 2048 GB, 2304 GB, 2500 GB, 2560 GB, 2816 GB, 3000 GB, 3072 GB, 3328 GB, ...

How big is Azure SQL Database Hyperscale? ›

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.

What are the different types of SQL pools in Azure? ›

Azure Synapse support three different types of pools – on-demand SQL pool, dedicated SQL pool and Spark pool.

What is the difference between elastic pool and single database in Azure? ›

I know Single Database will be used for "When Application needs a Single Database to work on". And Elastic Pool will be used for "When Application needs multiple databases to work on and need to share Common Resources like CPU and RAM".

What is the difference between Hyperscale and managed instance? ›

Hyperscale does not support automatic failover while Azure Managed instance does support it. You do not have point-in-time database restore with Hyperscale but you do have that with Azure Managed instances. Memory-optimized table variables have limited support on Hyperscale but have full support on Managed Instance.

Is Azure SQL Database elastic pool serverless? ›

Serverless is a compute tier for single databases in Azure SQL Database that automatically scales compute based on workload demand and bills for the amount of compute used per second.

What is the difference between SQL Hyperscale and business critical? ›

Hyperscale is a service tier within Azure SQL Database. It is different from the General Purpose and Business Critical tier in the fact that it can scale up to 100 TB and enters the HTAP (Hybrid Transactional Analytical Processing) market. It has a unique architecture.

What is the difference between elastic pool and managed instance in Azure? ›

From Microsoft: Elastic Pools support more databases per pool than available on a single Managed Instance. Additionally, there is built-in resource governance in elastic pools to ensure fair resource usage across databases.

What is the difference between standard and premium elastic pools in Azure SQL? ›

The primary distinction between Standard pools and Premium elastic pools is the number of eDTUs that any single database in the pool can burst to. In the case of Standard pools, the burst limit is 100 DTUs, and in the case of Premium pools the burst limit is 1000 DTUs.

How do I monitor my elastic pool in Azure? ›

Setup and Configuration
  1. Step 1: Add the Host Template. Add the Cloud - Azure - Elastic Pool Host Template to your Opsview Monitor host. ...
  2. Step 2: Add and configure variables required for this host. ...
  3. Step 3: Apply changes and the system will now be monitored.

What are the tiers of Azure SQL Database? ›

Azure SQL Database offers several service tiers to target different workloads: Basic, Standard (General Purpose), Premium (Business Critical), and HyperScale. They all grant you appropriate performance, security, and business continuity. The choice depends on your business requirements for storage and performance.

How do I backup my Azure SQL Database Hyperscale? ›

Go to the Hyperscale database for which you want to monitor backup and data storage metrics. In the Monitoring section, select the Metrics page. From the Metric dropdown list, select the Data backup storage, Data storage size, and Log backup storage metrics with an appropriate aggregation rule.

What are the capabilities of Hyperscale? ›

Hyperscale refers to a computing infrastructure's ability to rapidly and efficiently scale with demand, especially in data centers and cloud platforms, by adding resources like servers and storage. Key hyperscalers include AWS, Microsoft Azure, Google Cloud, Meta Platforms, Apple, and TikTok.

What is the difference between dedicated SQL pool and serverless SQL pool in Azure? ›

The basic differences between Synapse Serverless and the Dedicated pool are obvious: While the serverless doesn't store data, only access data from storage accounts and scale the MPP environment automatically, the dedicate SQL Pool keeps a static number of servers according to the service level we choose and a constant ...

What is the difference between SQL pool and SQL database Azure? ›

While a traditional SQL database is dependent on the computational resources of a single machine, a Synapse SQL Pool can distribute the processing of tables across up to 60 compute nodes depending on the service level. The more DWU's you've assigned, the more compute nodes will be used.

What are the 3 types of SQL database server architecture? ›

Three primary components make up SQL Server architecture: Protocol Layer, Relational Engine, and Storage Engine.

What is the maximum database size for elastic pool? ›

Standard elastic pool limits
eDTUs per pool50100
Max concurrent sessions per pool 33000030000
Min DTU per database choices0, 10, 20, 500, 10, 20, 50, 100
Max DTU per database choices10, 20, 5010, 20, 50, 100
Max storage per database (GB)10241024
6 more rows
Mar 3, 2023

What is the difference between relational database and Elasticsearch? ›

While RDBMS tend to have only one running instance, on a single machine (not distributed), Elasticsearch goes the opposite way and by default, is distributed and multi-instance.

What is the difference between Azure SQL Database and managed instance? ›

The most significant difference from SQL Database and SQL Managed Instance is that SQL Server on Azure Virtual Machines allows full control over the database engine.

How is hyperscale different from traditional data center? ›

But what makes these data centers different from traditional data centers? Unlike traditional data centers that can only support tens of servers, hyperscale data centers can support thousands of individual servers that operate via high-speed internet connectivity.

What is the difference between hyperscale and cloud? ›

While private cloud hosting is a good choice for organizations that are looking for sufficient availability, control, and security of applications, hyperscale works best for those who witness a steady growth and also for those who want to leverage the benefits of the cloud – without having to manage the complexities.

Why are cloud providers called Hyperscalers? ›

Hyperscalers get their name from hyperscale computing, a method of processing data that allows for software architecture to scale and grow as increased demand is added to the system.

What are the disadvantages of serverless SQL pool? ›

Serverless SQL pools can't provide an interactive experience in Power BI Direct Query mode if you're using complex queries or processing a large amount of data. Maximum concurrency is not limited and depends on the query complexity and amount of data scanned.

What is the difference between vCore and DTU? ›

In the vCore model, customers must make an explicit choice of both the hardware configuration and the number of vCores (logical CPUs). While DTU model does not offer these choices, hardware type and the number of logical CPUs used for every database and elastic pool are exposed via dynamic management views.

Which of the following scenarios would likely benefit from a SQL elastic pool? ›

It's likely each dealership's data would be stored in a seperate database. This scenario would likely benefit from elastic pools.

What defines a hyperscale data center? ›

Hyperscale data centers are massive business-critical facilities designed to efficiently support robust, scalable applications and are often associated with big data-producing companies such as Google, Amazon, Facebook, IBM, and Microsoft.

What is the difference between general purpose and business critical Azure SQL Database? ›

General Purpose is a budget-friendly tier designed for most workloads with common performance and availability features. Business Critical tier is designed for performance-sensitive workloads with higher availability features.

How do I migrate to Hyperscale in Azure? ›

Navigate to the database you wish to migrate in the Azure portal. In the left navigation bar, select Compute + storage. Select the Service tier drop-down to expand the options for service tiers. Select Hyperscale (On-demand scalable storage) from the dropdown menu.

What is the difference between spark pool and SQL pool in Azure Synapse Analytics? ›

It's called Azure Synapse Analytics. If you need a data warehouse, you can create a dedicated SQL pool, which lets you run SQL queries on structured, relational tables. If you want a data lake, then you can create a Spark pool, which lets you use Spark to query both structured and unstructured data.

What is the difference between single instance and instance pool in Azure? ›

The main difference between the two deployment models is that instance pools allow multiple SQL Server process deployments on the same virtual machine node, which are resource governed using Windows job objects, while single instances are always alone on a virtual machine node.

How many databases can you have in Azure SQL Database? ›

100 user databases, unless the instance storage size limit has been reached. Up to 280, unless the instance storage size or Azure Premium Disk storage allocation space limit has been reached. 32,767 files per database, unless the instance storage size limit has been reached. Maximum size of each data file is 8 TB.

What is the difference between Azure SQL single server and flexible server? ›

Flexible servers are best suited for all new developments and migration of production workloads to Azure Database for MySQL service. Single Server is a fully managed database service designed for minimal customization.

What are the different types of dedicated SQL pool table? ›

Table persistence
  • Regular table. A regular table stores data in Azure Storage as part of dedicated SQL pool. ...
  • Temporary table. A temporary table only exists for the duration of the session. ...
  • External table. ...
  • Hash-distributed tables. ...
  • Replicated tables. ...
  • Round-robin tables. ...
  • Common distribution methods for tables. ...
  • Table space summary.
Jul 26, 2022

What is per database setting for elastic pool? ›

The per database setting for an Elastic Pool is allocating the maximum DTU possible for each database inside the pool. The use of it is to make sure that one rogue database doesn't runaway will all the resources of the pool.

What is 3 tier architecture in Azure example? ›

The three-tier architecture is a well-established software application architecture that organizes applications at three levels of logical and physical computing: the display layer or the user interface; The layer of the application where the data is processed; and a data layer where application-related data is stored ...

Which is the best SQL database on Azure? ›

Taking first place, and thus being named "DBMS of the Year 2020," is PostgreSQL, a repeat winner. Microsoft took to the blogosphere to publicize its ranking, which set a record of its own, according to DB-Engines.

What is the difference between Gen 4 and Gen 5 Azure SQL Database? ›

Gen 5 uses faster local SSD disks (fast NVMe SSD) than Gen 4, so in Business Critical case there should be an advantage for Gen 5. In both cases tempdb is placed on local SSD both in General Purpose and Business Critical, so workload that are dependent on tempdb would run faster.

What is the maximum size of Azure SQL Database HyperScale? ›

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.

What is Azure SQL Database elastic pool? ›

Azure SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single server and share a set number of resources at a set price.

Are Azure SQL databases backed up automatically? ›

Azure SQL Database creates: Full backups every week. Differential backups every 12 or 24 hours. Transaction log backups approximately every 10 minutes.

What are the advantages of hyperscale cloud? ›

Hyperscale computing offers multiple business benefits when compared with traditional enterprise data centers, including the following:
  • Simplified management. Organizations can more easily manage their shifting computing needs.
  • Less downtime. ...
  • Increased operational efficiency. ...
  • Scalability.
Nov 9, 2022

What is Hyperconverged vs hyperscale? ›

Hyperconverged is optimized for those that value simplicity and hardware efficiency in smaller configurations, while hyperscale offers deployment flexibility, the ability to eliminate hardware lock-in, and greater efficiency as storage capacity scales.

What is the importance of hyperscalers? ›

A hyperscaler is a type of large-scale data center that offers massive computing resources, typically in the form of an elastic cloud platform. Organizations use them to deploy and manage large-scale applications and services.

What is Elasticsearch in Azure? ›

What is Elasticsearch on Azure? Elastic Stack on Azure is a deployment template that lets you deploy Elasticsearch clusters, including the full ELK Stack (Elasticsearch, Logstash and Kibana) in a fully automated manner.

What is the difference between Azure SQL Database and SQL Managed Instance? ›

SQL Managed Instance provides support for instance-scoped features enabling easy migration of existing applications, as well as sharing resources among databases. Whereas, SQL Server on Azure VMs provide DBAs with an experience most similar to the on-premises environment they're familiar with.

Is Elasticsearch an ETL tool? ›

There are separate ETL tools available for Elasticsearch. A few of these tools are Hevo Data, Logstash, Apache NiFi, Apache Spark, StreamSets, CloverDX.

What is the Azure equivalent of Elasticsearch? ›

Like Elasticsearch, Azure search utilizes indexers that extracts searchable text and metadata from an external Azure data source and populates a search index. Separate indexers are available for extracting this data from the Azure ecosystem (Azure SQL, Azure Cosmos, Azure Table Storage, etc.).

Why use Elasticsearch vs SQL? ›

Elasticsearch is a very good alternative to using SQL Server full-text search. It is not a relational database like SQL Server but is open-source so cost is lower. MySQL is another open-source option to look into depending on the requirements of your projects.

How do I create an elastic job in Azure SQL Database? ›

Create a credential for job execution in the Job database using PowerShell or T-SQL. Define the target group (the databases you want to run the job against) using PowerShell or T-SQL. Create a job agent credential in each database the job will run (add the user (or role) to each database in the group).

Videos

1. Intro to Azure SQL Hyperscale
(Data Lounge)
2. Azure SQL Database and Elastic Pools at Scale
(cloud simplified)
3. Know latest & coolest features in Azure SQL Database Hyperscale By Pooja Kamath & Balmukund Lakhani
(DataPlatformGeeks & SQLServerGeeks)
4. Azure SQL Hyperscale Deep Dive By Denzil Ribeiro
(DataPlatformGeeks & SQLServerGeeks)
5. Build fast, scalable data system on Azure SQL Database Hyperscale | Clearent
(Microsoft Mechanics)
6. Introducing Azure SQL Database Managed Instance
(Microsoft Mechanics)
Top Articles
Latest Posts
Article information

Author: Gov. Deandrea McKenzie

Last Updated: 05/17/2023

Views: 6492

Rating: 4.6 / 5 (46 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Gov. Deandrea McKenzie

Birthday: 2001-01-17

Address: Suite 769 2454 Marsha Coves, Debbieton, MS 95002

Phone: +813077629322

Job: Real-Estate Executive

Hobby: Archery, Metal detecting, Kitesurfing, Genealogy, Kitesurfing, Calligraphy, Roller skating

Introduction: My name is Gov. Deandrea McKenzie, I am a spotless, clean, glamorous, sparkling, adventurous, nice, brainy person who loves writing and wants to share my knowledge and understanding with you.