In the context of SQL Server Always On Availability Groups (AGs), automatic seeding refers to the process where the primary replica automatically transfers a database to secondary replicas without requiring manual backup and restore. While there aren’t “types” of automatic seeding per se, there are different methods for adding databases to AGs, and automatic seeding is one of them. Here’s a breakdown:

Methods of Adding a Database to an AG

Method	Description	Pros	Cons
Automatic Seeding	SQL Server uses database mirroring endpoints to seed the database directly	Fast, no manual backup/restore, supports compression with TF 9567	Requires proper endpoint configuration and permissions
Manual Seeding	DBA manually backs up and restores the database on each secondary replica	Full control over backup/restore process	Time-consuming, error-prone
Join Only	Used when the database is already restored with NORECOVERY on secondary	Useful for pre-staged databases	Requires manual restore beforehand

Automatic Seeding Details

• Introduced in SQL Server 2016
• Uses database mirroring endpoints for data transfer
• Requires:
  • Database in FULL recovery model
  • Endpoint connectivity between replicas
  • Permission on each secondary to create the seeded database: ALTER AVAILABILITY GROUP [YourAGName] GRANT CREATE ANY DATABASE;
• Can be monitored via DMVs:
  • sys.dm_hadr_automatic_seeding
  • sys.dm_hadr_physical_seeding_stats

Enhancements with Trace Flags

• TF 9567: Enables compression during automatic seeding
• TF 9571: Disables automatic seeding to the default database path (use when the secondary’s data and log paths differ from the primary’s)

Adding a database to an Always On Availability Group (AG) in SQL Server is a structured process that ensures high availability and disaster recovery. Here’s a professional step-by-step guide:

Prerequisites

Before adding a database to an AG, ensure:

• The database is in FULL recovery model
• A full backup and transaction log backup have been taken
• The database is not already part of another AG
• The database is read-write, multi-user, and Auto-Close is disabled
• The AG is healthy and all replicas are synchronised

Method 1: Using SQL Server Management Studio (SSMS)

• Connect to the Primary Replica in SSMS.
• Expand Always On High Availability → Availability Groups.
• Right-click the AG name → Select Add Database.
• In the wizard:
  • Select the database(s) to add.
  • Connect to secondary replicas.
  • Choose a synchronisation method:
    • Automatic Seeding (if supported)
    • Full & Log Backup (manual restore required)
    • Join Only (if secondary already restored with NORECOVERY)
• Validate configuration and click Finish.

Full walkthrough: Microsoft Learn: Add a database to an availability group

Method 2: Using Transact-SQL

On the primary replica, run:

ALTER AVAILABILITY GROUP [YourAGName]
ADD DATABASE [YourDatabaseName];

Then, on each secondary replica, restore the database using:

-- Restore full backup
RESTORE DATABASE [YourDatabaseName]
FROM DISK = 'path_to_full_backup.bak'
WITH NORECOVERY;

-- Restore log backup
RESTORE LOG [YourDatabaseName]
FROM DISK = 'path_to_log_backup.trn'
WITH NORECOVERY;

Finally, join the database to the AG:

ALTER DATABASE [YourDatabaseName]
SET HADR AVAILABILITY GROUP = [YourAGName];

From my SQL Server DBA handbook.