MSSQL AG and WSFC config on azure IaaS VM & onprem

 MS doc:

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial-single-subnet?view=azuresql

Always On Availability Group Implementation Checklist	https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial-single-subnet?view=azuresql
Phase	Task ID	Task Description	Configuration Detail / Requirement
1. Prep	1.1	Verify Prerequisites	2 SQL instances in Availability Set; Domain accounts; Failover Clustering installed.
1. Prep	1.2	Open Firewall Ports	Allow 1433 (SQL), 5022 (Mirroring), 59999 (AG Probe), 58888 (Cluster Probe).
2. Cluster	2.1	Create Cluster	Create one-node cluster (e.g., SQLAGCluster1) via Failover Cluster Manager.
2. Cluster	2.2	Assign Static IP	Set Cluster Core IP to an available Static IP in the subnet (not a duplicate of the VM IP).
2. Cluster	2.3	Add Second Node	Run Add Node Wizard to join the second SQL Server VM to the cluster.
2. Cluster	2.4	Configure Quorum	Set up a File Share Witness on a separate VM; grant Cluster account Full Control.
3. SQL Config	3.1	Enable Always On	Enable Always On in SQL Server Configuration Manager on both nodes; restart services.
3. SQL Config	3.2	Prepare Database	Create DB (MyDB1); create a backup share; take a Full Backup to initialize log chain.
4. AG Wizard	4.1	Create AG	Run New Availability Group Wizard; name it (e.g., MyTestAG); add second replica.
4. AG Wizard	4.2	Data Sync	Use the backup share for "Full" synchronization or manually restore with NO RECOVERY.
5. Load Balancer	5.1	Deploy Standard LB	Create an Internal Load Balancer (Standard SKU) in the same region/resource group.
5. Load Balancer	5.2	Frontend IPs	Add two Static IPs: one for the AG Listener and one for the Cluster Core.
5. Load Balancer	5.3	Backend Pool	Add the NICs of both SQL VMs (exclude the witness server).
5. Load Balancer	5.4	Health Probes	Create probes: 59999 for the Listener and 58888 for the Cluster Core.
5. Load Balancer	5.5	LB Rules	Create rules for Port 1433 and 58888; Enable Floating IP (Direct Server Return).
6. Listener	6.1	Add Client Access Point	In Cluster Manager, add "Client Access Point" resource for the listener name.
6. Listener	6.2	Set IP & Dependencies	Set Listener IP to LB Static IP; set AG dependency on Name, and Name on IP.
6. Listener	6.3	Run PowerShell	Execute Set-ClusterParameter for ProbePort (59999/58888) and SubnetMask (255.255.255.255).
7. Finalize	7.1	Exclude Ports	Use netsh to exclude 58888 and 59999 from dynamic port range on all nodes.
7. Finalize	7.2	Set Port & Test	Set Listener Port in SSMS (default 1433); test connection using sqlcmd -S <listenerName> -E.

Mind Map:

Reference Notes for onprem setup:

step 1: 2 servers (part of same OU)

step 2: Activate Windows server failover clustering on both nodes

(add roles & features -> failover clustering feature)

step 3: dedicated interface for cluster comm

step 4: Use domain user accounts instead of service account for AG

step 5: Add the domain user to local admin grou[

control userpasswords2

step 6: Configure failover cluster using failover cluster manager

a.validate the cluster (select servers to be part of cluster and run checks)

b.on successful previous step, create cluster [choose nodes, assign wsfc cluster ip]

c. configure witness [configure cluster quorum settings] - choose a shared folder from AD

step 7: Launch mssql setup on node 1 (parallel do in other node as well)

a. new sql server standalone install or add feature on both nodes at same step

b. enter product key (for eval edition, no need) and click next

c. accept -> next

d. choose features to install (select db engine - mandatory)

e. set name for instance if needed else leave it to default

f. enter domain user/password , set service start to automatic for agent and db engine (browser leave it in disabled mode)

g. choose mixed mode for authentication (choose sa user)

h. allow for install to complete

step 8: Configure SQL server to function as Always on AG

a. Open config manager

b. right click on sql sever

c. choose always on ag, check "enable always on ag"

d. restart the sql server service

e. repeat this on other node

step 9: Open AD user/computer

Look for db servers - add both nodes and grant them all permissions to manage the objects[enable advanced option in view]

step 10: open ssms to setup db

a. connect to mssql node 1 sql server

b. create a new test db (set recovery model to full)

c. Secure a full backup for db (rightclick->task >full backup)

d. Go to AG -> choose new AG (give a new name)

e. select the db

f. add replica now to add the other node [choose sync commit]

g. go through the tabs

1. Choose listner tab (enter listener dns name -> port -> static ip -> Add vip)

2. choose automatic seeding

3. click next & finish

h. AG setup is complete

step 11: Perform test connection using listener

step 12: AG dashboard validation

step 13: Perform failover & failback testing

Note: 

Note: In case you have (Always on AG with 2 nodes)

1) MSSQL 2017 (didnt test in later version yet)

2) you have a FW restriction between the 2 nodes of the cluster!!!

3) you restricted SQL Server to a dedicated port for TCP/IP and your sure the dynamic port of SQL Server will fail due to the FW restrictions

You attempt to perform a failover testing using your AG, you will see your "Servername" field greyed out with just the <servername>/<MSSQL instance name>

In such situation you will not be able to perform connection tests using GUI.

To work around the situation, Enable options (in GUI screen), go to "Advanced Connection Parameters"

Enter the following...

NetworkLibrary=dbmssocn;Server=<servername>\<named instance>,<nondefault port>

This should help you connect, you can generate script from here to perform failover using commands next time.

Thanks