Configuring AlwaysOn Read-Only Replicas

Configuring AlwaysOn Read-Only Replicas

How to configure read-only secondary replicas

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

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish