Tag: sql-server

Overview

SQL Server Always On Availability Groups offer HA for multiple databases using Windows Server Failover Clustering (WSFC). When deployed in Azure VMs, the environment mimics on-premises architecture with additional Azure-specific components.

---

🧱 1. Prerequisites

Requirement	Details
Azure VMs	At least 2 (recommended: DS-series or higher) with SQL Server installed
Windows Server	2016 or later
SQL Server Edition	Enterprise (Standard supports Basic AG)
Domain Controller	Required for WSFC (can be Azure VM or AD DS)
Virtual Network	All VMs in same region/VNet; subnet peering enabled if required
Static IPs	Assign private static IPs for cluster nodes
Load Balancer	Needed for listener IP configuration in Azure
Quorum Witness	Optional but recommended (can use Azure File Share Witness)

---

🖥 2. Environment Setup

🔹 Virtual Machines

• Deploy 2+ Azure VMs with SQL Server (Enterprise)
• Join all VMs to your Active Directory domain

🔹 Virtual Network

• Ensure VMs are in the same VNet and subnet
• Enable network connectivity via internal DNS or custom DNS

---

⚙️ 3. Configure Windows Failover Cluster (WSFC)

Step-by-Step:

1. Install Failover Clustering Feature powershellCopyEditInstall-WindowsFeature -Name Failover-Clustering -IncludeManagementTools
2. Validate Cluster Configuration
   • Open Failover Cluster Manager
   • Validate with both nodes and all required tests
3. Create the Cluster
   • Use a static IP address (do not register with Azure DNS)
   • E.g., New-Cluster -Name SQLCluster -Node SQL1,SQL2 -StaticAddress 10.0.0.100
4. Configure Cluster Quorum
   • Use File Share Witness (hosted in a 3rd VM or Azure File Share)

---

🛠 4. Enable Always On in SQL Server

On each SQL Server VM:

1. Open SQL Server Configuration Manager
2. Enable Always On Availability Groups in the SQL Server instance properties
3. Restart the SQL Server service

---

📦 5. Create and Configure Availability Group

1. Open SQL Server Management Studio (SSMS)
2. Create a database and ensure full recovery model
3. Take a full and log backup
4. Launch New Availability Group Wizard
   • Add replicas (SQL instances)
   • Enable automatic failover (if synchronous)
   • Add databases
   • Choose backup preferences
   • Create listener (initially leave blank)

---

🌐 6. Configure Azure Load Balancer for Listener

Azure does not support automatic ARP updates for failover cluster IPs, so a Load Balancer is required.

🧩 Create Load Balancer

• Type: Internal
• Frontend IP: Same subnet as SQL nodes
• Backend Pool: Add SQL VMs
• Health Probe: Port 59999, custom TCP listener
• Load Balancing Rule:
  • Port: 1433 (SQL)
  • Backend port: 1433
  • Floating IP: Enabled
  • Session persistence: None

🔧 Add Listener in SQL

• Run in SQL: sqlCopyEditALTER AVAILABILITY GROUP [YourAG] ADD LISTENER 'AGListener' (WITH IP ((N'10.0.0.200', N'255.255.255.0')), PORT=1433);

---

🔄 7. Test Failover

• Open Failover Cluster Manager or use SSMS
• Perform a manual failover to test behavior
• Verify listener redirection and database accessibility

---

🧪 8. Monitoring and Maintenance

• Use SQL Agent Alerts, Azure Monitor, and Log Analytics
• Enable email notifications for failover events
• Regularly check:
  • Health of replicas
  • Cluster events
  • Quorum state
  • Azure LB health probes

---

✅ Best Practices

• Use Accelerated Networking on all SQL VMs
• Use Premium SSD for data/log disks
• Configure automatic backups to Azure Blob
• Use NSG rules to control access to SQL ports
• Document your failover/failback procedure

---

📎 Optional Enhancements

Feature	Purpose
Azure File Share Witness	Avoid 3rd VM just for quorum
Azure Bastion	Secure RDP access without public IPs
Azure Recovery Services Vault	Protect databases with point-in-time restore
SQL Managed Instance	Consider for PaaS-like HA features