Did you create the listener first via TSQL then execute the powershell script? I followed Microsoft documentation and was under the impression the powershell script will also create the listener inside MSSQL. But it seems it does not. Am I right about it? Thanks.
This video is extremely helpful! Thank you for that. Question: what would happen if you didn't do the very last step which is to link the Load Balancer to the Windows Cluster? The step that you did with the Powershell. Will the SQL Server AlwaysOn AG still work?
It will not failover as cluster wouldn’t know what to do if primary goes down. You will have to go in and bring the resource online by running more commands.
Reason I asked is I have a setup very similar to yours in Azure. The Windows Server 2019 cluster contains two Azure SQL VM's. I have a Load Balancer and Availability Group. I have not run the Powershell script yet like you did. Let's call my vm's SQL1 and SQL2. SQL1 being the primary. When I stopped the sql server service on SQL1, it failed over to SQL2. I was able to connect SQL2 using the cluster virtual IP (the one application uses). Then I manually failed back from sql2 to sql1. Now SQL1 is the primary again. No issue whatsoever. I only use the virtual IP to connect to my sql server. When I do SELECT @@SERVERNAME, I always get right the server. I did these tests using SSMS on-prem. Also I have no issue connecting to SQL1 or SQL2 regardless of its role using the server IP (or the virtual IP). That's why I am confused about the Powershell to link LB and AG in your setup. I understand the reason you did this was because you said in your video, you could only connect to the primary. You weren't able to connect to your secondary and this PS script fixes that and not that it fixed failover functionality in your setup if I'm understanding your video correctly. I was under the impression the PS script is needed to make BOTH LB and WSFC working together correctly. So I'm still researching if I need to run your PS script in my setup.
Thanks for this video! Do we always have to complete the AG Listener and then setup Load Balancer in Azure. We have been trying to get this working, our cloud folks configured the load balancer first and now we are trying to setup AG listener and the ip fails to match. Any thoughts?
Disable the load balancer, configure AG and reenable the load balancer - it should work.if that doesn’t work, just remove the IP part of the load balancer and then read it.
Hello Bhai, This is Mainoddin Khazi , I have configured always on successfully, But due to some reason still not able to access the listener from secondary replica. Please guide me or help on this
Please be sure to go over the steps. Especially these. 1. Make sure in your Resource Group, All your sql servers involved in cluster is there? 2. You have communication between your SQL Servers and DNS Server/s 3. Please be sure to disable firewall between all servers to see if it resolves the issue, you can later on open the required port if you need to have firewall on. 4. Your FrontEnd IP address in your LB is exactly the same as your AG Listener. Now - since it doesn't produce any log, you might want to go through Event Viewer of both servers and DNS to see what might be causing this blockage from one server to another to give me clue of if something is missed in the setup. 5. If nothing works, I have to take a look at your environment Live or you can provide me Azure Portal and UserName and password for me to go in and take a look. Please do not share your portal and user information in comments, just email at below Email ID, you can delete the user once issue is resolved. Please share stuff on Raza@TechbrothersIT.com
@@TechBrothersIT Thank you so very much for your response. I was able to sort this issue by adding Probe port(80) in the inbound rule on both the SQL VMs. You guys are doing great job. Thank you again :)
This looks different from MS documentation I referred. Should the IP address for LB be dynamic when we create it? Or shouldn’t matter at all? 🧐
Thank u so much for this video...it's really helped!
I'm great fan of this channel. Learnt alot from this Techbros.
Awesome , it helped ..... especially the load balancing .
Glad to hear that it was helpful.
Did you create the listener first via TSQL then execute the powershell script? I followed Microsoft documentation and was under the impression the powershell script will also create the listener inside MSSQL. But it seems it does not. Am I right about it? Thanks.
This video is extremely helpful! Thank you for that. Question: what would happen if you didn't do the very last step which is to link the Load Balancer to the Windows Cluster? The step that you did with the Powershell. Will the SQL Server AlwaysOn AG still work?
It will not failover as cluster wouldn’t know what to do if primary goes down. You will have to go in and bring the resource online by running more commands.
Reason I asked is I have a setup very similar to yours in Azure. The Windows Server 2019 cluster contains two Azure SQL VM's.
I have a Load Balancer and Availability Group. I have not run the Powershell script yet like you did. Let's call my vm's SQL1 and SQL2. SQL1 being the primary. When I stopped the sql server service on SQL1, it failed over to SQL2. I was able to connect SQL2 using the cluster virtual IP (the one application uses). Then I manually failed back from sql2 to sql1. Now SQL1 is the primary again. No issue whatsoever. I only use the virtual IP to connect to my sql server. When I do SELECT @@SERVERNAME, I always get right the server. I did these tests using SSMS on-prem. Also I have no issue connecting to SQL1 or SQL2 regardless of its role using the server IP (or the virtual IP). That's why I am confused about the Powershell to link LB and AG in your setup. I understand the reason you did this was because you said in your video, you could only connect to the primary. You weren't able to connect to your secondary and this PS script fixes that and not that it fixed failover functionality in your setup if I'm understanding your video correctly. I was under the impression the PS script is needed to make BOTH LB and WSFC working together correctly. So I'm still researching if I need to run your PS script in my setup.
One more note to add. My LB frontend IP is my AG IP. I did hear you mention this in your video.
Instablaster
Where can we get that PS script pls?
Thanks for this video! Do we always have to complete the AG Listener and then setup Load Balancer in Azure. We have been trying to get this working, our cloud folks configured the load balancer first and now we are trying to setup AG listener and the ip fails to match. Any thoughts?
Disable the load balancer, configure AG and reenable the load balancer - it should work.if that doesn’t work, just remove the IP part of the load balancer and then read it.
I am talking about frontend IP configuration- leave the rest same and the reconfigure front end IP
Hello Bhai, This is Mainoddin Khazi , I have configured always on successfully, But due to some reason still not able to access the listener from secondary replica. Please guide me or help on this
Tried all the steps as mentioned, but still having issues connecting from secondary node. Kindly provide your assistance
Please be sure to go over the steps. Especially these.
1. Make sure in your Resource Group, All your sql servers involved in cluster is there?
2. You have communication between your SQL Servers and DNS Server/s
3. Please be sure to disable firewall between all servers to see if it resolves the issue, you can later on open the required port if you need to have firewall on.
4. Your FrontEnd IP address in your LB is exactly the same as your AG Listener.
Now - since it doesn't produce any log, you might want to go through Event Viewer of both servers and DNS to see what might be causing this blockage from one server to another to give me clue of if something is missed in the setup.
5. If nothing works, I have to take a look at your environment Live or you can provide me Azure Portal and UserName and password for me to go in and take a look. Please do not share your portal and user information in comments, just email at below Email ID, you can delete the user once issue is resolved.
Please share stuff on Raza@TechbrothersIT.com
@@TechBrothersIT Thank you so very much for your response.
I was able to sort this issue by adding Probe port(80) in the inbound rule on both the SQL VMs.
You guys are doing great job. Thank you again :)
@@tejeshpujari5595 Thanks Bro. That helped