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

Question

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

Answer 1

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.