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?

  • Two things. Maybe instead of IP try with FQDN for your dev box. Did you check firewall setup for sql server and ag ports ? – Kuba Miazek Feb 13 '15 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 '15 at 16:17
  • Do you have listeners object in Linked Server on both nodes ? How it is configures ? – Kuba Miazek Feb 14 '15 at 6:58
  • Btw for read only routing: Note that having your server configured to use dynamic ports is not recommended. – Kuba Miazek Feb 14 '15 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 '15 at 14:14
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
  • Can you add how the script works ? – Kin Shah Aug 9 '15 at 2:45
  • 1
    yes , just need free moment – Kuba Miazek Aug 10 '15 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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