Working with Microsoft SQL Server AlwaysOn Availability Groups

David_Fergen — Fri, 05 Feb 2021 21:59:07 GMT

To connect to an AlwaysOn availability group, we specify the hostname of the AlwaysOn listener in the SQL Server source endpoint, instead of a specific server in the group. If the AlwaysOn listener is listening on a non-default port we can add the port number after the hostname, e.g. pugsql_dsx,5012

The AlwaysOn listener will redirect connections to specific servers, and if the specific servers are also using non-default ports we need to specify this when configuring the AlwaysOn availability group, by executing the following command (note that we can specify a port after the replica name) ...

ALTER AVAILABILITY GROUP [{ag_name}] MODIFY REPLICA ON '{replica_name}' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL='TCP://{replica_name}:{Port}'))

The section above is explained in the User Guide Chapter 8 "Adding and Managing Source Endpoints", under the section "Using Microsoft SQL Server as a source" .. "Working with Microsoft SQL Server AlwaysOn Availability Groups".

I have verified that in order to allow a listener on a specific port (non-default port) we need to define the Alias name in order to force the connection to use specific TCP

We can give this alias name in the program name is CLICNFG.EXE and is usually under c:\windows\system32

Here are the steps as well:

Step 1: Go to windows explorer open the path C:\Windows\System32 and open application “cliconfg”

Step 2: It will redirect SQL Server Client Network Utility tab. Then go to Alias tab

Step 3: Click on the Add button and select TCP/IP from Network Libraries put the sever name from connection string followed by instance name (use back slash after hostname) for e.g. servername = PUGSQL3.ug.sbicdirectory.com and uncheck “Dynamically determine port” checkbox and put the port number you want to use and put an Alias name which you want to give in Server Alias name and click on ok button.

NOTE: May have to use fully qualified name of SQL Servers.

You can also follow below link for more information on this.

https://sqlandme.com/2011/05/05/create-sql-server-alias-cliconfg-exe/

Here is a query you can use to find out if named pipes is enabled:

SELECT 'Named Pipes' AS [Protocol], iif(value_data = 1, 'Yes', 'No') AS isEnabled FROM sys.dm_server_registry WHERE registry_key LIKE '%np' AND value_name = 'Enabled' UNION SELECT 'Shared Memory', iif(value_data = 1, 'Yes', 'No') FROM sys.dm_server_registry WHERE registry_key LIKE '%sm' AND value_name = 'Enabled' UNION SELECT 'TCP/IP', iif(value_data = 1, 'Yes', 'No') FROM sys.dm_server_registry WHERE registry_key LIKE '%tcp' AND value_name = 'Enabled'

Re: Working with Microsoft SQL Server AlwaysOn Availability Groups

SoonerWatts — Wed, 23 Aug 2023 21:31:13 GMT

I'm trying to resolve an issue we have where the target is an AlwaysOn setup. This article has come up a few times and I just want to confirm that the latter scenario only applies when a nonstandard port is being used. The section talking about running the cliconfg to set the Alias, etc.

article Working with Microsoft SQL Server AlwaysOn Availability Groups in Official Support Articles

Working with Microsoft SQL Server AlwaysOn Availability Groups

Re: Working with Microsoft SQL Server AlwaysOn Availability Groups