1

As per below MSDN blog, I see there are 2 ways we can create load balancer listener .

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-classic-ps-sql-int-listener/

But I am NOT finding exact scenario where I need to use them? I would like external applications connect to my SQL server which is highly available (AlwaysOn). which load balancer i should configure?

Article here http://clusteringformeremortals.com/2015/01/01/step-by-step-how-to-configure-a-sql-server-failover-cluster-instance-fci-in-microsoft-azure-iaas-sqlserver-azure-sanless/ says that,

Once the cluster is configured, you will need to create the internal load balancer(ILB) which will be used for all client access. Clients that connect to SQL Server will need to connect to the ILB instead of connecting directly to the cluster IP address. If you were to try to connect to the cluster directly at this point you would see that you cannot connect to the cluster from any remote system. Even SQL Server Management Studio will not be able to connect to the cluster directly at this point

Update on How to configure it

I am using below script to create internal load balancer and it got succeeded.

       // Define variables
       $ServiceName = "XYZ"
       $AGNodes = "ABC01","ABC02"
       $SubnetName = "MYSUBNETNAME" 
       $ILBStaticIP = "10.249.XXX.XXX"
       $ILBName = "AGListenerLB" 

       // Create the ILB
       Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ILBName -SubnetName $SubnetName -ServiceName $ServiceName -StaticVNetIPAddress $ILBStaticIP

       // Configure a load balanced endpoint for each node in $AGNodes using ILB
       ForEach ($node in $AGNodes)
       {
           Get-AzureVM -ServiceName $ServiceName -Name $node | Add- AzureEndpoint -Name "ListenerEndpoint" -LBSetName "ListenerEndpointLB" -Protocol  tcp -LocalPort 1433 -PublicPort 1433 -ProbePort 59999 -ProbeProtocol tcp -ProbeIntervalInSeconds 10 -InternalLoadBalancerName $ILBName -DirectServerReturn  $true | Update-AzureVM
       }

Once above script is successfull then we need to run the below script

   # Define variables
   $ClusterNetworkName = "Cluster Network 1" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
   $IPResourceName = "DEVAG_10.249.XXX.XXX" # the IP Address resource name
   $ILBIP = “10.249.XXX.XXX” # the IP Address of the Internal Load Balancer (ILB)

     Import-Module FailoverClusters

If you are using Windows Server 2012 or higher, use the Get-Cluster Resource command. If you are using Windows Server 2008 R2, use the cluster res command. Both commands are commented out. Choose the one applicable to your environment and remove the # at the beginning of the line to convert the comment to an executable line of code.

     Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
     # cluster res $IPResourceName /priv enabledhcp=0 address=$ILBIP probeport=59999  subnetmask=255.255.255.255
0

External - Uses the public Virtual IP address of the cloud service that hosts the Virtual Machines. Use when you need to access the listener from outside the virtual network, including from the internet, i.e. public facing

Internal - Uses Internal Load Balancing (ILB) with a private address for the listener. Use when you only access the listener from within the same virtual network. This includes site-to-site VPN in hybrid scenarios, i.e. not public facing

  • Got you. this helped me now to choose internal load balancing. – kudlatiger Mar 29 '16 at 6:39
  • stacylaray, I have updated my question. – kudlatiger Mar 29 '16 at 6:42

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.