How to Configure Cluster-Aware Updating (CAU) on Windows Server 2012 R2

How to Configure SQL Server AlwaysOn Availability Groups on Windows Server 2012 R2

SQL Server 2012 and 2014 AlwaysOn Availability Groups (AG) provide high availability and disaster recovery for SQL Server databases without requiring shared storage. An AG groups multiple user databases that fail over together, with one primary replica handling read-write workloads and up to four secondary replicas providing redundancy (synchronous for HA, asynchronous for DR). Clients connect to a Listener — a virtual network name with an IP address — that automatically routes to the current primary. AlwaysOn requires a Windows Server Failover Cluster (WSFC) as its underlying health monitoring infrastructure, but unlike traditional clustering, AlwaysOn does NOT require shared storage between nodes.

Prerequisites

– Windows Server 2012 R2 Failover Cluster with 2+ nodes (all must be cluster members)
– SQL Server 2012 or 2014 Enterprise (or Standard for basic AG with 2 nodes in 2014) installed on all nodes
– All SQL instances must use the same SQL Server service account
– All nodes in the same AD domain
– Shared or matching database files (for initial synchronization via backup/restore)
– An IP address for the AG Listener on each subnet
– Port 5022 open between all AG replicas (mirroring endpoint)

Step 1: Create the Windows Server Failover Cluster

Import-Module FailoverClusters

# Install Failover Clustering feature on all nodes
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools
Install-WindowsFeature -Name RSAT-Clustering-PowerShell

# Run cluster validation (on any node — tests all nodes)
Test-Cluster -Node "SQLPROD01","SQLPROD02" -Include "Network","Storage","System Configuration"

# Create the cluster with a static IP
New-Cluster -Name "SQLCLUSTER01" `
    -Node "SQLPROD01","SQLPROD02" `
    -StaticAddress "10.10.1.100" `
    -NoStorage  # AlwaysOn doesn't need shared storage

# Verify cluster
Get-Cluster | Format-List Name, QuorumType, QuorumResource

# Configure file share witness (if 2-node cluster — prevents split-brain)
Set-ClusterQuorum -FileShareWitness "\fileserverSQLClusterWitness"

# Verify all nodes are up
Get-ClusterNode | Select-Object Name, State | Format-Table -AutoSize

Step 2: Enable AlwaysOn Availability Groups on Each SQL Instance

# Enable AlwaysOn on each SQL Server instance (must be a cluster node)
# Run on SQLPROD01:
Enable-SqlAlwaysOn -ServerInstance "SQLPROD01" -Force

# Run on SQLPROD02:
Enable-SqlAlwaysOn -ServerInstance "SQLPROD02" -Force

# Restart SQL Server service on both nodes to apply the change
Restart-Service MSSQLSERVER

# Verify AlwaysOn is enabled
Get-Item "SQLSERVER:SQLSQLPROD01DEFAULT" | 
    Select-Object IsHadrEnabled

# If using a named instance (e.g., SQLPROD01SQLPROD)
Enable-SqlAlwaysOn -ServerInstance "SQLPROD01SQLPROD" -Force

Step 3: Create the Database Mirroring Endpoint

AlwaysOn uses a TCP endpoint (the mirroring endpoint) on each replica for synchronization traffic. This must be created on every replica before creating the AG:

Import-Module SQLPS -DisableNameChecking

# Create the endpoint on PRIMARY (SQLPROD01)
Invoke-Sqlcmd -ServerInstance "SQLPROD01" -Query @"
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CORPsvc_SQLEngine];
"@

# Create the endpoint on SECONDARY (SQLPROD02)
Invoke-Sqlcmd -ServerInstance "SQLPROD02" -Query @"
CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CORPsvc_SQLEngine];
"@

# Verify endpoints on both instances
Invoke-Sqlcmd -ServerInstance "SQLPROD01" -Query @"
SELECT name, state_desc, role_desc, connection_auth_desc, encryption_algorithm_desc
FROM sys.database_mirroring_endpoints
"@

Step 4: Prepare Databases for the Availability Group

Import-Module SQLPS -DisableNameChecking

$primary = "SQLPROD01"
$secondary = "SQLPROD02"
$databases = @("SalesDB","OrderDB","InventoryDB")
$backupShare = "\fileserverSQLAGSetup"

New-Item -ItemType Directory -Path $backupShare -Force

foreach ($db in $databases) {
    Write-Host "Preparing $db for AlwaysOn..."
    
    # Ensure database is in FULL recovery model
    Invoke-Sqlcmd -ServerInstance $primary -Query @"
ALTER DATABASE [$db] SET RECOVERY FULL
"@
    
    # Take a full backup
    Invoke-Sqlcmd -ServerInstance $primary -Query @"
BACKUP DATABASE [$db] 
TO DISK = N'$backupShare$db`_Full.bak'
WITH FORMAT, INIT, COMPRESSION, STATS = 10
"@
    
    # Take a log backup (required for backup chain)
    Invoke-Sqlcmd -ServerInstance $primary -Query @"
BACKUP LOG [$db]
TO DISK = N'$backupShare$db`_Log.bak'
WITH FORMAT, INIT, COMPRESSION, STATS = 10
"@
    
    # Restore on secondary WITH NORECOVERY (leaves DB in restoring state)
    Invoke-Sqlcmd -ServerInstance $secondary -Query @"
RESTORE DATABASE [$db] 
FROM DISK = N'$backupShare$db`_Full.bak'
WITH NORECOVERY, REPLACE, STATS = 10
"@
    
    Invoke-Sqlcmd -ServerInstance $secondary -Query @"
RESTORE LOG [$db]
FROM DISK = N'$backupShare$db`_Log.bak'
WITH NORECOVERY, STATS = 10
"@
    
    Write-Host "$db prepared on secondary" -ForegroundColor Green
}

Step 5: Create the Availability Group

Import-Module SQLPS -DisableNameChecking

$agName     = "AG_Production"
$primary    = "SQLPROD01"
$secondary  = "SQLPROD02"
$primaryEP  = "TCP://SQLPROD01.corp.local:5022"
$secondaryEP= "TCP://SQLPROD02.corp.local:5022"
$databases  = @("SalesDB","OrderDB","InventoryDB")
$listenerIP = "10.10.1.101"

# Build the AG creation SQL
$dbList = $databases | ForEach-Object { "N'$_'" }
$dbListStr = $dbList -join ", "

Invoke-Sqlcmd -ServerInstance $primary -Query @"
CREATE AVAILABILITY GROUP [$agName]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
      FAILURE_CONDITION_LEVEL = 3,
      HEALTH_CHECK_TIMEOUT = 30000,
      DB_FAILOVER = ON,
      DTC_SUPPORT = NONE)
FOR DATABASE $dbListStr
REPLICA ON 
N'SQLPROD01' WITH (
    ENDPOINT_URL = N'$primaryEP',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SESSION_TIMEOUT = 10,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
    PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL)
),
N'SQLPROD02' WITH (
    ENDPOINT_URL = N'$secondaryEP',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SESSION_TIMEOUT = 10,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY),
    PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL)
)
LISTENER N'AGLISTEN01' (
    WITH IP ((N'$listenerIP', N'255.255.255.0')),
    PORT = 1433
);
"@

Write-Host "Availability Group created. Joining secondary..."

# Join the secondary replica to the AG
Invoke-Sqlcmd -ServerInstance $secondary -Query @"
ALTER AVAILABILITY GROUP [$agName] JOIN;
"@

# Join each database on the secondary
foreach ($db in $databases) {
    Invoke-Sqlcmd -ServerInstance $secondary -Query @"
ALTER DATABASE [$db] SET HADR AVAILABILITY GROUP = [$agName];
"@
    Write-Host "Joined $db to AG on secondary" -ForegroundColor Green
}

Step 6: Verify Availability Group Health

Import-Module SQLPS -DisableNameChecking

# Check AG status
Invoke-Sqlcmd -ServerInstance "SQLPROD01" -Query @"
SELECT ag.name AS AGName,
       ar.replica_server_name AS Replica,
       rs.role_desc AS Role,
       rs.operational_state_desc AS OperationalState,
       rs.connected_state_desc AS ConnectedState,
       rs.synchronization_health_desc AS SyncHealth,
       ar.availability_mode_desc AS Mode,
       ar.failover_mode_desc AS FailoverMode
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states rs ON ar.replica_id = rs.replica_id
ORDER BY AGName, Role
"@

# Check database synchronization status
Invoke-Sqlcmd -ServerInstance "SQLPROD01" -Query @"
SELECT ag.name AS AGName,
       db.name AS DatabaseName,
       drs.synchronization_state_desc AS SyncState,
       drs.synchronization_health_desc AS SyncHealth,
       drs.redo_queue_size AS RedoQueueKB,
       drs.redo_rate AS RedoRateKB_s,
       drs.log_send_queue_size AS SendQueueKB
FROM sys.availability_groups ag
JOIN sys.availability_databases_cluster adc ON ag.group_id = adc.group_id
JOIN sys.databases db ON adc.database_name = db.name
JOIN sys.dm_hadr_database_replica_states drs ON db.database_id = drs.database_id
ORDER BY AGName, DatabaseName
"@

Step 7: Test Failover

Import-Module SQLPS -DisableNameChecking

$agName = "AG_Production"

# Perform a manual failover (planned — synchronous, no data loss)
# Run from the TARGET secondary (SQLPROD02)
Invoke-Sqlcmd -ServerInstance "SQLPROD02" -Query @"
ALTER AVAILABILITY GROUP [$agName] FAILOVER;
"@

Write-Host "Manual failover initiated. SQLPROD02 is now primary."
Start-Sleep 10

# Verify the new primary
Invoke-Sqlcmd -ServerInstance "SQLPROD02" -Query @"
SELECT @@SERVERNAME AS Server, 
       ISNULL(HADR.role_desc,'STANDALONE') AS AGRole
FROM sys.dm_hadr_availability_replica_states HADR
JOIN sys.availability_replicas AR ON HADR.replica_id = AR.replica_id
WHERE AR.replica_server_name = @@SERVERNAME
"@

# Fail back to original primary
Invoke-Sqlcmd -ServerInstance "SQLPROD01" -Query @"
ALTER AVAILABILITY GROUP [$agName] FAILOVER;
"@
Write-Host "Failed back to SQLPROD01"

Verification

# Comprehensive AlwaysOn AG verification
Import-Module SQLPS -DisableNameChecking

Write-Host "=== AlwaysOn AG Health ===" -ForegroundColor Cyan

# AG status
$agHealth = Invoke-Sqlcmd -ServerInstance "SQLPROD01" -Query @"
SELECT ag.name, ags.primary_replica, ags.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_group_states ags ON ag.group_id = ags.group_id
"@
$agHealth | Format-Table -AutoSize

# Listener status
Invoke-Sqlcmd -ServerInstance "SQLPROD01" -Query @"
SELECT agl.dns_name, agl.port, aglip.ip_address, aglip.ip_subnet_mask
FROM sys.availability_group_listeners agl
JOIN sys.availability_group_listener_ip_addresses aglip ON agl.listener_id = aglip.listener_id
"@ | Format-Table -AutoSize

# Connect via listener to verify routing
Invoke-Sqlcmd -ServerInstance "AGLISTEN01" -Query "SELECT @@SERVERNAME, GETDATE()"

Summary

SQL Server AlwaysOn Availability Groups on Windows Server 2012 R2 deliver enterprise-grade HA and DR without shared storage. The deployment requires a functioning WSFC, mirroring endpoints on each replica, databases prepared via backup-restore with NORECOVERY, an AG configured with synchronous commit and automatic failover for HA replicas, a Listener for transparent client connectivity, and optionally asynchronous replicas at remote DR sites. Synchronous replicas provide automatic failover with zero committed data loss, while asynchronous DR replicas provide business continuity for site disasters with minimal RPO. The Listener abstracts the underlying replica topology so application connection strings never need to change after a failover event.