SQLServer Automatic Database Provisioning

Automated SQL Server database provisioning into Always On Availability Groups using Automatic Seeding.

The SQL Server Always On Automatic Database Provisioning framework is a PowerShell utility that discovers eligible user databases on local SQL Server instances and provisions them into a selected Availability Group. It combines SQL Server Agent event-driven execution with a recurring schedule to reduce manual steps when new databases are created.

The PowerShell script detects SQL Server instances installed on the local Windows host, selects Availability Groups for which those instances are currently PRIMARY, validates the target replicas, and processes eligible databases. It can initialize the database backup chain, configure automatic seeding, add a database to the Availability Group, and monitor secondary-replica health. Optional Transparent Data Encryption (TDE) uses an existing server asymmetric key.

A separate discovery-only mode lists pending databases without applying changes. All provisioning operations are restricted to a verified local PRIMARY replica; a host with no local PRIMARY target has no provisioning work to perform.

 

The automation covers

  • Automatic discovery of local SQL Server instances and Availability Groups where the local replica is PRIMARY.
  • Optional selection of a specific target Availability Group and database exclusion list.
  • New-database detection through a SQL Server Agent WMI CREATE_DATABASE alert, when the required msdb Service Broker functionality is enabled.
  • A configurable recurring SQL Server Agent schedule as a fallback when an event is missed or a database is not yet ready.
  • Discovery-only mode for listing eligible and pending databases without configuration changes.
  • Validation of database state, read-only status, existing Availability Group membership, and current PRIMARY role.
  • Automatic seeding configuration and CREATE ANY DATABASE permission on secondary replicas.
  • Optional Transparent Data Encryption using a pre-existing asymmetric key, with key-presence validation on each replica.
  • FULL recovery-model configuration and initialization of full and transaction log backups in a configured backup location.
  • Adding eligible databases to the target Availability Group and waiting for automatic seeding and acceptable synchronization health.
  • Detection of failed automatic seeding with an optional single restart attempt per database processing cycle.
  • Execution logging, failed-database counts, and safeguards against concurrent executions on the same Windows host.

Key Capabilities

  • Local PRIMARY discovery – Identifies local SQL Server instances and acts only on Availability Groups whose local replica is PRIMARY. When more than one matching Availability Group exists, an explicit target must be specified.
  • Event-driven and scheduled execution – A SQL Server Agent WMI alert starts the provisioning job following a CREATE_DATABASE event when supported; a configurable recurring schedule invokes the same job as a safety net.
  • Controlled database selection – Excludes system databases, database snapshots, distribution databases, and user-configured exclusions. Skips databases that are offline, read-only, or already belong to another Availability Group.
  • Backup-chain initialization – Switches a candidate database to FULL recovery if needed and creates a full backup and transaction log backup, with configurable compression and checksums.
  • Optional asymmetric-key TDE – Verifies the configured asymmetric key on all replicas, enables TDE on newly provisioned databases when requested, and checks encryption state. Key creation and distribution are prerequisites, not operations performed by this utility.
  • Automatic seeding and HA health – Configures seeding on secondary replicas, provisions the database, and polls replica connectivity, synchronization state, suspension status, and synchronization health within a configurable timeout.
  • Existing database handling – Leaves healthy databases already in the target Availability Group unchanged and attempts seeding recovery for existing target-group databases whose replica health is not acceptable.
  • Concurrency and execution traceability – Uses a host-level mutex to prevent overlapping executions on the same Windows host, records per-database results in a log file, and propagates failures to SQL Server Agent.

Project Files

  • Invoke-AOAGDatabaseProvisioning.ps1 – PowerShell provisioning engine (version 2.0). Performs discovery, validation, database preparation, optional encryption, automatic seeding, health checks, and logging.
  • Invoke-AOAGDatabaseProvisioning.json – External configuration for the target Availability Group, backup destination, compression, checksums, TDE, exclusions, timeouts, recovery behavior, and minimum dbatools version.
  • Create-Invoke-AOAGDatabaseProvisioning-Job.sql – SQL Server Agent installer. Recreates the provisioning job and relevant alert objects, adds a recurring schedule, and configures the WMI CREATE_DATABASE alert when msdb Service Broker is enabled.
  • Invoke-AOAGDatabaseProvisioning.cmd – Optional command-file launcher for manual execution of the PowerShell script; the SQL Server Agent job invokes the PS1 directly.

Main PowerShell Functions

Discovery and target validation

  • Get-LocalSqlInstances – Discovers locally installed SQL Server Database Engine instances and their connection targets.
  • Get-LocalPrimaryAvailabilityGroups – Identifies Availability Groups for which a specified local SQL instance is PRIMARY.
  • Resolve-LocalProvisioningTargets – Builds a set of local PRIMARY provisioning targets, respecting an optional target Availability Group.
  • Assert-DbatoolsCompatibility – Loads dbatools and checks the configured minimum module version.
  • Assert-TargetAgIsPrimary – Verifies that the active SQL connection remains on the PRIMARY replica before critical operations.
  • Get-AgReplicas – Retrieves the target Availability Group replica topology and connectivity information.
  • Get-EligibleDatabases – Lists eligible user databases, omitting configured exclusions.
  • Assert-DatabaseReady – Checks that a database still exists, is ONLINE, and is not read-only.

Provisioning and encryption

  • Configure-AutomaticSeeding – Sets secondary replicas to AUTOMATIC seeding and grants CREATE ANY DATABASE on the secondaries.
  • Assert-TdeKeyOnAllReplicas – When encryption is enabled, validates the configured asymmetric key on all target replicas.
  • Enable-DatabaseTde – Creates a database encryption key if necessary, enables TDE, and waits for encryption to complete.
  • Initialize-DatabaseBackupChain – Ensures FULL recovery and creates full and transaction log bootstrap backups.
  • Get-DatabaseAgMembership – Checks existing database membership before provisioning.
  • Add-DatabaseToAg – Adds an eligible database to the selected Availability Group.
  • Process-Database – Coordinates validation, optional TDE, backups, addition to the Availability Group, seeding, and final health checks.

Monitoring and recovery

  • Get-DatabaseHaHealth – Evaluates secondary replica connectivity, suspension, synchronization state, and health.
  • Get-LatestSeedingFailures – Retrieves the latest failed automatic-seeding records for a database.
  • Restart-AutomaticSeeding – Reapplies AUTOMATIC seeding mode on the configured secondary replicas.
  • Wait-AutomaticSeeding – Polls health until the database meets the accepted state or a timeout occurs, with optional one-time recovery.
  • Get-SecondaryTdeSummary – Checks that TDE-enabled databases are encrypted on secondary replicas.
  • Write-RunLog – Writes timestamped execution progress and outcomes to the local provisioning log.

Configuration Options

The JSON configuration file allows administrators to customize the provisioning workflow without editing its main PowerShell logic:

  • TargetAvailabilityGroup – Optional target AG name. When omitted, discovery requires an unambiguous local PRIMARY target per instance.
  • BootstrapBackupPath – Destination for full and transaction log bootstrap backups; the executing SQL Server service must have access.
  • DatabaseExclude – Databases that must not be provisioned automatically.
  • BackupCompression / BackupChecksum – Enable or disable compression and checksum options on bootstrap backups.
  • EncryptionEnabled / TdeAsymmetricKey / TdeAlgorithm – Optional asymmetric-key TDE settings; encryption is disabled in the supplied default configuration.
  • EncryptionTimeoutMinutes / SeedingTimeoutMinutes / PollSeconds – Timeouts and polling frequency for encryption and seeding checks.
  • RestartFailedAutomaticSeeding – Controls whether failed automatic seeding triggers a recovery attempt.
  • MinimumDbaToolsVersion / MetadataQueryTimeoutSeconds – Minimum dbatools version and SQL metadata query timeout.

System Requirements

  • An existing, operational SQL Server Always On Availability Group with at least one secondary replica and SQL Server Agent running on participating SQL Server instances.
  • Windows PowerShell and the dbatools module installed on the execution host; the supplied JSON requires dbatools version 1.1.145 or later.
  • SQL Server permissions sufficient to inspect Availability Group metadata, configure seeding and replica permissions, run backups, optionally enable TDE, and add databases to the Availability Group.
  • Connectivity from the execution host to the PRIMARY and all applicable secondary SQL Server replicas.
  • A writable backup destination accessible to the SQL Server service account; sufficient backup storage capacity and retention/cleanup arrangements.
  • For optional TDE: the configured asymmetric key must already exist and be usable on every participating replica.
  • For event-driven triggering: SQL Server Agent WMI event support and enabled msdb Service Broker. The recurring schedule still applies if the WMI alert is not installed.
  • The SQL Agent job owner must exist on each instance, and its execution context must have the Windows and SQL Server permissions required by the PowerShell workflow.

Installation and Execution

  • Copy the PowerShell script, JSON configuration, and optional CMD launcher to the intended folder on each participating server. Review the JSON values, especially the target Availability Group, backup location, exclusions, TDE settings, and timeouts.
  • Review the SQL installer script before execution. Set its PowerShell script path, SQL Agent job owner, and schedule interval to values appropriate for each instance.
  • Run the SQL installer on each SQL Server replica. It recreates the associated provisioning job and alert objects; do not execute it while an existing provisioning job is running.
  • Run the PowerShell script with -Discover to verify detected local PRIMARY targets and pending databases without applying changes.
  • Use the supplied CMD file for optional manual execution, or let the SQL Server Agent WMI alert and recurring schedule invoke the PowerShell script automatically.
  • Review the local Invoke-AOAGDatabaseProvisioning.log and SQL Server Agent job history for per-database progress, warnings, timeouts, and provisioning failures.

Scheduling: The supplied SQL installer defines a 60-minute recurring schedule by default (@ScheduleMinutes = 60). The interval can be changed in the SQL script before installation. The installer creates the WMI alert only when msdb Service Broker is enabled.

Operational considerations: Provisioning is not a read-only operation: it can modify the database recovery model, create full and log backups, enable TDE if configured, change secondary seeding permissions, and add databases to an Availability Group. Review the configuration and execute within your organization's change-management procedures. The utility does not create Availability Groups, deploy asymmetric keys, or automatically move databases from a different Availability Group.

A few more words about us

 

DESIGN AND DEVELOPMENT OF WEBSITES

From simple personal websites to full corporate platforms, blogs, catalog sites, and complete e‑commerce solutions with shopping cart functionality *, we design and develop web experiences tailored to each client. We also deliver large‑scale and high‑complexity projects, including fully customized web applications.

\* An e‑commerce shopping cart allows customers to select products, review their order, and complete checkout and payment.

WEB DEVELOPMENT METHOD AND PROCESSES

At PARP Solutions, we take responsibility for every stage of the development process. We plan each phase in close collaboration with the client, ensuring that the final product meets the objectives defined at the start of the project.

With solutions adapted to a wide range of industries and customer profiles, we focus on understanding each client’s needs and delivering results with the quality expected by both sides.

We remain fully committed to supporting clients with updates, improvements, and ongoing implementations in their operational environment.