Oct 11 2018 10:01 PM
Oct 11 2018 10:01 PM
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.
Oct 29 2018 01:44 AM
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.