Sql-server – AlwaysOn Listener and DNS

It can connect from SQL 1 server, which is the primary for AG.

By "connect", do you mean it can ping AG-LISTENER from SQL1?

It sounds like what your problem might be is with the port number you chose for your listener. By choosing 5525, you are selecting a non-default port (1433 would be the default).

So when you try to connect to the listener, what does your connection string look like? I'm guessing it looks something like this:

data source = ag-listener; initial catalog = ...

You have two options here. You can either be explicit with your listener's port number:

data source = ag-listener,5525; initial catalog = ...

Likewise, if you're testing this out with SQL Server Management Studio (SSMS), then for the Connect to Server dialog box, instead of putting in ag-listener for the Server name text box, put in ag-listener,5525.

Or you can change the port that your listener is listening on to 1433 (read the below BOL reference before considering this change):

alter availability group YourAvailabilityGroupName
modify listener 'AG-LISTENER'
(
    port = 1433
);

It is worth noting when you can use the default port (1433). Take a look at this reference on BOL explaining when you can and can't use 1433 for the listener (excerpt copy/pasted below for reference):

You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

(portions omitted for brevity)

If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

EDIT: If the above isn't your problem (as seen from your comment below) then, after you look through the logs, I'd say the next best course of action is to start looking at the network traffic to see what is (and isn't) happening. You can use a network monitoring tool like netmon to accomplish this.

Another thing I'd do, and I realize you said the firewall isn't a problem, but I'd see if the port is actually listening (my favorite tool for this is portqry).

The reason that this fails outside of the internal Rackspace location is due to the URL Endpoints being set to a value that is not able to be connected through from your local environment.

I discuss this process at a high level in this blog post, however to quickly recap the point that needs to be made here:

The endpoint url is the address where the connection will be routed in order to connect and run their queries. [roughly speaking]

The URL is active directory specific FQDN, so I cannot use it as is from ourside the the racksapce domain. The ROR URL is some thing like "tcp://1234-db1.abc.intensive.int:1433"

This means that the client driver is going to be re-pointed to this address; if the address is not reachable then the driver won't be able to connect and you'll have a problem - which you happened to run into.

In your example, the client driver will get back "tcp://1234-db1.abc.intensive.int:1433" and attempt to connect to it like it would any other instance of SQL Server. Since it can't (as you've stated) you won't be able to be routed and your connection should then be on the primary. What I did was I changed the ROR URL for each node to public IP address instead of the FQDN/ Host-name, so when the request is sent to AG listener from outside it knows where to hand-off in the public facing IP. It looks like, if we have to connect from outside I have to use public IP (Eg: 72.32.XX.XX) as opposed to tcp://1234-db1.abc.intensive.int:1433 in the ROR.

While the IP may or may not need to be public (different architectures at different companies' may or may not require different things), it seems in your case it does... though I'm not a networking architecture guru so I can't comment on how/why this was implemented or any technical challenges observed.

Also when we deploy the application in the same domain as the db servers Active Directory domain "abc.intensive.int" would that pose a problem when apps try to connect to the db AG listener?

The domain shouldn't make a difference here, the real difference is whether or not your DNS is setup where it can do the lookups required to get the information (IIRC this would be a reverse lookup zone but don't quote me on that). If you can resolve the DNS name, it will work fine, if you can't it obviously won't... this is more of a question for your DNS/AD admins as they hold all the information to that kingdom.