This post has been republished via RSS; it originally appeared at: DataCAT articles.
First published on MSDN on Feb 03, 2014Authored by Sanjay Mishra
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.
SQL Server Browser
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.
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.