· Uwe Janke
Commands / Test-sqmDistributedAgMigrationPrerequisite
sqmSQLTool › Commands › Test-sqmDistributedAgMigrationPrerequisite

Test-sqmDistributedAgMigrationPrerequisite Always On

Read-only preflight for the migration of an AlwaysOn availability group to a new cluster by Distributed AG. Checks both sides (connection, sysadmin, version and edition, HADR, AG role and health, conflicts, endpoint, database state, name clashes, paths and space on the target, DNS and port, backup path) and returns blockers and warnings with cause and remedy in German and English.

Execution Flow

START Local: dbatools installed? blocker if missing Per side (source and target): connect, sysadmin, SQL version, edition, HADR Distributed AG needs 2016 SP1 or later, Enterprise or Developer Per side: AG exists, regular AG, PRIMARY on this node, HEALTHY blockers; AG state read from the availability catalog views Per side: already in another Distributed AG? name conflict? endpoint state, port, role an existing own Distributed AG is only a warning (resume) Source: databases synchronizing and not suspended, file list blockers for suspended or not synchronizing databases Source against target: version, collation, CONNECT right of the service account target older than source is a blocker Target: database name clash, file paths (xp_fileexist), free space per volume name clash blocks; paths block on SQL 2016, otherwise warn; space warns TDE: every certificate thumbprint of the source databases must exist on the target blocker; FILESTREAM and In-Memory files give a warning Automatic seeding only: target replicas AUTOMATIC, earlier seeding failures, log space on the source replica seeding mode blocks; the rest warns; CREATE ANY DATABASE with several replicas warns Network from this machine: DNS and TCP port of both FQDNs warnings; what matters is the server to server connection Manual seeding only: backup path given, UNC, reachable path missing is a blocker Return Checks, Blockers, Warnings, Passed, CanProceed CanProceed only if no blocker failed; the function never changes anything DONE

Synopsis

Read-only preflight for the migration of an AlwaysOn availability group to a new cluster by Distributed AG. An unreachable instance does not throw: it becomes a blocker with cause and remedy and the remaining checks of the other side still run. Every check carries a German and an English message and remedy, so the result can be shown directly in the migration wizard.

Beyond the basics the function checks what makes seeding fail late: TDE certificates on the target (same thumbprint), the seeding mode of every target replica, earlier failed seeding runs, the free space of the log volumes on the source and FILESTREAM or In-Memory files. With -SeedingMode Manual the automatic-seeding checks are skipped and the backup path is checked instead. CREATE ANY DATABASE cannot be queried; with more than one target replica the function therefore always warns and names the command to run on the other replicas.

DNS and port tests run from the machine that executes the command. The connection that really matters is server to server, therefore these two checks are warnings only.

Syntax

Test-sqmDistributedAgMigrationPrerequisite
    -SourceInstance <String>  -SourceAgName <String>
    -TargetInstance <String>  -TargetAgName <String>
    [-SqlCredential <PSCredential>]
    [-DistributedAgName <String>]    # default: Source_Target
    [-SourceFqdn <String>] [-TargetFqdn <String>]
    [-EndpointPort <Int32>]          # default 5022
    [-SeedingMode Automatic|Manual] [-BackupPath <String>]

Parameters

ParameterTypeDefaultDescription
-SourceInstanceString,Instance (node or listener) of the source. It has to host the PRIMARY of the source AG.
-SourceAgNameString,Name of the source AG.
-TargetInstanceString,Instance of the new cluster. It has to host the PRIMARY of the target AG.
-TargetAgNameString,Name of the target AG. It has to exist already.
-SqlCredentialPSCredential,Optional credential for both sides.
-DistributedAgNameString<Source>_<Target>Planned name of the Distributed AG. Used for the name-conflict check.
-SourceFqdn / -TargetFqdnString,FQDNs for LISTENER_URL. When given, DNS resolution and the TCP port are tested from this machine.
-EndpointPortInt325022Expected endpoint port (1024 to 65535). A different actual port is a warning.
-SeedingModeStringAutomaticAutomatic or Manual. Manual additionally checks the backup path.
-BackupPathString,Backup path for manual seeding. A UNC share that both SQL service accounts can use is recommended.

Return Value

PropertyDescription
ChecksAll checks with Id, Category/CategoryEn, Name/NameEn, Severity (Blocker or Warning), Status (Pass, Fail, Warn), MessageDe/MessageEn and RemedyDe/RemedyEn.
Blockers / WarningsThe failed and the warned checks.
PassedNumber of passed checks.
CanProceed$true only when no blocker failed.
SourceDatabasesNames of the databases in the source AG (used by the wizard for the plan).
DistributedAgName, TimestampPlanned name and time of the check.

Examples

Example 1, Run the checks

Test-sqmDistributedAgMigrationPrerequisite -SourceInstance "SQL01" -SourceAgName "ProdAG" `
    -TargetInstance "NEW01" -TargetAgName "ProdAG_NEW"

Example 2, With FQDNs and the endpoint port, show only problems

$r = Test-sqmDistributedAgMigrationPrerequisite -SourceInstance "SQL01" -SourceAgName "ProdAG" `
    -TargetInstance "NEW01" -TargetAgName "ProdAG_NEW" `
    -SourceFqdn "sql01.corp.local" -TargetFqdn "new01.corp.local" -EndpointPort 5022
$r.Checks | Where-Object Status -ne 'Pass' | Format-Table Status, NameEn, MessageEn, RemedyEn -Wrap

Example 3, Manual seeding with a backup share

Test-sqmDistributedAgMigrationPrerequisite -SourceInstance "SQL01" -SourceAgName "ProdAG" `
    -TargetInstance "NEW01" -TargetAgName "ProdAG_NEW" `
    -SeedingMode Manual -BackupPath "\\fileserver\sqlbackup"

Example 4, Use the result in a script

$r = Test-sqmDistributedAgMigrationPrerequisite @params
if (-not $r.CanProceed) { $r.Blockers | ForEach-Object { Write-Warning "$($_.NameEn): $($_.MessageEn)" } }
⬆ Index
All Commands