Share via

AlwaysOn Availability Group in Windows Azure VM: Client Connectivity Scenarios

  • Article

Author: Sanjay Mishra

Contributors: Piyush Ranjan, Steven Schneider

Reviewers: Ed Muth, Chuck Heinzelman, Silvano Coriani, Rama Ramani, Juergen Thomas, Cephas Lin, Lori Clark, Luis Carlos Vargas Herring

Once you have created AlwaysOn Availability Groups in Windows Azure VM (tutorial) and created the Availability Group Listener (tutorial), it is important to know how the applications will connect to it.

Depending upon the location of the client application, the connection string will vary based on:

  • How you perform name resolution for the SQL Server
  • How do you authenticate connections from the client

Let’s consider 3 distinct scenarios:

  1. The client is an Azure VM (IaaS VM or PaaS role instance), that is joined to the same Active Directory domain as the SQL Server VMs you are connecting to.
    • The client could potentially be on-premises, joined to the same Active Directory domain as the SQL Server VMs, through site-to-site VPN.
  2. The client is an Azure VM (IaaS VM or PaaS role instance), that is part of the same Azure VNET as the SQL Server VMs you are connecting to, but NOT joined to the same Active Directory domain as the SQL Server VMs you are connecting to.
    • The client could potentially be on-premises, part of the same address space that is joined to the Azure VNET containing the SQL Server VMs, through site-to-site VPN.
  3. The client is anywhere but 1 and 2. This includes the clients in Azure, but on different VNETs, as well as the clients outside Azure (such as on-premises, and not joined through VPN).

Scenario 1: The Client joined to the same AD Domain as SQL Server

Slide1

Figure 1: The Client joined to the same AD Domain as SQL Server

As illustrated in Figure 1, the client is joined to the same domain as the SQL Server VMs. If the client application runs in a Windows Azure VM, then it is easy to join that VM to the domain. If the client application is in a PaaS role instance, it is possible to join the Pass role VMs to the domain (how to achieve this is outside the scope of this article).

Irrespective of whether the client is an Azure VM (or an on-premises machine joined to the same AD domain through site-to-site VPN) or a Pass role instance, it can perform name resolution to the AG Listener, because the AG Listener is a virtual name registered in the DNS.

Of the 3 scenarios discussed here, this is the only scenario that allows clients to connect to SQL Server using Windows authentication. So, you can have a connection string that looks like:

Data Source="ListenerTestAG,98765";Initial Catalog=AdventureWorks; Integrated Security=True;…

In this example, ListenerTestAGis the name of the AG Listener. The Listener is running on port 98765. If the Listener is not running on the default SQL Server port 1433, you must specify the port number in the connection string as shown in the example.

Scenario 2: The Client is in the same VNET, but not joined to the AD Domain

Slide2

Figure 2: The Client is in the same VNET, but NOT joined to the AD Domain

This is very similar to the previous scenario, except that the client is not joined to the AD domain that SQL Server VMs are part of. The Client is still part of the same VNET.

If the client runs in an Azure VM, while provisioning the VM, place it in the same VNET as the SQL Server VMs. One thing to note here is that before you provision the client VM(s), make sure a properly configured DNS Server is listed in the VNET on the portal, as shown in Figure 3:

pic

Figure 3: Specify DNS information in the VNET configuration

A common mistake is forgetting to register a DNS Server with the VNET, leading to failure of name resolution queries. Once the DNS is listed in the VNET definition, new VMs provisioned in this VNET will be aware of the DNS and can perform name resolution to the Availability Group Listener.

However, unlike scenario 1, the application must use SQL Server authentication rather than Windows authentication to connect to SQL server. The connection string will look like:

Data Source="ListenerTestAG.TestDomain.com,98765";Initial Catalog=AdventureWorks; User ID=<sqluser>;Password=<sqluserpw> ;…

Compared to the connection string of scenario 1, two differences should be noted:

  • The AG Listener is specified with a fully qualified domain name (FQDN). The use of FQDN in scenario 1 will work too, but there it is optional, whereas in scenario 2, it is required.
  • SQL Server authentication is used.

Scenario 3: The client is anywhere but scenarios 1 and 2 above

Unless the client is in the same VNET or in the same domain as the SQL Server VMs, name resolution cannot be performed to the AG Listener or to the individual SQL Server instances. Whether the client is inside Windows Azure but outside the specific VNET (Figure 4), or the client is outside Azure (Figure 5), it does not make any difference from the perspective of connectivity options.

Slide3

Figure 4: The Client is in Azure, but outside the specific VNET

Slide4

Figure 5: The Client is outside Azure

The clients in Figures 4 and 5 have no access to the DNS Server configured in the VNet that hosts the SQL Server instances and the AG Listener. Such a client, therefore, cannot perform name resolution for the AG Listener or the individual SQL Server instances. However, the Cloud Service hosting the SQL Server instances has a publicly visible name (with a public IP address) and can be reached from anywhere with public Internet access.

For the same reason as scenario 2, the client cannot use Windows authentication, but needs to use SQL Server authentication. The connection string will look like:

Data Source="TestAGcs.cloudapp.net,98765";Initial Catalog=AdventureWorks; User ID=<sqluser>;Password=<sqluserpw> ;…

The significant difference compared to scenario 1 and 2 is that this connection string uses the cloud service name (TestAGcs) instead of the AG Listener name. It is worth mentioning that you can use the cloud service name in the connection string for scenario 2 as well.

Enter Access Control Lists (ACLs)

As mentioned earlier, the cloud service is a public name with a public IP address. Moreover, the AG Listener is implemented using the cloud service external endpoint in Windows Azure. Exposing SQL Server to connect through the public endpoints can be a security concern.

ACLs (https://msdn.microsoft.com/en-us/library/windowsazure/dn376541.aspx) must be used to protect the publicly visible AG Listener endpoints. Use of ACLs on the AG Listener endpoints, enables one to selectively permit or deny connections to SQL Server from specific clients.

Further Reading

Comments

  • Anonymous
    February 25, 2014
    Hi,   I don´t understand one thing, in the tutorials Microsoft says that you only can connect to AG Listener by IP Public, but in your scenario 1, you say that it is possible connect by IP Private. Please, which is the correct?

  • Anonymous
    February 28, 2014
    @ Jose Antonio, Thanks for your question. AG Listener uses the public IP of the cloud service. Can you show me where you found "private" in this article?

  • Anonymous
    June 30, 2015
    Hi, I am setting up AlwaysOn setup in Azure and I am facing problem while connecting to Listener from same Vnet but from different subnet.As mentioned above senario 1 is working perfectly.But now my client is in different subnet (10.0.1.0/29(3)) and HA setup (with Listener,WSFC,DC and SQL instances) in different subnet (10.0.0.0/24(251)). Ip of my listener is 10.0.0.13 and I am not able to ping it from any above mentioned machines. Do you guys have any idea? Thanks,