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.

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:

The preceding graph shows the DTU's utilization by time for...

Elastic Jobs

An elastic jobs runs as a cloud service in your Azure environment and allows you to execute a T-SQL script across one or more databases in:

• An elastic pool

• A shard set

• A customer-defined database collection

Elastic jobs can be scheduled as and when required. Job successes and failures are logged for troubleshooting and you can also enable retries after a failure.

Use Cases

Elastic jobs are commonly used for:

• Database Management and Maintenance

  Elastic jobs can be used for deploying schema changes across, running database maintenance jobs such as index rebuild, collecting database performance data, or updating reference data in a shard set.

• Reporting

  Elastic jobs can be used to aggregate data from a shard set and into a single reporting table. The reporting table can then be fed to Power BI, SSRS, or any of the reporting or visualization tools for creating reports. Normally, you would have to connect to each and every shard in a shard set to run the report query and insert the...

Summary

In this chapter, you learned the simple and cost-effective way of managing multiple Azure SQL Databases using an elastic pool.

You learned when and how to use an elastic pool effectively to be cost-effective without affecting database performance.

You also learned how to use elastic jobs to manage and maintain the databases in an elastic pool.