How to configure SharePoint + SQL on Azure VM to use Internal Load Balancer

Task

While creating SharePoint 2016 MinRole farm in Azure with altogether 14 servers, it took me a while to understand how to configure SQL Server connection on SharePoint servers. In more simple farms without SQL Server AlwaysOn Availability Groups it was straightforward to simply set SQL Alias using cliconfg tool on each SharePoint server. However now that there would be Internal Azure Load Balancer (ILB) via which all traffic would flow from SharePoint servers to SQL Server, this SQL Alias wasn’t working.

Trying to point SQL Alias prodsql to the IP address of the ILB just didn’t work, but as soon as I pointed prodsql to one single SQL Server everything worked nicely.

Solution

As many times, the solution was simple, but for some reason I was just too stuck to the “always use SQL server alias” thought to realize, that this time I in fact needed to remove the SQL Alias from all SharePoint servers and instead create DNS A record that points from prodsql to the ILB IP address.

Case closed.