Project Documentation: SQL Server Always On Availability Group Deployment
Environment: Azure
Resource Group: RGProd
Virtual Network: vnet-eastus
Subnet: snet-eastus-1
1. Server Configuration & Credentials
Additional Account:
- SQL Service Account: sqladmin / sql@123456789 (Used to run SQL Server services on both nodes)
Network Details:
- Domain: prodb.com
- Default Gateway: 172.16.0.1
- Subnet Mask: 255.255.255.0 (/24)
- Azure DNS Zone: Configured for prodb.com with an associated record set.
2. Deployment Phases
Phase 1: Domain Controller (DC) Setup
- Initial Configuration: VM DC was provisioned with the static IP 172.16.0.4.
- DNS Role: Installed and configured the Active Directory Domain Services role, promoting the server to a Domain Controller for the new prodb.com forest.
- Firewall: Windows Firewall was disabled to facilitate unobstructed communication during setup.
Phase 2: Member Server Preparation (Node1 & Node2)
- Domain Join: Both SQL nodes (Node1, Node2) were joined to the prodb.com domain.
- Firewall Configuration: The Windows Firewall was configured to allow specific ports critical for clustering and SQL Server communication:
- SQL & AG: 1433 (SQL), 5022 (Database Mirroring Endpoint)
- Clustering: 135, 3343
- Load Balancer Health Probe: 59999
- General: 80 (HTTP), 137
- Connectivity Validation: Full bidirectional network connectivity and DNS resolution between DC, Node1, and Node2 was confirmed.
Phase 3: Failover Cluster Installation
- Feature Installation: The Failover Clustering feature was added to both Node1 and Node2.
- Cluster Validation: The cluster configuration was validated successfully to ensure all prerequisites were met.
- Cluster Creation: The two-node cluster, named procluster, was created. A static IP address 172.16.0.40 was assigned to the cluster core resource.
Phase 4: SQL Server Installation & Configuration
- Installation: SQL Server was installed on both cluster nodes.
- Service Account Configuration: The SQL Server and SQL Server Agent services on both nodes were configured to run under the domain service account sqladmin.
- Enabling Always On: The Always On Availability Groups feature was enabled via SQL Server Configuration Manager, requiring a restart of the SQL Server service on both nodes.
Phase 5: Always On Availability Group (AG) Deployment
- Database Preparation: A user database was created. A full backup was performed to ensure it was in the correct recovery model for AG synchronization.
- AG Creation: The Availability Group (proag) was created:
- Node1 was designated as the initial Primary Replica.
- Node2 was added as a Synchronous Secondary Replica.
- The database was successfully joined to the AG.
Phase 6: Load Balancer & Listener Configuration
Azure Load Balancer: An Internal Azure Load Balancer (Standard SKU) was deployed in RGProd.
- Backend Pool: Node1 and Node2 were added.
- Health Probe: Configured on port 59999.
- Load Balancing Rule: A rule for port 1433 was created with Floating IP (Direct Server Return) enabled.
- Frontend IP: 172.16.0.100
Cluster IP Configuration: The following PowerShell script was executed to integrate the cluster with the Azure Load Balancer. This configures the cluster's IP resource to respond to the health probe on the specified port.
powershell
$ClusterNetworkName = "Cluster Network 1"
$IPResourceName = "proag_172.16.0.100" # Name of the Cluster IP Resource
$ILBIP = "172.16.0.100" # IP of the Azure Load Balancer
[int]$ProbePort = 59999 # Health Probe Port
Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{
"Address"="$ILBIP";
"ProbePort"=$ProbePort;
"SubnetMask"="255.255.255.255";
"Network"="$ClusterNetworkName";
"EnableDhcp"=0
}
Phase 7: Read-Only Routing Configuration
To direct read-intent applications to the secondary replica, read-only routing was configured using PowerShell.
powershell
# Set the read-only routing URLs for each replica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://Node1.prodb.com:1433" -Path "SQLSERVER:\SQL\Node1\Default\AvailabilityGroups\proag\AvailabilityReplicas\Node1"
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://Node2.prodb.com:1433" -Path "SQLSERVER:\SQL\Node1\Default\AvailabilityGroups\proag\AvailabilityReplicas\Node2"
# Define the read-only routing list (order of precedence) on the primary replica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "Node2","Node1" -Path "SQLSERVER:\SQL\Node1\Default\AvailabilityGroups\proag\AvailabilityReplicas\Node1"
0 Comments