SQL Server 2017 – Availability Group Not Routing to Secondary Server

availability-groupssql serversql-server-2017

I've set up a dev AlwaysOn environment on SSMS 2017.
I have my AG set up:

I also ran the following scripts:

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Dev-DB1.test.com:5022'));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Dev-DB2.test.com:5022'));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('DEV-DB2','DEV-DB1')));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('DEV-DB1','DEV-DB2')));

now when I connect to the listener (using SSMS2017) with ApplicationIntent=ReadOnly and I run select @@SERVERNAME it still shows me DEV-DB1. I thought it would show me that I'm getting data from the secondary DEV-DB2

What am I missing?

Best Answer

Found my issue: I was under the impression that my End Point URL should be the same as my routing URL, which was going to port 5022. after reading the following article read_only_routing_url I was able to identify my actual routing URL port which was 1433.

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.

Sql-server – Availability Group: ReadIntent connection failing

Script to create read only rounting:

set up read-only intent routing

Test it:

sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername" -Kreadonly 
sqlcmd -S "YOUR_LISTENER" -d dbname -Q "select @@servername"

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com \instance'
with (SECONDARY_ROLE(ALLOW_CONNECTIONS = all));-- not read_only

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com\instance'
with (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'tcp://node1.DevDomain.com:49598'));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instance'
with (SECONDARY_ROLE(ALLOW_CONNECTIONS = all));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instance'
with (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'tcp://node2.DevDomain.com\instance:49877'));

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node1.DevDomain.com\instance'
with (
        PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (
                'node1.DevDomain.com\instance'
                ,'node2.DevDomain.com\instance'
                ))
        );

alter AVAILABILITY
group [AG] MODIFY REPLICA on N'node2.DevDomain.com\instanced'
with (
        PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (
                'node2.DevDomain.com\instancee'
                ,'node1.DevDomain.com\instance'
                ))
        );
go

Best Answer

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

Sql-server – Availability Group: ReadIntent connection failing

Related Question