powershelldba.de

AlwaysOn Setup: Traditional T-SQL vs. Automated PowerShell

Configuring SQL Server AlwaysOn Availability Groups sounds like a one-liner: add the nodes, create the group, add a listener. But in reality, you're managing Kerberos, endpoints, service accounts, Windows Failover Clustering, and ten critical steps on multiple servers. Manual T-SQL takes hours. Automation takes minutes.

The Traditional Approach: Manual T-SQL Steps

Configuring AlwaysOn the "traditional" way means logging into each node, checking prerequisites, and running T-SQL on each server in sequence. Here's the sequence:

-- On the PRIMARY node only:
-- Step 1: Enable HADR (requires service restart)
ALTER SERVER CONFIGURATION SET HADR ON;
GO

-- Step 2: Create the HADR endpoint (port 5022 standard)
CREATE ENDPOINT Hadr_endpoint
   STATE = STARTED
   AS TCP ( LISTENER_PORT = 5022 , LISTENER_IP = ALL )
   FOR DATABASE_MIRRORING (
      ROLE = ALL,
      AUTHENTICATION = WINDOWS NEGOTIATE,
      ENCRYPTION = SUPPORTED
   );
GO

-- Step 3: Grant CONNECT permission on endpoint
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DOMAIN\ServiceAccount];
GO

-- Step 4: Create a test database with FULL recovery
CREATE DATABASE TestDB;
GO
ALTER DATABASE TestDB SET RECOVERY FULL;
GO

-- Step 5: Full backup (required for AG)
BACKUP DATABASE TestDB TO DISK = 'D:\Backups\TestDB_Full.bak';
BACKUP LOG TestDB TO DISK = 'D:\Backups\TestDB_Log.trn';
GO

-- Step 6: Create the Availability Group
CREATE AVAILABILITY GROUP MyAG
   WITH (
      CLUSTER_TYPE = WSFC,
      AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
      DB_FAILOVER = OFF
   )
   FOR DATABASE TestDB
   REPLICA ON
      N'Node1' WITH (
         ENDPOINT_URL = N'TCP://Node1.domain.com:5022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         FAILOVER_MODE = AUTOMATIC,
         BACKUP_PRIORITY = 100
      ),
      N'Node2' WITH (
         ENDPOINT_URL = N'TCP://Node2.domain.com:5022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         FAILOVER_MODE = AUTOMATIC,
         BACKUP_PRIORITY = 90
      );
GO

-- On EACH secondary node:
-- Step 7: Enable HADR (requires service restart on each)
ALTER SERVER CONFIGURATION SET HADR ON;
GO

-- Step 8: Create same endpoint as primary
CREATE ENDPOINT Hadr_endpoint
   STATE = STARTED
   AS TCP ( LISTENER_PORT = 5022 , LISTENER_IP = ALL )
   FOR DATABASE_MIRRORING (
      ROLE = ALL,
      AUTHENTICATION = WINDOWS NEGOTIATE,
      ENCRYPTION = SUPPORTED
   );
GO

-- Step 9: Grant CONNECT permission
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DOMAIN\ServiceAccount];
GO

-- Step 10: Restore database from primary (NORECOVERY chain)
RESTORE DATABASE TestDB FROM DISK = 'D:\Backups\TestDB_Full.bak' WITH NORECOVERY;
RESTORE LOG TestDB FROM DISK = 'D:\Backups\TestDB_Log.trn' WITH NORECOVERY;
GO

-- Step 11: Join the AG on secondaries
ALTER AVAILABILITY GROUP MyAG JOIN;
GO

-- Back on PRIMARY:
-- Step 12: Create a listener (IP/hostname for client connections)
CREATE AVAILABILITY GROUP LISTENER MyAG_Listener
   ON AVAILABILITY GROUP MyAG
   WITH (
      IP (('192.168.1.50', '255.255.255.0')),
      PORT = 1433
   );
GO

This is simplified—in reality you must also:

The Automated Approach: PowerShell GUI Tool

The AlwaysOnSetup.ps1 tool is a Windows Forms application that orchestrates every step automatically. No T-SQL typing required. Here's what it does:

Key Capabilities

Feature Manual T-SQL AlwaysOnSetup
Cluster/Node Detection Manual—you find nodes yourself Automatic—queries WSFC, lists all nodes
SQL Info Reading Manual—check version, HADR status on each Automatic—collects SQL version, service account, HADR state
Service Account Detection Manual—must know account name Automatic—reads from WMI on each node
Kerberos Testing Manual—setspn, network monitoring Automatic—tests Windows-Auth first; falls back to SQL if Kerberos fails
HADR Activation Manual—SQL command on each node, service restart Automatic—executes, waits for restart, confirms ready
Endpoint Configuration Manual—same code on each node Automatic—creates endpoint, grants permissions on all nodes
Database Preparation Manual—backup, restore on secondaries Automatic—creates test DB, runs full backup, restores to all secondaries
AG Creation & Join Manual—CREATE on primary, ALTER...JOIN on each secondary Automatic—creates on primary, joins all secondaries, manages Autoseed
Listener Configuration Manual—CREATE AVAILABILITY GROUP LISTENER with IP/port Automatic—creates and binds listener, verifies DNS resolution
AG Sync Job Manual—SQL job schedule (if desired) Automatic—creates daily AG status synchronization job at 02:00
SPN Validation Manual—AD setspn lookup, domain admin ticket Automatic—checks SPNs, generates AD team request file if needed
Cleanup Manual—remember to drop temp logins Automatic—removes temporary logins after completion

Error Handling: Manual vs. Automated

Manual T-SQL Approach

If you hit an error mid-setup, you're stuck. Did HADR activation fail on Node2? Now you must:

Automated Approach

AlwaysOnSetup handles expected errors gracefully:

Operational Efficiency

Time Savings

Manual T-SQL: 2–4 hours (depending on prerequisites, errors, and troubleshooting)
AlwaysOnSetup: 10–20 minutes (after prerequisites are met: WSFC healthy, modules installed, Kerberos working or SQL-Auth ready)

Prerequisites That Both Require

Where Automation Adds Most Value

You gain the most benefit when:

Configuration and Customization

AlwaysOnSetup provides a PropertyGrid UI where you can override detected values:

After configuration, one click—"Start Configuration"—runs all steps end-to-end.

When to Use Each

Use Manual T-SQL When:

Use AlwaysOnSetup When:

The Bottom Line

Manual T-SQL gives you maximum control—at the cost of time, risk, and human error. AlwaysOnSetup gives you speed, safety, and repeatability—by automating the routine and keeping you in control for exceptions.

The tool doesn't replace your knowledge of AlwaysOn; it frees you to focus on the design decisions that matter: failover strategy, backup preferences, and disaster recovery topology. It handles the mechanics so you can handle the architecture.

← Back to Blog