通过

SQL Server Availability Groups in Azure VM setup with AAD Domain Services

  • 项目

Reviewed by: Steven Schneider, Sanjay Mishra, Mike Weiner, Kun Cheng, Dimitri Furman, Arvind Shyamsundar, Mahesh Unnikrishnan, Luis Carlos Vargas Herring and Nimesh Nedungadi

Deploying SQL Server Availability Groups in Azure VMs typically involved provisioning two additional VMs to host an Active Directory Domain Controller. With Azure Active Directory (AAD) Domain Services (aka managed domain services on Azure), it is possible to deploy SQL Server Availability Groups (AG) in Azure, instead of deploying VMs for the AD. AAD Domain Services provides services such as domain join, group policy, LDAP, Kerberos/NTLM authentication that are fully compatible with Windows Server Active Directory and the best part is, it’s completely managed by Azure. With AAD Domain Services there is no need for you to deploy, manage, and patch domain controllers in the cloud. Our earlier Azure AG deployment documents required you to provision two domain controller VM’s (primary and secondary) but with AAD Domain Services you just have to ask Azure to do it for you. The overhead of managing the domain controller VM’s is gone with AAD Domain Services.

The current pre requisite is that AAD Domain services can be enabled only in Classic Virtual Network. We know that you might have started with a classic virtual network in the past and all new virtual networks you create will be mostly the recommended Azure Resource Manager (ARM) based. Good news is that VM’s in ARM Virtual Network can leverage AAD Domain Services enabled in Classic Virtual once these two networks are connected through VNET Peering or VNET to VNET VPN gateway . We went with VNET Peering for this post and AG setup was seamless. For detailed network considerations with AAD Domain services check this link.

SQL Server Always On AG scenarios with AAD Domain Services

We cover the following two scenarios in this post:

Scenario 1:
Enabling AAD Domain Services in Classic Virtual Network and deploying two SQL Server 2016 Classic VM’s (Windows Server 2012) and then setting up AG

Scenario 2:
Leveraging AAD Domain Services enabled in Classic Virtual Network from an ARM virtual network by adding an ARM based SQL Server 2016 VM as replica to the existing AG.

Detailed steps and screenshots

Steps 1-4 cover Scenario 1
1. We enabled AAD Domain Services on a classic virtual network
You can start with the excellent documentation from here and follow along Task 1 to Task 5

As displayed below, VNETAADDSTEST is the name of my Classic Virtual Network clip_image002

In the below screenshot CONTOSO100.onmicrosoft.com is the DNS name of managed domain. Note that the DNS server IP's in above screenshot are the actual primary and secondary IP of managed domain.

clip_image004

2. Next, we deployed two SQL Server 2016 Classic VM's from Azure gallery images in a cloud service:

clip_image006

3. Then we added the VMs to managed domain (CONTOSO100.onmicrosoft.com).
Here is a sample “Welcome to the domain” screenshot for SQL2016VM1: clip_image008

4. Then we configured SQL Server Always On AG successfully, by following detailed steps documented here
Displayed below is the screenshot showing an healthy Always On dashboard clip_image010

Steps 5-10 cover Scenario 2
5. Then, we created an ARM Virtual Network called ARMVnetAADDSTest.
Displayed below is a screenshot from Azure Portal showing create virtual network configuration pane. clip_image011

6. We then peered it with the classic virtual network that we created earlier. Go to Azure Portal | Virtual Network | ARMVnetAADDSTest | under Settings, click Peerings | Click Add
This screenshot shows the Add Peering configuration pane. clip_image012

7. We then updated the DNS settings (highlighted below) for the ARM Virtual Network to point to AAD Domain Services IP’s

clip_image013

8. We added a third SQL Server 2016 VM ("ARMSQL2016VM3") in the ARM virtual network

clip_image014

9. Then we added the third ARM VM to managed domain
The screenshot shows “Welcome to the domain” message for the third VM “ ARMSQL2016VM3”
clip_image016

10. Finally we added the third VM to Windows Server Failover Clustering (WSFC) and to the existing AG and it just worked (Given our careful planning and execution of the previous steps, it is no surprise that it just works!)
The screenshot shows a healthy AG dashboard after adding the third VM as replica. clip_image018

Points worth mentioning

1. If you have existing AAD users and a Cloud-only or Synced tenant, please follow the recommendation here (Task 5)
2. We created an user (named it SQLInstall) and added it to "AAD DC Administrators" group and used this user credentials to add VM's into managed domain. "AAD DC Administrators” group has all the permissions needed to join a VM to domain.After creating the user you would get a temporary password. Log on to Azure Portal with it (in this example SQLInstall@contoso100.onmicrosoft.com) to change the password.
3. When we created the ARM virtual network, we made sure the subnet address does not overlap with the existing one in classic virtual network, this is something you must be careful as VNET peering feature will not get enabled if there are overlapping IP address spaces.
4. Make sure to create or select a classic virtual network in an Azure region where AAD Domain Services is currently available.

We have plans in near future to enable AAD Domain Services in ARM Virtual Network. In summary, this post was to showcase two scenarios that would benefit from AAD Domain Services. So, go ahead and enjoy this integrated functionality and let us know if you have any questions or feedback.

Update July 31,2017

  • In this blog we created an AG spanning classic and ARM deployment model and so, we didn't configure listener as Azure load balancer cannot span across deployment models.
  • To create listener (external or internal), existing documentation should be followed as long as all the Azure VM's involved are either in classic or ARM mode.
  • In the ARM mode here's the high level sequence for creating AG and listener:
    • Enable AAD Domain Services for classic VNET
    • Create an ARM VNET and peer it with the above classic VNET
    • Deploy ARM SQL Server VM's and create AG
    • Create external or internal listener. Make sure to setup listener using a domain account that's added to "AAD DC Administrators" group in AAD Domain Services.