0

I have 2 instances of SQL Server on two Windows Servers, namely:

  • D-MSSQL01-DE (v.2014)
  • D-MSSQL02-DE\SQL2019 (v.2019)

And seperate server for AGs:

  • D-MSSQL02-DE (v.2014)
  • D-MSSQL02-DE\SQL2019 (v.2019)

Listeners:

  • D-AGSQL01-PP (v.2014 listener)
  • D-AGSQL04-PP (v.2019 listener)

I'm after setting up new AG and it's listener on SQL server 2019 with new IP:port assigned by networkteam. Now, when I connect to this newely created listener, somehow under "Always On High Availability" I see 2014 AG instance! Why is this connecting to SQL2014 instead of 2019?

enter image description here

11
  • So you created new instance of SQL Server D-MSSQL-1-DE\SQL2019 and also a new listener of this ?. Can you run select @@version on this instance and post the output
    – Shanky
    Commented Nov 14, 2019 at 11:32
  • It's 2019 Microsoft SQL Server 2019. Shorter version of the output: "Microsoft SQL Server 2019 (RTM-GDR) (...) - 15.0.2070.41 (X64) on Windows Server 2016 Standard 10.0 (...)"
    – Jakubee
    Commented Nov 14, 2019 at 11:36
  • Seems strange, are you giving port no while connecting via SSMS ?
    – Shanky
    Commented Nov 14, 2019 at 11:41
  • No, never. Just Host name. And beside, on SQL Server 2019 Properties "Service" tab I see "Host Name" being "D-MSSQL01-DE"... that's odd.
    – Jakubee
    Commented Nov 14, 2019 at 11:45
  • Ok try connecting with AGName;port_no and see if that works.
    – Shanky
    Commented Nov 14, 2019 at 11:49

2 Answers 2

0

Both your 2014 & 2019 instances are running on the same hardware--just different ports.

While you don't specify which ports they are using, the 2014 instance is the default instance, so it's probably running on the default port (1433), and the named instance is running on some other port (who knows... You'd have to check SQL Server Configuration Manager). When you connect to the named instance, the SQL Browser Service helps with name/port resolution, or you can specify the port in the connection string.

It sounds like your AG Listener for your 2019 instance is potentially also using a non-default port (You'd have to check the Listener configuration in SSMS... If both listeners are on port 1433, keep reading). Unfortunately, the SQL Browser Service can't do it's magic on AG Listener names.

If you look at the related documentation, it explains that using a non default Listener port means you need to include the port in your connection string:

You can also designate a non-standard listener port; however this means that you will also need to explicitly specify a target port in your connection string whenever connecting to the availability group listener.

SHOULD you be using a non default port? In your case, yes. You have to. In fact, if you are running both listeners on the same port, that would be the problem! The documentation continues:

If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

In your case, you are running multiple services (SQL 2014 & SQL 2019) on the same cluster node. This creates a conflict where both sqlservr.exe processes are both trying to listen on 1433, thus causing a port conflict. In this case, you'll need to change one of the listeners to use a different port, and also specify that port when connecting (Ex to connect to D-AGSQL04-PP on port 56789, specify server name in the format D-AGSQL04-PP,56789)

1
  • Agreed. The listener ports need to be different. And listenername,Port should be the ideal way of connecting. Commented Nov 14, 2019 at 12:02
0

Listener is using port 1436. From "TCP/IP Properties" I see the IP per instance are the same BUT the ports differ. v2014 uses 1433 BUT 2019 instance uses 1435 (should be the same as listener?)

It seems the same issue with port number 1433:

When you connect using listener without port number, by default it try to establish connection on default port 1433. Since the listener is not hosting any SQL Service it has to redirect communication to available primary replica. When it reach to primary replica with port number 1433

Consider custom ports always better choice in availability implementation (in following ares), and always avoid port conflict (usage of same ports) while there are multiple instances in one VM/SERVER:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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