· Uwe Janke
Commands / Show-sqmDistributedAgGui
sqmSQLTool › Commands › Show-sqmDistributedAgGui

Show-sqmDistributedAgGui Always On

Windows-style wizard for the migration of an AlwaysOn availability group to a new AlwaysOn cluster by Distributed AG: connect, configure, preflight checks, summary with dry run, execution, synchronization, failover, listener move and finish. Resumable, with error handling per step and German or English interface.

Execution Flow

START Welcome: new migration, resume, or operations (status, failover, rollback) saved migrations are listed; damaged state files are marked and not loaded Connection: source and target, Windows or SQL login, load AGs both sides are tested in the background; same instance, HADR off, no sysadmin are rejected Configuration: DAG name, endpoint port, FQDNs, service account, seeding every field is validated live; defaults come from the servers Preflight checks (Test-sqmDistributedAgMigrationPrerequisite) errors block, warnings must be acknowledged Summary with T-SQL preview and dry run (-WhatIf), type the DAG name to confirm nothing changes on the servers before the start is confirmed Execution: New-sqmDistributedAvailabilityGroup, per database Add-sqmDatabaseToDistributedAg on error: cause, fix and Retry, Skip (already exists) or Abort Synchronization: live state, queues and seeding; Next only when all databases are ready refresh every 10 seconds Failover (Invoke-sqmDistributedFailover) with three confirmations; Rollback afterwards possible blocked until all databases are ready; logins and jobs confirmed; type the DAG name Listener move: remove from old AG, cluster/AD team creates it, register on target mandatory wait of 15 minutes, instruction to copy for the team Finish: result and the clean-up T-SQL to copy (not executed automatically) state file and report folder are kept DONE

Synopsis

A Windows-style wizard for the migration of an AlwaysOn availability group to a new AlwaysOn cluster by Distributed AG. It calls only the existing sqmSQLTool functions. All actions run in a background runspace, so the window stays usable. After every completed step the state is saved without passwords; an interrupted migration can be resumed, and a lock file keeps two wizards from working on the same migration. German and English can be switched in the window.

The failover procedure and the listener move have not been tested against two real WSFC clusters in the lab. Verified are the T-SQL queries against SQL Server 2022, the preflight checks, the error classification and the interface. Run the complete flow on a test system before productive use.
The wizard does not remove the Distributed AG automatically. The clean-up DROP AVAILABILITY GROUP is offered as T-SQL to copy and should only run after a stable production period; afterwards a rollback is no longer possible.

Screenshots

Connection page

Preflight checks page

Synchronization page

Illustrative screenshots with sample data, rendered in the lab.

Longer pauses

A week between the first seeding and the cut-over is covered. Seeding continues by itself in the Distributed AG; close the wizard after the seeding and resume later via Resume at the synchronization page.

  • The state folder is local by default. To resume on another machine start the wizard with -StatePath pointing to a share. The start page shows the folder in use.
  • The preflight checks do not run again on resume, but the database list is re-read at every refresh of the synchronization page. New databases in the source AG show up in the log and are checked at failover.
  • Logins, Agent jobs and linked servers are not transferred by the wizard. The failover page asks you to confirm that they are (for example with Sync-sqmLoginsToAlwaysOn).
  • Failover and listener move belong in the same maintenance window.
  • A lock file from another machine is never treated as orphaned; the message names the file so you can delete it by hand.

Error handling

CategoryCauseFix
LoginThe server rejects the login.Check user name and password.
Unreachable / TimeoutServer, port, firewall or load.Check name, port, firewall; the step can be retried safely.
PermissionThe account lacks rights.sysadmin is required on both sides.
Endpoint connectionThe endpoint link between the clusters is down.Check port, firewall, CONNECT right and FQDN.
Backup pathNot reachable or writable for a SQL service account.Use a UNC path and authorize both service accounts.
Already existsObject from an earlier run or a name conflict.Skip the step if it is your own object.

Syntax

Show-sqmDistributedAgGui
    [-Language de|en]        # default: module language, switchable in the window
    [-StatePath <String>]     # folder for state and lock files

Parameters

ParameterTypeDefaultDescription
-LanguageStringmodule languagede or en. Can be switched in the window.
-StatePathString<Output>\DistributedAgMigrationFolder for the state file (JSON, no passwords) and the lock file of a migration.

Examples

Example 1, Start the wizard

Import-Module sqmSQLTool
Show-sqmDistributedAgGui

Example 2, English interface and a shared state folder

Show-sqmDistributedAgGui -Language en -StatePath "\\fileserver\dba\migration"

Example 3, The same steps on the command line (dry run of the failover)

Invoke-sqmDistributedFailover -SqlInstance "SQL01" -TargetInstance "NEW01" `
    -AvailabilityGroupName "ProdAG_ProdAG_NEW" -WhatIf
⬆ Index
All Commands