3

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?

8
  • Two things. Maybe instead of IP try with FQDN for your dev box. Did you check firewall setup for sql server and ag ports ? Feb 13, 2015 at 7:21
  • FQDN won't work because there's no trust between domains so the name won't resolve. (Tried anyway and it failed.) There's no firewall issue as I can connect to each node individually by IP. The only issue is when going through the Listener with ApplicationIntent=ReadOnly.
    – Henry Lee
    Feb 13, 2015 at 16:17
  • Do you have listeners object in Linked Server on both nodes ? How it is configures ? Feb 14, 2015 at 6:58
  • Btw for read only routing: Note that having your server configured to use dynamic ports is not recommended. Feb 14, 2015 at 7:02
  • I'm not sure what you mean by Linked Servers - they have nothing to do with AG groups. So, no I don't have any Linked Servers configured. Also, ports are static.
    – Henry Lee
    Feb 14, 2015 at 14:14

1 Answer 1

1

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
2
  • Can you add how the script works ?
    – Kin Shah
    Aug 9, 2015 at 2:45
  • 1
    yes , just need free moment Aug 10, 2015 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.