Read_Write access to replica of azure sql

Question

How to provide read_write access to azure sql replica. By default it is read only

Answer 1

It's important that you understand that Azure SQL Database replicas are primarily used for read-scale out purposes and not for read/write scenarios. By design, the replicas are read-only to offload read-only query workloads and not intended for read/write access. This design supports scaling out read-heavy workloads without impacting the primary database's performance.

For read-write access, you might be looking at a failover scenario rather than a read replica. Azure SQL Database offers Failover Groups to automatically manage read-write and read-only access rules during failovers. In a failover group, the primary server is read-write, and the secondary server(s) in a different region can serve read-only traffic until a failover happens. After a failover, the roles are reversed.

Similar to Failover Groups but requires manual intervention for failovers. This feature is used for creating readable secondary replicas (up to four) in the same or different geographic location. The secondary databases are read-only until a failover is performed manually. After the failover, the secondary database becomes the primary and can accept read-write transactions.

For scenarios requiring both read and write capabilities across databases, consider using a sharding pattern. Sharding involves distributing data across multiple databases (shards) to spread out the load. Each shard can handle read-write operations, but this requires application logic to manage the distribution of data and queries across shards.

Answer 2

Hi @Utkarsh upadhyay

Azure SQL Database's read replicas are designed to be read-only. This is a built-in feature of the service to ensure data consistency and integrity. If you need to perform write operations, you would need to do so on the primary database.

Thank You!

Answer 3

Thanks for posting your question in the Microsoft Q&A forum.

By default, secondary replicas in Azure SQL Database are configured in read-only mode for performance and consistency reasons. To enable read-write access on the secondary replica, you need to modify the replica's database settings.

In the Azure portal, navigate to the Azure SQL Database replica.

Under Settings, select Geo-Replication.

Select the secondary replica database.

Under Geo-Replication Settings, toggle the Readable Secondary setting to Read-write.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful