We have a Azure Database with a read replica. However the read replica seems to be effected by the load of the primary database. The primary database is in an elastic pool.
Does the read replica make use of the same elastic pool?
2
-
1You will need to provide more details. How are getting a read replica? Just by deploying premium/business-critical or via active geo-replication/failover group? What kind of effect on read replica?– SqlWorldWideCommented Jun 25, 2021 at 13:30
-
Business critical read replica. When we run heavy loads that take up a lot of cpu the read replica starts timing out.– MurdockCommented Jun 25, 2021 at 14:54
Add a comment
|
1 Answer
Read replica is not using resources from the same elastic pool as the primary (read/write) node.
As part of High Availability architecture, each single database, elastic pool database, and managed instance in the Premium and Business Critical service tier is automatically provisioned with a primary read-write replica and several secondary read-only replicas. The secondary replicas are provisioned with the same compute size as the primary replica. The read scale-out feature allows you to offload read-only workloads using the compute capacity of one of the read-only replicas, instead of running them on the read-write replica. This way, some read-only workloads can be isolated from the read-write workloads, and will not affect their performance. The feature is intended for the applications that include logically separated read-only workloads, such as analytics. In the Premium and Business Critical service tiers, applications could gain performance benefits using this additional capacity at no extra cost.
I suggest you monitor your resources of the read-only replica. Especially to find out if you are hitting the maximum limit of resource for that database that is getting time out. I will start here sys.dm_db_resource_stats (Azure SQL Database).
Here is a list of DMV's you can use to Monitoring and troubleshooting read-only replicas.
Read this Q&A Azure Elastic Pool Database SQL Connections are Timing Out - DTU Purchasing model answered by David Browne from Microsoft. Even though this question is talking about the primary node, you will experience the same if your read-only replica uses up all allocated resources.
Ref: Use read-only replicas to offload read-only query workloads
answered Jun 25, 2021 at 21:52
