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.
-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
-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.
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.”
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
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