Facts about SQL Elastic Pool in Azure – (Interview Q & A)

Facts about SQL Elastic Pool in Azure 

(Interview Q & A)

SQL Elastic Pool

This service comes under Database in Azure. Elastic pool works for SQL Databases, it is a simple, cost-effective solution for managing and scaling multiple databases which have varying and unpredictable usage demands.

Elastic Pool is a collection of databases with a shared set of resources managed via a SQL Database Server.

Elastic pool contains multiple databases which reside on a single server and share a fixed number of resources at a set price.

 Important Points

There is no per-database charge for elastic pools. You are billed for each hour a pool exists at the highest eDTU or vCores, regardless of usage or whether the pool was active for less than an hour.

Elastic pools enable the developer to purchase resources for a pool shared by multiple databases to accommodate unpredictable periods of usage by individual databases. Resources configuration can be done based either on the DTU-based purchasing model or the vCore-based purchasing model.

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. Additional resources can be added to an existing pool with minimum downtime. 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 remove databases from the pool. If a database is predictably under-utilizing resources, move it out.

When moving databases into or out of an elastic pool, there is no downtime except for a brief period of time (on the order of seconds) at the end of the operation when database connections are dropped.

Benefits

1.The more databases you can add to a pool the greater your savings become. Depending on your application utilization pattern, it’s possible to see savings with as few as two S3 databases.

2. A single database can be moved in and out of an elastic pool, which gives us flexibility.

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

When a database can join Pool

A database should be considered for a pool when its peak utilization is about 1.5 times greater than its average utilization.

DTU-based purchasing model example An S3 database that peaks to 100 DTUs and on average uses 67 DTUs or less is a good candidate for sharing eDTUs in a pool. Alternatively, an S1 database that peaks to 20 DTUs and on average uses 13 DTUs or less is a good candidate for a pool.

Business continuity options for databases in an elastic pool

Pooled databases generally support the same business continuity features that are available to single databases.

Point-in-time restore

Point-in-time restore uses automatic database backups to recover a database in a pool to a specific point in time.

Geo-restore

Geo-restore provides the default recovery option when a database is unavailable because of an incident in the region where the database is hosted

Active geo-replication

For applications that have more aggressive recovery requirements than geo-restore can offer

Creating a new SQL Database elastic pool using the Azure portal

Two ways to create:

1. All services >> Databases >> SQL Elastic Pools
2. Or you can create an elastic pool by navigating to an existing server and clicking + New poolto create a pool directly into that server.

 Important Point

You can create multiple pools on a server, but you can’t add databases from different servers into the same pool.

Pre-requisite to create Elastic Pool:

1. Subscription
2. Resource Group
3. Elastic Pool Name
4. Server
5. Compute + Storage

Interview Q & A based on Elastic Pool

1. What is an eDTU?

eDTU stands for the elastic Data Throughput Unit. It is a unit used to measure several counters like CPU, Memory, disk read, writes and other similar counters related to performance. With eDTUs, you can measure the performance offered by the different options

2. What is the difference between DTU and vCore?

With the DTU model you pay one fixed price for your compute (or IO/memory), as well as your data storage and back up retention. With the vCore model you have separate charges for your compute (what type of node or compute power you’re using) and a separate charge for your storage.

3. What components the DTU of Azure SQL consists of?

The DTU model is based on the Database Transaction Unit, and is a blended mix of CPU, I/O and memory (RAM) capabilities based on a benchmark OLTP workload called ASDB. The vCore model is based on the number of virtual CPU cores you require, and this can be scaled up as your workload increases

4. What is public IP and Private IP in Azure?

Public IP addresses allow Internet resources to communicate inbound to Azure resources. Public IP addresses enable Azure resources to communicate to Internet and public-facing Azure services. … A resource without a public IP assigned can communicate outbound

5. What will happen when SQL Azure database will reach the max size?

If the SQL Azure database will reach the max size, data read or fetch operations will still work on it but create, insert or update operations will stop with it. You can choose to drop, delete or truncate the data in this condition.

https://docs.google.com/forms/d/e/1FAIpQLSdQ8y7cpqlcR4PyCNOGFRYJ2bp0dNFb25GykncrHKbKDZLJwQ/viewform?embedded=true“>http://