Commands / Add-sqmDatabaseToDistributedAg
Add-sqmDatabaseToDistributedAg
Always On sqmSQLTool v1.8.2+ · Add ✓ -WhatIf supported
Adds a database to a Distributed Always On Availability Group in a defined sequence of steps: verify the database on the primary, create a compressed full backup, follow up with a log backup, restore both to the secondary instance using NORECOVERY, join the restored database to the secondary AG, and finally monitor synchronization state.

Each step is recorded in a Steps list on the returned [PSCustomObject]. On success the object carries Status = 'SUCCESS'; any unhandled error returns Status = 'FAILED' with the error message.

Prerequisites: A secondary AG and the Distributed AG relationship must already be established. The -BackupPath must be reachable from both instances (UNC share or local path with appropriate permissions).

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMEPrimary SQL Server instance (source). Accepts pipeline input.
-AvailabilityGroupNamestringRequired, Name of the Distributed AG.
-DatabaseNamestringRequired, Database to add to the Distributed AG.
-SecondaryInstancestringRequired, SQL Server instance on the secondary cluster where the database is restored.
-BackupPathstringOptionalC:\BackupsDirectory for full (.bak) and log (.trn) backup files. Created automatically if not present.
-SqlCredentialPSCredentialOptional, Used for all connections (primary and secondary).
-EnableExceptionswitchSwitch$falseRe-throw errors instead of returning a FAILED result object.
-WhatIf / -ConfirmswitchOptional, Standard ShouldProcess support (ConfirmImpact: High).

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME BackupPath exists? NO New-Item -ItemType Directory -Force YES DB exists on primary? NO throw: DB not found YES catch (outer): log error return Status='FAILED' Step 1, Full Backup BACKUP DATABASE TO DISK WITH COMPRESSION, INIT Step 2, Log Backup BACKUP LOG TO DISK WITH COMPRESSION, INIT Step 3, Restore on Secondary RESTORE DATABASE WITH NORECOVERY, REPLACE RESTORE LOG WITH NORECOVERY Step 4, Join to Secondary AG ALTER DATABASE SET HADR AVAILABILITY GROUP = [AG] Step 5, Monitor Synchronization Start-Sleep 5s → query dm_hadr_database_replica_states record synchronization_state_desc + synchronization_health_desc Return [PSCustomObject] Status='SUCCESS' DatabaseName, AvailabilityGroup, BackupFile, LogBackupFile, Steps[] DONE

Examples

Add a database to a Distributed AG via a shared backup path
Add-sqmDatabaseToDistributedAg `
    -SqlInstance "SQL01" `
    -AvailabilityGroupName "MyDAG" `
    -DatabaseName "MyDb" `
    -SecondaryInstance "DR-SQL01" `
    -BackupPath "\\FileServer\SQLBackups"
Capture step results and check overall status
$result = Add-sqmDatabaseToDistributedAg -SqlInstance "SQL01" -AvailabilityGroupName "MyDAG" `
          -DatabaseName "MyDb" -SecondaryInstance "DR-SQL01"

if ($result.Status -ne 'SUCCESS') {
    Write-Warning "Failed: $($result.Error)"
    $result.Steps | Format-Table
}
← Back
Command Index
Related
Add-sqmDatabaseToAG