MS SQL AlwaysOn Availability Groups with Managed Service Accounts

Create the Windows Server Failover Cluster instance

Start the Failover Cluster Manager and run the Create Cluster Wizard.
Enter the FQDN of the servers that will participate in the SQL Availability Group. Do not include the file share witness, which will be specified later.

Provide the cluster name and assign a static IP for the cluster object.
On the confirmation screen, uncheck the option to add all eligible storage. For an Availability Group, there is no shared storage configured and therefore no storage to add to the cluster.

Configure a File Share as a Cluster Witness

An independent file share will be utilized as a witness for the cluster. The file share should not be located on either of the cluster members, but you may otherwise use any existing file servers in the environment or create one dedicated to the cluster.

From the Failover Cluster Manager, right click the cluster name in the left panel, select More Actions, and select Configure Cluster Quorum Settings.

Select the option “Select the quorum witness”
Select the option “Configure a file share witness”

Provide the path to the file share.

Note: The file share specified must be writable by the failover cluster instance computer object. In the case of this example, the failover cluster instance computer account was WSNOCMSSQL00.

Confirm and save the changes.

Enable AlwaysOn functionality for MS SQL

Launch the SQL Server Configuration Manager.
Select “SQL Server Services” from the left panel and open the properties dialog for the SQL server instance.
Select the AlwaysOn High Availability tab and enable the AlwaysOn Availability Groups option.

Configure the Managed Service Account for MS SQL

In the same properties dialog, select the “Log On” tab.
Assign the group Managed Service Account created earlier. To assign the account, enter the account name and leave the password boxes empty.

Restart the SQL instance.

Create the Availability Group

From here on, it’s standard procedure for creating and managing Availability Groups. The remainder of the guide is for completeness.

Launch the SQL Server Management Studio and start the New Availability Group Wizard.

Specify the Availability Group Name. In the example of this guide, the Availability Group Name is WSNOCMSSQL01, which was the second cluster computer object we pre-created. If any databases already exist on the SQL server, you may opt to add them to the new Availability Group.

Set the replica server and enable automatic failover.

Create an Availability Group Listener, which will serve as the connection target for communication with the databases within the SQL AlwaysOn Availability Group.

Select an option for initial data synchronization for the new Availability Group. In this example, we use a file share to perform the initial data synchronization. If you use a file share, the group Managed Service Account that was created for the SQL process must have write access to the target path.

Be aware that the initial data synchronization only copies the database data to the replica nodes. System level security objects are not copied. Please ensure that the service account(s) used to access the database are configured on all SQL Servers within the Availability Group or database access may be lost during a failover.

Validate and confirm.