SQL11UPD01-TSCRIPT-05

This wiki is a transcript of a previously recorded video.

Related content assets: 

• Presentation: Introducing SQL Server 2012 AlwaysOn Readable Secondary Replicas (SQL11UPD01-DECK-03) 
• Video: Introducing SQL Server 2012 AlwaysOn Readable Secondary Replicas (SQL11UPD01-REC-05)

Slide: Title slide

Welcome to introducing SQL Server 2012 AlwaysOn.

Slide: Introduction to SQL Server 2012 AlwaysOn Readable Secondaries

This video will be an introduction to SQL Server 2012 AlwaysOn Readable Secondaries. My name is Jonathan Kehayias. I’m a SQL Server MVP, and a Microsoft Certified Master for SQL Server 2008. I’m also a Principal Consultant with SQLskills.

Slide: Improved Efficiency and Cost-Effectiveness

In SQL Server 2012 we get improved efficiency and cost effectiveness of the implementation of high availability in our environment through the use of AlwaysOn Readable Secondaries.

Slide: What Happens When…

If your business wants to make use of the mostly unused failover servers for reporting against real-time business data, in SQL Server 2008 R2 and prior, database mirroring would have required a snapshot for management of the mirror databases for reporting purposes. Snapshot data does not change, requiring a new snapshot to keep the data up-to-date, plus there’s a connection migration that has to occur to move the reporting from the old snapshot to the new snapshot.

Snapshots will exist inside of the system until they’re cleaned up, even after a failover occurs, so you have to be able to manually handle what happens in the event of a mirroring failover inside of the environment to make sure that the reporting application gets disconnected, if appropriate, from the snapshot while the database mirroring is in a principal role on the server and how do you clean up the snapshots associated with it being a past mirror but now it’s in a principal role.

If you have a reporting workload that’s running against a database mirror that has a snapshot created, it is possible that you can have that reporting workload block the database mirroring processes inside of database mirroring.

If you’re using log shipping and you use RESTORE … WITH STANDBY, that provides near real-time access to business data but the caveat there is that every time the log restore operations have to occur, to keep the log-shipped secondary rolling forward, the applications that are connected are going to have to be disconnected because the log restore operation requires exclusive access to the database to roll it forward.

In SQL Server 2012 AlwaysOn Readable Secondaries actually enable read-only access for offloading reporting workloads to one of the secondary replicas in the environment.

Now the read-only workload inside of a Readable Secondary has zero interference with the data transfer from a primary replica in the environment. If you use a Readable Secondary inside of the environment you can also configure waits for offloading backup operations that allows your Readable Secondaries to actually your perform your log backups instead of having to perform those against your primary inside of your environment.

Slide: Readable Secondary: Client Connectivity

So with the Readable Secondary, the client connectivity story around this is that the connection behavior is defined by the Availability Group replica option for each of the secondaries. The secondaries can be configured so that they either are enabled for read access when they’re in the secondary role or they’re not enabled.

There are two different choices for what the read-only connection access is going to be. It can reject all connections, which is the default unless you change it, or it can allow connections that are made specifying the Application Intent equals Read Only connection problem in the connection string. This is a new feature in the SQL Server 2012 connection string that allows the application to specify that it wants to connect to a read-only secondary inside of an Availability Group environment.

The last option is that it can accept all connections, which basically would allow an end user to specify the server name for the secondary and they would be able to connect directly into that secondary. When they’re using Application Intent ReadOnly they’re actually connecting to the Availability Group Listener.

The way that routing happens when they connect to the Availability Group Listener is through what’s called read-only routing and that enables the redirection of client connections to new Readable Secondaries after a failover as a part of the application connecting to the Availability Group Listener. So when the connection specifies the Listener virtual name plus Application Intent equals ReadOnly, as it connects to the Listener the Listener will actually re-route that connection to a read-only secondary inside of the environment, offloading that workload from the primary.

It is possible to go to different Readable Secondaries if available to balance read-only access inside of the environment as a part of this configuration as well.

Slide: Readable Secondary: Read-Only Routing

So when we look at read-only routing… the client basically connects to the Availability Group Listener virtual name and when they connect to the Availability Group Listener, if they don’t… if they just have a standard connection string, they’re going to be routed to the primary server for read and write operations.

However, if they connect with a ReadOnly Application Intent value set for their connection string, when they connect to the Availability Group Listener, the Availability Group Listener will reroute that connection to a Readable Secondary based on the read-only routing configuration that’s been made for the Availability Group in the environment.

Slide: Readable Secondary: Query Performance on the Secondary

Associated with Readable Secondaries there are some challenges for query performance on the secondary, especially if you’re offloading reporting workloads. Query workloads typically require index and columns statistics for the optimizer to make an efficient query plan, and read-only workloads on a secondary replica may require different statistics than the workload that’s occurring as a part of the OLTP operations on the primary.

Users cannot create different statistics themselves because the secondary’s in read-only mode so it can’t be modified. But SQL Server will automatically create any required statistics and store them as temporary statistics in tempdb on the secondary node while it’s in a readable state.

If you need different indexes on the secondary workload, they actually have to be created in the primary replica so they’ll be present on the secondary for the read-only workload that’s occurring. You have to take care when you’re actually doing this though to make sure that you don’t create additional indexes that increase the maintenance overhead or increase the amount of log data that has to be passed between the primary and the secondaries inside of the environment reducing performance.

Slide: Readable Secondary: Offloading Backups to a Secondary

If you’re using Readable Secondaries there is an option to offload backups to the secondary and any replica of the database can actually perform transaction log backups and COPY_ONLY full backups only. You can’t do differential backups and you can’t perform a full backup that’s not using WITH COPY_ONLY against a secondary or one of the replicas inside of the environment.

The backup jobs can actually be configured on all of the replicas and you can set preferences so that the job only runs on a preferred replica at a time. This means that you have no script or job changes associated with a failover inside of your environment.

All of the log backups that are taken across all of the replicas form a single chain of log backups for recovery. Because you have backups that are occurring across different nodes or different replicas inside of the environment, it can make trying to piece together what the recovery chain is for the database a bit more complex because there is no single point of backup history in msdb. Each of the transaction log backups [Correction: Jon means backup history entry, not the backups themselves] will be written to the local msdb for whatever replica or secondary that the backup was taken against.

To help with this there’s a new advancement called the Database Recovery Advisor and this is a tool that allows you to point to a network path and select all the files that you would like to have evaluated for creating a recovery sequence and it actually will read those backup files and based on the log sequence numbers build an entire restore operation for you, based on backups that have occurred across multiple secondaries or servers in the environment. And then you can generate a script or optionally have the Database Recovery Advisor perform a full restore of all those backups automatically for you in the environment.

Slide: Readable Secondary: Workload Impact on the Secondary

With the Readable Secondary there are some considerations associated with what the workload impact on the secondary might be. Read-only workloads on mirror database setups using traditional database mirroring can easily block the replay of transactions from the principal.

However, using Readable Secondaries, the reporting workload actually uses snapshot isolation to avoid blocking the replay of any transactions inside of the database. The snapshot isolation avoids read locks which could block the REDO background thread and in the event that there ever was a lock that would cause a deadlock against the REDO thread, the REDO thread will never be chosen as a deadlock victim. Whatever report is running will be the deadlock victim in that scenario.

If you make DDL changes to your primary, for example you alter a table, those DDL operations require a schema lock associated with being able to get full control of the object to make the DDL change inside of the environment. In order to do that, if you have a long, complex running query, the DDL replay operation may be blocked inside of the secondary. And if that occurs, there’s a new extended event which will fire and this can allow you to do some programmatic determination or resumption of reporting based on the blocking that’s occurring inside of the system. That event is the sqlserver.lock_redo_blocked event inside of Extended Events and this event is monitored by the AlwaysOn health session that is running by default when you create an Availability Group inside of SQL Server 2012.

Slide: Summary

So in summary, SQL Server 2012 allows much more efficient use of the IT infrastructure with allowing that your failover servers be available for read-only workloads – your read-only secondaries are continuously updated by the primary without having to disconnect the reporting workload inside of the environment.

You can also improve the performance of workloads by offloading it to your secondary replicas or failover servers. And basically removing all the read-only I/O from your production primary instance and allowing it to handle all of the write I/O and you can also offload your transaction log backups and any COPY_ONLY full backups that you need to do to your failover server, improving the performance of the primary workload against your primary server.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)