How can we scale the read operations on our SQL DB instance?

Question

How can we scale the read operations on our SQL DB instance?

Shea Martin 0

We have a web application with a SQL Server DB. The application sees about 40-75 daily active users, with only half of those performing more than a couple tasks. So a pretty light load at this point.

Our production Azure SQL DB is a "Basic" tier running on 200 DTU's, and has about 25GB of data allocated. I have experimented with setting the DTU back down to 50, and as a user, you can't tell the difference until a users runs a report that involves analyzing historical data. But as soon as users run reports we get the odd timeout.

We have optimized the reports pretty well. Is it possible for them to be better, I am sure, but they are not written by a 1st year junior dev, that is for sure.

We are in a bit of a cost saving measure, and our SQL instance is 50% of our Azure costs right now. ($500 CAD for our SQL instance).

I started looking into creating a read-only replica, but there it seems the data sync feature is going away: https://azure.microsoft.com/en-us/updates?id=sql-data-sync-retirement

The recommendation is to use HyperScale or go to a higher pricing tier which has auto-readonly-scale-out. But it is getting confusing, and I could use some advice.... (I guess our 'Developer' Support Plan now only allows us access to this Q&A instead of talking to someone.)

Hyperscale in "serverless" could potentially save us money, as we have such low usage, other than the odd spike. Is there any way to estimate our monthly cost of a serverless hyperscale instance?

Any insights on optimizing our bill and performance, would be helpful. There has to be a more efficient way to support so few users without comprimising performance.

Shea Martin 0 Reputation points

2025-06-29T13:56:07.4+00:00

We did a dev and test run of this. Today we are doing the production run. All of these comments helped, just not closing until we have had a couple weeks running on the new version.

That @Sai Raghunadh M for the explanations and patience.

Thanks @Alberto Morillo That is a good tip on the memory.

I will report back in a few weeks.

2 answers

Your answer

Shea Martin 0 Reputation points

2025-06-29T13:56:07.4+00:00

We did a dev and test run of this. Today we are doing the production run. All of these comments helped, just not closing until we have had a couple weeks running on the new version.

That @Sai Raghunadh M for the explanations and patience.

Thanks @Alberto Morillo That is a good tip on the memory.

I will report back in a few weeks.

Answer 1

Sai Raghunadh M 4,640 Microsoft External Staff Moderator

Hi @ Shea Martin

Given your scenario with 40–75 daily active users, a 25 GB Azure SQL Database on the Basic tier (200 DTUs), and report-related timeouts, here are some strategies to scale read operations and optimize your $500 CAD/month cost. Since Azure SQL Data Sync is retiring,

A couple of options might help you scale reads without increasing cost unnecessarily:

Hyperscale with Serverless Compute:

Hyperscale is designed for scalability and can handle large databases efficiently. The serverless compute tier automatically scales based on workload demand and pauses during inactivity, which could save costs for your low-usage scenario.

You can estimate the monthly cost using the Azure Pricing Calculator. It allows you to input your expected usage patterns and compute requirements to get a tailored cost estimate.

Read-Only Replicas:

While the Azure SQL Data Sync feature is retiring, Hyperscale includes auto-read-only-scale-out, which creates replicas for read-heavy workloads. This could improve performance for report generation without requiring a higher pricing tier.

Elastic Pools:

If you have multiple databases with varying usage, consider elastic pools. They allow you to share resources across databases, optimizing costs.

Optimize Query Performance:

Since reports are causing timeouts, further optimization of queries might help. Techniques like indexing, partitioning, or caching frequently accessed data could reduce the load on the database.

Azure Hybrid Benefit:

If you have existing SQL Server licenses, you can use Azure Hybrid Benefit to reduce costs.

Please go through these Documentations that might help you:

https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview?view=azuresql&tabs=general-purpose

https://learn.microsoft.com/en-us/azure/azure-sql/database/cost-management?view=azuresql

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Shea Martin 0 Reputation points

2025-04-24T02:21:11.11+00:00

EDIT: for some reason this response did not show up initially, so I posted again below this post. The post below is more coherent, so you can likely just ignore this post, and respond to the one below.

Thanks this is helpful. I think the HyperScale option is they way I am going to go. However, I have some questions.

For estimating cost of using the serverless option it is tricky.

I assume the CPU Used field is basically what I am estimating average load to be? i.e., I would put 2 if the server was going to be operating at full load for the month? and 1 if I am guessing the average load would be 50%?

For the duration field, the only way it wouldn't be all the seconds in the month would be if we manually paused it for some reason (especially because there is no auto-pause with HyperScale).

I also see that when I am choosing Serverless, it does ask how may HA replicas (HAR) to estimate. But when I am scaling my server in the portal, I have the option of have 0-4 of them. I assume that is because the HARs consume vcore-seconds at the same price as the primary? When does the HAR bill/run? Would it only start running/billing when the load is heavy, and it scales-out, and hands off non-write queries? Or when the application connected with "ApplicationIntent=ReadOnly"? Is there a lag when the HAR scales out?

For the provisioned mode, I can save quite a bit by reserving an instance. I have been using Azure SQL for 6 years, so I think it is safe to make a reservation. For a provisioned Hyperscale instance, when does the HAR run? Does it just run all the time that the primary runs? Can we set a schedule for the HAR to pause it? Does it auto pause when it is not needed?

Thanks in advance for your help.
Shea Martin 0 Reputation points

2025-04-24T15:33:17.7266667+00:00

Thanks for this information.

I have a few questions about the HyperScale tier.

When I am using the cost estimator: https://azure.microsoft.com/en-us/pricing/calculator/

Questions regarding "Serverless" for the HyperScale Tier:

I get the option to to enter "CPU Used", and "Memory Used", and "Duration". For Duration, I am using the number of seconds in a month because we have a web application with users across 5 time zones, that we would not be able to pause the DB. For "CPU Used" and "Memory Used", I assume these are my estimations for average load? i.e., if I think it will run at 50% load with 2 vCores, then I would enter 1.0 for the "CPU Used" value?

There is no option to select a High Availability Replica (HAR) for read scale-out. Is because the HAR instance just bills at the same rate as the primary? Does the HAR run/bill for the same amount of time as the primary? Or if the HAR only is only used for a few queries during a 1 hour period, that the HAR would only bill for an hour?

Questions regarding "Provisioned" for the HyperScale Tier:

Does the HAR bill/run all the time, or only as needed (the same question I had for Serverless, above)?

If I do reserverations, this option is probably more affordable. With a 2 vCore Hyperscale instance, plus a HAR, should I reserver 4 vCores, or 2? Or other?

Testing HyperScale tier with our application.

I created a copy of our dev DB, and would like to scale it to the HyperScale tier to test it out and make sure it is 100% compatibly. But on our dev DB's (currently Basic tier), the HyperScale option is disabled. Why is that? How can I enable it? It is available to turn on in our production DB, but I would like to test it with our test environment first.

Thanks in advance for your help.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2025-04-24T17:02:05.2+00:00
Hi @ Shea Martin

Estimating Serverless Costs:

In the Azure Pricing Calculator:

CPU Used = your estimated average usage. Example: If you're using a 2 vCore configuration and expect ~50% load, enter 1.0.

Memory Used = average GB of memory utilized (not maximum available).

Duration = total seconds in a month if the DB will be running continuously (since Hyperscale Serverless does not support auto-pause).

Read Scale-Out (High Availability Replicas / HAR):

HARs are billed like a normal compute replica — same cost per vCore.

They run continuously when enabled, not just during query execution.

You can connect with ApplicationIntent=ReadOnly to route queries to them.

They do not scale dynamically or pause when idle.

Cost Planning for Provisioned Mode + Reservations:

If you’re using 2 vCores for the primary and enable 1 HAR, reserve 4 vCores total (2+2).

Azure Reservations apply to both primary and HAR if you have enough reserved vCores in the region.

This setup may offer better cost predictability over serverless if your usage is consistent.

Testing Hyperscale in Dev

If the Hyperscale option is greyed out, it's likely because you're using the Basic tier, which doesn’t support it.

To enable it:

Temporarily scale your dev DB to General Purpose tier.

Once on General Purpose, you’ll be able to switch to Hyperscale.

After testing, you can scale back down or remove the test DB.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Shea Martin 0 Reputation points

2025-04-24T22:01:08.8+00:00

So I created a POC HyperScale DB with one HAR. I modified the connection string for running reports to have ApplicationIntent=ReadOnly. Now even a simple reports time out. The same report runs fine without ApplicationIntent=ReadOnly (so not using the HAR). What are we doing wrong? This is the resource: https://portal.azure.com/#@brewninja.net/resource/subscriptions/1c359bba-7522-4ef7-ae5f-6ca3cb813ea1/resourceGroups/bn-group-test/providers/Microsoft.Sql/servers/bn-sql-serv-test-2/databases/bn-sql-db-test-hyperscale-poc/overview

I found the solution to my problem. I had to grant execute permission to my SQL user. That didn't come over in the migration. So reading from the HAR works!
Shea Martin 0 Reputation points

2025-04-25T00:38:12.4466667+00:00

A further question about reservations on a provisioned HyperScale instance.

It seems that with provisioning, there is no cost savings to the scaling? I am just paying for 2 vCores for a full month even it never scales past 1. Whereas with serverless, if it idles along with a small load and only used 0.75vCores, I pay less.

In the case of adding a (single) replica, I would just be paying for 4 vCores for a full month even if the replica doesn't get used.

In other words, if I choose 'provisioned' there is no actual auto-scaling involved right? Am I understanding that correctly?
Shea Martin 0 Reputation points

2025-04-25T01:44:19.35+00:00
A further question about reservations on a provisioned HyperScale instance.

It seems that with a provisioned instance, there is no actual scaling. I am just paying for those vCores full time. Even the HARs don't really scale, I am just paying for them full time. It isn't any different than a non-HyperScale DB.

So if I am comparing scenarios:

#1. 2 vCore, provisioned with 1 HAR:

with a 3 year commitment, my cost would be about $350 CAD/months.

I get full access to 4 vCores (assuming I make good use of the HAR).

#2. 4 vCore, provisioned, no HAR:

with a 3 year commitment, my cost would be about $350 CAD/months.

I get full access to 4 vCores (assuming I make good use of the HAR).

I don't have to worry about ApplicationIntent

#3. 2 vCore (max), serverless, with 1 HAR:

If the primary and the HAR both average 0.68 vCore usage or less, my cost would be about 2x$275CAD or $550. (0.68 seems to be where billing stops going down).

#4. 6 vCore (max), serverless, with no HAR:

If I average 0.87 of a load, my cost is $350/month (same as provisioned), but during peak times, I have access to 6 vCores.

I don't have to worry about ApplicationIntent (not that this is difficult, just a few lines of code).

Questions:

Assuming my average monthly load is less than 0.87 vCores, it seems like Scenario #4 is the best option?

It seems like scenario #3 doesn't really make sense in any scenario. It is cheaper to just scale vCores than to add HAR. Why would anyone choose this option?

What would be the reason to choose #1 over #2? They are the price, and access to the same number of vCores, without having to categorize all my application access into read/writes. Is it because reading from the HAR is faster than reading from the primary (while there are writes happening)?

Initially I was going to do scenario #1. But (if my assumptions are correct) after typing this out, I think it makes sense to go with Option #4 for a couple weeks, so I have a better idea of what my actual average load is. Then use that information to determine if I should switch to either #1 or #2.

Looking forward to your thoughts.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2025-04-25T15:35:40.8033333+00:00

Hi @ Shea Martin

Thank you for the detailed follow-up and excellent job troubleshooting the HAR access issue! It's reassuring to know that enabling EXECUTE permissions resolved the timeout when using ApplicationIntent=ReadOnly. This is a common problem during migrations, so it's great that you identified it quickly.

Your cost and behavior assessments of the various scenarios are spot on, and your breakdown is very well thought out. Let me confirm and address each of your points:

Provisioned vs. Serverless:

You're correct — provisioned Hyperscale does not auto-scale. You're billed for the allocated vCores (including any HARs) 24/7, whether they're fully utilized or idle.

In contrast, serverless Hyperscale adjusts cost based on actual CPU and memory usage, making it a more flexible and cost-effective choice for workloads with variable or light usage patterns — exactly like your scenario.

Scenario #1 vs. #2: You're spot on — these provide the same total vCore capacity and cost, assuming equal usage of the HAR. The only benefit of #1 is performance isolation for reporting workloads. If your app doesn’t suffer from heavy read/write contention, Scenario #2 simplifies things by avoiding the need for ApplicationIntent=ReadOnly.

Scenario #3: Agreed — it rarely makes financial sense unless you need a dedicated read replica for frequent analytics/reporting and are okay with the additional cost. Most users would instead scale the primary and avoid the HAR entirely in serverless mode.

Scenario #4: This is an excellent plan — starting with serverless and setting the max at 6 vCores gives you room to handle peak loads, and the flexibility to scale cost down automatically during quiet periods. It's a very smart move to run this for a few weeks and then reassess with actual usage metrics.

Hope this helps. Do let us know if you any further queries.

Answer 2

The storage of Basic, S0 and S1 tiers is not intended for Production databases. The Production application using those tiers is destined for poor performance.

"Regarding data storage, the Standard S0 and S1 service tiers are placed on Standard Page Blobs. Standard Page Blobs use hard disk drive (HDD)-based storage media and are best suited for development, testing, and other infrequently accessed workloads that are less sensitive to performance variability" Source here.

The rest of standard tiers use remote SSD storage. Premium tiers using locally attached SSD storage.

About considering Serverless option, please know the following: "Memory for serverless databases is reclaimed more frequently than for provisioned compute databases. This behavior is important to control costs in serverless and can impact performance." (Source). This means you may see queries that retrieve a good amount of data taking some good time to place data in memory, thus showing really bad performance.

DTU premium tiers offer a free replica that allow you to redirect read workload to the replica.

Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2025-04-28T16:41:57.24+00:00

Hi @ Shea Martin

Following up to see if the above answers helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

How can we scale the read operations on our SQL DB instance?

2 answers

Your answer