Sql-server – ODBC connection for testing Availability Group Listener

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

There really isn't a concept of a connection string in the Availability Group. The AG is based on a Windows Cluster and a Virtual Name in the cluster that is of the type of Availability Group Listener. This listener is configured in Management Studio in the Availability Groups container in Object Explorer.

The example that was given above is correct. SQL01 and SQL02 are actual SQL servers that are individual. They happen to be instances of SQL on these nodes, but the nodes (or computers) participate in the Windows Cluster. When you configure Availability Groups, you specify the Instances that will participate and create a listener which becomes your server name or Data Source in your connection strings for your application. To create the listener you have to have a Name and IP.

SQL Management Studio will not have a connection string for the AG. You can specify the AG Listener name in your connection in Management Studio, but it would be the AG name just like your web.config or any other application file config that needs to connect to SQL Server.