Overview

AlwaysOn group is functionality for Microsoft SQL server which aims to provide high availability by providing failover capabilities for a database.

In order to configure a AlwaysOn Group in SQL Server you firstly must configure:

Failover Windows Cluster (You can check how in the windows section: Failover Cluster Config )

2 SQL Stand-alone Binary installations

P.S. You can check the windows section fo how to configure it.

Once it is configured let's start the installation of stand-alone SQL :)

Installation of SQL binaries

Double blick on Setup

Product Key → Enter your product key → Next

License Terms → Accept → Next

Microsoft Update → Next

Install Setup Files → If all is fine → Next

Feature Selection → Click on “Database Engine Services” → Next

Instance Configuration → Choose Instance Name → Next

Server Configuration → Choose relevant → Next

Database Engine Configuration → Choose relevant → Next

Ready to Install → Next

In the end you should have something similar to the image below, depending on the settings you provided:

Enable AlwaysOn

As everything with Microsoft Servers, stuff have to be enabled, otherwise you will receive the following issue which you try to configure them:

As said on the error, let's enable it on both servers:

SQL Server Configuration Manager → SQL Server services → Right Click “SQL Server” → Properties

SQL Server Properties → Click on the “AlwaysOn High Availability” tab → Click the “Enable AlwaysOn Availability Groups”

After the change has been made, make sure to restart the service, so the changes will take effect.

Configuration

To configure it, open the AlwaysOn Tab on the Management studio:

And let's follow the configuration:

Introduction → Next

Specify Options → Specify the Availability Group Name → Next

Specify Database → Specify the databases you wish in that Group → Next

Specify Replicas → Choose which Server to be primary and which Secondary and other configuration settings → Next

Select Data Synchronization → Choose the approriate (p.s. I started with automatic but then I provided shared location) → Next

Validation → Validate that the checks passed → Next

Summary → Next

Results → Close

The result of the configuration should be something like that:

You can use also the following script for manual configuration in the Appendix.

After all that you should have all databases selected throughout the configuration in restoring mode ON the second machine:

I know that from security point of view, this isn't the best, but I try to make it working and later will improve security, I promise.

Fixing Issues

There were couple little problems, as always.

Wrong Service User

The default user didn't have right to access the DCOM, therefore I had to change the user which was used from Local user to SQLDOM\Administrator.

AlwaysOn Group Listener

The listener, I have decided to create it after I create the AlwaysOn group. So be sure to enable it after the creation if you didn't do it during.

Not Synchronizing

After the initial creation, the replicas were fine, but the database wasn't synching. So don't forgot to join the database to the group after that

Appendix

Manual AlwaysOn Script

Disable/Enable Jobs on Primary