How To Scale multiple Azure SQL Databases with SQL elastic pools

What is SQL elastic pool?

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 Azure SQL Database server and share a set number of resources at a set price.  Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.

Details:

SaaS developers build applications on top of large scale data-tiers consisting of multiple databases. A common application pattern is to provision a single database for each customer. But different customers often have varying and unpredictable usage patterns, and it is difficult to predict the resource requirements of each individual database user. Traditionally, you had two options:

  • Over-provision resources based on peak usage and over pay, or
  • Under-provision to save cost, at the expense of performance and customer satisfaction during peaks

Elastic pools solve this problem by ensuring that databases get the performance resources they need when they need it. Now here comes the question what is Multi-tenant SaaS database tenancy patterns.

Multi-tenant SaaS database tenancy patterns

In the Software as a Service (SaaS) model, your company does not sell licenses to your software. Instead, each customer makes rent payments to your company, making each customer a tenant of your company.

In return for paying rent, each tenant receives access to your SaaS application components, and has its data stored in the SaaS system.

The term tenancy model refers to how tenants’ stored data is organized:

  • Single-tenancy:  Each database stores data from only one tenant.
  • Multi-tenancy:  Each database stores data from multiple separate tenants (with mechanisms to protect data privacy).
  • Hybrid tenancy models are also available.

How to choose the appropriate tenancy model

In general, the tenancy model does not impact the function of an application, but it likely impacts other aspects of the overall solution. The following criteria are used to assess each of the models:

  • Scalability:
    • Number of tenants.
    • Storage per-tenant.
    • Storage in aggregate.
    • Workload.
  • Tenant isolation:  Data isolation and performance (whether one tenant’s workload impacts others).
  • Per-tenant cost:  Database costs.
  • Development complexity:
    • Changes to schema.
    • Changes to queries (required by the pattern).
  • Operational complexity:
    • Monitoring and managing performance.
    • Schema management.
    • Restoring a tenant.
    • Disaster recovery.
  • Customizability:  Ease of supporting schema customizations that are either tenant-specific or tenant class-specific.

The tenancy discussion is focused on the data layer. But consider for a moment the application layer. The application layer is treated as a monolithic entity. If you divide the application into many small components, your choice of tenancy model might change. You could treat some components differently than others regarding both tenancy and the storage technology or platform used.

Elastic pools

When databases are deployed in the same resource group, they can be grouped into elastic pools. The pools provide a cost-effective way of sharing resources across many databases. This pool option is cheaper than requiring each database to be large enough to accommodate the usage peaks that it experiences. Even though pooled databases share access to resources they can still achieve a high degree of performance isolation.

Design of multi-tenant app with database-per-tenant, using elastic pool.
MSDN Document

Azure SQL Database provides the tools necessary to configure, monitor, and manage the sharing. Both pool-level and database-level performance metrics are available in the Azure portal, and through Azure Monitor logs. The metrics can give great insights into both aggregate and tenant-specific performance. Individual databases can be moved between pools to provide reserved resources to a specific tenant. These tools enable you to ensure good performance in a cost effective manner.

Elastic pools enable the developer to purchase resources for a pool shared by multiple databases to accommodate unpredictable periods of usage by individual databases. You can configure resources for the pool based either on the DTU-based purchasing model or the vCore-based purchasing model. The resource requirement for a pool is determined by the aggregate utilization of its databases. The amount of resources available to the pool is controlled by the developer budget. The developer simply adds databases to the pool, sets the minimum and maximum resources for the databases (either minimum and maximum DTUs or minimum or maximum vCores depending on your choice of resourcing model), and then sets the resources of the pool based on their budget. A developer can use pools to seamlessly grow their service from a lean startup to a mature business at ever-increasing scale.

Within the pool, individual databases are given the flexibility to auto-scale within set parameters. Under heavy load, a database can consume more resources to meet demand. Databases under light loads consume less, and databases under no load consume no resources. Provisioning resources for the entire pool rather than for single databases simplifies your management tasks. Plus, you have a predictable budget for the pool. Additional resources can be added to an existing pool with no database downtime, except that the databases may need to be moved to provide the additional compute resources for the new eDTU reservation. Similarly, if extra resources are no longer needed they can be removed from an existing pool at any point in time. And you can add or subtract databases to the pool. If a database is predictably under-utilizing resources, move it out.

The correct pool size:

The best size for a pool depends on the aggregate resources needed for all databases in the pool. This involves determining the following:

  • Maximum resources utilized by all databases in the pool (either maximum DTUs or maximum vCores depending on your choice of resourcing model).
  • Maximum storage bytes utilized by all databases in the pool.

For available service tiers for each resource model, see the DTU-based purchasing model or the vCore-based purchasing model.

In cases where you can’t use tooling, the following step-by-step can help you estimate whether a pool is more cost-effective than single databases:

  1. Estimate the eDTUs or vCores needed for the pool as follows:For DTU-based purchasing model: MAX(<Total number of DBs X average DTU utilization per DB>,

    <Number of concurrently peaking DBs X Peak DTU utilization per DB)For vCore-based purchasing model: MAX(<Total number of DBs X average vCore utilization per DB>,

    <Number of concurrently peaking DBs X Peak vCore utilization per DB)
  2. Estimate the storage space needed for the pool by adding the number of bytes needed for all the databases in the pool. Then determine the eDTU pool size that provides this amount of storage.
  3. For the DTU-based purchasing model, take the larger of the eDTU estimates from Step 1 and Step 2. For the vCore-based purchasing model, take the vCore estimate from Step 1.
  4. See the SQL Database pricing page and find the smallest pool size that is greater than the estimate from Step 3.
  5. Compare the pool price from Step 5 to the price of using the appropriate compute sizes for single databases.

Understand The Concept Using An Example:

Imagine you work for an innovative Yoga company that uses Azure Cognitive Services to visually analyze hundreds of physical attributes and metrics of your customers. Customers come to your location to do yoga, and you use the intelligent systems to track their health progress and make health recommendations every time they visit. You designed your system to store customer data in an Azure SQL database.

The business became successful. As the business grew, franchises were sold globally and there are now thousands of locations. However, your company continues to develop, maintain, and run the intelligent systems. What started as gym has become a software as a service (SaaS) company. You now need to maintain and scale separate Azure SQL databases for each location. However the data resource requirements for each location are unique, some serve larger areas than others, and they run promotions at different times of year. As result, the rates of growth vary and load is unpredictable.

Create a SQL elastic pool

Software as a Service (SaaS) providers like the yoga company often need to provision a SQL database for each customer, in our case, for each location. As a service provider, you’ll need to react to unpredictable workloads, especially when storing customer data. You may not have visibility as to how quickly each client will grow or when demand will spike.

When to use an elastic pool?

SQL elastic pools are ideal when you have several SQL databases that have a low average utilization but have infrequent but high utilization spikes. In this scenario, you can allocate enough capacity in the pool to manage the spikes for the group but the total resources can be less than the sum of all of the peak demand of all of the databases. Since the spikes are infrequent, a spike from one database will be unlikely to impact the capacity of the other databases in the pool.

Create an elastic pool

SQL elastic pools must be hosted in a SQL server. You’ll specify an existing server or create a new server when creating an elastic pool.

Like many Azure resources, elastic pools can be created from the Azure portal, or the Azure CLI using the az sql elastic-pools create command, or via PowerShell using the New-AzSqlElasticPool command.

Add databases to an elastic pool

Databases can be added using the Azure portal, the Azure CLI, or PowerShell.

When using the portal, you can add a new pool to an existing SQL server. Or you can create a new SQL elastic pool resource and specify the server.

When using the CLI, call az sql db create and specify the pool name using the --elastic-pool-name parameter. This command can move an existing database into the pool or create a new one if it doesn’t exist.

When using PowerShell, you can assign new databases to a pool using New-AzSqlDatabase and move existing databases using Set-AzSqlDatabase.

You can add existing Azure SQL databases from your Azure SQL server into the pool or create new databases. And you can mix service tiers within the same pool.

Get Your Hands Dirty

ADMIN_LOGIN=”ServerAdmin” #replace with your value
RESOURCE_GROUP=84b3a1c4-40f1-45cf-8438-057859a0e2b3 #replace with your value
SERVERNAME=FitnessSQLServer-$RANDOM #replace with your value
LOCATION=
PASSWORD=

Create a server named FitnessSQLServer

az sql server create \
–name $SERVERNAME \
–resource-group $RESOURCE_GROUP \
–location $LOCATION \
–admin-user $ADMIN_LOGIN \
–admin-password $PASSWORD

Add a database named FitnessVancouverDB to FitnessSQLServer-#

az sql db create \
–resource-group $RESOURCE_GROUP \
–server $SERVERNAME \
–name FitnessVancouverDB

Add a database named FitnessParisDB to FitnessSQLServer-#.

az sql db create \
–resource-group $RESOURCE_GROUP \
–server $SERVERNAME \
–name FitnessParisDB

Create a SQL elastic pool

We’re ready to set up the resources for the SQL elastic pool. We’ll switch to the portal to create the elastic pool.

  1. Sign into the Azure portal  using the same account you used to activate the sandbox.
  2. Click on + Create a resource and search for SQL Elastic database pool.
  3. Select the SQL Elastic database pool result and then press the Create button on the next screen.
  4. Give your new elastic pool a meaningful name such as FitnessSQLPool, and ensure the 84b3a1c4-40f1-45cf-8438-057859a0e2b3 resource group is selected.
  5. In the Server section, ensure your existing FitnessSQLServer-# server is selected.
  6. Press Create. The SQL elastic pool may take several minutes to provision.

Add existing databases to the elastic pool

  1. Open your newly created SQL elastic pool in the Azure portal and navigate to the Overview section. Note there are currently no databases in the pool.
  2. Select Configure in the Settings section.
  3. Select the Databases tab.
  4. Click + Add databases.
  5. Select the databases for both locations.
  6. Click Apply.
  7. Click Save.

Congratulations, you’ve successfully added databases to a SQL elastic pool.

Manage SQL elastic pools performance and cost

SQL elastic pools can help reduce server costs. To make effective use of elastic pools, their capacity must be configured correctly. To configure an elastic pool, we need to understand how SQL server performance is measured and priced, using either a DTU-based or vCore-based purchasing model.

DTU-based pricing model

A database transaction unit (DTU) is a unit of measurement for the performance of a service tier in Azure and is based on a bundled measure of compute, storage, and IO resources. Compute sizes are expressed in terms of Database Transaction Units (DTUs) for single databases or elastic Database Transaction Units (eDTUs) for elastic pools.

If demand exceeds the available resources for your tier for any resource (CPU, storage or IO), the performance of your database is throttled. This model is best for customers who want simple, pre-configured resource options available in three tiers: basicstandard, and premium.

vCore-based pricing model

A virtual core (vCore) represents the logical CPU offered with an option to choose between generations of hardware and physical characteristics of hardware (for example, number of cores, memory, storage size).

The vCore-based purchasing model gives you flexibility and control over individual resource consumption and a straightforward way to translate on-premises workload requirements to the cloud. This model allows you to choose compute, memory, and storage based upon their workload needs within the general purpose or business critical service tier.

The chart below provides a comparison of the two pricing models:

DTU vs. vCore Pricing Model
MSDN Documents

Review cost estimates

The Azure portal displays the results of your pool settings as an estimated monthly bill. The charts below display the DTU-based pricing and vCore-based pricing models.

DTU-based Pricing Model

DTU Pricing Model
MSDN Documents

vCore-based Pricing Model

vCore Pricing Model
MSDN Documents

Source: Microsoft Azure documents

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s