Configure Read-Only Access on an Availability Replica (SQL Server)

By default both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an AlwaysOn availability group. This topic describes how to configure connection access on an availability replica of an AlwaysOn availability group in SQL Server 2012 by using SQL Server Management Studio, Transact-SQL, or PowerShell.

For information about the implications of enabling read-only access for a secondary replica and for an introduction to connection access, see About Client Connection Access to Availability Replicas (SQL Server) and Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups).

  • Before you begin:  

    Prerequisites and Restrictions

    Security

  • To configure access on an availability replica, using:  

    SQL Server Management Studio

    Transact-SQL

    PowerShell

  • Follow Up:  After Configuring Read-Only Access for an Availability Replica

  • Related Tasks

  • Related Content

Before You Begin

Prerequisites and Restrictions

  • To configure different connection access, you must be connected to the server instance that hosts the primary replica.

Security

Permissions

Task

Permissions

To configure replicas when creating an availability group

Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

To modify an availability replica

Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Arrow icon used with Back to Top link[Top]

Using SQL Server Management Studio

To configure access on an availability replica

  1. In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.

  2. Expand the AlwaysOn High Availability node and the Availability Groups node.

  3. Click the availability group whose replica you want to change.

  4. Right-click the availability replica, and click Properties.

  5. In the Availability Replica Properties dialog box, you can change the connection access for the primary role and for the secondary role, as follows:

    • For the secondary role, select a new value from the Readable secondary drop list, as follows:

      • No
        No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.

      • Read-intent only
        Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

      • Yes
        All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.

    • For the primary role, select a new value from the Connections in primary role drop list, as follows:

      • Allow all connections
        All connections are allowed to the databases in the primary replica. This is the default setting.

      • Allow read/write connections
        When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. Connections where the Application Intent connection property is set to ReadOnly are not allowed. This can help prevent customers from connecting a read-intent work load to the primary replica by mistake. For more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

Arrow icon used with Back to Top link[Top]

Using Transact-SQL

To configure access on an availability replica

Note

For an example of this procedure, see Example (Transact-SQL), later in this section.

  1. Connect to the server instance that hosts the primary replica.

  2. If you are specifying a replica for a new availability group, use the CREATE AVAILABILITY GROUP Transact-SQL statement. If you are adding or modifying a replica of an existing availability group, use the ALTER AVAILABILITY GROUP Transact-SQL statement.

    • To configure connection access for the secondary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the SECONDARY_ROLE option, as follows:

      SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

      where,

      • NO
        No direct connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.

      • READ_ONLY
        Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

      • ALL
        All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.

  3. To configure connection access for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

    PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

    where,

    • READ_WRITE
      Connections where the Application Intent connection property is set to ReadOnly are disallowed. When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. For more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

    • ALL
      All connections are allowed to the databases in the primary replica. This is the default setting.

Example (Transact-SQL)

The following example adds a secondary replica to an availability group named AG2. A stand-alone server instance, COMPUTER03\HADR_INSTANCE, is specified to host the new availability replica. This replica configured to allow only read-write connections for the primary role and to allow only read-intent connections for secondary role.

ALTER AVAILABILITY GROUP AG2 
   ADD REPLICA ON 
      'COMPUTER03\HADR_INSTANCE' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER03:7022',
         PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE ),
         SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY )
         ); 
GO

Arrow icon used with Back to Top link[Top]

Using PowerShell

To configure access on an availability replica

Note

For a code example, see Example (PowerShell), later in this section.

  1. Change directory (cd) to the server instance that hosts the primary replica.

  2. When adding an availability replica to an availability group, use the New-SqlAvailabilityReplica cmdlet. When modifying an existing availability replica, use the Set-SqlAvailabilityReplica cmdlet. The relevant parameters are as follows:

    • To configure connection access for the secondary role, specify the ConnectionModeInSecondaryRole secondary_role_keyword parameter, where secondary_role_keyword equals one of the following values:

      • AllowNoConnections
        No direct connections are allowed to the databases in the secondary replica and the databases are not available for read access. This is the default setting.

      • AllowReadIntentConnectionsOnly
        Connections are allowed only to the databases in the secondary replica where the Application Intent property is set to ReadOnly. For more information about this property, see Using Connection String Keywords with SQL Server Native Client.

      • AllowAllConnections
        All connections are allowed to the databases in the secondary replica for read-only access.

    • To configure connection access for the primary role, specify ConnectionModeInPrimaryRole primary_role_keyword, where primary_role_keyword equals one of the following values:

      • AllowReadWriteConnections
        Connections where the Application Intent connection property is set to ReadOnly are disallowed. When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. For more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

      • AllowAllConnections
        All connections are allowed to the databases in the primary replica. This is the default setting.

    Note

    To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server 2012 PowerShell environment. For more information, see Get Help SQL Server PowerShell.

To set up and use the SQL Server PowerShell provider

Example (PowerShell)

The following example, sets the both the ConnectionModeInSecondaryRole and ConnectionModeInPrimaryRole parameters to AllowAllConnections.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole "AllowAllConnections" ` 
-InputObject $primaryReplica
Set-SqlAvailabilityReplica -ConnectionModeInPrimaryRole "AllowAllConnections" ` 
-InputObject $primaryReplica

Arrow icon used with Back to Top link[Top]

Follow Up: After Configuring Read-Only Access for an Availability Replica

Read-only access to a readable secondary replica

Factors that might affect triggers and jobs after a failover

If you have triggers and jobs that will fail when running on a non-readable secondary database or on a readable secondary database, you need to script the triggers and jobs to check on a given replica to determine whether the database is a primary database or is a readable secondary database. To obtain this information, use the DATABASEPROPERTYEX function to return the Updatability property of the database. To identify a read-only database, specify READ_ONLY as the value, as follows:

DATABASEPROPERTYEX([db name],’Updatability’) = N’READ_ONLY’

To identify a read-write database, specify READ_WRITE as the value.

Arrow icon used with Back to Top link[Top]

Arrow icon used with Back to Top link[Top]

Arrow icon used with Back to Top link[Top]

See Also

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups)

About Client Connection Access to Availability Replicas (SQL Server)