Azure SQL. Scale out database to improve performance (read only)

Question

We have a database that is used quite heavily (almost 100% of requests are selects), and now database becomes a bottleneck.

We were looking for a solution that would let us scale this database out (horizonally) and load balance request across multiple instances

In perfect scenario what we are looking for is a way to dynamically add-remove instances (in a way similar to how you could scale instances of Azure Web App).

So far, the best solution I found is 'Read scale out using read-only replicas': https://docs.microsoft.com/en-us/azure/azure-sql/database/read-scale-out. And this looks like a perfect solution, the only problem is I don't see how I could add more replicas there?

Answer 1

An RDBMS generally can't scale out horizontally without sharding the data in some way. Also, the data in an RDBMS must be normalized, which isn't appropriate for every data set.

Another thing to note is that dynamic scalability is different from autoscale. Autoscale is when a service scales automatically based on criteria, whereas dynamic scalability allows for manual scaling with a minimal downtime.

One possible solution might be this PowerShell script to monitor and scale a single SQL Database

Also a migration to Cosmos DB might be a solution as well.