Enable AlwaysOn Availability Groups on all cluster nodes

AlwaysOn Availability Groups needs to be enabled on every instance where you want to use it.

  • Select Properties of the instance in the SQL Server Configuration Manager.
  • Tab: AlwaysOn High Availability
  • [x] Enable AlwaysOn Availability Groups
  • Restart the service of the reconfigured instance.

Create share for the DB backups

You need a share for the DB backups.

  • The share have to be writable for SQL Server Agent.
      • serdb1scvmm0a

Create an Availability Group and Listener

  • Start the SQL Server Management Studio and connect the installed instance.
      • CONTDB1C0N0\SCVMM0
  • Create testing database
  • Set the Recovery Model to Full (should be set by default).
  • Back up the new database to the share. If it is possible then choose highly-available (clustered) file share. For the testing purposes I chose the regular file share.
      • \\contmng0\Replicas\contdb1c0\AG\agscvmm0\test.bak

Availability Group Wizard

  • New Availability Group Wizard...
  • Set name of the new Availability Group.
      • agscvmm0
  • Select the test database.
  • Add Replica servers
  • You may set as readable only the Replicas with synchronous Commit.
  • In production environment you may want to set the secondary as non-readable because these nodes will be busy by receiving all the changes done on the primary and you might not want to slow them down by a lot of queries.
  • Configure Listener.
      • Listener DNS Name: contdb1c0agvmm
      • Port: 1433
      • Network Mode: Static IP
  • Specify the shared folder where the data will be backed up and restored to the secondary replicas.
  • If it is possible then choose highly-available (clustered) file share. For the testing purposes I chose the regular file share.
      • \\contmng0\Replicas\contdb1c0\AG\agscvmm0
  • Check the validation status.
  • Create
  • Check the results.

Failover Cluster Manager

  • In the Failover Cluster Manager you can now see the new role. The name is the same as the name of the Availability Group. Client Access Name was added into DNS and you can use it in your application to connect to the Listener.
  • DNS record of the Listener
« Previous Next »

Part of the series

  1. Building Microsoft SQL Server AlwaysOn Availability Groups - Part 1 - Design
  2. Building Microsoft SQL Server AlwaysOn Availability Groups - Part 2 - Create cluster and configure OS
  3. Building Microsoft SQL Server AlwaysOn Availability Groups - Part 3 - Installation
  4. Building Microsoft SQL Server AlwaysOn Availability Groups - Part 5 - Make another DB Highly-Available - Add it to Availability Databases