Always On

New-sqmDistributedAvailabilityGroup

Establishes a Distributed Availability Group between two independent WSFC clusters, joining an existing secondary-side AG to a primary-side AG across cluster boundaries — the cornerstone of cross-datacenter / cross-cluster Always On topologies.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes (High)
Output: PSCustomObject

Execution Flow

START dbatools available? throw error STEP 1: Validate primary AG state = HEALTHY Healthy? Status=FAILED, return Yes STEP 2: Validate secondary AG exists on target cluster -ServiceAccount given? No CREATE LOGIN + GRANT CONNECT STEP 3a: CREATE AVAILABILITY GROUP ... WITH (DISTRIBUTED) on primary -EnableAutoSeed? No STEP 3c: GRANT CREATE ANY DATABASE (secondary) STEP 3d (CRITICAL): ALTER AVAILABILITY GROUP ... JOIN AVAILABILITY GROUP on secondary — registers it into the distributed AG STEP 4: Verify via sys.availability_groups (is_distributed=1) STEP 5: Write detailed report TXT (NextSteps) Return PSCustomObject (Status=SUCCESS) DONE
High-impact, cross-cluster operation. Step 3d (the secondary's JOIN AVAILABILITY GROUP) is the critical action that actually establishes the distributed topology — omitting it leaves a one-sided, non-functional configuration. Always validate the primary AG is HEALTHY before proceeding.

Parameters

ParameterTypeRequiredDescription
-PrimaryInstanceStringRequiredPrimary-side SQL instance (primary replica of the source AG).
-PrimaryAgNameStringRequiredName of the source (primary-side) Availability Group.
-SecondaryInstanceStringRequiredSecondary-side SQL instance (primary replica of the target AG).
-SecondaryAgNameStringRequiredName of the target (secondary-side) Availability Group.
-PrimaryFqdnStringRequiredFully qualified DNS name of the primary cluster's listener/endpoint.
-SecondaryFqdnStringRequiredFully qualified DNS name of the secondary cluster's listener/endpoint.
-EndpointNameStringOptionalMirroring endpoint name. Default: Hadr_endpoint.
-ServiceAccountStringOptionalService account to GRANT CONNECT ON ENDPOINT.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-EnableAutoSeedSwitchOptionalGrants CREATE ANY DATABASE on the secondary for automatic seeding.
-SeedingModeStringOptionalAutomatic / Manual. Default: Automatic.
-OutputPathStringOptionalReport output directory. Default: C:\System\WinSrvLog\MSSQL.
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning a Failed status object.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support (ConfirmImpact=High).

Return Value

Returns a PSCustomObject with: Status (SUCCESS / FAILED), PrimaryInstance, PrimaryAgName, SecondaryInstance, SecondaryAgName, DistributedAgName, SeedingMode, Timestamp, Steps, ReportFile, NextSteps.

Examples

Example 1 — Join two clusters into a distributed AG

New-sqmDistributedAvailabilityGroup -PrimaryInstance "SQL01" -PrimaryAgName "AG-DC1" -SecondaryInstance "SQL11" -SecondaryAgName "AG-DC2" -PrimaryFqdn "ag-dc1-lst.firma.de" -SecondaryFqdn "ag-dc2-lst.firma.de"

Example 2 — Manual seeding with service account

New-sqmDistributedAvailabilityGroup -PrimaryInstance "SQL01" -PrimaryAgName "AG-DC1" -SecondaryInstance "SQL11" -SecondaryAgName "AG-DC2" -PrimaryFqdn "ag-dc1-lst.firma.de" -SecondaryFqdn "ag-dc2-lst.firma.de" -SeedingMode "Manual" -ServiceAccount "FIRMA\svc-sql"