Auto Seeding vs. Manual Seeding for AG Secondary Replicas in SQL Server

Introduction

When you add a database to an Always On availability group (AG), the first goal is to place a transactionally consistent copy on each secondary replica so data movement can begin. SQL Server 2022 supports two methods for this: automatic seeding and manual seeding. This post explains what technically happens in each case, why automatic seeding is convenient, and why it may not be ideal for very large databases (VLDBs).

How Automatic Seeding Works

With automatic seeding, SQL Server performs a streaming backup and restore over the AG’s database mirroring endpoints. The primary replica takes a virtual device interface (VDI) backup of the database and streams it directly to the secondary, where it is restored automatically. This eliminates the need to create and manage backup files.

To allow the AG to create the database on the secondary, you must grant it permission:

ALTER AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE;

You then enable automatic seeding by setting the seeding mode:

ALTER AVAILABILITY GROUP [AGName]
  MODIFY REPLICA ON 'SecondaryServer'
  WITH (SEEDING_MODE = AUTOMATIC);

During automatic seeding, the following occurs:

• The database backup is streamed through the AG endpoint.
• The transaction log on the primary cannot be truncated until seeding finishes.
• The process runs single-threaded, limiting throughput when seeding multiple databases.
• Compression is optional and enabled via trace flag 9567, reducing bandwidth use but increasing CPU load.
• File paths are taken from the replica’s defaults; they do not need to match across replicas, but they must exist.

Progress can be monitored using the DMVs sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats.

How Manual Seeding Works

Manual seeding follows the traditional backup and restore workflow:

1. Take a full and log backup of the primary database.
2. Copy the backups to each secondary.
3. Restore them using WITH NORECOVERY, optionally specifying WITH MOVE if file paths differ.
4. Join the database to the AG using SSMS or T-SQL to begin synchronization.

Manual seeding can also be performed using the Add Database wizard:

• Full: SQL Server performs the backup, copy, and restore automatically via a shared network path.
• Join only: You prepare the database manually and simply attach it to the AG.

Manual seeding allows:

• Use of backup compression, which typically reduces backup size and I/O at the cost of CPU.
• Precise control over file layout and placement.
• Scheduling of backups and restores during maintenance windows to minimize impact.

Why Automatic Seeding Is Convenient

Automatic seeding removes the operational complexity of managing backups and restores. It requires minimal setup, works for both new and existing AGs, and can be combined with manual seeding if needed. It’s ideal for small to medium databases on high-speed, reliable networks where the simplicity outweighs the performance trade-offs.

Why It’s Not Ideal for Very Large Databases

Automatic seeding introduces performance and management limitations that become significant for VLDBs:

• The streaming process is limited by network throughput. A multi-terabyte database can take many hours over a 1 Gbps link.
• Transaction logs on the primary cannot truncate during seeding, leading to potential log growth.
• The single-threaded implementation restricts concurrency when seeding multiple databases.
• Compression requires trace flag 9567, which increases CPU usage on the primary.
• There is less control over file placement, which is important in large environments with multiple data files or custom storage layouts.

Manual seeding avoids these problems by allowing you to use compressed backups, control timing, and carefully manage file placement.

Choosing the Right Method

Automatic seeding works best for smaller databases and straightforward configurations where convenience is the main goal. Manual seeding is the better choice for VLDBs, for environments with complex storage layouts, or when you need to control CPU, bandwidth, and timing. Some organizations use a hybrid approach—pre-restoring a recent full backup manually, then letting automatic seeding catch up with the delta.

Conclusion

Both methods are supported in SQL Server 2022. Automatic seeding provides an elegant, low-maintenance option for routine deployments, while manual seeding remains essential for large or performance-sensitive databases. The choice depends on your operational needs, database size, and infrastructure.

Documentation References

• Use automatic seeding to initialize a secondary replica
• Manually prepare a secondary database for an availability group
• Add a database to an availability group (Wizard)
• Tune compression for availability groups (trace flag 9567)
• Backup compression (SQL Server)