SQL AOAG with listener ( without windows clustering)
-
Hello,
I am deploying SQL 2019 Enterprise edition with AOAG ( without windows clustering).
Steps done :
1. SQL installation done on 2 nodes
2. Service account is added as sysadmin on both servers
3. Enabled AOAG on both nodes
4. Database created & backup taken
5. Configuration of AG is done. Database added. Listener is created
6. SPN created in AD for the service account for both the nodes with server name and FQDN.
Issue : Not able to connect via Listener in SSMS. Even it is not pinging ( error : Destination unreachable). However, it is providing IP address when NSlookup to listener name is done
Kindly suggest
-
have you tried Listener,PORT ?
-
Yes, tried.. but failing
-
Check DC to see if the listener exists as a machine on the network.
-
have you ensured to see if TCP is enabled as a protocol on the SQL Server Configuration Manager
and are you sure there are no firewall rules preventing you from accessing the server/port?
-
Hello All,
Checked the DC, Listener name is created as Machine name and TCP/IP protocol is also enabled for the default instance.
I have added the listener name with its IP in windows host file ( rebooted afterward)., but the issue persists.
Listername name / network is not getting pinged and it is not working via telnet
I tried to configure listener with custom port & default port both but same error
-
A listener works by creating a DNS entry that points to your server. It should also create a listener entry within your SQL Server instances to tie the two together.
Are you sure that a AG listener is supported outside of a cluster.
You can check that both of things exist. If you Ping the listener name you should get a response showing it has hit the target. If the Ping fails you have no DNS entry and you will never be able to connect to your server.
You can check if SQL is listening for connections coming from the listener IP address. If it is not then you will not be able to connect.
BTW you should never add the service account as local admin (unless you are running SQL2000 or below). It is a very good way to help an escalation of privileges attack. The list of rights needed for the service account is well documented and can be found via Google etc.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
I just noticed, have you created a SPN for the listener name
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Hello All,
SQL AOAG with listener is supported from sql 2017 onwards but that will provided read scale capability. This will take away the HA mode of AOAG with listener being configured on individual node wise.
After discussion with relevant stake holder, we have changed the configuration and now SQL server AOAG is with Windows Clustering ( as their requirement is there for HA).
Pretty straight configuration for this.
-
Sometimes, you need to use : SQLserver instant name + port (1433). I would use another port if you can.
The error message could indicate you have a network connectivity / firewall / DC issue.
Note: As always, please check these settings in a test platform to make sure they work well.
DBASupport
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply