This article talks about Azure SQL Elastic DB Pools, Microsoft announced the preview availability of the new Basic and Premium Elastic Database Pools Tiers with existing Azure SQL Database service.  Elastic Database Pools enable us to run multiple, isolated and independent databases that can be auto-scaled automatically across a private pool of resources dedicated to just you and your apps.  This provides a great way for software-as-a-service (SaaS) developers to better isolate their individual customers in an economical way.


SQL DB elastic pools provide a simple cost effective solution to manage the performance goals for multiple databases that have widely varying and unpredictable usage patterns. A common SaaS application pattern is the single-tenant database model: each customer is given their own database. Each customer (database) has unpredictable resource requirements for memory, IO, and CPU. With these peaks and valleys of demand, how do you allocate resources efficiently and cost-effectively? Traditionally, you had two options: (1) over-provision resources based on peak usage and over pay, or (2) 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 and when they need it. They provide a simple resource allocation mechanism within a predictable budget. n SQL Database, the relative measure of a database's ability to handle resource demands is expressed in Database Transaction Units (DTUs) for single databases and elastic DTUs (eDTUs) for elastic databases in an elastic pool.

You can easily scale out Azure SQL databases using the Elastic Database tools. These tools and features let you use the virtually unlimited database resources of Azure SQL Database to create solutions for transactional workloads, and especially Software as a Service (SaaS) applications.

Elastic Database features 

Elastic Database features are composed of the following:

    -Elastic Database client library: The client library is a feature that allows you to create and maintain sharded databases. See Get started with Elastic Database tools.

    -Elastic Database split-merge tool: moves data between sharded databases. This is useful for moving data from a multi-tenant database to a single-tenant database (or vice-versa). See Elastic database Split-Merge tool tutorial.

    -Elastic Database jobs (preview): Use jobs to manage large numbers of Azure SQL databases. Easily perform administrative operations such as schema changes, credentials management, reference data updates, performance data collection or tenant (customer) telemetry collection using jobs.

    -Elastic Database query (preview): Enables you to run a Transact-SQL query that spans multiple databases. This enables connection to reporting tools such as Excel, PowerBI, Tableau, etc.

    -Elastic transactions: This feature allows you to run transactions that span several databases in Azure SQL Database. Elastic database transactions are available for .NET applications using ADO .NET and integrate with the familiar programming experience using the System.Transaction classes.

In this below figure, colors of the database represent schemas. Databases with the same color share the same schema.

  1. A set of Azure SQL databases are hosted on Azure using sharding architecture.
  2. The Elastic Database client library is used to manage a shard set.
  3. A subset of the databases are put into an Elastic Database pool. (See What is a pool?).
  4. An Elastic Database job runs scheduled or ad-hoc T-SQL scripts against all databases.
  5. The split-merge tool is used to move data from one shard to another.
  6. The Elastic Database query allows you to write a query that spans all databases in the shard set.
  7. Elastic transactions allows you to run transactions that span several databases.

The figure below shows the horizontal and vertical dimensions of scaling, which are the basic ways the elastic databases can be scaled.

Horizontal scaling refers to adding or removing databases in order to adjust capacity or overall performance. This is also called “scaling out”. Sharding, in which data is partitioned across a collection of identically structured databases, is a common way to implement horizontal scaling.

Vertical scaling refers to increasing or decreasing the performance level of an individual database—this is also known as “scaling up.”

What is Sharding?

Sharding is a technique to distribute large amounts of identically-structured data across a number of independent databases. It is especially popular with cloud developers creating Software as a Service (SAAS) offerings for end customers or businesses. These end customers are often referred to as “tenants”. Sharding may be required for any number of reasons:

    -The total amount of data is too large to fit within the constraints of a single database

    -The transaction throughput of the overall workload exceeds the capabilities of a single database

    -Tenants may require physical isolation from each other, so separate databases are needed for each tenant

    -Different sections of a database may need to reside in different geographies for compliance, performance or geopolitical reasons.

To manage scaled-out sharded databases, the Elastic Database jobs feature (preview) enables you to reliably execute a Transact-SQL (T-SQL) script or apply a DACPAC (data-tier application) across a group of databases

Elastic Database jobs

Elastic Database jobs
is currently a customer-hosted Azure Cloud Service that enables the execution of ad-hoc and scheduled administrative tasks, which are called jobs. With jobs, you can easily and reliably manage large groups of Azure SQL Databases by running Transact-SQL scripts to perform administrative operations.

To easily scale out databases on SQL Azure, use a shard map manager. The shard map manager is a special database that maintains global mapping information about all shards (databases) in a shard set. The metadata allows an application to connect to the correct database based upon the value of the sharding key. In addition, every shard in the set contains maps that track the local shard data (known as shardlets).