Introduction

Always On Availability Group (aka AG) feature in SQL Server provides a great high-availability and disaster recovery (aka HADR) solution. While AG is mostly used for HADR, it can also be used to alleviate the pain from a single database server. AG allows to add new servers to offload reporting needs (read replicas) without any prior domain knowledge.

This blog post is going to talk about using XE session to help transition read queries to a new server.

The most common scenario of a need to offload reporting is having a single server for OLTP to operate the business and Data Warehouse (aka DW) server to improve it. With growing reporting needs from DW and added products and service on OLTP, the single OLTP server will start having performance issues. While adding more resources (aka scale-up) will buy more time, adding a real-time copy of OLTP is a better long term solution (aka scale-out).

So how do add an OLTP copy? AG setup, will require a new Database Server and AG as a data synchronization mechanism (synchronous mode for time sensitive needs and asynchronous mode for more period based reporting needs). The existing OLTP server will become a source of truth (AG primary), while the new database server will have a synchronized queryable database (aka AG read replica) that is now available for all the read requests from DW.

Problem

Since all the DW queries (ETL, Reporting, Cubes, ML) were written before AG, they are not AG aware. AG Read replica source is built on a connection string directive called Read-Intent only which instructs SQL Server to redirect all the calls. Obviously all the existing DW connections would not have it, so what do we do now? How would we identify all the DW read queries that need a connection string change? Again, without changing all the DW connection strings, all the existing queries will be still hitting the existing OLTP server.

Solution

Introducing Extended Event sessions (aka XE session). XE is essentially a replacement for SQL Profiler which enables us to see everything that is hitting the SQL Server while offering better graphical interface and scripting capabilities. To make sure XE doesn’t become a problem while trying to solve another problem, we add filters to make sure we only capture specific DW queries, i.e. queries that are still hitting the existing OLTP server. Once we can identify those queries and their application sources, we can modify the connection string with read intent only and utilize AG read replica.

The following script creates a new XE session that ignores system queries, includes queries that are going against a Database called OLTP_Database from a server called DW while excluding queries from SentryOne monitoring software.

CREATE EVENT SESSION [DW going against primary] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
    
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) 
	
AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%OLTP_Database%') 
	
AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'DW') 
	
AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'SentryOne 20.0-Server')))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU
,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Dislaimer

This blog post is partially based on the following resources:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server?view=sql-server-ver15#:~:text=The%20Always%20On%20availability%20groups%20feature%20is%20a,a%20set%20of%20user%20databases%20for%20an%20enterprise.

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/sql-server-extended-events-sessions?view=sql-server-ver15#:~:text=%20The%20following%20aspects%20of%20an%20Extended%20Events,of%20an%20Extended%20Events%20session%2C%20such…%20More%20