February 26, 2015 at 9:42 am
I have set up a 2 node availability group to take advantage of using the secondary node for a read-only replica. I actually am having two issues. The first being I can connect to the primary node using the listener dns name and ip address, but no longer can connect via its actual host name or ip address. I can ping the address with no problem, but I can't connect to port 1433 using the actual host name or ip address. I am no problem connecting to the secondary node using its host name, but can not get to it through the listener using the applicationintent=readonly. Eventually I would like for everything to connect through the listener name, but for now still need to connect via the server's host name and don't understand why; I have spent an hour already trying to find a solution, but everything I read is that the primary node should be able to be connected via both the host name and the listener name.
Has anyone run into this and fixed it?
February 26, 2015 at 9:52 am
sherrerk (2/26/2015)
I am no problem connecting to the secondary node using its host name, but can not get to it through the listener using the applicationintent=readonly.
Can you provide details of the readonly routing config scripts you ran on the primary?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 26, 2015 at 9:56 am
I followed this article: https://msdn.microsoft.com/en-us/library/hh710054.aspx and originally set up everything using the wizards.
February 26, 2015 at 10:15 am
ok, so what scripts did you run to configure readonly routing?
Would expect to see something like this
ALTER AVAILABILITY GROUP [STOKEAG_1] MODIFY REPLICA ON
N'STOKECSCLNODE1\INST1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [STOKEAG_1] MODIFY REPLICA ON
N'STOKECSCLNODE1\INST1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://stokecsclnode1.stokecs2.co.uk:58001'));
ALTER AVAILABILITY GROUP [STOKEAG_1] MODIFY REPLICA ON
N'STOKECSCLNODE2\INST1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [STOKEAG_1] MODIFY REPLICA ON
N'STOKECSCLNODE2\INST1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://stokecsclnode2.stokecs2.co.uk:58001'));
ALTER AVAILABILITY GROUP [STOKEAG_1] MODIFY REPLICA ON
N'STOKECSCLNODE3\INST1' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [STOKEAG_1] MODIFY REPLICA ON
N'STOKECSCLNODE3\INST1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://stokecsclnode3.stokecs2.co.uk:58001'));
ALTER AVAILABILITY GROUP [STOKEAG_1] MODIFY REPLICA ON
N'STOKECSCLNODE1\INST1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST =
'STOKECSCLNODE3\INST1', 'STOKECSCLNODE2\INST1', 'STOKECSCLNODE1\INST1' )));
ALTER AVAILABILITY GROUP [STOKEAG_1] MODIFY REPLICA ON
N'STOKECSCLNODE2\INST1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST =
'STOKECSCLNODE3\INST1', 'STOKECSCLNODE1\INST1', 'STOKECSCLNODE2\INST1')));
ALTER AVAILABILITY GROUP [STOKEAG_1] MODIFY REPLICA ON
N'STOKECSCLNODE3\INST1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST =
'STOKECSCLNODE2\INST1', 'STOKECSCLNODE1\INST1', 'STOKECSCLNODE3\INST1')));
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 26, 2015 at 10:21 am
Post the results of this query run against the primary replica
selectreplica_id
, group_id
, replica_metadata_id
, replica_server_name
, owner_sid
, endpoint_url
, availability_mode
, availability_mode_desc
, failover_mode
, failover_mode_desc
, session_timeout
, primary_role_allow_connections
, primary_role_allow_connections_desc
, secondary_role_allow_connections
, secondary_role_allow_connections_desc
, create_date
, modify_date
, backup_priority
, read_only_routing_url
from sys.availability_replicas
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 26, 2015 at 10:21 am
I am going to give it a try again, in the mean time I figured out why I could not connect to the primary node using its host name. TCP/IP was disabled in the network configuration which now confuses me even more. First why did setting up AlwaysOn disable it and with it disabled why could I connect through the listener?
February 26, 2015 at 10:26 am
You still haven't answered my question, did you run any scripts as part of the Readonly routing setup, if so please post details.
If you didn't, then you have no RO routing urls configured and RO routing will not work, it's not a wizard based configuration. The meta query I supplied will confirm if you have any RO routing urls configured
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2015 at 3:57 pm
I had to tear down everything because it was in our QA environment and they thought it was affecting them. I spent some time setting everything back up today. I have gotten closer to having it work 100% like I would like. In my environment right now I am only interested in having a readonly replica and not have a HA solution yet. I put the output of the query in but it may be unreadable, is there a better way to insert it? At the current time I can go to both the primary and secondary hosts and everything works like I expect. If I try to update the secondary I get a message stating it's readonly. I can connect to the listener and it connects to the primary with no problem. I add applicationintent=readonly to the connection string and it connects to the secondary with default database master. When I try to change to one of my replicated databases I get a message stating "The database <database name> is not accessible". Thanks for the help so far, that got me further down the path.
Here are the scripts I have run so far:
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA1-1A' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA1-1A' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://qa-db-data1-1a.corp.membersuite.com:1433'));
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA1-1A' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA2-1A' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://qa-db-data2-1a.corp.membersuite.com:1433'));
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA1-1A' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST =
('QA-DB-DATA2-1A')));
ALTER AVAILABILITY GROUP QA_AG ADD LISTENER 'QADB' ( WITH IP (('10.0.9.232','255.255.255.0')), PORT=1433)
And the output of the query you gave:
replica_idgroup_idreplica_metadata_idreplica_server_nameowner_sidendpoint_urlavailability_modeavailability_mode_descfailover_modefailover_mode_descsession_timeoutprimary_role_allow_connectionsprimary_role_allow_connections_descsecondary_role_allow_connectionssecondary_role_allow_connections_desccreate_datemodify_datebackup_priorityread_only_routing_url
658F562D-A7C9-4C0C-A17B-254D9899893A9C1B792A-0D8E-4AC3-8D00-BCE66CF954B365537QA-DB-DATA1-1A0x010500000000000515000000962C98B2E7FCE7D35811429550040000TCP://qa-db-data1-1a.corp.membersuite.com:50220ASYNCHRONOUS_COMMIT1MANUAL103READ_WRITE1READ_ONLY2015-03-05 16:02:25.1802015-03-05 16:02:25.18050TCP://qa-db-data1-1a.corp.membersuite.com:1433
6D1AB8EE-A006-4A8B-9FCC-8CB68CF5A6369C1B792A-0D8E-4AC3-8D00-BCE66CF954B3NULLQA-DB-DATA2-1ANULLTCP://qa-db-data2-1a.corp.membersuite.com:50220ASYNCHRONOUS_COMMIT1MANUAL102ALL2ALLNULLNULL50TCP://qa-db-data2-1a.corp.membersuite.com:1433
March 6, 2015 at 3:35 am
sherrerk (3/5/2015)
I add applicationintent=readonly to the connection string and it connects to the secondary with default database master.
When connecting to a readable secondary you need to specify the database name too
sherrerk (3/5/2015)
When I try to change to one of my replicated databases I get a message stating "The database <database name> is not accessible".
Because you're specifying this twice
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA1-1A' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
I think your script from above should be more this
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA1-1A' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA1-1A' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://qa-db-data1-1a.corp.membersuite.com:1433'));
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA2-1A' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA2-1A' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://qa-db-data2-1a.corp.membersuite.com:1433'));
ALTER AVAILABILITY GROUP [QA_AG] MODIFY REPLICA ON
N'QA-DB-DATA1-1A' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST =
('QA-DB-DATA2-1A')));
ALTER AVAILABILITY GROUP QA_AG ADD LISTENER 'QADB' ( WITH IP (('10.0.9.232','255.255.255.0')), PORT=1433)
sherrerk (3/5/2015)
Thanks for the help so far, that got me further down the path.
You're welcome, please don't forget to credit posts as a marked solution if they were helpful
I'll review the output and get back if there are any anomalies
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 6, 2015 at 8:41 am
I actually caught the error in the script after I posted it, but specifying the database in the connection string was the problem... That would have been done through the application connection string, but I was testing from management studio and was letting it go to the default database which was master. Anyway thanks for the help, everything is working exactly like it should now.
March 6, 2015 at 9:58 am
you are also specifying the same replicas secondary role twice, so be careful with that too.
I can see from the query results that luckily QA-DB-DATA2-1A is set to accept readonly connections as a secondary anyway, so this hasn't caused you an issue.
However, had it been set to not allow connections and you were relying on the script to change this you would then face an issue
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply