Use Case: A virtual machine requires private network traffic to an Azure SQL Server for stringent network security requirements. There can not be any network traffic over the internet and Azure backbone network.

Introduction

When creating an Azure SQL Database Server, there are three typical options for connectivity. I will give simplistic description of each. And this applicable at the server level and not database level.

1. Default
   Through a public endpoint of <yourdatabasename>.database.windows.net that is accessible from the internet. There is no IP address static IP Address known.
   The Deny public network access setting is unchecked

2. Virtual Network Rule and Service Endpoints

Virtual network rules is a form of security firewall for Azure SQL Server. You specify and virtual network and subnet that a resource such as a VM resides in. The service endpoint enables a private IP address in this subnet to reach the Azure SQL. In other words, provides more secured connectivity in the form a firewall rule.

There are further considerations and details to read at https://docs.microsoft.com/en-us/azure/azure-sql/database/vnet-service-endpoint-rule-overview

3. Private Endpoints
Connecting to Azure SQL Server with private endpoints is the most secure option, but is more complex. A private endpoint for Azure SQL server produces a network interface with a known private IP address that has secured connectivity to the the Azure SQL Server service.

Use Case Architecture

The components involved to have secured private endpoint with Azure SQL Server

• The private endpoint is an azure resource on its own you need you to provide the VNet and Subnet that the network interface will be created and the Azure SQL Server itself.

• The network interface card is representative of the private IP address within the range of the subnet that the VM in this use case can connect into.

• The Azure Private DNS zone is an option to use to create n DNSA record that maps to the database fully qualified domain name (FQDN) such as mydatabase.database.net to the private IP address of the network interface card (NIC).
  

• Virtual Network Link. An important piece of this puzzle which is often overlooked is the link between the virtual networks, where the DNS requests come from, to the Azure Private DNS Zone. In this case the the App1Vnet is linked to this private DNS Zone. You can have multiple VNets. It is easier to manage when you have a hub VNet in a hube and spoke topology to have a link to your private DNS zones. These links are often autogenerated and so that is a reason why it isn’t apparent to the understanding of the all how DNS resolution works end to end.

Final Remarks

Hope you got a simple understanding and overview of DNS resolution so that it enables you to dig deeper into other online articles and Microsoft Documentation such as in https://docs.microsoft.com/en-us/azure/private-link/tutorial-private-endpoint-sql-portal