Now that we have our SQL AAG up and running, there’s still some configuration left to do. In this installment I cover SQL 2014 file share witness confiugration. In my example I’m doing a 2-node AAG, which means that we need a file share witness to help establish quorum. If you have a NAS appliance, you can easily create a share on there and use it. In my case I’m assuming 100% Windows, so we will be using a third member server as our FSW. There’s nothing too special about this FSW, except for some permissions. Storage space is very minimal.
SQL 2014 Always-on AG Pt. 1: Introduction
SQL 2014 Always-on AG Pt. 2: VM Deployment
SQL 2014 Always-on AG Pt. 3: Service Accounts
SQL 2014 Always-on AG Pt. 4: Node A Install
SQL 2014 Always-On AG Pt. 5: Unattended Node B
SQL 2014 Always-on AG Pt. 6: Cluster Configuration
SQL 2014 Always-on AG Pt. 7: TempDB
SQL 2014 Always-on AG Pt. 8: Max Mem & Email
SQL 2014 Always-on AG Pt. 9: SQL Maintenance
SQL 2014 Always-On AG Pt. 10: AAG Setup
SQL 2014 Always-On AG Pt. 11: File Share Witness
SQL 2014 Always-On AG Pt. 12: Kerberos
Create File Share
1. On a WS2012/R2 member server (not either SQL server) open Server Manager, go to File and Storage Services, click on Shares, then from the Tasks menu select New Share. If you don’t have that option, add the File Server Role and wait for the installation to complete. No reboot is needed.
2. Select the SMB Share – Quick file share profile.
3. Select the appropriate volume the share will be created on.
4. Use a share name in the format of: <Cluster name>-FSW (e.g. SDSQL03-FSW).
5. Enter a description in the format of: <Cluster Name> Cluster File Share Witness.
6. Uncheck allow caching of share and enable encrypt data access.
7. Customize the permission, disable inheritance and remove all inherited permissions.
8. Give the cluster computer object (e.g. SDSQL03) full control. If you want, you could also give administrators access so they can peek inside.
9. Finish the wizard and wait for the share to be created.
SQL 2014 File Share Witness Configuration
1. On a SQL server launch the Failover Cluster Manager.
2. Right click on the root cluster object (e.g. SDSQL03.contoso.local), select More Actions and then click Configure Cluster Quorum Settings.
3. Select Select the quorum witness.
4. Select Configure a file share witness.
5. Enter the file share path. Click through the remainder of the wizard and verify the FSW was successfully configured.
6. Verify Quorum configuration is now using a file share witness.
In this installment we configured the SQL cluster to use a file share witness. This is needed when you have an even number of servers in the SQL AAG. You can use either a NAS appliance, or another Windows member server. In the final two installments we will configure Kerberos and SSL. Check out Part 12 for Kerberos details.