Forum Discussion

Deleted's avatar
Deleted
Oct 12, 2018

Not able to read data from secondary replica in SQL HA

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.

  • Pio-Balistoy's avatar
    Pio-Balistoy
    Copper Contributor
    Oct 29, 2018

    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.

Resources