Chapter 7. Elastic Pools

This chapter teaches you about how to save costs, and manage and scale multiple SQL databases by using elastic pools. You'll also learn how to implement elastic jobs to manage and maintain databases in an elastic pool.

By the end of this chapter, you will be able to:

  • Explain the purpose of elastic pools
  • Identify when to use elastic pools
  • Select the size an elastic pool
  • Configure elastic jobs

Introducing Elastic Pools

An Azure SQL Database Elastic Pool is a cost-effective solution for managing and scaling a group or pool of multiple Azure SQL Databases, with the utilization pattern characterized by low average utilization and infrequent spikes.

Introducing Elastic Pools

Figure 7.1: Elastic Pools

All databases in an elastic pool:

  • Belong to one Azure SQL server
  • Share a set number of eDTUs
  • Share a set number of elastic pool storage
  • Are priced for eDTUs and not individual databases like DTUs
  • Can scale up to the given maximum amount of eDTUs
  • Have a guaranteed minimum number of eDTUs

When Should You Consider Elastic Pools?

In Chapter 6, Scaling Out Azure SQL Database, we worked on sharding the toystore database into four individual shards. Each shard had 50 pieces of a customer's/tenant's data.

Let's say that each individual database is sized to a Standard S3 service tier, for example, 100 DTUs, and has a DTU utilization as shown in the following graph:

When Should You Consider Elastic Pools?

The preceding graph shows the DTU's utilization by time for the toystore_shard1 database. It is evident from the graph that toystore_shard1 has an average DTU utilization of around 30 DTU and a spike of 80 DTU around 11:00 AM.

Let's say that the other three shards have similar graphs, however, they peak at different times, as shown in the following graph:

When Should You Consider Elastic Pools?

The preceding graph shows the four shards in a combined graph. The average utilization is under 40 DTUs and the peak utilization is 90 DTUs. The database peaks at different points in time.

At this point, one may argue that you should use the Standard S2 service tier, which offers 50 DTU and costs less than S3. This would suffice for most of the database's workload, which is below 50 DTUs, however, this would result in performance degradation for peak hours when the utilization is 90 DTU, which is much greater than 50 DTUs.

You have two options here:

  • Over-provision (Standard S3) to provide optimum performance for peak hours at a higher cost.
  • Under-provision (Standard S2) to save costs at the expense of lower performance and bad customer experience during peak hours.

Elastic pools provide you with a third option, which provides the optimum performance at a lower cost.

The four shards are grouped together in an elastic pool with an eDTU of 100, as shown in the following image:

When Should You Consider Elastic Pools?

Figure 7.2: Grouping of Shards in the Elastic Pool

This means that a database:

  • In peak hours can consume a maximum of 100 eDTU to meet the performance demand
  • In off-peak hours (under light loads) can consume less eDTUs
  • Under no load consumes 0 (zero) eDTUs

This not only solves the problem of over- and under-provisioning, but also saves costs, as you only have to pay for eDTUs and not the individual databases' DTUs.

A Standard S3 service tier that has a DTU of 100 is priced at $147/month. Four such databases will cost $588/month.

An elastic pool that has an eDTU of 100 is priced at $221/month, which means that you save $367 per month (62% cost reduction) if you have the database in an elastic pool.

Let's say that as the number of customers increases, you plan to further shard the databases into eight shards. This means that you would have eight databases in an elastic pool. This would result in an 85% monthly cost reduction.

This is where elastic pools are very beneficial.

Sizing an Elastic Pool

Elastic pools have great benefits, but only if they are sized properly. Otherwise, one may end up spending a lot more than expected on elastic pools.

Azure SQL Databases automatically analyze historical database utilization numbers and provide elastic pool configuration recommendations on the Azure portal. They also provide eDTU estimates for a custom group of databases on a server. You can add or remove databases from the custom group to get the eDTU recommendations. Once you are satisfied with the pool's configuration, you can create the pool.

If you have to manually estimate whether or not an elastic pool would be more cost-effective than having individual databases, follow these steps:

  1. Find the estimated eDTU using the following formula:
    • MAX(<Total number of DBs X Average DTU utilization per DB>, Number of concurrently peaking DBs X Peak DTU utilization per DB)
  2. Find the estimated pool storage by adding the individual database storage. Find the eDTU that provides the estimated storage using this link: https://azure.microsoft.com/en-in/pricing/details/sql-database/managed/.
  3. Using the link given in step 2, find the smallest eDTU which is greater than the largest eDTU from steps 1 and 2.
  4. Compare the costs of the pool and the individual databases to evaluate the pricing benefits.

Let's apply the preceding method to our toystore example:

  1. Estimated eDTU as per step 1:
    Total Number of DBs= 4
Average DTU utilization per DB = 30
Number of concurrently peaking DBs = 1
Peak utilization per DB = 90
Estimated eDTUs as per Step 1 above
= MAX (4 * 30,1*90) => MAX (120, 90) =120
The estimated eDTU as per Step 1 is 120.
  2. Estimate eDTU as per step 2:
    • Let's say that each shard has a maximum storage of 100 GB. This means that the maximum storage for all four shards would be 4 * 100 = 400 GB.
    • As per the pricing details link, the 100 eDTUs per pool suffices the preceding storage need:
      Sizing an Elastic Pool
    • Therefore, the estimated eDTU as per step 2 is 100.
  3. Estimated eDTU as per Step 3:
    • The smallest eDTU which is greater than the largest eDTU as per Step 1 (120 eDTU) and Step 2 (100 eDTU) is 200. However, let's consider that the 100 eDTUs as 120 eDTU is closer to 100 than 200.

Comparing the Pricing

In this chapter, we calculated and compared that the 100 eDTU pool saves 67% of costs compared to having four individual Azure SQL Databases.

Sizing Best Practices

The following best practices will help you correctly size an elastic pool:

Minimum Number of Databases

The minimum number of databases required for a pool to be cost-effective is driven by the following formula:

Sum of individual database DTUs > 1.5 X eDTUs

For example, for the toystore pool to be more cost-effective, the minimum number of databases in a pool can be calculated as:

Sum of Individual database DTUs > 150 (1.5 X 100)

This means that you need:

  • At least two Azure SQL Databases of Standard S3 tier for the pool to be cost-effective
  • At least four Azure SQL Databases of Standard S2 tier for the pool to be cost-effective
  • At least eight Azure SQL Databases of Standard S1 tier for the pool to be cost-effective

Maximum Number of Concurrently Peaking Databases

An elastic pool allows an Azure SQL Database to peak to the maximum eDTU available for that particular pool. However, not all databases can peak to the maximum eDTU at the same time.

The maximum number of concurrently peaking databases shouldn't be greater than 2/3 or 67% of the total number of databases in the pool.

For example, the toystore pool has four databases, which means that for the pool to be effective, you only need 67% of the four databases in the pool, which means you only need two databases. If more than two databases concurrently peak, then the pool has to be sized by more than 100 eDTU. If the pool is resized to more than 100 eDTU, then more S3 databases need to be added to keep the pool cost-effective.

Note

If all of the databases in a pool have some utilization at any given point in time, then than 67% of the databases can peak simultaneously.

DTU Utilization per Database

The ideal utilization pattern of a database to be considered for an elastic pool should be low average utilization and short, infrequent high utilization. This utilization pattern is best for sharing eDTUs. If a database has a high average utilization, then it would take most of the eDTUs. This means that the other databases wouldn't get the required eDTUs and would have a lower performance.

A database with a peak utilization that is 1.5 times greater than its average utilization is a good candidate for a pool.

Create an Elastic Pool and Add Toystore Store Shards to the Elastic Pool

In this section, we will understand how to create an elastic pool and add shards to it. Let us go back to our example of ToyStore Ltd. Mike analyses the report of eDTUs, and thinks of switching to Standard Service Tier 3. However, this would involve higher costs, so he plans to save on costs without worsening customer experience during peak hours. He creates an elastic pool and adds toystore store shards to it through the following steps:

  1. Open a browser and login to the Azure portal (https:\portal.azure.com) using your Microsoft Azure credentials.
  2. From the left-hand navigation menu, select All resources. Under All Resources, click the toyfactory Azure SQL server to open the toyfactory overview blade.
  3. In the toyfactory overview blade, select New pool from the top menu:
    Create elastic poolscreating an Elastic Pool and Add Toystore Store Shards to the Elastic Pool
  4. In the Elastic database pool blade, provide:
    1. The elastic pool name in the Name section.
    2. The pricing tier as Standard.
    3. Select Configure pool to add databases to the pool and configure the eDTUs.
    4. In the Configure pool blade, select Add databases:
      Create elastic poolscreating an Elastic Pool and Add Toystore Store Shards to the Elastic Pool
    5. The Configure pool blade allows you to add and remove databases from a pool. As you add and remove databases from the pool, the blade will show the estimated eDTU usage for the selected databases for the last 14 days. This helps in choosing the right database for the pool:
      Create elastic poolscreating an Elastic Pool and Add Toystore Store Shards to the Elastic Pool

      You will get an empty graph here as there isn't any workload running on the databases.

      You can also change the eDTU max and eDTU min per database. The default eDTU min is 0 and the default eDTU max is 100:

      Create elastic poolscreating an Elastic Pool and Add Toystore Store Shards to the Elastic Pool
    6. In the Add databases blade, choose toystore_shard_1_50, toystore_shard_50_100, toystore_shard_100_150, and toytore_150_200 and then click the Select button to continue:
    Create elastic poolscreating an Elastic Pool and Add Toystore Store Shards to the Elastic Pool

    Observe that the Peak DTU and Average DTU utilization for each database is listed in the database list. This helps choosing the correct database for the pool. It's zero here as we haven't run any workloads on the databases.

    Once you are done selecting the databases, click the Select button to continue.

  5. You will be taken back to the Elastic database pool blade. Check the Summary and Pricing. Click OK to create the pool:
    Create elastic poolscreating an Elastic Pool and Add Toystore Store Shards to the Elastic Pool
    • Once the pool is created, you will see it in the overview section of the toystore Azure SQL Server:

      Create elastic poolscreating an Elastic Pool and Add Toystore Store Shards to the Elastic Pool

    • If you wish to modify the pool in the future, you can do so by selecting the pool from the SQL elastic pools section shown in the preceding image.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
52.15.47.218