MS SQL Server Fail-over Cluster Implementation

Prerequisites:

1. Two Windows Server VMs (2016/2019/2022)
2. Each VM needs:
   • Minimum 8GB RAM (16GB recommended)
   • 4 vCPUs
   • 100GB+ storage for OS and SQL Server
   • Active Directory domain-joined
   • SQL Server Enterprise Edition installed
   • Network connectivity between VMs

Step 1: Prepare Both VMs

1. Ensure both VMs are patched with the same updates
2. Join both VMs to the same Active Directory domain
3. Install SQL Server Enterprise Edition on both nodes:
   • Use the same instance name, file paths, and configuration
   • Install with the same service accounts
   • Include SQL Server Replication feature

Step 2: Configure Windows Failover Clustering

1. Add the Failover Clustering feature on both nodes
2. Run the Cluster Validation wizard to check for any issues
3. Create the Windows Failover Cluster with a unique name and IP address
4. Verify the cluster is healthy in Failover Cluster Manager

Step 3: Configure SQL Server Always On

1. Enable SQL Server Always On feature in SQL Server Configuration Manager on both nodes
2. Create a database mirroring endpoint on both servers (port 5022)
3. Create the Availability Group on the primary node
4. Add the database(s) to the Availability Group
5. Join the secondary replica to the Availability Group
6. Create a listener for client connectivity

PowerShell Script

Step 4: Test Failover Functionality

Manually fail over the Availability Group:

Switch-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\VM1\DEFAULT\AvailabilityGroups\SQLAG"

Perform a forced failover (for disaster recovery testing):

Switch-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\VM1\DEFAULT\AvailabilityGroups\SQLAG" -AllowDataLoss

Verify client connectivity through the listener after failover

Performance Tuning:

# Adjust synchronization mode for better performance
Set-SqlAvailabilityReplica -AvailabilityMode "AsynchronousCommit" -Path "SQLSERVER:\SQL\VM1\DEFAULT\AvailabilityGroups\SQLAG\AvailabilityReplicas\VM2"