SQL Server 2019 availability groups – Introduction to R/W redirection

writeThis is a second write-up about SQL Server 2019 CTP2.0 and availability group new features. This time the interesting one is about READRWRITE redirection capabilities. A couple of months ago, I wrote about SQL Server 2017 and new read-scale capabilities and listener management challenges regarding the operating system. Indeed, there are some scenarios where including listener will not as easy as we did with common ones on the top of Windows operation system. A list of these scenarios is listed in the BOL.

Without a listener, I would say that read-only connections are not a big deal because they are supposed to work regardless the replica’s role – either PRIMARY or secondary the game is not the same with read-write connections. The connection’s write-part may trigger errors if they attempt to run write queries and guess what, this issue is addressed by new READWRITE capabilities of AGs in SQL Server 2019.

Let’s set the context of my lab environment:

This is a pretty simple environment that includes 2 replicas in synchronous mode. Automatic failover is obviously not available in read scale topology.

My first test was to attempt a R/W client connection from the secondary to see if redirection applied on the primary. My configuration script includes the new READ_WRITE_ROUTING_URL requested for the redirection to the primary.

:CONNECT WIN20161\SQL2019CTP2
CREATE AVAILABILITY GROUP AG2019   
WITH ( CLUSTER_TYPE =  NONE )  
FOR DATABASE  [AdventureWorks2016]   
REPLICA ON 
'WIN20161\SQL2019CTP2' WITH   
(  
	ENDPOINT_URL = 'TCP://WIN20161.dbi-services.test:5026',  
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
	FAILOVER_MODE = MANUAL,  
	-- Secondary role => we need to allow all connection types 
	-- R/O + R/W to allow R/W connections
	SECONDARY_ROLE (
		ALLOW_CONNECTIONS = ALL,   
		READ_ONLY_ROUTING_URL = 'TCP://WIN20161.dbi-services.test:1459' 
	),
	PRIMARY_ROLE (
		ALLOW_CONNECTIONS = READ_WRITE,   
		READ_ONLY_ROUTING_LIST = ('WIN20162\SQL2019CTP2'),
		READ_WRITE_ROUTING_URL = 'TCP://WIN20161.dbi-services.test:1459' 
	),  
	SESSION_TIMEOUT = 10,
	SEEDING_MODE = AUTOMATIC  
),   
'WIN20162\SQL2019CTP2' WITH   
(  
	ENDPOINT_URL = 'TCP://WIN20162.dbi-services.test:5026',  
	AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
	FAILOVER_MODE = MANUAL, 
	SECONDARY_ROLE (
		ALLOW_CONNECTIONS = ALL,   
		READ_ONLY_ROUTING_URL = 'TCP://WIN20162.dbi-services.test:1459' 
	),   
	PRIMARY_ROLE (
		ALLOW_CONNECTIONS = READ_WRITE,   
		READ_ONLY_ROUTING_LIST = ('WIN20161\SQL2019CTP2'),
		READ_WRITE_ROUTING_URL = 'TCP://WIN20162.dbi-services.test:1459' 
	), 
	SESSION_TIMEOUT = 10,
	SEEDING_MODE = AUTOMATIC
);
GO 

ALTER AVAILABILITY GROUP [AG2019] GRANT CREATE ANY DATABASE
GO

:CONNECT WIN20162\SQL2019CTP2
ALTER AVAILABILITY GROUP [AG2019] JOIN WITH (CLUSTER_TYPE = NONE);
GO  

ALTER AVAILABILITY GROUP [AG2019] GRANT CREATE ANY DATABASE
GO

My AG current state is as follows and meets all R/W redirection prerequisites: the WIN20162\SQL2019CTP2 secondary replica is online and the primary replica includes READ_WRITE_ROUTING_URL as well.

We may notice new sys.availability_replicas view column concerning shipped with this new SQL Server version including R/W URL settings and primary connection mode as well.

Let’s try a R/W connection to the secondary replica WIN20162\SQL2019CT2 with the following connection string. ApplicationIntent parameter is not specified meaning I will use R/W intent by default.

$connectionString = "Server=WIN20162\SQL2019CTP2; Integrated Security=False; uid=sa; pwd=xxxx; Initial Catalog=AdventureWorks2016;"

I got the expected result. My connection is transparently redirecting to my primary replica WIN20161\SQL2019CT2 as shown below:

Let’s switch my connection intent to read-only (connection parameter ApplicationIntent=ReadOnly):

$connectionString = "Server=WIN20162\SQL2019CTP2; Integrated Security=False; uid=sa; pwd=xxxx; Initial Catalog=AdventureWorks2016; ApplicationIntent=ReadOnly"

The connection stays on the secondary read-only accordingly to my AG configuration:

This new capability did the expected job. Obviously if you miss some prerequisites redirection will not occur but at the moment of this write-up I didn’t find out any “obvious” extended event to troubleshoot R/W routing events as we may already use for R/O routing. Probably in the next CTP …

In a nutshell, this new feature concerns R/W redirections from a secondary to a primary replica. But for curiosity, I tried to perform the same test for R/O redirection with ApplicationIntent=ReadOnly but without luck.

$connectionString = "Server=WIN20161\SQL2019CTP2; Integrated Security=False; uid=sa; pwd=xxxx; Initial Catalog=AdventureWorks2016; ApplicationIntent=ReadOnly"

To confirm my R/O topology is well configured, I implemented a AG listener as I did in a previous blog post about AG read-scale scenarios. For my test, I replaced the server value by the listener name in the following connection string. My AG listener is listen on port 1459  …

$connectionString = "Server=lst-ag2019,1459; Integrated Security=False; uid=sa; pwd=xxxx; Initial Catalog=AdventureWorks2016; ApplicationIntent=ReadOnly"

… and it ran successfully as show below. My connection is well redirected from the primary – WIN20161\SQL2019CTP2 to the secondary WIN20162\SQL2019CTP2:

Well, it seems that R/O redirection without using a listener is not implemented yet but it is probably out of the scope of SQL2019 AG redirection capabilities.

See you!

By David Barbarin