Azure SQL VM's in Availability Set

sydney 1

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:

SQLRG (name of resource group)
SQLVM1 (sql vm 1)
SQLVM2 (sql vm 2)
SQLAS (name of availability set)
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

vipullag-MSFT 24,636 Reputation points

2020-06-22T03:02:30.997+00:00

@sydney

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.

1 answer

msrini-MSFT 9,261 Reputation points Microsoft Employee

2020-06-21T20:57:12.12+00:00

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
Please sign in to rate this answer.
sydney 1 Reputation point

2020-06-21T22:21:43.43+00:00

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

msrini-MSFT 9,261 Reputation points Microsoft Employee

2020-06-21T22:28:59.387+00:00

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.

sydney 1 Reputation point

2020-06-22T17:37:14.143+00:00

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

msrini-MSFT 9,261 Reputation points Microsoft Employee

2020-06-22T18:55:49.657+00:00

I tried deploying a SQL VM and I do see the same. It is expected The one with type VM is where you RDP and configure.
SQL VM is just a interface where you can configure Key Vault and other stuffs. You can consider it like a PAAS interface for your SQL.

msrini-MSFT 9,261 Reputation points Microsoft Employee

2020-06-22T19:04:51.607+00:00

Yes it is expected. I do see the same in my subscription. Type - VM is basically the one which you RDP to. Type - SQL VM is like a PAAS interface of this VM created by the platform.

sydney 1 Reputation point

2020-06-22T19:32:32.337+00:00

So is it an actual virtual machine?

msrini-MSFT 9,261 Reputation points Microsoft Employee

2020-06-22T20:38:02.277+00:00

Type VM is actual Virtual Machine (IAAS). Azure creates SQL VM so you can integrate your SQL VM to other Azure services like Key Vault.

If this answers your question, mark it as answer to help other community members.
Sign in to comment

Share via

Azure SQL VM's in Availability Set

1 answer