Sql-server – Availability Group: ReadIntent connection failing

availability-groupssql-server-2012

I have two nodes configured in an AG group in a dev domain:

node1.DevDomain.com 
node2.DevDomain.com

My own workstation is on our production domain:

MyMachine.ProdDomain.com

When I test ApplicationIntent=ReadOnly connections from a machine within DevDomain I am correctly directed to the readable secondary. Note, the connection string specifies the Listener name as I am in the same domain.

However, when I test ApplicationIntent=ReadOnly connections from my own workstation (different domain) I get the connectivity error below. Note, in this case I need to specifiy the Listener IP since the I am outside the domain and the name will not resolve.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is conf
igured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.).Exception.Message

Server: Windows 2012, SQL 2012. My machine: Windows 7.

I believe I've got the Routing Lists and URLs correct (and again, this works fine from within the domain).

I know the nodes of the cluster need to be in the same domain, but I would think I'd be able to connect from outside the domain by specifying the Listener by IP, but maybe not?

I thought perhaps I had a DNS issue in the DevDomain, but everything seems to resolve fine inside that domain.

Can I do this / what do I need to be looking at to resolve this?

Best Answer

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
Related Question