Always-on AG configurations

Enable Backup

Create a new Database called “Database” (open SQL Server Management Studio (SSMS) and click create new database) and do a full backup for this database (right click Tasks -> Backup).

You don’t need to do this step on the second node, since you will set this database to be part of the AG.

In other cases, it’s best practice to have a backup even if you have a High Available (HA) solution.

Enable Always On

  1. Open SQL Server Configuration Manager (SSCM) and enable the Always On checkbox stormtroopocat

Create Availability Group

Open SSMS and create the first Availability Group

Step by step

Result:

stormtroopocat

Done.

Test the HA

Open the SSMS right click and simulate failure

or Hard shutdown the active instance from Amazon EC2 Console

test-failover

Fine tuning

  1. Open/Run dnsmgmt.msc Look for the endpoint record, how many records do you see? How to solve it?

    See solution

  2. When failing over, you might notice that the cluster is trying to set the wrong IP on the instance, since only one IP can run on one instance, we can set the cluster to try running those IP only on the instance that is relevant

    See solution