Deploy SQL database elastic pool
Elastic pools are a deployment option in which you purchase Azure compute resources (CPU, memory, and storage) that is then shared among multiple databases defined as belonging to the same pool. An easy comparison to an on-premises SQL Server is that an elastic pool is like a SQL Server instance that has multiple user databases. By using elastic pools, you can easily manage pool resources while at the same time potentially saving costs. Elastic pools also facilitate easy scalability up to the set limits such that if a single database within the pool needs resources due to an unpredictable workload, the resources are there. If the entire pool needs additional resources, a simple slider option within the Azure portal will facilitate scaling the elastic pool up or down.
Creating new elastic pools
Using the Azure portal, click Create a Resource and then search for “SQL Elastic database pool” and you will see the screen shown below.
Click Create shown in the image above in order to launch the screen shown in the image below.
Adding a database to an existing pool
Using the Azure portal, locate the pool to which you are adding a database, as showing in the image below.
The image below shows the process for selecting which database(s) you wish to add to the pool.
Click Apply on the screen shown in the image below.
Click Apply one more time and the database will be added to the elastic pool.
Managing pool resources
The Azure portal delivers a wealth of information regarding the state and health of the elastic pool. You can view resource utilization and see which database is consuming the most resources. This information can be helpful for diagnosing performance issues or identity a database that might not be a good fit for the pool, such as when one database is consuming the vast majority of pool resources. The image below shows an elastic pool with even resource utilization.
If you need to adjust the pool to decrease or increase resources allocated to the pool, you can make that change via the Configure option in the Pool settings section of the Elastic Pool management blade.
From that blade, you can quickly and easily adjust:
- Pool size including DTUs, vCores, and storage size.
- Service Tier
- Resources per database
- Which databases are included in the pool, by adding or removing them.
As shown in the image below, you can adjust numerous settings in the Elastic Pool. Many of these changes can be made online, including the min and max DTUs or vCores per database. You can change the size of total size of the pool or add and remove databases from the pool as needed. Active connections will be dropped as the resizing completes.
Probably the most useful feature is the ability to monitor Database Resource Utilization, as shown in the image below. This feature allows you to easily see how databases are performing within the pool.
An elastic pool is a good fit for multi-tenant databases where each tenant has its own copy of the database. Balance the workload across databases so as not to allow one database to monopolize all the pool’s resources.