SQL Server Availability Group Automatic Seeding

In SQL Server 2016 Microsoft introduced Automatic Seeding for Availability Groups (AG). The Automatic Seeding process streams the database files directly to the secondary server(s) using the database mirroring endpoints, removing the need to restore the databases, before joining them to an AG.

Considerations

• To use the Automatic Seeding option, the SQL Servers in the Availability Group must be version 2016 (or newer) with SQL Server Management Studio 17.4 or later (if using the Availability Group Wizard in SSMS).
• A full backup is required, to establish replication, for each database before it is joined to an AG.
• While the automatic seeding process is running, the transaction log cannot be truncated. If the primary database replica is in use this can result in significant transaction log growth.
• The automatic seeding process is single-threaded, which can affect performance if the AG has more than one database.
• Only 5 databases (at a time) can be joined to an AG using the automatic seeding process.
• Database size and network bandwidth impact seeding performance. Large databases, and networks with slower connections between servers, may experience better performance using the backup-and-restore process (instead of Automatic Seeding).
• SQL Server 2016
  • Database files are created with the same path on the secondary replica as the path on the primary replica. All directories must already exist.
• SQL Server 2017 (and newer)
  • Database files on the secondary replica can be created with different file paths from the primary replica. This allows for cross-platform availability groups (with replicas on Windows and Linux servers).

Dynamic Management Views

The Automatic Seeding process runs in the background, so Microsoft has added 2 Dynamic Management Views (DMV) that can provide progress/status information.

• sys.dm_hadr_automatic_seeding: Provides a history of each seeding attempt, including:
  • start & completion times
  • current state (completed or failed)
  • number of attempts
  • failure state description (reason if seeding attempt failed)
• sys.dm_hadr_physical_seeding_stats: Displays the status of a seeding attempt while it is running for both primary and secondary replicas, including:
  • start & end time (UTC)
  • database size
  • transferred size
  • transfer rate
  • estimated completion time (UTC)
  • disk & network wait time
  • failure message
  • compression enabled

Compression

By default, SQL Server does not compress the Automatic Seeding data stream. If compression is enabled, network traffic will be reduced, at a cost of increased CPU load. Global trace flag 9567 can be used to enable compression for all Availability Groups on the SQL Server instance.