Solution for database scalability
AZ-304 exam is retired. AZ-305 replacement is available.
In this, we will understand the database scalability in Azure SQL Database using the Elastic Database tools. However, these tools and features will let you use the database resources of Azure SQL Database for creating solutions for transactional workloads and Software as a Service (SaaS) applications.
Elastic Database features
- Firstly, the Elastic Database client library allows us to create and maintain sharded databases.
- Secondly, the Elastic Database split-merge tool moves data between sharded databases.
- Thirdly, Elastic Database jobs for managing large numbers of databases in Azure SQL Database.
- Then, an Elastic Database query enables us to run a Transact-SQL query that spans multiple databases.
- Lastly, Elastic transactions that allow you to run transactions that span several databases.
Architecture Graph with Elastic Database features in relation to a collection of databases
In this graph:
- Firstly, a set of SQL databases is hosted on Azure using sharding architecture.
- Secondly, the Elastic Database client library is used for managing a shard set.
- Thirdly, a subset of the databases is put into an elastic pool.
- After that, an Elastic Database job runs scheduled or ad hoc T-SQL scripts against all databases.
- Then, the split-merge tool is used for moving data from one shard to another. And, the Elastic Database query allows you to write a query that spans all databases in the shard set.
- Lastly, Elastic transactions allow you to run transactions that span several databases.
Horizontal and vertical scaling
Horizontal scaling means adding or removing databases in order to adjust capacity or overall performance that means “scaling out”. Sharding, in which data is partitioned across a collection of identically structured databases, is a common way to implement horizontal scaling. On the other hand, Vertical scaling means increasing or decreasing the compute size of an individual database that is “scaling up.”
However, most cloud-scale database applications use a combination of these two strategies. For example, a Software as a Service application can use horizontal scaling for provisioning new end-customers. And, vertical scaling allows each end-customer’s database to grow or shrink resources as needed by the workload.
Moving data from multiple to single-tenancy databases
While developing a SaaS application, it is typical to provide prospective customers a trial version of the software. So, for this, it is cost-effective to use a multi-tenant database for the data. However, when a prospect becomes a customer, a single-tenant database is better since it provides better performance. And, if the customer had created data during the trial period, use the split-merge tool to move the data from the multi-tenant to the new single-tenant database.
Dynamically scale database resources with minimal downtime
Azure SQL Database and SQL Managed Instance enable to dynamically add more resources into the database with minimal downtime. However, it includes a switch that removes the connectivity to the database for a short duration. This mitigation can be using retry logic.
Azure SQL Database provides the DTU-based purchasing model and the vCore-based purchasing model. Whereas Azure SQL Managed Instance only offers the vCore-based purchasing model.
- Firstly, the DTU-based purchasing model provides a mixture of computing, memory, and I/O resources in three service tiers for supporting lightweight to heavyweight database workloads. This includes Basic, Standard, and Premium. However, the performance levels within each tier provide a different mix of these resources.
- Secondly, the vCore-based purchasing model gives access to choose the number of vCores, the amount of memory, and the amount and speed of storage. This purchasing model offers three service tiers such as General Purpose, Business Critical, and Hyperscale.
Azure SQL Database gives the ability to dynamically scale your databases:
- Firstly, with a single database, you can use either DTU or vCore models for defining the maximum amount of resources in accordance with the database.
- Secondly, elastic pools enable you to define the maximum resource limit per group of databases in the pool.
Azure SQL Managed Instance allows scaling:
- SQL Managed Instance uses vCores mode that enables us to define maximum CPU cores and maximum storage allocated to your instance. However, all databases within the managed instance will share the resources allocated to the instance.
Reference: Microsoft Documentation, Documentation 2