Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups)

The AlwaysOn Availability Groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). A readable secondary replica allows read-only access to all its secondary databases. However, readable secondary databases are not set to read-only. They are dynamic. A given secondary database changes as changes on the corresponding primary database are applied to the secondary database. For a typical secondary replica, the data in the secondary databases is in near real time. Furthermore, full-text indexes are synchronized with the secondary databases. In many circumstances, data latency between a primary database and the corresponding secondary database is only a few seconds.

Security settings that occur in the primary databases are persisted to the secondary databases. This includes users, database roles, and applications roles together with their respective permissions and transparent data encryption (TDE), if enabled on the primary database.

Note

Though you cannot write data to secondary databases, you can write to read-write databases on the server instance that hosts the secondary replica, including user databases and system databases such as tempdb.

AlwaysOn Availability Groups also supports the re-routing of read-intent connection requests to a readable secondary replica (read-only routing). For information about read-only routing, see Using a Listener to Connect to a Read-Only Secondary Replica (Read-Only Routing).

In this Topic:

• Benefits

• Prerequisites for the Availability Group

• 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.

[Top]

Prerequisites for the Availability Group

• Readable secondary replicas (required)

  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.

  Note

  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.

    Note

    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).

Note

For information about availability group listeners and more information about read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

[Top]

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.

  Note

  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.

Note

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.

[Top]

Performance Considerations

This section discusses several performance considerations for readable secondary databases

In This Section:

• Data Latency

• Read-Only Workload Impact

• Indexing

• Statistics for Read-Only Access Databases

Data Latency

Implementing read-only access to secondary replicas is useful if your read-only workloads can tolerate some data latency. In situations where data latency is unacceptable, consider running read-only workloads against the primary replica.

The primary replica sends log records of changes on primary database to the secondary replicas. On each secondary database, a dedicated redo thread applies the log records. On a read-access secondary database, a given data change does not appear in query results until the log record that contains the change has been applied to the secondary database and the transaction has been committed on primary database.

This means that there is some latency, usually only a matter of seconds, between the primary and secondary replicas. In unusual cases, however, for example if network issues reduce throughput, latency can become significant. Latency increases when I/O bottlenecks occur and when data movement is suspended. To monitor suspended data movement, you can use the AlwaysOn Dashboard or the sys.dm_hadr_database_replica_states dynamic management view.

Read-Only Workload Impact

When you configure a secondary replica for read-only access, your read-only workloads on the secondary databases consume system resources, such as CPU and I/O from redo threads, especially if the read-only workloads are highly I/O-intensive.

Also, read-only workloads on the secondary replicas can block data definition language (DDL) changes that are applied through log records. Even though the read operations do not take shared locks because of row versioning, these operations take schema stability (Sch-S) locks, which can block redo operations that are applying DDL changes.

Be aware of best practices around building queries, and exercise those best practices in the secondary databases. For example, schedule long-running queries such as aggregations of data during times of low activity.

Note

If a redo thread is blocked by queries on a secondary replica, the sqlserver.lock_redo_blocked XEvent is raised.

Indexing

To optimize read-only workloads on the readable secondary replicas, you may want to create indexes on the tables in the secondary databases. Because you cannot make schema or data changes on the secondary databases, create indexes in the primary databases and allow the changes to transfer to the secondary database through the redo process.

To monitor index usage activity on an secondary replica, query the user_seeks, user_scans, and user_lookups columns of the sys.dm_db_index_usage_stats dynamic management view.

Statistics for Read-Only Access Databases

Statistics on columns of tables and indexed views are used to optimize query plans. For availability groups, statistics that are created and maintained on the primary databases are automatically persisted on the secondary databases as part of applying the transaction log records. However, the read-only workload on the secondary databases may need different statistics than those that are created on the primary databases. However, because secondary databases are restricted to read-only access, statistics cannot be created on the secondary databases.

To address this problem, the secondary replica creates and maintains temporary statistics for secondary databases in tempdb. The suffix _readonly_database_statistic is appended to the name of temporary statistics to differentiate them from the permanent statistics that are persisted from the primary database.

Only SQL Server can create and update temporary statistics. However, you can delete temporary statistics and monitor their properties using the same tools that you use for permanent statistics:

• Delete temporary statistics using the DROP STATISTICS Transact-SQL statement.

• Monitor statistics using the sys.stats and sys.stats_columns catalog views. sys_stats includes a column, is_temporary, to indicate which statistics are permanent and which are temporary.

For more information about SQL Server statistics, see Statistics.

In This Section:

• Stale Permanent Statistics on Secondary Databases

• Limitations and Restrictions

Stale Permanent Statistics on Secondary Databases

SQL Server detects when permanent statistics on a secondary database are stale. But changes cannot be made to the permanent statistics except through changes on the primary database. For query optimization, SQL Server creates temporary statistics on the secondary database and uses these statistics instead of the stale permanent statistics.

When the permanent statistics are updated on the primary database, they are automatically persisted to the secondary database. Then SQL Server uses the updated permanent statistics, which are more current than the temporary statistics.

If the availability group fails over, temporary statistics are deleted on all of the secondary replicas.

Limitations and Restrictions

• Because temporary statistics are stored in tempdb, a restart of the SQL Server service causes all temporary statistics to disappear.

• The suffix _readonly_database_statistic is reserved for statistics generated by SQL Server. You cannot use this suffix when creating statistics on a primary database. For more information, see Statistics.

[Top]

Capacity Planning Considerations

• Readable secondary replicas can require space in tempdb for two reasons:

  • Snapshot isolation level copies row versions into tempdb.

  • Temporary statistics for secondary databases are created and maintained in tempdb. The temporary statistics can cause a slight increase in the size of tempdb. For more information, see Statistics for Read-Only Access Databases, later in this section.

• When you configure read-access for one or more secondary replicas, the primary databases add 14 bytes of overhead on deleted, modified, or inserted data rows to store pointers to row versions on the secondary databases. This 14-byte overhead is carried over to the secondary databases. As the 14-byte overhead is added to data rows, page splits might occur.

  The row version data is not generated by the primary databases. Instead, the secondary databases generate the row versions. However, row versioning increases data storage in both the primary and secondary databases.

  The addition of the row version data depends on the snapshot isolation or read-committed snapshot isolation (RCSI) level setting on the primary database. The table below describes the behavior of versioning on a readable secondary database under different settings.

  Readable secondary replica?	Snapshot isolation or RCSI level enabled?	Primary Database	Secondary Database
  No	No	No row versions or 14-byte overhead	No row versions or 14-byte overhead
  No	Yes	Row versions and 14-byte overhead	No row versions, but 14-byte overhead
  Yes	No	No row versions, but 14-byte overhead	Row versions and 14-byte overhead
  Yes	Yes	Row versions and 14-byte overhead	Row versions and 14-byte overhead

[Top]

Related Tasks

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

• Configure Read-Only Routing for an Availability Group (SQL Server)

• Create or Configure an Availability Group Listener (SQL Server)

• Monitor Availability Groups (Transact-SQL)

• View Availability Replica Properties (SQL Server)

• Use the New Availability Group Dialog Box (SQL Server Management Studio)

[Top]

Related Content

• SQL Server AlwaysOn Team Blog: The official SQL Server AlwaysOn Team Blog

[Top]

See Also

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

About Client Connection Access to Availability Replicas (SQL Server)

Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)

Statistics