Setting up SQL Always On Cluster with Azure Active Directory Domain Services AAD DS and Dedicated NIC on Azure
I refer to various guides ranging from SQL Always On Cluster Setup Guide, and brief documentation on SQL Always On with AAD Domain Services and have decided to write a comprehensive guide which might save you hours or even days of fun.
Here are some constrains which the AAD DS might have, and some workaround for it.
- You can only create computer objects (OU: AADDC Computers) if the AD user is part of the AAD DS Administrator. Hence add your user account on Azure Active Directory management page, to the AAD DS Administrator group.
- You should not delete users (OU: AADDC Users) directly from AAD DS Administrative Center. You can delete users from the Azure Portal.
- AD users will only be propagated to AAD DS after resetting and changing their password on myapps.microsoft.com
- Administrative Center does not allow creation of users. Create users in the Azure Active Directory Panel in your Azure Portal.
- When creating your AAD DS, it is highly recommended to use a valid domain name, such as email@example.com . Choose wisely, as every mistake which requires you to recreate your AAD DS will take 45-60 mins each.
- Do not delete system generated objects on your AAD DS DNS records. Deleting known computer objects (VMs, Clusters) is fine. If you accidentally delete system generated objects, you will need to refer to point 4. Recreate your AAD DS, go and have a coffee, and come back in an hours time.
- Do not delete system generated computer objects on your (OU: AADDC Computers). Refer to step 4. If you did, redo your AAD DS, go have lunch now.
- If you really wish to create user objects, you will need to create an additional OU. But…. these users in this newly created OU will not have permissions to create computer objects. These users (in a different OU) are are not allowed to be part of AAD DS Administrative Group. Hence if you attempt to create a failover cluster with a domain account in a custom OU, it will fail.
- Create an account such as firstname.lastname@example.org with AAD DS Administrator Group membership.
- I’ve tried, but there is no direct method to reduce effective permission for a user with AAD DS Administrator Group membership.
Here are some common preparations and information before setting up SQL Always On Cluster on SQL Management Studio
- Ensure NT System\Authority has permission to
- Alter Any Availability Group
- Connect SQL
- View server state
- WSFC Cluster IP is different from SQL Always On Cluster Listener IP.
- If you wish to have dedicated NIC for HADR replication, ensure that your additional NIC resides in a different subnet from the VM’s primary NIC subnet
- There are limitations to what SQL Management Studio’s Wizard can achieve. Dedicated NIC setup for mirror endpoints requires additional SQL scripts. Just ensure that the mirroring endpoint is create prior to running the wizard.
- There is a difference between SQL Always On Group Name and SQL Always On Group Listener Name. The latter is the one which influences the FQDN.
- Creation of SQL Always On Cluster Listener is easily done with the Wizard, just ensure that your load balance on Azure is create beforehand.
- There are occasions which you might face “partition failed” when creating your WSFC. Instead of creating the cluster with both primary and secondary node, the workaround would be to create the cluster with a single node, and add the secondary node there after.
- If you encounter issues where only specific servers can connect to your SQL Always On Cluster via the FQDN, you might be having issues with your WSFC. This would happen when you have multiple NIC on each node, and the FQDN for each node pointing to 2 IP address on your DNS records.
- If point 8 happens to you. Shutdown your server on Azure, move your secondary NIC to a different subnet. Remove all orphan objects on AAD DS using Active Directory Administrative Center. Assuming you have already setup your load balancer for your SQL Always On Cluster. re-create your WSFC adding one node at a time, add your quorum witness, toggle your SQL Configuration Manager for Failover Cluster, remove all existing mirroring endpoints, create an mirror endpoint with dedicated NIC on a seperate subnet, create the SQL Always On Cluster using the Wizard (and ensure you create the listener using the wizard, if you skip this step, you might need to redo due to constrains of AAD DS permissions).