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