· Uwe Janke
Commands / Add-sqmDatabaseToAG

Add-sqmDatabaseToAG

Always On sqmSQLTool v1.8.2+ · Add ✓ -WhatIf supported
Adds one or more databases to an Always On Availability Group using Automatic Seeding. For each database the function: verifies it is not already part of an AG, switches the recovery model to Full if needed and takes a FULL backup right afterwards, drops any existing copy on secondary replicas, and finally calls Add-DbaAgDatabase -SeedingMode Automatic. When -All is used, databases are added sequentially to avoid simultaneous seeding load.

Target AG (v1.9.151+): -AvailabilityGroup is optional. Without it, the function looks at the AGs in which -SqlInstance is the primary replica. If there is exactly one, it is used. If there are several, the function lists them and asks which one to use. In a non-interactive session (Agent job, -NonInteractive) it does not ask but stops with the list of AG names, so pass -AvailabilityGroup there.

Full recovery and log chain (v1.9.151+): after SET RECOVERY FULL the database keeps behaving like SIMPLE until the first full backup, and Add-DbaAgDatabase rejects it. The function therefore takes a FULL backup directly after the switch, and also for a database that is already Full but has not been backed up since (last_log_backup_lsn is NULL). The backup goes to the instance's default backup directory or to -BackupPath. It runs before anything is dropped on the secondaries: if it fails, the database is reported as BackupFailed and nothing else is touched.

Prerequisite: Automatic Seeding must already be configured on all replicas. Use Invoke-sqmSqlAlwaysOnAutoseeding if not yet set up.

TDE-encrypted databases (v1.9.133+): seeding an encrypted database has two conditions, and the function checks both before it changes anything, because the normal flow drops the database on the secondaries first. Automatic Seeding of an encrypted database requires SQL Server 2019 or newer on every replica, and the certificate protecting the database encryption key must already exist on each secondary. A replica below SQL 2019 means the database is skipped with status TdeUnsupportedVersion; there the only route is backup/restore. If just the certificate is missing, -SyncTdeCertificate distributes it: export from the primary, then CREATE CERTIFICATE ... FROM FILE on every secondary that lacks it. Databases without TDE are unaffected, and for them no certificate query runs at all.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMEPrimary SQL Server instance.
-SqlCredentialPSCredentialOptional, SQL or Windows credential.
-AvailabilityGroupstringOptionalautoName of the target availability group. Omitted: the AG in which the instance is primary; with several such AGs the function asks (non-interactive: stops with the list of names).
-Databasestring[]Optional, One or more database names. ParameterSet: Specific. Ignored when -All is set.
-AllswitchSwitch$falseAdd all accessible user databases not yet in any AG. ParameterSet: All.
-BackupPathstringOptionalinstance defaultTarget directory for the FULL backup taken after switching to Full recovery (or when the log chain is missing).
-SyncTdeCertificateswitchSwitch$falseTDE only: export the encryptor certificate from the primary and create it on every secondary that does not have it yet. Matched by thumbprint, not by name. Without this switch such a database is skipped with status TdeCertificateMissing. Requires -TdeCertificateBackupPath and -TdeCertificatePassword.
-TdeCertificateBackupPathstringOptional, Directory for the temporary certificate export (.cer + .pvk). BACKUP CERTIFICATE writes it under the primary's SQL service account and CREATE CERTIFICATE reads it under the secondary's, so this must be a share both accounts can reach. Mandatory together with -SyncTdeCertificate.
-TdeCertificatePasswordSecureStringOptional, Protects the exported private key (.pvk) and decrypts it again on the secondaries. Mandatory together with -SyncTdeCertificate.
-TdeMasterKeyPasswordSecureStringOptional-TdeCertificatePasswordUsed to create the database master key in master on a secondary that has none yet. CREATE CERTIFICATE ... WITH PRIVATE KEY needs it there.
-KeepTdeCertificateBackupswitchSwitch$falseKeep the exported .cer/.pvk files. By default they are deleted after distribution, because the .pvk carries the private key of the TDE certificate.
-EnableExceptionswitchSwitch$falseThrow on error instead of logging and continuing.
-WhatIf / -ConfirmswitchOptional, Standard PowerShell ShouldProcess support. All four state-changing operations (RecoveryModel, Backup, Drop, Add) are individually gated.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME Resolve & validate AG, collect replicas No -AvailabilityGroup: AGs with this instance as primary → one: use it · several: ask Determine database list -All: Get-DbaDatabase (user, accessible) → exclude those already in any AG (Get-DbaAgDatabase) -Database: load by name · missing names → Warning · neither -All nor -Database → throw For each database, sequential (avoids simultaneous seeding load when using -All) Already in AG? YES continue → next DB Status: AlreadyInAG NO TDE encrypted? NO YES Every replica SQL Server 2019 or newer? Encrypted seeding needs major version 15+ · older replica → TdeUnsupportedVersion Encryptor certificate on every secondary? Matched by thumbprint, not by name · missing → Status: TdeCertificateMissing -SyncTdeCertificate → distribute the certificate Primary: BACKUP CERTIFICATE ... WITH PRIVATE KEY → path both service accounts can reach Each secondary without it: CREATE MASTER KEY (if missing) + CREATE CERTIFICATE ... FROM FILE RecoveryModel ≠ Full → set Full, then FULL backup Also backup if Full without log chain · failure → SetRecoveryFailed / BackupFailed, next DB For each secondary: DB exists? → Remove-DbaDatabase ShouldProcess · failure → Status: DropOnSecondaryFailed · Add step continues regardless Add-DbaAgDatabase -SeedingMode Automatic ShouldProcess · Success → Status: Success · failure → Status: AddFailed Return PSCustomObject[], one entry per database Status: Success · AlreadyInAG · NotFound · SetRecoveryFailed · BackupFailed · DropOnSecondaryFailed · AddFailed TDE: TdeUnsupportedVersion · TdeCertificateMissing · TdeCertificateNameConflict · TdeEncryptorNotCertificate DONE

Result status

One object per database, each carrying SqlInstance, DatabaseName, Status and Message. Every status other than Success means that database was left untouched.

StatusMeaning
SuccessAdded to the AG with Automatic Seeding.
AlreadyInAGDatabase is already a member of an availability group.
NotFoundA name given via -Database does not exist on the instance.
SetRecoveryFailedSwitching the recovery model to Full failed.
BackupFailedThe FULL backup after switching to Full recovery failed. Without it the log chain is missing, so nothing was dropped on the secondaries and the database was not added.
DropOnSecondaryFailedRemoving the existing copy on a secondary failed. The Add step still runs.
AddFailedAdd-DbaAgDatabase returned an error.
TdeUnsupportedVersionTDE database, but at least one replica is older than SQL Server 2019. The message names the replica. Use backup/restore instead of seeding.
TdeCertificateMissingTDE database whose certificate is absent on one or more secondaries, and -SyncTdeCertificate was not given. The message names the nodes.
TdeCertificateNameConflictA secondary already holds a certificate of the same name but with a different thumbprint. It belongs to another key and is never overwritten.
TdeEncryptorNotCertificateThe database encryption key hangs off an asymmetric key (EKM, Key Vault) rather than a server certificate. That key cannot be distributed as a file.
TdeCertificateSyncFailedExport or import of the certificate failed, usually a path the SQL service account of that node cannot reach.
TdeCertificateCheckFailed / TdeVersionCheckFailedA secondary could not be queried for its certificates or its version.
RecoverySkipped / BackupSkipped / DropSkipped / AddSkipped / TdeSyncSkipped-WhatIf variants: the step would have run.

Examples

Add a single database to an AG
Add-sqmDatabaseToAG -AvailabilityGroup "AG1" -Database "SalesDB"
Let the function pick the AG (asks if the instance is primary of several AGs)
Add-sqmDatabaseToAG -Database "SalesDB"
Add all user databases not yet in any AG
Add-sqmDatabaseToAG -SqlInstance "SQL01" -AvailabilityGroup "AG1" -All
Preview without making changes (-WhatIf)
Add-sqmDatabaseToAG -SqlInstance "SQL01" -AvailabilityGroup "AG1" -All -WhatIf
TDE-encrypted database, distributing the certificate to every secondary
$certPw = Read-Host -AsSecureString "Private key password"
Add-sqmDatabaseToAG -SqlInstance "SQL01" -AvailabilityGroup "AG_PROD" -Database "PayrollDB" `
    -SyncTdeCertificate `
    -TdeCertificateBackupPath "\\fileserver\sqlcerts$" `
    -TdeCertificatePassword $certPw
Check the TDE preconditions of every database without changing anything
Add-sqmDatabaseToAG -SqlInstance "SQL01" -AvailabilityGroup "AG_PROD" -All -WhatIf |
    Where-Object Status -like "Tde*" |
    Format-Table DatabaseName, Status, Message -Wrap
Add multiple named databases with SQL credentials
$cred = Get-Credential
Add-sqmDatabaseToAG -SqlInstance "SQL01" -SqlCredential $cred `
    -AvailabilityGroup "AG_PROD" -Database "DB1","DB2","DB3"
⬆ Index
All Commands