{"id":92,"date":"2014-11-26T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/connecting-to-an-availability-group-listener-with-sql-server-management-studio"},"modified":"2026-03-18T21:51:25","modified_gmt":"2026-03-18T21:51:25","slug":"connecting-to-an-availability-group-listener-with-sql-server-management-studio","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2014\/11\/26\/connecting-to-an-availability-group-listener-with-sql-server-management-studio\/","title":{"rendered":"Connecting to an Availability Group Listener with SQL Server Management Studio"},"content":{"rendered":"<h2>Background<\/h2>\n<p>When a cluster has an Availability Group installed on it, there is an optional listener which is\u00a0used to manage connections through fail-over events. This listener has an IP address assigned to it and, when the Availability Group fails over within the same sub-net, the listener simply comes online on the new node. That node will now own the IP address and all connections going to the virtual network name, of the listener, will go to the correct node. This behavior is the same as\u00a0using a SQL Server Fail-over Cluster Instance. There are, however, two features which the Availability Group listener has, which complicate things to a small degree.<\/p>\n<p>The first feature is\u00a0the ability to off-load read operations to a read-only replica. To coordinate this, there is a read-only routing list configured in SQL Server and the Availability Group Listener will accept a new connection parameter called\u00a0<em>ApplicationIntent<\/em>. You can read more on that parameter <a href=\"http:\/\/www.sqlhammer.com\/blog\/how-to-configure-sql-server-2012-alwayson-part-7-of-7\/\">here<\/a>. For now, I will simply state that passing in READONLY or, the default, READWRITE, will tell the listener to route you to different nodes of the cluster to meet your connection needs.<\/p>\n<p>The second feature is the ability to use a single virtual network name in multiple sub-nets. An Availability Group Listener can have multiple IP addresses, but only one active at any given time. When you fail-over to a different sub-net, the active IP is\u00a0disabled and the other sub-net\u2019s IP will come online. This is wonderful, but causes some issues due to the behaviors of your\u2019s or your server\u2019s local DNS cache and\u00a0<em>time-to-live<\/em> settings. To cut down time during a fail-over, the\u00a0<em>MultiSubnetFailover<\/em> parameter\u00a0was created. Once again, please read <a href=\"http:\/\/www.sqlhammer.com\/blog\/how-to-configure-sql-server-2012-alwayson-part-7-of-7\/\">here<\/a> for more detail.\u00a0This parameter allows the client to attempt a connection on multiple IP addresses at once. The first one to connection successfully wins. This works because the listener cannot have more than one IP address online at a time, so you will always get the active IP without any complications from an out-of-date DNS cache.<\/p>\n<h2>The SSMS challenge<\/h2>\n<p>So you have all of your applications and services using the ApplicationIntent and MultiSubnetFailover parameters. They\u2019ve run successfully and you\u2019ve conducted a few successful fail-over tests. So what is the problem?<\/p>\n<ul>\n<li>Sometimes, when I use SSMS to connect to my listener, it hangs for 15 seconds and then fails to connect. Sometimes it works, though.<\/li>\n<\/ul>\n<ul>\n<li>When I connect to the listener, I always hit the READ\/WRITE node and the other DBAs have been upset with the read load I\u2019m putting on the primary node.<\/li>\n<\/ul>\n<ul>\n<li>I can\u2019t save these parameters anywhere\u2026 I\u2019ll explain this problem\u00a0more down below.<\/li>\n<\/ul>\n<h3>Solving problems #1 and #2<\/h3>\n<p>To guarantee you will connect on the correct IP address and get routed to the correct node, you need to pass in the parameters mentioned above. In SSMS, you will need to follow these steps to connect.<\/p>\n<p>Enter your listener name and then select\u00a0<em>Options<\/em>.<\/p>\n<p><a href=\"\/wp-content\/uploads\/SSMS-2012-AG-connect-1.jpg\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/SSMS-2012-AG-connect-1.jpg\" alt=\"\" \/><\/a><\/p>\n<p>Setting your\u00a0<em>Connection time-out<\/em> to 30 seconds can be helpful here. It is not required to connect to an Availability Group listener but, if you forget to pass in the\u00a0<em>MultiSubnetFailover<\/em> parameter, it will allow for a successful connection, even if a bit delayed.<\/p>\n<p><a href=\"\/wp-content\/uploads\/SSMS-2012-AG-connect-2.jpg\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/SSMS-2012-AG-connect-2.jpg\" alt=\"\" \/><\/a><\/p>\n<p>Next, navigate to the\u00a0<em>Additional Connection Parameters<\/em> tab and entert the connection parameters as seen below.<\/p>\n<p><a href=\"\/wp-content\/uploads\/SSMS-2012-AG-connect-3.jpg\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/SSMS-2012-AG-connect-3.jpg\" alt=\"\" \/><\/a><\/p>\n<h3>Dealing with problem #3<\/h3>\n<p>Using the above method will connect you to your listener as expected. Problem #3 is the fact that these connection parameters cannot be saved anywhere. SQL Server Central Management Server and local Registered Servers do not have a means of storing this information. In my opinion, this is a major over-sight by the SSMS development \/ project management team and the feature has not been added in SSMS 2014 either. Please take the time to vote up this <a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/786323\/ssms-sql-server-management-studio-2012-missing-connection-properties-for-availability-groups\">connect.microsoft.com ticket<\/a> so that Microsoft will see our desire for this feature.<\/p>\n<p>With that being said, I have no solution for making this process easier, except for the connection time-out trick, that I mentioned above. After about 20 seconds of trying to hit the wrong IP address, the connection will find\u00a0the correct one\u00a0succeed. This means that, if you are OK hitting the primary replica, you don\u2019t need these parameters to make a connection. That time-out setting is persisted, so you won\u2019t have to change it each time you try to connect.<\/p>\n<hr \/>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/hammer-derik-172x140.png\" alt=\"\" \/><strong>Written by Derik Hammer of <a href=\"http:\/\/www.sqlhammer.com\/\">SQL Hammer<\/a><\/strong><\/p>\n<p>Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/high-availability\/\">high-availability<\/a>, <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/general\/disaster-recovery\/\">disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n<p>Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started <a href=\"http:\/\/www.sqlhammer.com\/\">SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n<p>Derik is the owner and lead author of SQL Hammer, a <a href=\"http:\/\/www.sqlhammer.com\/\">Microsoft SQL Server resource<\/a>.<\/p>\n<p>For more information, visit <a href=\"http:\/\/www.sqlhammer.com\/\">http:\/\/www.sqlhammer.com<\/a>. Follow Derik on <a href=\"http:\/\/twitter.com\/SQLHammer\">Twitter<\/a> for <strong>SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Background When a cluster has an Availability Group installed on it, there is an optional listener which is\u00a0used to manage connections through fail-over events. This listener has an IP address assigned to it and, when the Availability Group fails over within the same sub-net, the listener simply comes online on the new node. That node [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":531,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-92","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/92","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=92"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/92\/revisions"}],"predecessor-version":[{"id":355,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/92\/revisions\/355"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/531"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=92"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=92"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=92"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}