Configuring AlwaysOn Read-Only Replicas
How to configure read-only secondary replicas
Michael Otey
July 11, 2014
1 Min Read
![Configuring AlwaysOn Read-Only Replicas Configuring AlwaysOn Read-Only Replicas](https://eu-images.contentstack.com/v3/assets/blt07f68461ccd75245/bltb42951c9d1100e72/66193cae1f88d575f23142a2/qawebartdarkblue_0_5.jpg?width=1280&auto=webp&quality=95&format=jpg&disable=upscale)
Q: I want to offload some of my reporting workload to an AlwaysOn replica. How can I configure my AlwaysOn replica to allow read-only access?
A: While the primary replica allows read-write access by default, no connections are allowed to secondary replicas. If you want to a read-only SQL Server AlwaysOn replica, you need to specify the ALLOW_CONNECTIONS=READ_ONLY keyword or the secondary replica.
The following T-SQL example shows how you can add a read-enabled secondary replica to an existing AlwaysOn Availability group named MYSQLServerAG on the host named ORPORTSQL01.
ALTER AVAILABILITY GROUP MySQLServerAG ADD REPLICA ON 'ORPORTSQL01' WITH ( ENDPOINT_URL = 'TCP://ORPORTSQL01:7022', PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE ), SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY ) );GO
Related: Microsoft SQL Server 2012: AlwaysOn Availability Groups Feature
About the Author(s)
Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
You May Also Like