Search This Blog

Friday, October 27, 2017

Setup SQL Always On

Let build a SQL Always On...

Just follow the following steps to configure a high availability SQL Always On.

  • Node running Windows Server 2016
  • SQL Server 2012


1. Create a Windows Failover Cluster. Example 2 nodes

  • Node1
  • Node2

2. Install default standalone SQL instance on both nodes
3. Open SQL Server Configuration Manager, edit the Properties of SQL Server (MSSQLServer). On AlwaysOn High Availability tab, tick "Enable AlwaysOn Availability Groups and restart sql services

4. Open SQL Server Management Studio and Connect to SQL Server Instance
5. Create a new database first. Make sure that have performed a full database backup
5. Create a new availability group. Go to AlwaysOn High Availability | Availability Group | New Availability Group Wizard.

To create an availability group, you will need to:

  • specify an availability group name

  • select one or more user database

  • specify one or more instance of SQL Server to host secondary availability replicas

-Add Replica ->Select Node2
-Configure automatic failover
-set the synchronous commit
-set readable secondary

  • specify your availability group listener preference

  • select your initial data synchronization preference

-shared a folder.Make sure both nodes has Write permission Example:-

  • check the validation result of availability group creation

This will create and configure the SQL Always On Availability Group and join the database.

End result:-