2

In my work environment, I have two database machines:

  • DB01
  • DB02

Each database machine has three SQL Server instances:

  • MSSQLSERVER
  • FBG
  • Central

I've managed to set up availability groups with SQL Server listener for the 1st SQL Server instance on both machines, and created SQL Server replication and it's working fine.

Now, I would like to set up a new availability group for the second SQL Server instance FBG on both servers, and I did, but the problem is when I create the listener for the second availability group it goes for the first availability group, I must type listener name / instance name to get it working.

I wish to type only the availability group listener name. For example:

  • the first availability group name is AVG01 and its listener AvgList01
  • the second availability group name is AVG02 and its listener AvgList02

When I connect to AVG02 using the name AvgList02 it connects me to AVG01 although I'm not using its listener name.

I must use it this way to get it working AvgList02\FBG where the FBG is the name of the SQL Server instance.

The question

Where is the issue, and how to fix it?

  • 5
    by AVG, do you mean Availability Groups? If so, that is more commonly referred to as AG. AVG is the SQL Keyword for the average function – Max Vernon Jul 16 '19 at 16:51
  • 2
    This seems like the intended behavior to be perfectly honest. I would not expect to be able to connect to a secondary instance of SQL without specifying the instance name. AG or not. Since MSSQLSERVER is the default, it makes sense to me that your AvgList01 does not require the instance name. Where AvgList02/FGB is your second instance. – Jacob H Jul 16 '19 at 17:45
  • @JacobH But there's possibility of different instance name of primary and secondary replica. – Rajesh Ranjan Jul 17 '19 at 6:06
  • It comes down to port numbers. What port are the instances running on? What port is the listener configured for? Etc. Check here for a more detailed rundown. – John Eisbrener Jul 18 '19 at 13:39
0

Let me try answering the three concerns;

  1. I wish to type only the availability group listener name. For example:

    the first availability group name is AVG01 and its listener AvgList01

    the second availability group name is AVG02 and its listener AvgList02

Create an alias named AvgList02 for your named SQL Server instance FBG As it is AvgList01 works so no need to create an Alias

  1. When I connect to AVG02 using the name AvgList02 it connects me to AVG01 although I'm not using its listener name.

This is probably as a result of the TCP/IP properties for your default instance protocol in SQL Server configuration manager. By default the Listen All parameter is set to Yes and if i am not mistaken that will allow all the IP addresses on this server (AvgList02 inclusive) be able to connect to this instance with the port specified in IPALL tab of the TCP/IP properties

  1. I must use it this way to get it working AvgList02\FBG where the FBG is the name of the SQL Server instance.

This is one of the available options for connecting to a named instance. You can either create an alias (easier option) or review your TCP/IP configurations if you do not want to connect like this

| improve this answer | |
0

When I connect to AVG02 using the name AvgList02 it connects me to AVG01 although I'm not using its listener name. Where is the issue, and how to fix it?

Consider port configuration play key role in general. In your case most probably following might happened.

  1. Both availability groups's primary replica is same node (DB01 or DB02)
  2. There must be SQL Browser service running in each SQL Server
  3. Default SQL ports (1433) must have been assigned for listeners

When client tries to connect on listener name the IP would be bounded based on DNS response (in multi sub-net first successful IP bound) which lead client connection to pointing to primary replica.

Connection from AvgList02 to AG01 (AVG01):
When the request hitting primary replica (after IP bound from listener name), next part is port (consider wrong port as per point 3) the connection would be established to the service which is running on particular port number (1433).

Connection from AvgList02\FBG to AG02 (AVG02):
Similar to above case (after IP bound from listener name), since the instance name (FBG) mentioned in connection SQL Browser would take action to bound the connection with respective SQL Service that match with instance name mentioned in connection.

Summary:
I personally recommend to use custom port for SQL Server especially when multiple instances running in same server, and disable SQL Browser service, let every connection hard-code custom port number in connection string to connect respective instance without depending on SQL Browser service.

For availability group implementation, consider custom ports and avoid port conflict in following sections:

  1. SQL ports (Default: 1433)
  2. Endpoint ports (Default: 5022)
  3. Listener ports (no default)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.