SQL 2014 Always-on AG Pt. 11: File Share Witness

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.

Blog Series

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.

2014-10-13_19-14-01

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.

2014-10-13_19-08-51

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.

2014-10-13_19-26-10

 Summary

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.

Print Friendly, PDF & Email

Leave a Reply

1 Comment on "SQL 2014 Always-on AG Pt. 11: File Share Witness"

avatar
  Subscribe  
newest oldest most voted
Notify of
Matt
Guest
Matt

We are currently running VMware version 5.5, and in the past have experimented with using MSCS clusters in version 5.5 and gave up because of vMotion restrictions with SCSI bus sharing. This sounds almost like Always On does not require SCSI bus sharing and so it could be vmotioned without powering it down. Am I understanding this correctly?

Your blog is first rate, and I always come here for advice.

Thanks for your time and effort

Matt