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 (http://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: http://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.
Another alternative to specifying port number in the client connection string is the SQL Server Browser service (http://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.
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.
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.
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.
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
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):
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.
Please explain in detail how you use this port: 98765
@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.
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="MyServer1\TESTSQL,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.
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)?