AlwaysOn Availability Groups, Listener, Named Instances, Port Numbers, etc.

  • [アーティクル]

Author: Sanjay Mishra

Reviewers: David P. Smith (Active Network), Mike Ruthruff (Bungie Studios), Matt Neerincx, Luis Carlos Vargas Herring, Piyush Ranjan, Steven Schneider

 

My job provides me opportunity to work across different sections of customers – some customers who like all defaults (default instances, default port numbers, etc.), and some customers who don't like defaults at all. Customers who don't like defaults want full control over naming instances and choosing the port number the SQL Server service listens on.

As you know, one can have a Default SQL Server instance (MSSQLSERVER) or a named SQL Server instance. The Default SQL Server instance listens on port 1433, by default. Some customers don't like the port number to be known to the whole world, and may like their Default instance listen on a different custom port number.

A named SQL Server instance listens on a dynamic port, by default. The dynamic port is selected by the operating system, and therefore, may or may not meet the corporate policies of some customers, who may want specific applications use specific ports. In these cases, you may like to explicitly specify which port your SQL Server instance listens on. You can do this using the SQL Server Configuration Manager (https://technet.microsoft.com/en-us/library/ms177440.aspx), as shown in Figure 1.

 

 

 

 

 

 

 

 

 

 

 

 

Figure 1: Specifying a static port number for a SQL Server instance

More information on this in the KB article: https://support.microsoft.com/kb/823938.

When you specify a non-default static port number for a SQL Server instance, as in Figure 1, the connection string must specify the port number (assuming SQL Server Browser is not running, more on this a bit later), for example, port 54145 below:

Data Source="MyServer1\TESTSQL,54145";Initial Catalog=AdventureWorks; Integrated Security=True; ...

If you don't want to specify the port number in the application connection string, you can use SQL Server aliases on the client.

It is important to note that, if you have a Windows firewall on the SQL Server machine, you must open the corresponding port for inbound connections.

SQL Server Browser

Another alternative to specifying port number in the client connection string is the SQL Server Browser service (https://technet.microsoft.com/en-us/library/ms181087(v=SQL.105).aspx). SQL Server Browser service is running, the client can connect to the SQL Server instance without specifying the port number, such as:

Data Source="MyServer1\TESTSQL";Initial Catalog=AdventureWorks;Integrated Security=True; ...

It is important to note that, the SQL Server Browser service runs on the UDP port 1434, and if you have a Windows firewall on the SQL Server machine, you must open the UDP port 1434 for inbound connections. That is one more port to open in the firewall, and one more well-known port number.

AlwaysOn Availability Groups

The port assignments become a bit involved with AlwaysOn Availability Groups, because, you now deal with port numbers for multiple instances of SQL Server (AG primary and AG secondaries), as well as a port number for the AG Listener (Figure 2).

Figure 2: Specifying a port number for an AG Listener

You can choose the port numbers in many different ways.

  • Specify different port numbers for each instance and a different port number for the Listener.
  • Specify the same port number for all the instances (as a standard), and a different port number for the Listener.
  • Specify the same port number for all the instances as well as the Listener. This is possible because the IP address of the SQL Server instance is different from the IP address if the Listener.

The following examples illustrate the above scenarios. In each of the following examples, we have a 3-node Availability Group – each machine running a named SQL Server instance, and each machine has Windows firewall enabled.

Different port numbers for each instance (AG replica), and a different port number for the Listener

Figure 3 shows a SQL Server AlwaysOn AG deployment for High Availability and Disaster Recovery. Each of the three SQL Server instances are listening on different ports (12345, 23456, 34567 respectively), and the AG Listener is listening on port 98765.

Figure 3: SQL Server AlwaysOn AG HADR configuration, with different port numbers for each instance and a different port number for the AG Listener

In this case, if you have a Windows firewall on the SQL Server machines, you will need to allow two ports on each machine – the port on which the SQL Server instance is running (12345, 23456, 34567 respectively), as well as the port 98765 through the firewall on each machine. The application needs to specify the AG Listener port number while connecting to the Listener. For example, the connection string will look like:

Data Source="TestAGListen,98765";Initial Catalog=AdventureWorks; Integrated Security=True;…

Unlike connecting to a SQL Server instance, if you are connecting to the AG Listener, the SQL Server Browser doesn't help you omit the port number in the connection string. For the remaining examples, we will ignore the SQL Server Browser.

Same port number for all the instances (AG replicas), and a different port number for the AG Listener

Figure 4 shows a SQL Server AlwaysOn AG deployment for High Availability and Disaster Recovery. Each of the three SQL Server instances are listening on port 12345, and the AG Listener is listening on port 98765.

The client connection string for Figure 4 will be similar to the client connection string for Figure 3.

Figure 4: SQL Server AlwaysOn AG HADR configuration, with the same port number for all the SQL instances, and a different port number for the AG Listener

Same port number for all the instances as well as the Listener

Some customers use the same port number for the SQL Server instances as well as for the Listener (Figure 5, so that they need to allow only one port through the firewall.

Figure 5: SQL Server AlwaysOn AG HADR configuration with the same (non-default) port number for SQL Server instances and the AG Listener

In Figure 5, all the instances as well as the Listener use the same non-default port number (12345). Figure 6 shows an example where all the instances as well as the Listener use the same default port number (1433). In the example of Figure 6, the clients don't need to specify port number in their connection strings.

Data Source=TestAGListen;Initial Catalog=AdventureWorks; Integrated Security=True;…

Figure 6: SQL Server AlwaysOn AG HADR configuration with the same port number (1433) for SQL Server instances and the AG Listener

 

Since the SQL Server Browser doesn't help in the case of connecting to the AG Listener, some customers follow the following practice (Figure 5):

  • Don't turn on the SQL Browser service
  • Use the same non-default port number for SQL Server instances as well as for the Listener
  • Specify the port number whether connecting to a SQL Server instance, or connecting to the AG Listener

This practice may or may not work for all customers, but maintains consistency of the connection strings with or without the SQL Server Browser, with default or non-default port numbers, or whether connecting to the SQL Server instance or the AG Listener.

Comments

  • Anonymous
    February 07, 2014
    Please explain in detail how you use this port: 98765

  • Anonymous
    February 28, 2014
    @Hoss, In the specific example shown the Listener uses the port 98765. Figure 2 shows how to specify a Port number for the Listener. Figure 2 is showing port 1433, you can update it with 98765.

  • Anonymous
    March 04, 2014
    Sanjay, I am a regular reader of your great articles. QQ. The connection string that you mentioned into this article includes the port number as well as instance name. I may have forget this, but can you confirm that connection string will require both INSTANCE NAME and PORT # to connect to that instance ?       Data Source="MyServer1TESTSQL,54145";Initial Catalog=AdventureWorks; Integrated Security=True; This is unlike how we connect the SQL Server named instance using SSMS by using just the HOSTNAME,PORT# combination. Thanks Prashant Thakwani

  • Anonymous
    March 14, 2014
    Great explanation

  • Anonymous
    April 02, 2014
    Nice Post Sanjay! Let's say...All the three named Instances are running on different Non_default Static ports and Listener is running on 1433. How would you connect to the Listener in this case(Assuming Browser Service is running)?

  • Anonymous
    June 25, 2014
    There seems to be some confusion surrounding the ability to choose the listener ports (as opposed to taking the defaults).  If you do not take the defaults, I believe this will cause issues down the road.  Just my 2 cents. J.g. www.mssqlonlinecom

  • Anonymous
    September 06, 2014
    Finally an explaination about how the listener relates to the connection string

  • Anonymous
    March 30, 2015
    Excellent explanation to understand the differences between the ports and services. Every time that I step up in a AlwaysOn configuration I check the port and they're always configured with the default port 1433 Nice article as always!

  • Anonymous
    June 07, 2015
    Hi, I am newbie. I just have a small question(may be silly) but I am just making sure, we can do AlwaysOn setup by using all default sql instances unlike traditional sql clustering, right???

    • Anonymous
      June 24, 2016
      Hi Prasad - Both for Availability Groups and Failover Cluster Instances a default SQL Server instance can be used (vs named instance). For Availability Groups the instance can be a standalone. Hope this helps clarify.

  • Anonymous
    March 17, 2016
    I don't think it's a great idea to use examples that could never work, even if hypothetical. Repeated reference to port 98765, although hypothetical, is not usable, similar to using a hypothetical IP Address of 567.345.789.456.

  • Anonymous
    March 18, 2016
    Hi,I have two installed two sql server instances, both have separate port numbers.I have also created two listeners, again, both have different port numbers.When I connect to sql server using the listener names, listener 1, connects to database instance 1, but when I connect into sql server management studio using listener 2, it connects me through to instance 1. I can only use listener 2 correctly if I use the port number on the end. Is there another way around this?Very desperate for a solution.Thanks Charlene

  • Anonymous
    August 24, 2016
    Good one to know these aspects and thanks for sharing.

    • Anonymous
      December 21, 2016
      If possible different IP range between Cluster IP and SQL Listener ?Thanks

  • Anonymous
    March 03, 2017
    I have a multi-tenant server that has 4 SQL instances installed. The first instance (INST_1), setup was fine. The second instance (INST_2) failed while trying to add the listener. I tried to use the same as the first and creating a second. Is this not available to servers with more than one instance?

  • Anonymous
    April 03, 2017
    Hi Sanjay,Is it possible to use the named-instance name instead of the port in the connection string for the AG listener (e.g. LSTNR_CLU1_AG1\SQL01) if all the named instances constituting the AG run on different nodes of the WSFC and have the same instance name)?Cheers,Marc