Not able to read data from secondary replica in SQL HA

Deleted
Not applicable

Hi All,

 

I set up AG in SQL Server 2016 where i can read and write data in Primary node but same time i can not read data from secondary replica. 

Is there any way, sqL query or GUI to make secondary replica read only.

1 Reply

You can do that from SSMS GUI, drill down the the Avaialbility group then right-click properties. On the General Page there is a section for Availability Replica where all the replica is listed. On the column Readable Secondary you can change it to the setting you want.  Or by TSQL:

 

ALTER AVAILABILITY GROUP [AGName] MODIFY REPLICA ON N'InstanceName' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

 

You can change read_only to the setting you would like.