May 20, 2020 at 11:16 am
Hi,
I've got 2 x SQL 2017 CU20 (Dev edition) boxes in a Windows cluster with an AG and listener (non readable sync secondary). The DBs are synchronised and the AGL is contactable through SSMS on the primary server when I'm RDPd on.
The SPN is set up for the AGL name with the SQL server's service account, on the default 1433 port - which the listener is set to.
The listener is multi - subnet with static IPs on both subnets. I've tried multisubnetfailover=true in the SSMS (remote) connection to no avail.
When I ping the AGL by name I get the secondary server's subnet's IP back and no connection (unsurprisingly). If I ping -a the primary server's subnet's IP address I get "destination host unreachable".
The servers have Windows firewall enabled, but exclusions for port 1433 for all IPs from all IPs.
There are no errors on the SQL error log or the cluster logs.
Can anyone help? I'm stumped. Never had a similar problem before.
May 20, 2020 at 5:59 pm
When you say multi-subnet, I am thinking that is a multi-VLAN setup, correct?
Communication across VLANs may require additional configuration to allow the communication.
If it is really multi-subnet, I am assuming this is all on a single VLAN in which case you have to make sure your subnet mask is set properly or the servers won't be able to see each other.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 21, 2020 at 9:00 am
Not multi - VLAN, I believe; the servers are in AWS and VLANing isn't supported there. The subnet masks are set to make the multi - subnets, aren't they? So one will have say 1.2.3.x and the other, 1.2.4.x with mask of 255.255.255.0 or something equivalent?
The servers are 2016 and I installed SQL, it's IAAS not PAAS so I have full access to the SQL install.
May 21, 2020 at 1:19 pm
If you subnet mask is 255.255.255.0, then 1.2.3.x cannot see or talk to 1.2.4.x. with a subnet mask of 255.255.255.0 you can only communicate with 1.2.3.0 through 1.2.3.255.
I'd recommend reading up on networking and subnet masks.
The only other way for the machines to talk would be across the internet on their external IPs.
That being said, I've not worked much with cloud based solutions and not at all with AWS. My experience has been entirely in azure.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 28, 2020 at 7:36 am
I have not checked this situation, but a similar situation exists when a SQL Cluster is configured for multi subnets.
Have you checked the Windows Cluster dependency list for the AG listener addresses. If both subnets are linked by an AND condition this could be your problem. For a SQL Cluster you would replace the AND with an OR, it may be you need to do the same for an AG listener.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply