Hello everyone,

In this post, I will describe process of configuring AlwaysOn on SQL Server 2016. Always On availability groups feature is a high-availability and disaster-recovery solution for your SQL databases. Before you start with configuring, you need to meet some prerequisites. For successful implementation, you need:

  • SQL Server Standard 2016, or older in Enterprise edition > SQL installation process
  • Domain environment
  • Installed Failover Cluster feature on all SQL Servers and configured Failover Cluster > described here
  • Same configuration on all SQL Servers

Once you have installed SQL server and configured Failover Cluster, you need to enable AlwaysOn functionality to all SQL servers that will be part of AlwaysOn Availability groups. To accomplish this task, you need to configure SQL Server service by editing in SQL Configuration Management console.

You need to enable AlwaysOn Availability Groups on tab AlwaysOn High Availability. Service needs to be restarted to apply changes.

Once you enable this feature on all SQL Servers, you need to prepare environment for listener. Listener will be connection point for databases that are in AlwaysOn Availability Groups. You can create computer account in AD manually and give appropriate permissions to Cluster account to manage listener account. Also, you can delegate permissions to Cluster account to create computer account for listener during AlwaysOn configuration.

When you enable AlwaysOn feature and configure computer account for listener, you can start with configuring AlwaysOn for databases. Note, that desired database needs to be backed up before adding to AlwaysOn Availability Group. I will use SSMS to show you steps for AlwaysOn configuration.

  • Open SSMS, expand AlwaysOn High Availability, right click to Availability groups and select New Availability Group Wizard
  • On Specify name page, define availability group name
  • On page Select databases, select database that you want to add to availability group
  • On page Specify replica, click to Add Replica and login to replica server

  • When replica server is added, configure failover and commit type

  • Open tab Listener, and create new listener. Define listener name, port and IP address

  • On page Select data synchronization, select what type of initial sync you want. I have selected full synchronization

If everything is configured properly, you should receive “all green” on validation page and you can start with creating.

Successfully created Availability Group will report with image like as example below.

At the end, result is that you have highly available database 🙂

 

Cheers!

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.