question

sidneyl avatar image
0 Votes"
sidneyl asked msrini-MSFT commented

Azure SQL VM's in Availability Set

Hello,

I have a question regarding Azure Availability Set and specifically related to Azure SQL VM's. Assume I have the following configured in Azure:

  1. SQLRG (name of resource group)

  2. SQLVM1 (sql vm 1)

  3. SQLVM2 (sql vm 2)

  4. SQLAS (name of availability set)

  5. SQLLB (name of load balancer)

I am omitting the networking stuff and showing what's relevant for my question. The two vm's are members of domain
and have private static IP's and belong to SQLAS. I can RDP into VM1 and VM2; open SSMS and connect to these sql servers.

Here's my question: Can I use the frontend IP, assigned in the load balancer, and use it as an endpoint to connect to
these sql server VM's? From what I've read, the whole idea behind load balancer to load balance the traffic between member VM's. If problems are detected on SQLVM1, Azure will route traffic to SQLVM2. When I tried to connect using the frontend IP in SSM, i was not successful. Did I miss something?

Thanks















azure-sql-virtual-machinesazure-load-balancer
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@sidneyl

Please 'Accept as answer' if the provided information is helpful, so that it can help others in the community looking for help on similar topics.

0 Votes 0 ·

1 Answer

msrini-MSFT avatar image
0 Votes"
msrini-MSFT answered msrini-MSFT commented

Hi @SydneyLuu-9481,


Load balancer works on Layer 4. So you can load balance traffic between VMs of your backend pool over a TCP or UDP port.


If your SSMS ports on your VM is listening on port 1433, then create a rule in LB to load balance on port 1433. Note: Load balancing is active - active. So you cannot control to which VM you want to SSM to. Choose Load distribution as Client IP, so as long as client IP is same you will always reach the same VM. Make sure you configure health probe.


If you don't want to load balance and simply get rid of the public IPs of your SSMS server and access each server via LB's IP separately, then you can go for Inbound NAT rules in your LB.



Regards,
Msrini



· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Msrini,

I think I've done exactly just what you described.

In my LB, this is how I've configure these:

Health probe -> protocol =TCP, port = 1433
Load balance rule -> protocol =TCP, port = 1433, backend port = 1433

Initially I had port = 80 in health probe. Since it didn't work, I decided to change it to 1433.

Is there anything else I may have missed or need to do? Yes, I understand the setup it's Active - Active. All I wanted to do
now is to proof to myself this Azure High Availability works before I continue to do other installs on these SQL Server VM's.


Thanks


0 Votes 0 ·

Even after configuring the probes, if you getting connection timeout, make sure you allowed port 1433 on the NSG rule which you have associated in the backend VM. Only then the probe will be successful and traffic will be forwarded.

0 Votes 0 ·

Hi msrini-MSFT,

My availability set & load balancer are working now. There was an Azure firewall change we had to make
regarding my VM2. I tested the 'failover' and it worked!

I do have one last question regarding this environment I've set up. When I review my SQLRG, I see the following two
resources created when I initially deployed these two SQL VM's. For example, for VM1, I see

Name Type
------------------------------------------
VM1 Virtual Machine
VM1 SQL Virtual Machine

Obviously since I've deployed two Azure SQL VM's, I also have these same resources for my other VM2. When I rdp in, Virtual
Machine is the type I need. What is "SQL Virtual Machine"?

Thanks




0 Votes 0 ·
Show more comments