Sql-server – SQL SERVER – “ApplicationIntent=ReadOnly”Listener redirect to fully qualified domain name

availability-groupssql serversql server 2014

Hi all my DBA has setup a primary myNodeA and secondary node myNodeB and I'm trying to access the secondary node using "ApplicationIntent=ReadOnly" in JDBC connection string.

The problem we are facing is it redirects to just a server name and not a fully qualified domain name.

is there a setting or configuration in SQL Server to achieve this?

For example using that parameter redirects to myNodeB server name only

This needs to redirect to myNodeB.myDomain.com

Best Answer

My initial suspicion is that the initial setup didn't include the fully qualified name, but check for certain with the following statement:

SELECT ag.NAME AS "Availability Group"
    ,ar.replica_server_name AS "When Primary Replica Is"
    ,rl.routing_priority AS "Routing Priority"
    ,ar2.replica_server_name AS "RO Routed To"
    ,ar.secondary_role_allow_connections_desc
    ,ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
INNER JOIN sys.availability_replicas ar ON rl.replica_id = ar.replica_id
INNER JOIN sys.availability_replicas ar2 ON rl.read_only_replica_id = ar2.replica_id
INNER JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
ORDER BY ag.NAME
    ,ar.replica_server_name
    ,rl.routing_priority

The query was gratuitously lifted from this MSDN blog: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/22/modifying-alwayson-read-only-routing-lists/

In addition, make sure your JDBC driver supports the ApplicationIntent=ReadOnly parameter.

Related Question