• 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