How to Create a SQL Server 2016 AlwaysOn Availability Group- step by step

Copied from the below article

http://www.macaalay.com/2016/11/04/step-by-step-easy-instructions-on-how-to-create-a-sql-server-2016-alwayson-availability-group/

-------------------------------------------------------------------------------------------

SQL Server AlwaysOn Availability Group has been existing since SQL Server 2012 and its one of the best features SQL has to offer, while I haven’t used it in production since it was launched I tried using it for only testing but now that we have an Enterprise License we can use it in Production and enjoy all the other benefits of the Enterprise License. With SQL Server AlwaysOn it high availability and disaster recovery to a different level by making multiple copies of the database highly available and even use them as read-only workloads to be used on reporting, backups and other management tasks. With this technology it allow you to fail over a group of databases as a single entity unlike how we did it before with database mirroring where we can only do it one at a time, though that work this made it even better.

It greatly helps in disaster recovery, availability and even management of the databases and the servers it resides on. Today I will run you though the steps I made when I set up our server, it is quite a straightforward approach as long as you have all your requirements set up from your servers (Physical or VM) down to the patches (it needs to have the same OS patches installed)

Now lets start starting with the iSCSI.

1. Set Up iSCSI for Shared Storage

Now you might be wondering why we need to do this? If you haven’t set up Windows Server Failover Cluster then you need to know that this is required so that there is a shared disk in the OS to run the clustering.

First lets Run the iSCSI Initiator

Once its running it will ask you to run the service, click Yes so it starts automatically when your server reboots

The iSCSI Initiator Properties opens, go directly to the Discovery tab and click the Discover Portal button.

Type in the IP Address of the shared storage iSCSI interface in the IP Address or DNS Name textbox then click Advanced

Advanced setting open then on the Local Adapter dropdown box select Microsoft iSCSI Initiator then on the Initiator IP text box key in the IP Address of the NIC that is dedicated to your iSCSI connection. Click OK.

The shared storage/s will then be listed highlighted part of the image below.

Click the Target tab and in this tab you will see the LUNs that are available. Select the LUNs on the list then click the Connect button

Click the Target tab you should see the iSCSI target that you are connected to.

That’s it were done with the first server, now do the same thing on the other server then your done with the first step. Now lets configure Windows with a shared disk.

2. Configuring shared disk in the OS

Go to your Disk Management on one of the servers, there you will see the shared iSCSI storage displayed as Unknown, right-click on that then choose Initialize Disk.

Choose MBR if your shared storage does not exceed 2TB otherwise use GPT then click OK.

Right click on the Unallocated space then choose New Simple Volume

Assign a drive letter then click Next

Then format the volume, I chose NTFS and Quick Format

It then complete the process so click Finish afterwards

Now the disk will be online, turn it offline so you can configure the shared drive on the other server, the second cluster node. Right click on the drive then choose Offline.

Now go to the other server, open Disk Management, got to Actions then choose Rescan Disk is the shared disk does not show.

Now when the Disk shows Right click the new volume and Change Drive Letter and Paths

Assign the same drive letter you assigned on the first node, in this example it was “Q”

Now you’re all set and ready for the next step, creating a failover cluster.

3. Set Up Windows Server Failover Cluster

Now lets set up Failover Clustering, you need to add this a feature on your Windows. Open Server Manager and choose the Local Server in the left menu then on the content section and select Add Roles and Features. Click next until you are at the feature section, in here select and check the Failover Clustering., click Next.

Let it complete and install. Search for the Failover Cluster Manager then execute it.

You can click validate configuration to see if there are warnings or you can start creating the cluster. Choose and click Create Cluster.

Add the nodes you set up initially so in this example we have two. Click on Next.

It will start doing validation, if there are errors it will not continue but if there are warnings it will continue so its best to note this warnings and fix it as deemed possible.

Add a Cluster Name and give it a cluster IP address.

Choose the Add all eligible storage to the cluster and then click Next.

It will now start to create your cluster.

Now you had created the clusters and it will appear on the Clusters section of the Failover Cluster Manager.

Now you have your cluster you are ready to set up your SQL Server.

4. Set Up the SQL Server

I think this is the easiest part of the setup process, so lets start.

Mount your SQL Enterprise ISO then when the SQL Server Installation Center runs choose the Installation on the right pane. On the content pane choose New SQL Server stand-alone installation or add features to an existing installation

Move next until you hit the Feature Selection, choose Database Engine Services as a minimum then click Next.

Move next until you hit the Instance Configuration, choose default instance and click Next.

On Server Configuration Section assign the correct credentials to run your SQL Services. Click Next.

On the Database Engine Configuration section add the current user as the SQL Server Administrator. Click Next.

Specify the default paths you want to use for the Data Directories, for more guidance you can follow this best practices on how to separate your SQL Data.

Click Next and finish your installation.

Now do the exact same setup on the other node.

Once its completed you have set up all the necessary pre-requisites to run SQL Servers Always On Availability Groups. But before continuing you will need to add databases on one of the nodes, these are the databases you will set up below for the Availability Groups.

5. Configuring SQL Servers Always On Availability Groups

Now lets finish this and set up the core reason why you are here. Few more steps and you are done.
Open SQL Server Configuration Manager, choose the SQL Server instance, right click then choose Properties.

Go to the AlwaysOn High Availability tab and tick Enable AlwaysOn Availability Groups, do this as well on the other node.

It will warn you to restart the service for it to activate. Click OK.

Restart the service on both nodes by choosing the SQL Server Instance and clicking on the restart button.

Now run SQL Server Management Studio and connect to one of the nodes. Go to the AlwaysOn High Availability folder, right-click and choose New Availability Group Wizard

Click Next, you might want to choose Do not show this page again.

Indicate and Availability group name. Click Next.

Choose the databases you want to be on the Availability Groups. Click Next.

Now you need to specify the replicas, this is the second node of your SQL Cluster. Click on Add Replica.

Add the secondary server you created, tick all the Automatic Failover and Synchronous Commit check boxes. You will also want to indicate Yes on the Readable Secondary drop down.

Go to the Listener tab, indicate a Listener DNS Name, Port and set Network Mode to Static IP. Scroll down to add your IP Addresses.

Click Add.

Choose a Subnet and add the IPv4 Address, click OK.

Now you listener will have an IP Address. Click Next.

Select your synchronization preference. We choose Full, you will also need to indicate a shared folder location that is accessible by both databases, this is the place where the databases are backed up and restored from when setting up the Availability Groups. Click Next.

It will perform the validation and when its all successful click Next.

It will then start to configure, wait until this completes.

Once its finished the selected databases are now set up for AlwaysOn. To verify check it on the AlwaysOn High Availability folder of any of the servers and you will see your Replicas, Availability Databases and Availability Group Listeners.

That’s it you have configured SQL Server 2016 AlwaysOn Availability Group, you might not get it on the first run like I did due to the Warnings and Errors but the messages are quite intuitive so just follow the instructions and it will be straightforward.

Congratulations and have fun using your SQL Server 2016 AlwaysOn.