• Category: Microsoft

SQL 2014 Availability Group with Windows 2016 Cloud Witness

Install SQL 2014

SQL needs to be installed on each Windows 2016 Server

  1. Start the SQL 2014 Enterprise installer
    installsql 01
  2. Most of the screens can be continued through.  On the feature selection select 'SQL Server Replication' and management tools
    installsql 06
    installsql 07
  3. Feel free to change the instance or leave default
    installsql 08
  4. Configure the service account to run SQL along with adjusting service startup option
    installsql 09
  5. Configure SQL access.  I chose mixed mode.
    installsql 10
  6. Continue through the prompts

Configure SQL Service

Adjust the SQL instance so it can join the cluster.

  1. Run 'SQL Server 2014 Configuration' from the start menu and select 'SQL Server Services'
  2. View the properties of 'SQL Server'
    sqlsvc 01
  3. Select the 'AlwaysOn High Availabilty' tab and place a check in 'Enable 'AlwaysOn Availability Groups' and hit OK
    sqlsvc 02
  4. Restart the SQL server service
    sqlsvc 03

Create Database Backup

For this example I created a single database called 'testag' running only on the first node.  A backup is required to place in a availability group.

  1. Start the backup wizard on the database
    sqlbackup 01
  2. Run the backup with all defaults and location of your choice
    sqlbackup 02
  3. Verify backup is complete
    sqlbackup 03

Create Temporary Windows Share

The availability group wizard needs a Windows share that is accessible to all nodes where it will copy the backup files needed while setting up the group.  This share is only used when the group is created.

  1. Create a folder on the first node or use an existing path that will have access.  I used 'C:\TempShare'
    tempshare 01
    tempshare 02
    tempshare 03

Create SQL 2014 Availability Group

  1. Run the 'New Availability Group Wizard'
    createag 01
  2. Select the name for the availability group.
    createag 02
  3. Select the database to add to the group. (A backup must be created before this step.)
    createag 03
  4. Add replicas.  In this case I only have two replicas but again feel free to add the third.  Verify 'Synchronous' is selected.
    createag 04
  5. Select the share path created earlier
    createag 05

  6. AG will now create.
    createag 06