Setting up Microsoft SQL Server 2016 AlwaysOn

In Microsoft SQL Server on 03/05/2018 at 2:18 PM

I’m continued to learn, enhance my DBA skills and knowledge on Microsoft SQL Server AlwaysOn technology on New Windows Server 2016 and MS SQL Server 2016.

There are many an Availability Group Architecture terms that I needed to get familiars like:

Availability Group –
- AG is a container that represents a unit of fail-over
- AG can have one or more user databases
- When the availability Group fails over from one replica to another replica, all the user databases will fail over.
Primary Replica
- Microsoft SQL Server instance that is currently hosting the AG (Availability Group).
- The AG is active and hosting a user database that can be modified.
- There is only one primary instance at any give point in time.
Secondary Replica
- SQL Server 2012 supports four secondary replicas
- SQL Server 2014- 2016 supports eight secondary replicas
- is hosting a copy user database that cannot be modified.
Failover partner
- SQL Server 2012 – 2014 support one failover partner
- SQL Server 2016 – support two failover partners
- Is the secondary replica that configured to be auto failover destination when the Primary Replica goes wrong.
Synchronous vs Asynchronous
- Synchronous is designed to setup Microsoft SQL Server AlwaysOn on Premises
- Asynchronous is designed to setup Microsoft SQL Serer AlwaysOn outside Premises.

And, I used VMWare technology to setup my environments. First, I’m going to preparing three (3) Virtual Machine in VMWare

- Computer Name: INDSQLP1N1
  - Processor: Intel @R Core (TM) i7-6700 HQ CPU @2.6GHz
  - Memory: 8 GB
  - O/S: Window Server 2016
  - Installed Microsoft SQL Server 2016 SP1
- Computer Name: INDSQLP2N2
  - Processor: Intel @R Core (TM) i7-6700 HQ CPU @2.6GHz
  - Memory: 8 GB
  - O/S: Window Server 2016
  - Installed Microsoft SQL Server 2016 SP1
- Computer Name: INDSQLP3N
  - Processor: Intel @R Core (TM) i7-6700 HQ CPU @2.6GHz
  - Memory: 8 GB
  - O/S: Window Server 2016
  - Installed Microsoft SQL Server 2016 Sp1
Second, Installed and setup Window Server 2016 Standard edition on each VM.
Third, I joined the Virtual Machine into my Domain Controller.
Forth, I am going to configure Window Server Failover Clustering with Node majority.
- There are no share disk storage
Forth, I installed and setup Microsoft SQL Server 2016 SP1 on each Virtual Machine.
Next, I turned on the AlwaysOn High Availability features.
And, make sure the user database must be using full recovery mode.
A full database backup must have been performed.
Finally, I started setting up and configure the new Availability Group that called “365_AvailabilityGroup”