• Limitations and Restrictions
• Performance Considerations • Capacity Planning Considerations • Related Tasks
• Related Content
Benefits
Directing read-only connections to readable secondary replicas provides the following benefits: • Offloads your secondary read-only workloads from your primary replica, which conserves its
resources for your mission critical workloads. If you have mission critical read-workload or the workload that cannot tolerate latency, you should run it on the primary.
• Improves your return on investment for the systems that host readable secondary replicas. In addition, readable secondaries provide robust support for read-only operations, as follows: • Temporary statistics on readable secondary database optimize read-only queries. For more
information, see Statistics for Read-Only Access Databases, later in this topic.
• Read-only workloads use row versioning to remove blocking contention on the secondary databases. All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are
explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.
Prerequisites for the Availability Group
• Readable secondary replicas (required) Note
The database administrator needs to configure one or more replicas so that, when running under the secondary role, they allow either all connections (just for read-only access) or only read-intent connections.
Optionally, the database administrator can configure any of the availability replicas to exclude read-only connections when running under the primary role.
For more information, see About Client Connection Access to Availability Replicas (SQL Server).
• Availability group listener
To support read-only routing, an availability group must possess an availability group listener. The read-only client must direct its connection requests to this listener, and the client's connection string must specify the application intent as "read-only." That is, they must be read-intent connection requests.
• Read only routing
Read-only routing refers to the ability of SQL Server to route incoming read-intent
connection requests, that are directed to an availability group listener, to an available readable secondary replica. The prerequisites for read-only routing are as follows:
• To support read-only routing, a readable secondary replica requires a read-only routing URL. This URL takes effect only when the local replica is running under the secondary role. The read-only routing URL must be specified on a replica-by-replica basis, as needed. Each read-only routing URL is used for routing read-intent connection requests to a specific readable secondary replica. Typically, every readable secondary replica is assigned a read-only routing URL.
• Each availability replica that is to support read-only routing when it is the primary replica requires a read-only routing list. A given read-only routing list takes effect only when the local replica is running under the primary role. This list must be specified on a replica-by- replica basis, as needed. Typically, each read-only routing list would contain every read- only routing URL, with the URL of the local replica at the end of the list.
Read-intent connection requests are routed to the first available readable
secondary on the read-only routing list of the current primary replica. There is no load balancing.
For more information, see Configure Read-Only Routing for an Availability Group (SQL Server).
For information about availability group listeners and more information about read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (AlwaysOn Availability Groups).
Note
Note
Limitations and Restrictions
Some operations are not fully supported, as follows:
• As soon as a readable secondary replica joins the availability group, the secondary replica can start accepting connections to its secondary databases. However, if any active
transactions exist on a primary database, row versions will not be fully available immediately on the corresponding secondary database. Any active transactions that existed on the primary replica when the secondary replica was configured must be committed or rolled back. Until this process completes, the transaction isolation level mapping on the secondary database is incomplete and queries are temporarily blocked.
Running long transaction will impact the number of versioned rows kept.
• Change tracking and change data capture are not supported on secondary databases that belong to a readable secondary replica:
• Change tracking is explicitly disabled on secondary databases.
• Change data capture can be enabled on a secondary database, but this is not supported. • Because read operations are mapped to snapshot isolation transaction level, the cleanup of
ghost records on the primary replica can be blocked by transactions on one or more secondary replicas. The ghost record cleanup task will automatically clean up the ghost records on the primary replica when they are no longer needed by any secondary replica. This is similar to what is done when you run transaction(s) on the primary replica. In the extreme case on the secondary database, you will need to kill a long running read-query that is blocking the ghost cleanup. Note, the ghost clean can be blocked if the secondary replica gets disconnected or when data movement is suspended on the secondary database. This state also prevents log truncation, so if this state persists, we recommend that you remove this secondary database from the availability group.
• The DBCC SHRINKFILE operation might fail on the primary replica if the file contains ghost records that are still needed on a secondary replica.
If you query the sys.dm_db_index_physical_stats dynamic management view on a server instance that is hosting a readable secondary replica, you might encounter a REDO blocking issue. This is because this dynamic management view acquires an IS lock on the specified user table or view that can block requests by a REDO thread for an X lock on that user table or view.